CODEWITHSUNDEEP
c#sql-serverwinforms
Tan
Tan

Reputation: 788

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

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());

                        }

Upvotes: 0

Views: 59

Answers (2)

Vincent
Vincent

Reputation: 882

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

though i have no way of testing

Upvotes: 0

Steve
Steve

Reputation: 216293

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

Upvotes: 2

Related Questions