How to insert date from asp page textbox to sql server

Question

I have a button within my web page to inserts a few values into sql server columns. One of these values happens to be of data type Date. The following is my code for my asp.net page:

 protected void Button1_Click(object sender, EventArgs e)
{
     con.Open();
     SqlCommand cmd1 = new SqlCommand("insert into dbo.FillTable values ('TextBox2.Text', 'TextBox1.Text', 'FA0005')",con);
     SqlDataAdapter dr = new SqlDataAdapter(cmd1);
     con.Close();
     DataSet dl = new DataSet();
     dr.Fill(dl);
     //Label5.Text = dl.Tables[0].Rows[1][9].ToString();

}

I want to be able to have the user enter the date in the format (yyyy-MM-dd), which is the date format for my sql server. "TextBox2" is the textbox that holds the date input. Whenever I simply hard code the date as for ex. '2010-01-01', '50', 'FA0005', it works well and inserts the record. However, when I code is as 'TextBox2.Text', 'TextBox1',etc. It gives me an error saying "Conversion failed when converting date and/or time from character string". Can someone help me with this? Its confusing me because having the date in 'yyyy-mm-dd' format works well, which is same as the textbox.

You're trying to explicitly insert the text `TextBox2.Text` into a date field, which is the cause of your error. You also want to look into parameterized queries; if you allow users to pass in values, you are vulnerable to SQL injection attacks. — LittleBobbyTables - Au Revoir, Oct 02 '12 at 14:25
Agreed: http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection. — rsbarro, Oct 02 '12 at 14:26
Since you're using sql server, it may be beneficial to look into stored procedures as well. — DJ Quimby, Oct 02 '12 at 14:27

Mike Perrenoud · Accepted Answer · 2012-10-02T14:33:46.437

protected void Button1_Click(object sender, EventArgs e) 
{ 
     con.Open(); 
     SqlCommand cmd1 = new SqlCommand(string.Format("insert into dbo.FillTable values ('{0}', '{1}', 'FA0005')", TextBox2.Text, TextBox1.Text), con); 
     SqlDataAdapter dr = new SqlDataAdapter(cmd1); 
     con.Close(); 
     DataSet dl = new DataSet(); 
     dr.Fill(dl); 
}

Now, let's break down the string.Format function. It says that if I have a string to format like this "Hello {0}!", anything I pass in at the zero index of the function will replace every occurrance of {0}. So, let's say I have this string "Hello {0}, and I say again hello {0}!" and I used it like this string.Format("Hello {0}, and I say again hello {0}!", "world"), I would get a string like this "Hello **world**, and I say again hello **world**!".

Note

However, the above solution leaves you open to SQL Injection, so if you want to protect against that then let's go this route.

protected void Button1_Click(object sender, EventArgs e) 
{ 
     con.Open(); 
     SqlCommand cmd1 = new SqlCommand("insert into dbo.FillTable values (@TextBox2Val, @TextBox1Val, 'FA0005')", con); 
     cmd1.AddParameterWithValue( "TextBox1Val", TextBox1.Text );
     cmd1.AddParameterWithValue( "TextBox2Val", TextBox2.Text );
     SqlDataAdapter dr = new SqlDataAdapter(cmd1); 
     con.Close(); 
     DataSet dl = new DataSet(); 
     dr.Fill(dl); 
}

Now let's break this down. The statement sent to the SQL server is just what you see, with the @paramname in the string. But, it will send it as a prepare and prepare that statement with the values you provided in the AddParameterWithValue method. Note that here, as long as the value in the TextBox2.Text is a date you don't have to concern yourself with the format because SQL server will take care of that. Bear in mind that SQL server stores it in one format and you'll display it in another but it can convert from a myriad of formats as long as they are valid.

Now, as stated by @Willem, it would behoove you to ensure that the value in TextBox2.Text is in fact a date, so let's do that, add this snippet at the top of the function ...

DateTime theDate;
if (!DateTime.TryParse(TextBox2.Text, out theDate))
{
    // throw some kind of error here or handle it with a default value
    ... 
}

... and then modify the line with the AddParameterWithValue like this ...

cmd1.AddParameterWithValue( "TextBox2Val", theDate );

If you are making a proper solution, also validate the date string before inserting using `DateTime.TryParse()` or equivalent. — w5l, Oct 02 '12 at 14:33

score 3 · Answer 2 · answered Oct 02 '12 at 14:31

You don't quite have the mechanism of getting the text box values into the insert correct. Additionally this style of database insertion leaves you vulnerable to SQL Injection attacks. One better option would be to parameterize your SqlCommand, as follows:

 SqlCommand cmd1 = new SqlCommand("insert into dbo.FillTable values (@Date1, @Date2, @SomeString)",con);

Then, you can specify parameters as follows:

 cmd1.Parameters.AddWithValue("@Date1",TextBox1.Text);
 cmd1.Parameters.AddWithValue("@Date2",TextBox2.Text);
 cmd1.Parameters.AddWithValue("@SomeString,"FA0005");

Specifying parameters eliminates the SQL Injection risk, and also provides a clean mechanism for getting the values from your text boxes to your INSERT. Hope this helps.

score 2 · Answer 3 · answered Oct 02 '12 at 14:27

You're inputting the text "TextBox2.Text" into the database, not the value of the textbox. Remove the quotes from TextBox2.Text:

SqlCommand cmd1 = new SqlCommand("insert into dbo.FillTable values 
    ('" + TextBox2.Text + "', '" + TextBox1.Text + "', 'FA0005')",con);

As noted above, you're leaving yourself open to SQL Injection when you're appending strings like this.

How to insert date from asp page textbox to sql server

3 Answers3

Note