error in insert query

Question

0.00/5 (No votes)

See more:

My database table is Events

EventId int
EventName varchar(50)
EventDate  datetime

I try to insert eventname and eventdate through insert query by using code it is necessary for me to keep datatype datetime of column EventDate

My code is this

C#

protected void Button1_Click(object sender, EventArgs e)
   {
       SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\omar\\Documents\\Visual Studio 2005\\WebSites\\Project2\\App_Data\\Database.mdf;Integrated Security=True;User Instance=True");
       con.Open();
       string que="insert into Events(EventName,EventDate) values('"+TextBox1.Text+"','"+TextBox2.Text+"')"; /* I try to a store date thorugh insert query and i use textbox2.text error comes conversion of chardatatype resulted in an out of range datetime */
       SqlCommand com=new SqlCommand(que,con);
       com.ExecuteNonQuery();
       con.Close();
   }

Posted 30-Apr-11 4:18am

om56

Updated 30-Apr-11 4:39am

TweakBird

v4

Add a Solution

3 solutions

Solution 1

http://www.java2s.com/Code/ASP/Development/ConverttextinasptextboxintoDateC.htm[^]
OR
Convert date in textbox to either datetime or null

I hope the above information will be helpful. If you have more concerns, please let me know.

Posted 30-Apr-11 4:42am

Monjurul Habib

Updated 30-Apr-11 4:44am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

thatraja · Accepted Answer · 2011-04-30T07:33:00

Don't do that way
Use parameterized SQL queries[^]

C#

SqlConnection objConnection = new SqlConnection(_ConnectionString);
objConnection.Open();
SqlCommand objCommand = new SqlCommand(
   "INSERT INTO Events(EventName,EventDate) VALUES(@EventName,@EventDate)",
   objConnection);

SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "@EventName";
parameter1.SqlDbType = SqlDbType.NVarChar;
parameter1.Direction = ParameterDirection.Input;
parameter1.Value = categoryName;

objCommand.Parameters.Add(parameter1);

SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "@EventDate";
parameter2.SqlDbType = SqlDbType.DateTime;
parameter2.Direction = ParameterDirection.Input;
parameter2.Value = categoryName;

objCommand.Parameters.Add(parameter2);

objCommand.ExecuteNonQuery();
objConnection.Close();

BTW don't write Database related coding in your form or page. write in a separate layer. Refer this article. Three Layer Architecture in C# .NET[^]

TweakBird · Accepted Answer · 2011-04-30T04:44:00

Solution 2

Try this sql statement

C#

string que="insert into Events(EventName,EventDate) values('"+TextBox1.Text+"',"+Convert.ToDateTime(TextBox2.Text)+")";

P.S: Try to do with command parameters to avoid sql injection attacks. Have a look here[^]

See some date time conversion issues[^]

Posted 30-Apr-11 4:44am

TweakBird

Updated 30-Apr-11 4:50am

v2

Comments

Monjurul Habib 30-Apr-11 10:46am

nice advice. but what if user gives invalid date into the Textbox.

TweakBird 30-Apr-11 10:56am

Validate datetime format before doing insert. is your application winforms or Asp.net?
if C# with winforms see here : http://stackoverflow.com/questions/371987/validate-a-datetime-in-c
otherwise (ASP.Net) see this http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/Calendar/Calendar.aspx

Monjurul Habib 30-Apr-11 11:56am

nice links. my 5

TweakBird 30-Apr-11 13:17pm

Thank you.

Prasanta_Prince 30-Apr-11 13:07pm

Good one.

RaviRanjanKr 30-Apr-11 13:42pm

Nice Answer! My 5 :)

TweakBird 30-Apr-11 13:48pm

Thank you Ravi