VB:NET, SQL DateTime 1

[magnus2]

Hi! I have trouble sending a DateTime variable from VB code to a SQL database DateTime field. I use this:

StrSQL = "INSERT INTO FyllTid (A, B, C, D, Time)" &
" VALUES (" & a & ", " & b & ", " & c & ", " & d & ", " & time & ")"

Time is DATETIME in SQL and time is DateTime in VB.NET. What could be the problem? The other variables is being sent but not the datetime.

Grateful for comments! Kind regards! /Magnus

 

[harmmeijer]

I think you set the date format before executing the insert
SET DATEFORMAT mdy

Or make sure that localization on the machine that runs the VB code is the same as the SQL server.




Greetings, Harm Meijer

 

[chrissie1]

1. time is a keyword thus better not used as variable name.
2. Like Harm said, are they both the same format.
3. Did you try to debug and if so does it give an error message.
4. To avoid SQL injection (look it up on google) you should use parameters. This should also solve problem number 2.



Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[magnus2]

Hi!

SET DATEFORMAT mdy

is not allowed by the build error manager in VB.NET.

The database is local on my machine. Could the localization still be a problem?

I'm still struggling

 

[magnus2]

Hi again!

The variable name is not the problem. Sorry for troubling you with that. No debugging error messages. I will look up SQL injection. Thanks for your time so far! Thank you very much!

 

[harmmeijer]

StrSQL = "SET DATEFORMAT mdy
;INSERT INTO FyllTid (A, B, C, D, Time)" &
" VALUES (" & replace(a,"'","''") & ", " & replace(b,"'","''") & ", " & replace(c,"'","''") & ", " & replace(d,"'","''") & ", " & time.ToString("MM/dd/YYYY HH:mm") & ")"





Greetings, Harm Meijer

 

[magnus2]

Thanks again! This also seems to work:

StrSQL = "INSERT INTO FyllTid (A, B, C, D, Time1)" &
" VALUES (" & a & ", " & b & ", " & c & ", " & d & ", '" & time1 & "')"

The ' signs around time1 does the trick!!
Thanks!

 

[RiverGuy]

You've to wrap a DateTime in single quotes in SQL as well.

 

[chiph]

magnus2 -

StrSQL = "INSERT INTO FyllTid (A, B, C, D, Time1)" &
" VALUES (" & a & ", " & b & ", " & c & ", " & d & ", '" & time1 & "')"

Be sure to Google on SQL Injection like chrissie1 suggested. By using string concatenation to build your SQL, you are leaving yourself wide open to an attack.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[RiverGuy]

Chip,

This all depends on how the program works. If you concatenate something directly from a TextBox, then there will be a problem. But if a numeric or date variable is validated on the client, then there should be no problem concatenating it.

 

[chrissie1]

and now you mention it, there was no need for replace(a,"'","''") because i think they are numeric values, because they don't have single quotes around them.
But saying that if he uses parameters, he wouldn't even have to bother with the replace(a,"'","''") because it takes care of that aswell.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[chiph]

But if a numeric or date variable is validated on the client, then there should be no problem concatenating it.

I'm afraid the reality is that most people doing string concatenation of their SQL probably aren't doing much validation of their inputs. :-(

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[magnus2]

I needed to use ' signs also around a double variable to make SQL accept a double with more than one decimal. Strange! Should I always use ' around my variables?

Maybe parameters then, but I think my code will work on my local database.

Thanks!
/Magnus

 

[chiph]

Using ADO Parameters take care of the single-quote problem, too. So you don't have problems adding customers named "O'Brien" to your database.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first