Click here to Skip to main content
15,936,546 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear users,

I am having problem with inserting date into SQL server. My code is as following:

C#
protected void btnSave_Click(object sender, EventArgs e)

        {
            using (SqlConnection con = new SqlConnection("Data Source=MEHDI-PC\\SQLEX;Initial Catalog=PIMS;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {

                    string DatePrinted = "";

                    if (txtDatePrinted.Text != "")
                        DatePrinted = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DatePrinted = "NULL";

                    string DateReceived = "";

                    if (txtDateReceived.Text != "")
                        DateReceived = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DateReceived = "NULL";

                    string DueDate = "";

                    if (txtDueDate.Text != "")
                        DueDate = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DueDate = "NULL";

                    string ActualDate = "";

                    if (txtActualDate.Text != "")
                        ActualDate = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        ActualDate = "NULL";

                    String sql = "Insert into dbo.Documents(Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,[Content],Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response) values ('" + txtRef.Text + "','" + txtSubject.Text + "','" + ddlSource.Text + "' ,'" + ddlDestination.Text + "','" + ddlMedium.Text + "','" + DatePrinted + "','" + DateReceived + "','" + ddlDocumentType.Text + "','" + cbxAction.Checked + "','" + DueDate + "','" + ActualDate + "','" + txtContent.Text + "','" + txtTag.Text + "','" + txtIssue.Text + "','" + txtAttachment.Text + "','" + txtNotes.Text + "','" + ddlAssignedTo.Text + "','" + txtReplyRef.Text + "','" + ddlPriority.Text + "','" + ddlStatus.Text + "','" + ddlResponse.Text + "')";
  
                    cmd.Connection = con;
                    cmd.CommandText = sql;

                    con.Open();
                    //dataset object to get all select statement results
                    //DataSet ds = new DataSet();

                    //sql dataadoptor to fill dataset
                    cmd.ExecuteNonQuery();
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                MultiView1.SetActiveView(vRecord);
                txtRef.Text = string.Empty;
                txtSubject.Text = string.Empty;
                ddlSource.Text = string.Empty;
                ddlDestination.Text = string.Empty;
                ddlMedium.Text = string.Empty;
                txtDatePrinted.Text = string.Empty;
                txtDateReceived.Text = string.Empty;
                ddlDocumentType.Text = string.Empty;
                cbxAction.Checked = false;
                txtDueDate.Text = string.Empty;
                txtActualDate.Text = string.Empty;
                txtContent.Text = string.Empty;
                txtTag.Text = string.Empty;
                txtIssue.Text = string.Empty;
                txtAttachment.Text = string.Empty;
                txtNotes.Text = string.Empty;
                ddlAssignedTo.Text = string.Empty;
                txtReplyRef.Text = string.Empty;
                ddlPriority.Text = string.Empty;
                ddlStatus.Text = string.Empty;
                ddlResponse.Text = string.Empty;
            }

            }


After I run this code, and enter any date value in the datefield, it throws this message:

SqlException unhandled : Incorrect syntax near '12'


I am failed to see anything wrong with my code. Please help me identify the problem.

You help will be much appreciated.

Regards
Posted
Comments
Mahesh Bailwal 20-Jun-13 3:40am    
Debug and check your CommandText at run time, your dynamic sql insert statement might contain single quotes which are not properly handled.

http://stackoverflow.com/questions/6148661/exception-on-inserting-single-quote-in-c-sharp
[no name] 20-Jun-13 5:07am    
Yes You are correct. I changed dynamic sql insert to Parameterized insertion and problem got solved. Thanks alot for your help.
Member 12777707 5-Oct-16 12:47pm    
thank you for the solution

1 solution

There is plenty wrong with your code. Starting with major inefficiency, moving into dangerous SQL Injection susceptibility, and of course the problem you are complaining about...

Why are you parsing txtDateReceived.Text so many times? Surely you can see that it would be a lot clearer if you parsed it to a DateTime value once (and probably used TryParse instead so you could report a problem to the user if he types it wrong) instead?

Then the SQl...Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
C#
String sql = "INSERT INTO dbo.Documents(Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,[Content],Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response) VALUES (@REF, @SUBJ, @SRC, @DEST...

cmd.Connection = con;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@REF", txtRef.Text);
cmd.Parameters.AddWithValue("@REF", txtSubject.Text);
cmd.Parameters.AddWithValue("@REF", ddlSource.Text);
...

And your reported problem will almost certainly disappear as well..
 
Share this answer
 
Comments
Zoltán Zörgő 20-Jun-13 3:40am    
How true! +5
Aarti Meswania 20-Jun-13 4:09am    
5! +
[no name] 20-Jun-13 5:27am    
Dear OriginalGriff,

Your answer worked. I changed my code as much as I could according to your instructions. It is now allowing me to enter date as well as leave it empty if I dont want to enter it which solves my situation. Only problem that I just happened to notice is, if I leave a date field 'empty' on my form.... code automatically enters a 'dummy-date' in SQL database engine. That is

dummy-date entered into SQL database engine is = 1900-01-01

My code is:

Collapse | Copy Code
protected void btnSave_Click(object sender, EventArgs e)

{
using (SqlConnection con = new SqlConnection("Data Source=MEHDI-PC\\SQLEX;Initial Catalog=PIMS;Integrated Security=True"))
{
using (SqlCommand cmd = new SqlCommand())
{

string DatePrinted = "";

if (txtDatePrinted.Text != "")
DatePrinted = DateTime.Parse(txtDatePrinted.Text).ToString();
else
DatePrinted = "NULL";

string DateReceived = "";

if (txtDateReceived.Text != "")
DateReceived = DateTime.Parse(txtDateReceived.Text).ToString();
else
DateReceived = "NULL";

string DueDate = "";

if (txtDueDate.Text != "")
DueDate = DateTime.Parse(txtDueDate.Text).ToString();
else
DueDate = "NULL";

string ActualDate = "";

if (txtActualDate.Text != "")
ActualDate = DateTime.Parse(txtActualDate.Text).ToString();
else
ActualDate = "NULL";

String sql = "INSERT INTO dbo.Documents(Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,Content,Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response) VALUES (@Ref, @Subject, @Src, @Dst, @Medium, @Date_Printed, @Date_Received, @Document_Type, @Action_Required, @Due_Date, @Actual_Date, @Content, @Tag, @Issue_No, @Attachment, @Notes, @Assigned_To, @Reply_Ref, @Priority, @Status, @Response)";

cmd.Connection = con;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@Ref", txtRef.Text);
cmd.Parameters.AddWithValue("@Subject", txtSubject.Text);
cmd.Parameters.AddWithValue("@Src", ddlSource.Text);
cmd.Parameters.AddWithValue("@Dst", ddlDestination.Text);
cmd.Parameters.AddWithValue("@Medium", ddlMedium.Text);
cmd.Parameters.AddWithValue("@Date_Printed", txtDatePrinted.Text);
cmd.Parameters.AddWithValue("@Date_Received", txtDateReceived.Text);
cmd.Parameters.AddWithValue("@Document_Type", ddlDocumentType.Text);
cmd.Parameters.AddWithValue("@Action_Required", cbxAction.Checked);
cmd.Parameters.AddWithValue("@Due_Date", txtDueDate.Text);
cmd.Parameters.AddWithValue("@Actual_Date", txtActualDate.Text);
cmd.Parameters.AddWithValue("@Content", txtContent.Text);
cmd.Parameters.AddWithValue("@Tag", txtTag.Text);
cmd.Parameters.AddWithValue("@Issue_No", txtIssue.Text);
cmd.Parameters.AddWithValue("@Attachment", txtAttachment.Text);
cmd.Parameters.AddWithValue("@Notes", txtNotes.Text);
cmd.Parameters.AddWithValue("@Assigned_To", ddlAssignedTo.Text);
cmd.Parameters.AddWithValue("@Reply_Ref", txtReplyRef.Text);
cmd.Parameters.AddWithValue("@Priority", ddlPriority.Text);
cmd.Parameters.AddWithValue("@Status", ddlStatus.Text);
cmd.Parameters.AddWithValue("@Response", ddlResponse.Text);


cmd.Connection = con;
cmd.CommandText = sql;

con.Open();
//dataset object to get all select statement results
//DataSet ds = new DataSet();

//sql dataadoptor to fill dataset
cmd.ExecuteNonQuery();
}
if (con.State ==
OriginalGriff 20-Jun-13 5:36am    
If those are dates, then you need to check them before they get anywhere near your DB! What if the user accidentally enters "30 Feb", or "182 Squark"? You should either check and validate them as the first part of your method (and convert them to DateTime values to pass through to the DB) or (preferably) don't use textboxes at all - have you considered a DateTimePicker instead? It returns a DateTime value, and won't let them enter an invalid value.
[no name] 20-Jun-13 6:26am    
True that. I know what you are saying. But I have a button placed next to date text field for the users to click and select the date from that calendar.
Problem is, like I have 4 date fields (DatePrinted, DateReceived,ActualDate and DueDate), suppose one of these 4 dates are not present on the document and we leave that field empty while data-entry operation, our program should function to leave that field EMPTY (NULL) in SQL database engine. Currently, if I am leaving a date field empty, our code is inserting 1900-01-01 date in SQL database engine (Backend database).

Date validation is certainly a task but I am currently postponing it cause I want to do it with all other fields validation process.

Thanking you,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900