[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert date field into access with oledbcommand parameters

Posted on 2004-11-11
13
Medium Priority
?
3,065 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:rpter
  • 7
  • 5
13 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12555139
This is because Date is a reserved word, rename your date field to something else or enclose it in []
0
 

Author Comment

by:rpter
ID: 12555409
ah ok, yep missed that, but now i get a 'Data type mismatch', again on the date fields.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12555435
Could you post the new code ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:rpter
ID: 12555447
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
 
LVL 14

Expert Comment

by:ptakja
ID: 12555775
Typically you surround the date value with "#" as

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

Author Comment

by:rpter
ID: 12555841
already tried with that, it doesnt work.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12555970
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
 

Author Comment

by:rpter
ID: 12556140
i still get the same error.
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 1500 total points
ID: 12556239
Hmmm, I just tested it and it worked.

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

Author Comment

by:rpter
ID: 12556407
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12556485
just to be sure could you post your code again?
0
 

Author Comment

by:rpter
ID: 12563580
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
 

Author Comment

by:rpter
ID: 12563971
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question