Insert datetime into SQL TABLE
hI,
I have the following line of code in a VB6 project
SQLStmt = " INSERT INTO Recipes_West_Table " & _
"Values( " & Form1.Text1.Text & " , " Form1.txtVal1.Text & "," & Label2.Caption & " )"
Where Form1.Text.Text = datetime.now , Form1.txtVal1.Text is a texbox with a numerical value and
Label2.Caption is a string made up by 30 comma separated numerical values.
In the SQL Table I have 32 columns out of which the first one is DateTime, second is Float and the next 30 is float.If I replace in the SQL Statement the Form1.Text1.Text with a numetrical value and change the col datatype to float it works fine.But I need the first column to be datetime but I get an error saying I have a syntax muistake.Obviously because the statement works fine with a numerical value somehow I should convert that Form1.Text.Text to make SQL that I am coming with a datetime value.Please help as I am totally and utterly stuck by this problem.Thank you.
I have the following line of code in a VB6 project
SQLStmt = " INSERT INTO Recipes_West_Table " & _
"Values( " & Form1.Text1.Text & " , " Form1.txtVal1.Text & "," & Label2.Caption & " )"
Where Form1.Text.Text = datetime.now , Form1.txtVal1.Text is a texbox with a numerical value and
Label2.Caption is a string made up by 30 comma separated numerical values.
In the SQL Table I have 32 columns out of which the first one is DateTime, second is Float and the next 30 is float.If I replace in the SQL Statement the Form1.Text1.Text with a numetrical value and change the col datatype to float it works fine.But I need the first column to be datetime but I get an error saying I have a syntax muistake.Obviously because the statement works fine with a numerical value somehow I should convert that Form1.Text.Text to make SQL that I am coming with a datetime value.Please help as I am totally and utterly stuck by this problem.Thank you.
ASKER
If I use Values( '" & Form1.Text1.Text & "' I just comment the code out and if I use Values( #" & Form1.Text1.Text & "# I get incorrect synatx error.I simply don't understand why I have to format this texbox.text as If I check the value of the textbox it will be a proper datetime value ;like 8/12/2008 1:19:51 PM !! What's wrong with this value in this query I can't understand.SQL should just accept it.But It doesn't if I run the query in Query Analyzer I get the same error but if I put instaed of Values(8/12/2008 1:19:51 PM ,202,303,404,.... this Values('8/12/2008 1:19:51 PM' ,202,303,404,.... Then it works.When I check the table itself the ' signs are gone and I have only the datetime as IT should be.So SQL expects the ' but I don't know how to give it to SQL from the VB6 statement
I was trying to say that with the single '. Values('8/12/2008 1:19:51 PM' ... should work. When you added it, was it between the double quotes? If it is within your double quotes then it should not comment out line because it is literal text at that point.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
There was a missing ampersand as well before Form1.txtVal1.Text. Hope you figure it out. Good luck.
ASKER
Thanx
Values( #" & Form1.Text1.Text & "#
OR
Values( '" & Form1.Text1.Text & "'
Also, you want to convert the text to a date in VB code to make sure it is valid before inserting.