By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

Insert statement for the DateTime field

P: n/a
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,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

Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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,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>

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

Nov 21 '05 #2

P: n/a
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
Nov 21 '05 #3

P: n/a

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

"=?Utf-8?B?a2Q=?=" <kd@discussions.microsoft.com> wrote in
news:CF**********************************@microsof t.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,dateval) values(" & "'" & nameval & "',"
& "'" str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...


--
---------
Der Chef Groovy
ICQ: 1529949
Nov 21 '05 #4

P: n/a
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********@insightbb.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**********************************@microso ft.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,dateval) values(" & "'" & nameval & "',"
& "'" str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...


Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.