RajAntonyv

Hello,

I am having so much fun with this date datatype in SQL and VB.

I am trying to update a date column defined as smalldatetime and I am using a dtpicker to choose the date. I am using:

Set PerAcreRs = PerAcreConn.Execute("Update collection set collectionRddno = '" + txtColRddNo.Text + "', collectionamount = " + FormatCurrency(txtColAmount.Text, 2, vbTrue, vbFalse, vbFalse) + ", collectionstaffname = '" & _
txtStaffName.Text + "', collectiondate = " + Format(dtCol_CollectionDate.Value, "mm/dd/yyyy") + ", collectiontadmappg = '" + txtColTadMapPg.Text + "', collectionmapscopg = '" + txtMapscoPg.Text + "', collectioncheckdate =" & _
Format(dtColDateOnCheck.Value, "mm/dd/yyyy") + ", collectionreimbcheckno ='" + txtColReimbAdd_CheckNo.Text + "', collectioncomments ='" + txtColComments + "', CollectionDevTaxId_FK = '" + ColDevTaxId + "', CollectionLineNo_FK ='" & _
cmbColLineInfo.Text + "', CollectionLineSwrFrom_FK = '" + cmbColSwrStnFrom.Text + "', CollectionLineSwrTo = '" + cmbSwrStnTo.Text + "' where collectionrddno ='" + mshColSearch.Text + "'")

as my update statement. For some reason, all the dates come across as "1900-01-01 00:00:00".

I don't want to change the datatype because then I have to go and change lot of other code based on the date datatype.

I have been looking at this for hours trying different things but not been successful. I need another set of eyes for help.

Thanks and appreciate all your help,

Raj Antony V

davie

Try something like this for your date values.

arunendra

Hi

I know this is a very old post, but I still think some people will need this help. So here is the solution ....
First the problem : You need date and time info into the back end DB, i.e. sql server and you want to post this data through a vb6 form.
Solution : Use a parameterised statement to insert data.

Assumption : you have a table named StudentMaster where you need to put Student Registration number, student name, address, and the time of the entry.
Table columns : StudentRegNo, StudentName, StudentAddress, EntryTime
Statement :

dim str as string
str="insert into studentmaster (RegNo,StudentName,StudentAddress,EntryTime) Values(?,?,?,GETDATE())"
This last parameter is set as GETDATE(), and this function will help to insert the dateandtime value into sql server like 31/07/2012 9:12:00 PM

Hope this helps someone!

Arunendra