Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
3.00/5 (1 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.

mm is for minutes. MM is for months.
Might be thats creating issue while converting... just check.
Share this answer
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    
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
Use this format

Format(DTdocdt.Value, "MM/dd/yyyy")
Share this answer
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
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.
Share this answer
use single quotes and double quotes

'" Format(DTdocdt.Value, "MM/dd/yyyy")"'
Share this answer
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900