date time conversion problem when inserting data i

问题:

I used to query:

Dim a As String
a = "INSERT INTO tblVisitor(Name, Sex, TimeIn, EnterDate)
     VALUES('"& txtName.Text &"', '"& cboSex.Text &"', '"& Now() &"', '"& DateTime.Parse(cboEnterDate.Text) &"')"

myCommand = New SqlCommand(a, myConnection)
myCommand.ExecuteNonQuery()
........................................

Which cboEnterDate is my DateTime Picker. Then I got the message:

Conversion failed when converting date time from character string.

Please help.

回答1:

By constructing a string, you a) open yourself to SQL injection, and b) end up converting strings to datetimes to strings to datetimes.

If, instead, you use parameters:

Dim a As String
a = "INSERT INTO tblVisitor(Name, Sex, TimeIn, EnterDate)
     VALUES(@Name, @Sex, @TimeIn, @EnterDate)"

myCommand = New SqlCommand(a, myConnection)
myCommand.Parameters.AddWithValue("@Name",txtName.Text)
myCommand.Parameters.AddWithValue("@Sex",cboSex.Text)
myCommand.Parameters.AddWithValue("@TimeIn",DateTime.Now)
myCommand.Parameters.AddWithValue("@EnterDate",DateTime.Parse(cboEnterDate.Text))
myCommand.ExecuteNonQuery()

Which only performs a single conversion of string to datetime. Although, if cboEnterDate is a DateTimePicker, you can avoid treating it as a string at all:

myCommand.Parameters.AddWithValue("@EnterDate",cboEnterDate.Value)

回答2:

Look up using SQL Parameters. The code you are using now is not only prone to parsing issues w/r/t date formats, it is vulnerable to SQL injection attacks. Creating SQL statements via string concatenation is just a really, really bad idea.

回答3:

Change Now() to Now.ToShortTimeString That should fix the problem.

Edit: Oops and the DateTime.Parse(cboEnterDate.Text) add .ToShortDate to the end.

回答4:

When you use DateTime.Parse(cboEnterDate.Text) it parses the string into a datetime object. You should send the string cboEnterDate.Text directly and let the SQL server do the parsing