asked on

insert date field into access with oledbcommand parameters

i've got the following code, when i execute it i get an error (invalid command..), when i remove the date fields it works.
do i have to something special to insert the date fields?

Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand

' create the connection
objConn = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & file)

' create the command for the header
objCmd = New OleDbCommand("INSERT INTO OrderHead (Date, AmmendedDate, DocumentNumber, ContractNumber, " & _
"Supplier, SupplierName, Payment, TotalQty, " & _
"Yarn, Description, Weight, BarcodeInfo, PackingQtyPerCarton, Comments, " & _
"SpecSent, PurchaseOrder, Packaging, QualityApproved, PackagingApproved) " & _
"VALUES (@Date, @AmmendedDate@, DocumentNumber, @ContractNumber, " & _
"@Supplier, @SupplierName, @Payment, @TotalQty, " & _
"@Yarn, @Description, @Weight, @BarcodeInfo, @PackingQtyPerCarton, @Comments, " & _
"@SpecSent, @PurchaseOrder, @Packagaing, @QualityApproved, @PackagingApproved)", objConn)

' set the parameters
objCmd.Parameters.Add("@DocumentNumber", DocumentNumber)
objCmd.Parameters.Add("@ContractNumber", ContractNumber)

' Comment out the next 2 lines, and it works (when the date and ammendeddate field is removed from above)

objCmd.Parameters.Add("@Date", ContractDate)
objCmd.Parameters.Add("@AmmendedDate", AmmendedDate)

objCmd.Parameters.Add("@Supplier", Supplier)
objCmd.Parameters.Add("@SupplierName", SupplierName)
objCmd.Parameters.Add("@Payment", Payment)
objCmd.Parameters.Add("@TotalQty", TotalQty)
objCmd.Parameters.Add("@Yarn", Yarn)
objCmd.Parameters.Add("@Description", Description)
objCmd.Parameters.Add("@Weight", Weight)
objCmd.Parameters.Add("@BarcodeInfo", BarcodeInfo)
objCmd.Parameters.Add("@PackingQtyPerCarton", PackingQtyPerCarton)
objCmd.Parameters.Add("@Comments", Comments)
objCmd.Parameters.Add("@SpecSent", SpecSent)
objCmd.Parameters.Add("@PurchaseOrder", PurchaseOrder)
objCmd.Parameters.Add("@Packaging", Packaging)
objCmd.Parameters.Add("@QualityApproved", QualityApproved)
objCmd.Parameters.Add("@PackagingApproved", PackagingApproved)

' open the connection
objConn.Open()

Try
' insert the header
objCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(objCmd.CommandText)
MsgBox(ex.ToString)
End Try

RonaldBiemans

This is because Date is a reserved word, rename your date field to something else or enclose it in []

rpter

ASKER

ah ok, yep missed that, but now i get a 'Data type mismatch', again on the date fields.

RonaldBiemans

Could you post the new code ?

rpter

ASKER

new code below. ContractDate & AmmendedDate both return a Date field (and have valid dates in)

Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand

' create the connection
objConn = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & file)

' create the command for the header
objCmd = New OleDbCommand("INSERT INTO OrderHead (DocumentNumber, ContractNumber, " & _
"Supplier, SupplierName, Payment, TotalQty, " & _
"Yarn, Description, Weight, BarcodeInfo, PackingQtyPerCarton, Comments, " & _
"SpecSent, PurchaseOrder, Packaging, QualityApproved, PackagingApproved, [Date], AmmendedDate) " & _
"VALUES (@DocumentNumber, @ContractNumber, " & _
"@Supplier, @SupplierName, @Payment, @TotalQty, " & _
"@Yarn, @Description, @Weight, @BarcodeInfo, @PackingQtyPerCarton, @Comments, " & _
"@SpecSent, @PurchaseOrder, @Packagaing, @QualityApproved, @PackagingApproved, @Date, @AmmendedDate)", objConn)

' set the parameters
objCmd.Parameters.Add("@DocumentNumber", DocumentNumber)
objCmd.Parameters.Add("@ContractNumber", ContractNumber)
objCmd.Parameters.Add("@Date", ContractDate)
objCmd.Parameters.Add("@AmmendedDate", AmmendedDate)
objCmd.Parameters.Add("@Supplier", Supplier)
objCmd.Parameters.Add("@SupplierName", SupplierName)
objCmd.Parameters.Add("@Payment", Payment)
objCmd.Parameters.Add("@TotalQty", TotalQty)
objCmd.Parameters.Add("@Yarn", Yarn)
objCmd.Parameters.Add("@Description", Description)
objCmd.Parameters.Add("@Weight", Weight)
objCmd.Parameters.Add("@BarcodeInfo", BarcodeInfo)
objCmd.Parameters.Add("@PackingQtyPerCarton", PackingQtyPerCarton)
objCmd.Parameters.Add("@Comments", Comments)
objCmd.Parameters.Add("@SpecSent", SpecSent)
objCmd.Parameters.Add("@PurchaseOrder", PurchaseOrder)
objCmd.Parameters.Add("@Packaging", Packaging)
objCmd.Parameters.Add("@QualityApproved", QualityApproved)
objCmd.Parameters.Add("@PackagingApproved", PackagingApproved)

' open the connection
objConn.Open()

' insert the header
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

ptakja

Typically you surround the date value with "#" as

objCmd.Parameters.Add("@Date", "#" & ContractDate & "#")
objCmd.Parameters.Add("@AmmendedDate", "#" & AmmendedDate & "#")

rpter

ASKER

already tried with that, it doesnt work.

RonaldBiemans

It is because the date type in vb differs from date type in oledb add this and it will work

objCmd.Parameters.Add("@Date", System.Data.OleDb.OleDbType.DBDate)
objCmd.Parameters("@Date").value = ContractDate
objCmd.Parameters.Add("@AmmendedDate", System.Data.OleDb.OleDbType.DBDate)
objCmd.Parameters("@AmmendedDate").value = AmmendedDate

rpter

ASKER

i still get the same error.

ASKER CERTIFIED SOLUTION

RonaldBiemans

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rpter

ASKER

its a date field, but just to make sure i used the now keyword as below;

objCmd.Parameters.Add("@Date", System.Data.OleDb.OleDbType.DBDate)
objCmd.Parameters("@Date").Value = Now

it still didnt work. i'll do some more tests then, maybe its something on my database if it worked for you.

RonaldBiemans

just to be sure could you post your code again?

rpter

ASKER

here it is, so far....

Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand

' create the connection
objConn = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & file)

' create the command for the header
objCmd = New OleDbCommand("INSERT INTO OrderHead (DocumentNumber, ContractNumber, " & _
"Supplier, SupplierName, Payment, TotalQty, " & _
"Yarn, Description, Weight, BarcodeInfo, PackingQtyPerCarton, Comments, " & _
"SpecSent, PurchaseOrder, Packaging, QualityApproved, PackagingApproved, [Date]) " & _
"VALUES (@DocumentNumber, @ContractNumber, @Supplier, @SupplierName, @Payment, " & _
"@TotalQty, @Yarn, @Description, @Weight, @BarcodeInfo, @PackingQtyPerCarton, " & _
"@Comments, @SpecSent, @PurchaseOrder, @Packagaing, @QualityApproved, @PackagingApproved, @Date)", objConn)

' set the parameters
objCmd.Parameters.Add("@DocumentNumber", DocumentNumber)
objCmd.Parameters.Add("@ContractNumber", ContractNumber)
objCmd.Parameters.Add("@Date", System.Data.OleDb.OleDbType.DBDate)
objCmd.Parameters("@Date").Value = ContractDate ' Also tried replacing ContractDate with Now()
'objCmd.Parameters.Add("@AmmendedDate", AmmendedDate)
objCmd.Parameters.Add("@Supplier", Supplier)
objCmd.Parameters.Add("@SupplierName", SupplierName)
objCmd.Parameters.Add("@Payment", Payment)
objCmd.Parameters.Add("@TotalQty", TotalQty)
objCmd.Parameters.Add("@Yarn", Yarn)
objCmd.Parameters.Add("@Description", Description)
objCmd.Parameters.Add("@Weight", Weight)
objCmd.Parameters.Add("@BarcodeInfo", BarcodeInfo)
objCmd.Parameters.Add("@PackingQtyPerCarton", PackingQtyPerCarton)
objCmd.Parameters.Add("@Comments", Comments)
objCmd.Parameters.Add("@SpecSent", SpecSent)
objCmd.Parameters.Add("@PurchaseOrder", PurchaseOrder)
objCmd.Parameters.Add("@Packaging", Packaging)
objCmd.Parameters.Add("@QualityApproved", QualityApproved)
objCmd.Parameters.Add("@PackagingApproved", PackagingApproved)

' open the connection
objConn.Open()

' insert the header
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

rpter

ASKER

ok, so after a bit of searching on the net i've found plenty of references to the same error.
None of them however seems to have a solution, except for micrsoft themselves, which seem to recommend you change your field type to text!!!

a bit more playing around in vb and i got it to work, the problem seems to be with using parameters, if you dont use them it seems to work.
This isnt really the way i wanted to do it, as its a bit messy, but hey, if it works right....

i've awarded you the points, and you fixed the original question, although i'd like to know if you manage to figure out why my parameters dont work, maybe you could send me your code that you said worked??

Thanks for your help!

My new code;

Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand

' create the connection
objConn = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & file)

' create the command for the header
objCmd = New OleDbCommand("INSERT INTO OrderHead (DocumentNumber, ContractNumber, " & _
"Supplier, SupplierName, Payment, TotalQty, " & _
"Yarn, Description, Weight, BarcodeInfo, PackingQtyPerCarton, Comments, " & _
"SpecSent, PurchaseOrder, Packaging, QualityApproved, PackagingApproved, [Date], AmmendedDate) " & _
"VALUES ('" & _
DocumentNumber & "', '" & _
ContractNumber & "', '" & _
Supplier & "', '" & _
SupplierName & "', '" & _
Payment & "', " & _
TotalQty & ", '" & _
Yarn & "', '" & _
Description & "', '" & _
Weight & "', '" & _
BarcodeInfo & "', '" & _
PackingQtyPerCarton & "', '" & _
Comments & "', " & _
SpecSent & ", '" & _
PurchaseOrder & "', " & _
Packaging & ", " & _
QualityApproved & ", " & _
PackagingApproved & ", #" & _
ContractDate & "#, #" & _
AmmendedDate & "#)", objConn)

' open the connection
objConn.Open()

' insert the header
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(objCmd.CommandText)
MsgBox(ex.ToString)
End Try