Need to understanding error message

Question

hi guys am getting an error in ASP.NET( code behind in VB)and i don't know what it means. The error comes as i execute the code below to update a table in my database

Dim v_command1 As New SqlCommand
Dim v_sqlcode1 As String
v_sqlcode1 = "Update Session SET Endtime = DATEADD(Hour,'" & DRPL_DURATION.SelectedValue & "','" & TXTBX_START.Text & "') WHERE  SessionID = ('" & TXTBOX_SESSIONID.Text & "')"
        v_sqlconn.Open()

this code should allow me to be able to update the end time of a meeting when i decide to change how long it lasts for. the error am getting is this:

Additional information: Argument data type varchar is invalid for argument 2 of dateadd function.

does anyone have an idea what this error message might means.

Thanks

Use parameterized sql command objects, this is pure evil. – JonH Mar 30 '16 at 16:58 — JonH, Mar 30 '16 at 16:58

score 3 · Answer 1 · answered Mar 30 '16 at 17:00

3

Just as the error states, you can't use a varchar data type for the second argument to DATEADD(). It's looking for an integer.

If the value you're passing is that integer, simply remove the single-quotes:

"[...] Hour, " & DRPL_DURATION.SelectedValue & ", [...]"

If it's not an integer and you're actually trying to pass a string of text, then you'll need to re-think what you're doing because you can't add text to a date.

Note: I also highly recommend using parameterized queries instead of directly concatenating values like this. This introduces a SQL injection vulnerability into your code which is eliminated by query parameters. Additionally, you'll find that errors related to column data types are far more rare when you simply pass the value to the query engine and let the engine decide how to manage the data type.

answered Mar 30 '16 at 17:00

David

208,112
36
198
279

Thanks for the prompt reply. simply removing teh single qoutes have worked just fine. Could you please elaborate on your other comment of using paratmetrized quries as this is the first i've heard of them...thanks – D-Boy110 Mar 31 '16 at 13:34
@D-Boy110: This looks like a good example to get you started: http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i The idea is that when you concatenate values directly, you give the user an opportunity to change *values* into *code*, which is called SQL injection. But by using parameters, you explicitly tell the SQL driver what parts are code and what parts are values, and it handles them accordingly. – David Mar 31 '16 at 13:39

score 1 · Answer 2 · answered Mar 30 '16 at 16:59

1

https://msdn.microsoft.com/en-CA/library/ms186819.aspx

Dateadd is looking for a number as a 2nd arguement and you are passing it a string(varchar).

answered Mar 30 '16 at 16:59

Justin

175
8

score 0 · Answer 3 · answered Mar 30 '16 at 17:03

0

This error message is associated with the second argument of DATEADD(..). You need to use an integer (number) value.

See the documentation for more information DATEDD.

answered Mar 30 '16 at 17:03

e2a

952
7
18

Need to understanding error message

3 Answers3