No errors or results when executing simple query on SQL Server database

Question

This is my first approach to SQL Server. I have exported my Access DB to SQL Server and want to use it in my application. I have added the new SQL DB to my C# project and replaced OleDB with Sql. I am now unable to execute queries which where perfectly working with local DB in Access.

Query:

string query = @"SELECT SessionID, SemesterA, SemesterB, RoomID, SessionDate, SessionTimeStart, SessionTimeEnd" +
               " FROM [Session] " +
               " WHERE RoomID = @RoomID " +
               " AND SessionDate = getdate() ";

I have replaced Date() with getdate() as instructed by the VS error, but the query does not produce any result (should return one record, Access DB does)

My RoomSelect form code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AutoReg
{
    public partial class RoomSelect : Form
    {

        DataTable queryResult = new DataTable();
        public string RoomID;
        RoomActiveSession RoomActiveSessionForm = new RoomActiveSession();

        public RoomSelect()
        {
            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)
        {

            switch (listBox1.SelectedItem.ToString())
            {
                case "MB0302":
                    RoomID = listBox1.SelectedItem.ToString();
                    roomQuery();
                    break;

                case "MC1001":
                    RoomID = listBox1.SelectedItem.ToString();
                    roomQuery();
                    break;

                case "MC3203":
                    RoomID = listBox1.SelectedItem.ToString(); 
                    roomQuery();
                    break;

                case "MC3204":
                    RoomID = listBox1.SelectedItem.ToString();
                    roomQuery();
                    break;

            }
        }

        public void roomQuery()
        {
            string ConnStr = "Data Source=DUZY;Initial Catalog=AutoRegSQL;Integrated Security=True";

            SqlConnection MyConn = new SqlConnection(ConnStr);
            MyConn.Open();

            //SQL query that todays sessions for the given roomID
            string query = @"SELECT SessionID, SemesterA, SemesterB, RoomID, SessionDate, SessionTimeStart, SessionTimeEnd" +
               " FROM [Session] " +
               " WHERE RoomID = @RoomID " +
               " AND SessionDate = getdate() ";

            SqlCommand command = new SqlCommand(query, MyConn);

            command.Parameters.Add("RoomID", SqlDbType.Char).Value = RoomID;


            SqlDataAdapter adapter = new SqlDataAdapter(command);

            adapter.Fill(queryResult);

            if (queryResult.Rows.Count == 0)
            {
                MessageBox.Show("No active sessions today for the given room number");
                MyConn.Close();
            }
            else
            {

                RoomActiveSessionForm.SetDataSouce(queryResult);

                this.Hide();
                RoomActiveSessionForm.ShowDialog();

                MyConn.Close();
            }


        }

    }
}

When I run the program, I receive a message "No active sessions today for the given room number" which should be executed when there are no results to the query, but I know for a fact, that it should return one record)

if you execute your query against database do you get one record? — Ehsan
– Ehsan, Commented Aug 7, 2013 at 14:56
What is the expected value of SessionDate and what are you trying to pass in. GetDate() returns the time of the moment you ran your query, is that what you where wanting to do? — Scott Chamberlain
– Scott Chamberlain, Commented Aug 7, 2013 at 14:59

Gordon Linoff · Accepted Answer · 2013-08-07 14:47:12Z

6

The function getdate() actually returns a datetime. Try converting it to a date:

AND SessionDate = cast(getdate() as date)

The time component is probably the problem -- preventing a match between the date and the datetime.

answered Aug 7, 2013 at 14:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aaron Bertrand Over a year ago

+1 but this assumes the data stored doesn't have a time component - might need a convert on both sides or, better yet, a range query.

jaspernorth Over a year ago

AND SessionDate = cast(getdate() as date) - this line now prevents the message box from showing and the query continues by going to another form (this is the form where I display my results), but still no records shown

Scott Chamberlain Over a year ago

@jaspernorth in that case you need to put in a breakpoint, and step though with a debugger and find out what is not happening correctly as that is a different issue of you not displaying the results, mostlikely a issue with RoomActiveSessionForm.SetDataSouce

jaspernorth Over a year ago

It was my mistake to not refresh DGV in other form to display data. Your answer was very helpfull. Thanks @GordonLinoff

Gordon Linoff Over a year ago

@AaronBertrand . . . The Date() function in Access returns the date without a time. The OP says that replacing that with getdate() caused the code to stop returning results.

|

Collectives™ on Stack Overflow

No errors or results when executing simple query on SQL Server database

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related