Insert statement for the DateTime field

[kd]

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,datev al) values(" & "'" & nameval & "'," & "'"
str & "')"
....
...
ocmd.ExecuteNon Query()
....
....

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]]

"kd" <kd@discussions .microsoft.com> schrieb:

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,datev al) values(" & "'" & nameval & "'," &
"'"
str & "')"
...
..
ocmd.ExecuteNon Query()
...
...

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/cpconusingparam eterswithdataad apters.asp>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

[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.VarWC har))
cmd.Parameters. Add _
(New SqlParameter("@ MyDat", SqlDbType.DateT ime))
cmd.Parameters( 0).Value = "Cor"
cmd.Parameters( 1).Value = CDate( "16/03/2005 14:20") 'in Europe
cmd.ExecuteNonQ uery
////

I hope this helps,

Cor

[Chef Groovy]

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

"=?Utf-8?B?a2Q=?=" <kd@discussions .microsoft.com> wrote in
news:CF******** *************** ***********@mic rosoft.com:

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,datev al) values(" & "'" & nameval & "',"
& "'" str & "')"
...
..
ocmd.ExecuteNon Query()
...
...

--
---------
Der Chef Groovy
ICQ: 1529949

[J L]

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

John

On Sat, 09 Apr 2005 20:36:15 GMT, Chef Groovy
<ch********@ins ightbb.com> wrote:

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

"=?Utf-8?B?a2Q=?=" <kd@discussions .microsoft.com> wrote in
news:CF******* *************** ************@mi crosoft.com:

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,datev al) values(" & "'" & nameval & "',"
& "'" str & "')"
...
..
ocmd.ExecuteNon Query()
...
...