Insert statement for the DateTime field

[Guest]

Hi All,

I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
....
...
ocmd.ExecuteNonQuery()
....
....

The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"

The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!

I know that this is a simple syntax problem, which I don't seem to get right!

Would anybody be able to give me insert statement for the above?

Thanks.
kd

 

[Herfried K. Wagner [MVP]]

I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...

The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"

Instead of building the command strings yourself, use a parameterized
command object.

Using Parameters with a 'DataAdapter'
<URL:http://msdn.microsoft.com/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp>

 

[Cor Ligthert]

KD,

I had it in OleDB right before my eyes so have a look at this I changed it
to SQL however watch typos

\\\
cmd.CommandText = "INSERT INTO Tab1 (name, dateval) VALUES (@MyName,
@MyDat)"
cmd.Parameters.Add _
(New SqlParameter("@MyName", SqlDbType.VarWChar))
cmd.Parameters.Add _
(New SqlParameter("@MyDat", SqlDbType.DateTime))
cmd.Parameters(0).Value = "Cor"
cmd.Parameters(1).Value = CDate( "16/03/2005 14:20") 'in Europe
cmd.ExecuteNonQuery
////

I hope this helps,

Cor

 

[Chef Groovy]

might have to change it to
str = "#2005/03/16 14:20#"

 

[J L]

Yes, must use # to enclose the date. Or create parameters.

John