Click here to Skip to main content
14,384,785 members
Rate this:
Please Sign up or sign in to vote.
See more:
Dear all,

I faced a problme in insert of date.

strSQL = "INSERT into Bills(docdt,tc,glc _
") values (" & Format$(DTdocdt.Value, "dd/mm/yyyy") & "," & _
CType((xtc), String) & "," & _

But it is inserting 01/01/1900 00:00:00

Kind guide.

I shows that dtdocdt.value in a message box before insert to see the value which show correct date but inserting above date.
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

mm is for minutes. MM is for months.
Might be thats creating issue while converting... just check.
   
Comments
Jatinder Gupta 19-Sep-10 8:18am
   
Not sir the problem is same again after changes Format(DTdocdt.Value, "dd/MM/yyyy") the date showing 01/01/1900 00:00:00
Sandeep Mewara 19-Sep-10 11:25am
   
Use CONVERT....
Simon_Whale 20-Sep-10 7:20am
   
further to Sandeep's CONVERT(VARCHAR(20), @DateVariable, 103)
Jatinder Gupta 20-Sep-10 21:44pm
   
I'm inserting in that way
strSQL = "INSERT into Bills(docdt,tc,glc" & _
",docno,broker,amount) values (convert(varchar(20),@DTdocdt,103)," & _
CType((xtc), String) & "," & _
CType(custcmb.Items(custcmb.SelectedIndex), ListItem).ID & "," & _
(txtinvoice.Text) & "," & _
CType(cmbbrk.Items(cmbbrk.SelectedIndex), ListItem).ID & "," & _
CType(IIf(txttotal.Text = "", 0, txttotal.Text), String) & ")"


The Error occurs "Must Declare the scalar variable '@DTdocdt' ".

Kindly guide I'm passing write command or make mistake
Rate this:
Please Sign up or sign in to vote.

Solution 2

Use this format

Format(DTdocdt.Value, "MM/dd/yyyy")
   
Comments
Jatinder Gupta 20-Sep-10 21:52pm
   
Not sir the problem is same again after changes Format(DTdocdt.Value, "MM/dd/yyyy") the date shorted in database is 01/01/1900 00:00:00.

If I put a msgbox before that the data showing(09/21/2010) write in msgbox but stored 01/01/1900 00:00:00 in database
Rate this:
Please Sign up or sign in to vote.

Solution 4

I think you are very new to SQL and even to VB syntax.

First of all, you should enclose every string or date data in single quotes in SQL.

So even in the query that worked, you should enclose txtinvoice.text also.
"INSERT INTO SALES(docdt,docno) values ('" & Format(DTdocdt.Value, "MM/dd/yyyy") & "','" & (txtinvoice.Text) & "')" 


And in switched query, you have to switch the field names too.
"INSERT INTO SALES(docno,docdt) values ('" & txtinvoice.Text & "','" & Format(DTdocdt.Value, "MM/dd/yyyy") & "')"


I think this will help you.

If it solves your problem, mark it as answer.
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

use single quotes and double quotes

'" Format(DTdocdt.Value, "MM/dd/yyyy")"'
   
Comments
Jatinder Gupta 21-Sep-10 21:17pm
   
Thanks sir problem solved.

One more thing to I want to ask like if I passed the command like that it ok but I suffel the field and put the data at second it shows commant like as second example Kindly see the error


"INSERT INTO SALES(docdt,docno" & _
") values ('" & Format(DTdocdt.Value, "MM/dd/yyyy") & "'," & _
(txtinvoice.Text) & ")"

"INSERT INTO SALES(docdt,docno" & _
") values (txtinvoice.Text) & "," & _
'" & Format(DTdocdt.Value, "MM/dd/yyyy") & "')"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100