SQL query showing results when executed on SQL Server but when called from C# code not loading results

Question

I have a SQL Server query; when I run it in SQL Server, it is returning the expected results, when I call the same query from C# code to fill a dataset, there are no results. What is the problem? My SQL query is as below along with results when run in SQL Server

SELECT 
    t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, 
    t0.AddedBy  , 
    (SELECT 
         AVG(CAST(brandID AS bigint)) FROM brands) AS brandID_AVERAGE
FROM 
    brands t0 
WHERE  
    t0.cdt >= '2013-11-15 00:00:00' 
    AND t0.cdt <='2013-11-15 23:59:59' 
GROUP BY
    t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus, t0.AddedBy

and my results for the above query when run in SQL Server are :

brandID brandName   cdt udt brandstatus AddedBy brandID_AVERAGE
10  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
11  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
12  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
13  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
14  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
15  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778
16  khasim  2013-11-15 19:14:18.123 2013-11-15 19:14:18.123 1   1   52778

But when I call the same thing from C# with command.Parameters.AddWithValue, my dataset is always null.

My c# code is

private void button1_Click(object sender, EventArgs e)
{
    checkedListBox1.Items.Clear();
    Cursor.Current = Cursors.WaitCursor;

    string QueryB=@"SELECT t0.brandID, t0.brandName, t0.cdt, t0.udt, 
        t0.brandstatus, t0.AddedBy, 
        ( SELECT  AVG( CAST (brandID AS bigint)) FROM brands ) 
          AS brandID_AVERAGE FROM brands t0 
             WHERE  t0.cdt >= @VALUE1 and  t0.cdt <=@VALUE2  
             group by t0.brandID,t0.brandName,t0.cdt,t0.udt,
                      t0.brandstatus,t0.AddedBy";

    if (textBox1.Text != string.Empty && textBox2.Text != string.Empty)
    {
        if (checkBox1.Checked == false)
        {
            try
            {
                if (QueryB != string.Empty)
                {
                    using (SqlConnection con = new SqlConnection(Properties.Settings.Default.connectionString))
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand(QueryB, con);

                        if (checkBox6.Checked)
                        {
                            var sample1 = DateTime.ParseExact(textBox1.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") + " " + dateTimePicker2.Value.ToString("HH:mm:ss tt");
                            var sample2 = DateTime.ParseExact(textBox2.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") + " " + dateTimePicker1.Value.ToString("HH:mm:ss tt");

                            cmd.Parameters.AddWithValue("@Value1", sample1.ToString());
                            cmd.Parameters.AddWithValue("@Value2", sample2.ToString());
                        }
                        else
                        {
                            if (label2.Text.Contains("datetime"))
                            {
                                cmd.Parameters.AddWithValue("@Value1", DateTime.ParseExact(textBox1.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("MM-dd-yyyy") + " " + "00:00:00.000");
                                cmd.Parameters.AddWithValue("@Value2", DateTime.ParseExact(textBox2.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString("MM-dd-yyyy") + " " + "23:59:59.999");
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("@Value1", textBox1.Text);
                                cmd.Parameters.AddWithValue("@Value2", textBox2.Text);
                            }
                        }

                        DataSet ds = new DataSet();

                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        cmd.ExecuteNonQuery();

                        da.Fill(ds);
                        con.Close();

                        dataGridView1.DataSource = ds.Tables[0];
                        mysource = ds.Tables[0];
                        dataGridView1.BindingContext = new BindingContext();
                        foreach (DataGridViewColumn col in dataGridView1.Columns)
                        {
                            checkedListBox1.Items.Add(col.HeaderText.ToString());

                        }

You probably need a hard coded single quote on the front and end of each date. like t0.cdt >= '@VALUE1' — Chuck
– Chuck, Commented Mar 21, 2016 at 23:15
Do not use AddWithValue, in particular when you want to pass a DateTime value — Steve
– Steve, Commented Mar 21, 2016 at 23:15

Steve · Accepted Answer · 2016-03-21 23:26:27Z

2

I am not sure about your query, but there is a very probable culprit in your code and it is the AddWithValue call together with a datetime parameter.

Your code transform the datetime value in a string and pass it to AddWithValue. This conversion happens using the rules of your locale settings.
AddWithValue has no clue about the fact that the underlying datatable column is a DateTime and thus creates a parameter of String type. At this point the parameters are passed to the database engine that need to re-convert the string value back to a DateTime. This conversion happens using the rules established for your database and probably an value is what cause your query to fail.

Just use the Add method specifying the exact datatype and let the database engine extract the value correctly from the parameter

cmd.Parameters.Add("@Value1", SqlDbType.DateTime).Value = sample1.Date; //2013/11/15 00:00:00
cmd.Parameters.Add("@Value2", SqlDbType.DateTime).Value = sample2.AddSeconds(86339); // 2013/11/15 23:59:59

edited Mar 21, 2016 at 23:26

answered Mar 21, 2016 at 23:19

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Tan Over a year ago

i changed it to the way you suggested but still the same

Steve Over a year ago

Probably because the two datetime represent the same point in time and you need a whole day. Changed a bit the answer above

Tan Over a year ago

i understood the problem i am using date and time as 15/11/2013 00:00:00 AM which is ok 15/11/2013 24:00:00 which is not there sql server has 15/11/2013 23:59:59.999 this is changing to next day but my query is not converting to date or time format causing the problem.

Vincent · Accepted Answer · 2016-03-21 23:31:56Z

0

I think you need to replace cmd.ExecuteNonQuery(); with cmd.ExecuteReader()

though i have no way of testing

answered Mar 21, 2016 at 23:31

Vincent

8827 silver badges13 bronze badges

1 Comment

Vincent Over a year ago

You could try pulling out the data adapter and just try executing the reader, maybe narrow things down. So sorry to not be of more help

Collectives™ on Stack Overflow

SQL query showing results when executed on SQL Server but when called from C# code not loading results

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related