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
rpterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RonaldBiemansCommented:
This is because Date is a reserved word, rename your date field to something else or enclose it in []
0
rpterAuthor Commented:
ah ok, yep missed that, but now i get a 'Data type mismatch', again on the date fields.
0
RonaldBiemansCommented:
Could you post the new code ?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

rpterAuthor Commented:
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
0
ptakjaCommented:
Typically you surround the date value with "#" as

       objCmd.Parameters.Add("@Date", "#" & ContractDate & "#")
        objCmd.Parameters.Add("@AmmendedDate", "#" & AmmendedDate & "#")
0
rpterAuthor Commented:
already tried with that, it doesnt work.
0
RonaldBiemansCommented:
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
0
rpterAuthor Commented:
i still get the same error.
0
RonaldBiemansCommented:
Hmmm, I just tested it and it worked.

is contractdate a date field or a text field in a date format ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rpterAuthor Commented:
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.


0
RonaldBiemansCommented:
just to be sure could you post your code again?
0
rpterAuthor Commented:
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
0
rpterAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.