String Conversion problem in executing query

LalChand

string strConnection = @"server=.\SQLEXPRESS;Database=Northwind ; Integrated Security=SSPI";
string strCommand ="Select * from Orders where OrderDate='"+7/4/1996+"'";
sqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Orders");
dataGridView1.DataSource = dataSet.Tables["Orders"].DefaultView;
I want to populate the grid depending upon the date of Orders but my query is not interpreted as select * from Orders where orderDate='7/4/2001';

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kumar AG17y ago
My dear friend the date format in SLQ Server is "yyyy-MM-dd HH:mm:ss.MMM"

And SQL server will accept the date in any valid date format. Default will be local settings whether it is "MM-dd-yyyy" or "dd-MM-yyyy" etc.

Here in the above question he has problem passing values. So the best way to pass the values is by using parameters.

Hope this helps.
Roei Bar17y ago
well my friend, the issue is that SQL doesnt like dates in any manner, go to SQL
type the following

select getdate()

you will see the date format by month day year hours min, seconds and so on.

just pass in the date as it requests and it will work perfectly fine
Kumar AG17y ago
Use command parameters
Here is sample code
using (SqlConnection sqlconn = new SqlConnection("your Connection string"))
            {
                sqlconn.Open();
                using (SqlCommand cm= sqlconn.CreateCommand())
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "SELECT * FROM orders where orderdate >= @orderdate";
                    cm.Parameters.AddWithValue("@orderdate", "07-04-1996");
SqlDataAdapter dataAdapter = new SqlDataAdapter(cm);
                    dataAdapter.Fill(ds, "orders");

                }
            }