CODEWITHSUNDEEP
c#sql-server-2008datetimedatepicker
las
las

Reputation: 67

how to insert a datepicker datetime value into sql database

My SQL Server 2008 database has a table with a column of datatype datetime.

When I try to insert values into the datetime column I am getting error.

Incorrect syntax near '-'

My datetime picker has custom format yyyy-MM-dd e.g (2012-11-01)

Following is the code sample I used to insert datetime.

 System.DateTime myDate = default(System.DateTime);
 myDate = DateTimePickerPrint.Value;
 string query = string.Format("EXEC Save_Quotation_Bookshop '" + txt_QutationNo.Text + "','" + txt_CusCode.Text + "',#" + myDate + "#,");

Please any one have an idea ?

Upvotes: 1

Views: 19928

Answers (3)

marc_s
marc_s

Reputation: 754458

First off: STOP concatenating together your SQL code! This is an invitation for SQL injection attacks, and it's really bad for performance, too - use parametrized queries instead.

If you do - you won't have the problem of datetime/string conversion issues, either.....

Secondly: the "safe" format for a date-only DateTime in SQL Server is YYYYMMDD - without any dashes - only this format guarantees that it'll run on any SQL Server, regardless of your language, regional and dateformat settings.

Thirdly. if you want to execute a stored procedure - I would recommend using this approach:

System.DateTime myDate = default(System.DateTime);
myDate = DateTimePickerPrint.Value;

using (SqlConnection con = new SqlConnection(your-connection-string-here))
using (SqlCommand cmd = new SqlCommand("dbo.Save_Quotation_Bookshop", con))
{
    // tell ADO.NET it's a stored procedure (not inline SQL statements)
    cmd.CommandType = CommandType.StoredProcedure;

    // define parameters
    cmd.Parameters.Add("@QuotationNo", SqlDbType.VarChar, 50).Value = txt_QutationNo.Text;
    cmd.Parameters.Add("@CustomerCode", SqlDbtype.VarChar, 25).Value = txt_CusCode.Text;
    cmd.Parameters.Add("@SaleDate", SqlDbType.DataTime).Value = myDate;

    // open connection, execute stored procedure, close connection again
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

Don't use EXEC ...... as an inline SQL statement - tell ADO.NET that you're executing a stored procedure, supply the parameters - and you're done!

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

Try this

string query = String.Format("EXEC Save_Quotation_Bookshop '{0}','{1}','{2}'",txt_QutationNo.Text,txt_CusCode.Text, myDate);

OR

string query = string.Format("EXEC Save_Quotation_Bookshop @QutationNo,@CusCode,@myDate");

...
comm.Parameters.AddWithValue("@QutationNo", txt_QutationNo.Text);
comm.Parameters.AddWithValue("@CusCode", txt_CusCode.Text);
comm.Parameters.AddWithValue("@myDate", myDate);

Upvotes: 0

Robert Slaney
Robert Slaney

Reputation: 3722

Wrap the date in single quotes instead of #.

This string concatenation is a SQL injection waiting to happen. Use SqlCommand with parameters instead, then you don't have to worry about string conversion issues

Upvotes: 1

Related Questions