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

x
?
Solved

OleDb Update/Insert command

Posted on 2009-02-19
5
Medium Priority
?
844 Views
Last Modified: 2012-06-21
Am I doing this right. I keep getting the following error.
"System.Data.OleDb.OleDbException: No value given for one or more required parameters."

Here is some of the code

strSql = "Select Top 1 * From [debtors ledger];"
daDebtorsHead = New OleDb.OleDbDataAdapter(strSql, CnnDebtors)
daDebtorsHead.Fill(dsDebtorsHead, "DebtorsHead")

strSql = "INSERT INTO [debtors ledger] ([AREF], [REF], [ISSUE], [Doc_Type], [Number], [order_no], [DATE], [NETT], [VAT_RATE], [GROSS], [VAT], [PAID], [BAL], [COMPLETE], [Ledger_eff], [invoice_no], [Invoiced], [Printed], [TakenOut], [DocketName], [ContactName], [VATexempt], [Description], [Ref1], [Ref2], [Employee], [Department], [IsSale], [VatDiscount], [DebtorsBatchNo]) VALUES (@AREF, @REF, @ISSUE, @Doc_Type, @Number, @order_no, @DATE, @NETT, @VAT_RATE, @GROSS, @VAT, @PAID, @BAL, @COMPLETE, @Ledger_eff, @invoice_no, @Invoiced, @Printed, @TakenOut, @DocketName, @ContactName, @VATexempt, @Description, @Ref1, @Ref2, @Employee, @Department, @IsSale, @VatDiscount, @DebtorsBatchNo)"



cmdDHInsert = New OleDbCommand(strSql, CnnDebtors)
daDebtorsHead.InsertCommand = cmdDHInsert

I then fill dsDebtorsHead with all the records.

daDebtorsHead.Update(dsDebtorsHead, "DebtorsHead")

I have attached the code also.



Dim Ln As Double = 0
        Dim strSql As String = ""
 
        'Cnn
        Dim CnnDebtors As OleDbConnection 'MSAccess Debtors 'System.Data.OleDb.OleDbConnection 'MSAccess Debtors
        Dim CnnBD As SqlConnection = New SqlConnection ' SQL Branch Dockets
 
        'DebtorsHead
        Dim daDebtorsHead As OleDbDataAdapter
        Dim dsDebtorsHead As DataSet = New DataSet
        'Dim cmdBuildDebtorsHead As System.Data.OleDb.OleDbCommandBuilder
        Dim cmdDHInsert As OleDbCommand
 
        'DebtorsLine
        Dim daDebtorsLine As OleDbDataAdapter
        Dim dsDebtorsLine As DataSet = New DataSet
        Dim cmdBuildDebtorsLine As OleDbCommandBuilder
 
        'InvoiceHeader
        Dim daInvHead As SqlDataAdapter
        Dim dsInvHead As DataSet = New DataSet
        'InvoiceLine
        'Dim daInvLine As SqlDataAdapter
        'Dim dsInvLine As DataSet = New DataSet
        Dim drInvLines As SqlDataReader
        Dim cmdInvLines As SqlCommand = New SqlCommand
 
 
        Dim cmd As SqlCommand = New SqlCommand
 
 
        Try
            Ln = 1.01
            CnnDebtors = New System.Data.OleDb.OleDbConnection(strCnnDebtors)
            CnnDebtors.Open()
 
            Ln = 1.02
            strSql = "Select Top 1 * From [debtors ledger];"
            daDebtorsHead = New OleDb.OleDbDataAdapter(strSql, CnnDebtors)
            daDebtorsHead.Fill(dsDebtorsHead, "DebtorsHead")
            'cmdBuildDebtorsHead = New OleDb.OleDbCommandBuilder(daDebtorsHead)
            Ln = 1.021
            'daDebtorsHead.UpdateCommand = cmdBuildDebtorsHead.GetUpdateCommand
            'daDebtorsHead.InsertCommand = cmdBuildDebtorsHead.GetInsertCommand
 
            'strSql = "INSERT INTO [debtors ledger] ([AREF], [REF], [ISSUE], [Doc_Type], [Number], [order_no], [DATE], [NETT], [VAT_RATE], [GROSS], [VAT], [PAID], [BAL], [COMPLETE], [Ledger_eff], [invoice_no], [Invoiced], [Printed], [TakenOut], [DocketName], [ContactName], [VATexempt], [Description], [Ref1], [Ref2], [Employee], [Department], [IsSale], [VatDiscount], [DebtorsBatchNo]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            strSql = "INSERT INTO [debtors ledger] ([AREF], [REF], [ISSUE], [Doc_Type], [Number], [order_no], [DATE], [NETT], [VAT_RATE], [GROSS], [VAT], [PAID], [BAL], [COMPLETE], [Ledger_eff], [invoice_no], [Invoiced], [Printed], [TakenOut], [DocketName], [ContactName], [VATexempt], [Description], [Ref1], [Ref2], [Employee], [Department], [IsSale], [VatDiscount], [DebtorsBatchNo]) VALUES (@AREF, @REF, @ISSUE, @Doc_Type, @Number, @order_no, @DATE, @NETT, @VAT_RATE, @GROSS, @VAT, @PAID, @BAL, @COMPLETE, @Ledger_eff, @invoice_no, @Invoiced, @Printed, @TakenOut, @DocketName, @ContactName, @VATexempt, @Description, @Ref1, @Ref2, @Employee, @Department, @IsSale, @VatDiscount, @DebtorsBatchNo)"
            'strSql = "INSERT INTO [debtors ledger] ([Doc_Type], [Number]) VALUES (@Doc_Type, @Number)"
            cmdDHInsert = New OleDbCommand(strSql, CnnDebtors)
            daDebtorsHead.InsertCommand = cmdDHInsert
 
            '@AREF, @REF, @ISSUE, @Doc_Type, @Number, @order_no, @DATE, @NETT, @VAT_RATE, @GROSS, @VAT, @PAID, @BAL, @COMPLETE, @Ledger_eff, @invoice_no, @Invoiced, @Printed, @TakenOut, @DocketName, @ContactName, @VATexempt, @Description, @Ref1, @Ref2, @Employee, @Department, @IsSale, @VatDiscount, @DebtorsBatchNo
            Ln = 1.03
            strSql = "Select Top 1 * From [invoices lines];"
            daDebtorsLine = New OleDb.OleDbDataAdapter(strSql, CnnDebtors)
            daDebtorsLine.Fill(dsDebtorsLine, "DebtorsLine")
            cmdBuildDebtorsLine = New OleDb.OleDbCommandBuilder(daDebtorsLine)
 
            Ln = 1.031
            'daDebtorsLine.UpdateCommand = cmdBuildDebtorsLine.GetUpdateCommand
            'daDebtorsLine.InsertCommand = cmdBuildDebtorsLine.GetInsertCommand
 
 
            '"INSERT INTO debtors ledger (AREF, REF, ISSUE, Doc_Type, Number, order_no, DATE, NETT, VAT_RATE, GROSS, VAT, PAID, BAL, COMPLETE, Ledger_eff, invoice_no, Invoiced, Printed, TakenOut, DocketName, ContactName, VATexempt, Description, Ref1, Ref2, Employee, Department, IsSale, VatDiscount, DebtorsBatchNo) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
 
 
 
 
 
 
            Ln = 1.11
            CnnBD.ConnectionString = strCnnBranchDockets
            CnnBD.Open()
 
            Ln = 1.12
            strSql = "postGetInvHeadToPost"
            daInvHead = New SqlDataAdapter(strSql, CnnBD)
            daInvHead.Fill(dsInvHead, "InvHead")
 
 
 
            Ln = 1.13
            'strSql = "postGetInvLineToPost"
            'daInvLine = New SqlDataAdapter(strSql, CnnBD)
            'daInvLine.Fill(dsInvLine, "InvLine")
            cmdInvLines.Connection = CnnBD
            cmdInvLines.CommandType = CommandType.StoredProcedure
            cmdInvLines.CommandText = "postGetInvLineToPost"
            cmdInvLines.Parameters.Add("@BranchNo", SqlDbType.VarChar)
            cmdInvLines.Parameters.Add("@DocketId", SqlDbType.VarChar)
            cmdInvLines.Parameters.Add("@InvoiceNo", SqlDbType.VarChar)
 
 
 
 
 
 
            Ln = 2.01
            Dim rH As DataRow
            Dim rL As DataRow
 
            Dim DType As String = ""
            Dim LedgerEffect As Integer = 1
            Dim DocketName As String = ""
            Dim IsSale As Boolean = True
 
 
            Dim BranchNo As String = ""
            Dim DocketId As String = ""
            Dim InvoiceNo As String = ""
            Dim HOId As Integer = 0
 
            Dim hGross As Double = 0
            Dim hVat As Double = 0
            Dim hNett As Double = 0
            Dim BulkDiscount As Double = 0
            Dim VatExempt As Boolean
 
            Ln = 2.02
            'Post Head
            If dsInvHead.Tables("InvHead").Rows.Count > 0 Then
                For Each tmpRow As DataRow In dsInvHead.Tables("InvHead").Rows
 
                    Ln = 2.03
                    rH = dsDebtorsHead.Tables("DebtorsHead").NewRow
 
                    Ln = 2.04
                    BranchNo = tmpRow("BranchNo").ToString
                    Ln = 2.05
                    DocketId = tmpRow("DocketId").ToString
                    Ln = 2.06
                    InvoiceNo = tmpRow("InvoiceNo").ToString
                    Ln = 2.07
                    HOId = tmpRow("HOId")
                    Ln = 2.08
                    VatExempt = tmpRow("VatExempt")
                    Ln = 2.09
                    BulkDiscount = tmpRow("BulkDiscount").ToString
 
 
                    Ln = 2.11
                    Select Case DocketId
                        Case "rec"
                            Ln = 2.12
                            DType = "PAY"
                            LedgerEffect = -1
                            DocketName = "Receipt"
                            IsSale = False
                        Case "crn"
                            Ln = 2.13
                            DType = "CRE"
                            LedgerEffect = -1
                            DocketName = "Credit Note"
                            IsSale = True
                        Case "inv"
                            Ln = 2.14
                            DType = "INV"
                            LedgerEffect = 1
                            DocketName = "Invoice"
                            IsSale = True
                        Case Else
                    End Select
 
                    Ln = 2.21
                    If DocketId <> "rec" Then
                        Ln = 2.22
                        cmdInvLines.Parameters("@BranchNo").Value = BranchNo
                        cmdInvLines.Parameters("@DocketId").Value = DocketId
                        cmdInvLines.Parameters("@InvoiceNo").Value = InvoiceNo
 
                        Ln = 2.23
                        drInvLines = cmdInvLines.ExecuteReader
 
                        Ln = 2.24
                        hGross = 0
                        hVat = 0
                        hNett = 0
 
                        Dim lGross As Double = 0
                        Dim lVat As Double = 0
                        Dim lNett As Double = 0
                        Dim lVatRate As Double = 0
                        Dim lPrice As Double = 0
                        Dim lQuantity As Double = 0
 
                        Ln = 2.25
                        Do While drInvLines.Read
 
                            Ln = 3.01
                            rL = dsDebtorsLine.Tables("DebtorsLine").NewRow
 
                            Ln = 3.02
                            'Round([price]/(1+([vatrate]/100)),2)
                            'Vat: Round([price]-([price]/(1+([vatrate]/100))),2)
                            lPrice = drInvLines("Price")
                            lQuantity = drInvLines("Quantity")
                            lGross = lPrice * lQuantity
                            lVatRate = drInvLines("VatRate")
 
                            Ln = 3.03
                            If Not VatExempt Then
                                Ln = 3.04
                                lVat = Math.Round(lGross - (lGross / (1 + (lVatRate / 100))), 2)
                                lNett = lGross - lVat
                            Else
                                Ln = 3.05
                                lVat = 0
                                lNett = lGross
                            End If 'If Not VatExempt Then
 
                            Ln = 3.06
                            hGross += lGross
                            hVat += lVat
                            hNett += lNett
 
                            Ln = 3.11
                            rL("Doc_Type") = DType
                            Ln = 3.12
                            rL("INVOICE_NO") = InvoiceNo
                            Ln = 3.13
                            rL("NETT") = lNett
                            Ln = 3.14
                            rL("VAT_RATE") = lVatRate
                            Ln = 3.15
                            rL("GROSS") = lGross
                            Ln = 3.16
                            rL("VAT") = lVat
                            Ln = 3.17
                            rL("ITEM") = drInvLines("ProductCode").ToString
                            Ln = 3.18
                            rL("DESCRIPTION") = drInvLines("Description").ToString
                            Ln = 3.19
                            rL("INV_SENT") = False
                            Ln = 3.21
                            rL("PAID") = False
                            Ln = 3.22
                            rL("Quantity") = lQuantity
                            Ln = 3.23
                            rL("Returned") = 0
                            Ln = 3.24
                            rL("CurrencyPrice") = lPrice
                            Ln = 3.25
                            rL("DebtorsBatchNo") = DebtorsBatchNo
                            ' rL("s_Generation") = drInvLines("").ToString
                            'rL("s_GUID") = drInvLines("").ToString
                            'rL("s_Lineage") = drInvLines("").ToString
                            'rL("ReturnedDate") = drInvLines("").ToString
                            'rL("ChargeType") = drInvLines("").ToString
                            'rL("Sequence") = drInvLines("").ToString
                            'rL("MovementID") = drInvLines("").ToString
                            'rL("LineId") = drInvLines("").ToString
                            'rL("SalesOrderID") = drInvLines("").ToString
 
                            Ln = 3.31
                            dsDebtorsLine.Tables("DebtorsLine").Rows.Add(rL)
 
                        Loop
                        Ln = 3.32
                        drInvLines.Close()
 
                    Else
                        Ln = 3.41
                        hNett = tmpRow("TotalOwed")
                        hVat = BulkDiscount
                        hGross = hNett + hVat
                    End If 'If DocketId <> "rec" Then
 
 
 
 
                    Ln = 4.01
                    rH("REF") = tmpRow("CustomerID").ToString
                    Ln = 4.02
                    rH("Doc_Type") = DType
                    Ln = 4.03
                    rH("Number") = InvoiceNo
                    Ln = 4.04
                    rH("DATE") = tmpRow("TransDate").ToString
                    Ln = 4.05
                    rH("NETT") = hNett
                    Ln = 4.06
                    rH("GROSS") = hGross
                    Ln = 4.07
                    rH("VAT") = hVat
                    Ln = 4.08
                    rH("BAL") = hGross
                    Ln = 4.09
                    rH("COMPLETE") = True
                    Ln = 4.11
                    rH("Ledger_eff") = LedgerEffect
                    Ln = 4.12
                    rH("Invoiced") = True
                    Ln = 4.13
                    rH("Printed") = True
                    Ln = 4.14
                    rH("DocketName") = DocketName
                    Ln = 4.15
                    rH("VATexempt") = tmpRow("VATexempt").ToString
                    Ln = 4.16
                    rH("Ref1") = tmpRow("Reference").ToString
                    Ln = 4.17
                    rH("Ref2") = tmpRow("Reference2").ToString
                    Ln = 4.18
                    rH("Employee") = tmpRow("UserName").ToString
                    Ln = 4.19
                    rH("Department") = BranchNo
                    Ln = 4.21
                    rH("IsSale") = IsSale
                    Ln = 4.22
                    rH("VatDiscount") = 0
                    Ln = 4.23
                    rH("DebtorsBatchNo") = DebtorsBatchNo
 
                    rH("AREF") = "1"
                    rH("ISSUE") = "1"
                    rH("order_no") = "1"
                    rH("VAT_RATE") = 0
                    rH("PAID") = 0
                    rH("invoice_no") = "1"
                    rH("TakenOut") = Date.Now
                    rH("ContactName") = "1"
                    rH("Description") = "1" ' contains gen code of products in lines
 
                    Ln = 4.31
                    dsDebtorsHead.Tables("DebtorsHead").Rows.Add(rH)
 
                Next tmpRow
 
 
 
 
                'Check if I can use a transaction on the access
 
                daDebtorsHead.InsertCommand = cmdDHInsert
                daDebtorsHead.InsertCommand.ExecuteNonQuery()
 
 
 
                Ln = 4.41
                daDebtorsHead.Update(dsDebtorsHead, "DebtorsHead")
                Ln = 4.42
                daDebtorsLine.Update(dsDebtorsLine, "DebtorsLine")
 
            Else
                WriteToLog(PostLog, "No Records To Post.")
            End If ' dsInvHead.Tables("InvHead").Rows.Count > 0 Then
 
 
            Ln = 4.43
            PostToDebtors = True
 
 
 
 
 
        Catch ex As Exception
            Errs.LogError(ex, PostLog, "PostToDebtors Ln: " & Ln)
        End Try
 
 
 
        Ln = 4.51
        daDebtorsHead = Nothing
        dsDebtorsHead = Nothing
        'cmdBuildDebtorsHead = Nothing
        daDebtorsLine = Nothing
        dsDebtorsLine = Nothing
        cmdBuildDebtorsLine = Nothing
 
        daInvHead = Nothing
        dsInvHead = Nothing
        'daInvLine = Nothing
        'dsInvLine = Nothing
        drInvLines = Nothing
        cmdInvLines = Nothing
 
        cmd = Nothing
 
 
 
 
        CnnDebtors.Dispose()
        CnnBD.Dispose()

Open in new window

0
Comment
Question by:Eamon
  • 3
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
Ken Fayal earned 2000 total points
ID: 23680291
I'm not seeing where you actually put in the data for the @parameters.
0
 
LVL 1

Author Comment

by:Eamon
ID: 23680509
Kaptain
I was trying to use the OleDbCommandBuilder but was getting a syntax error in the insert command.
I think this was because of the tablename debtors ledger should have looked like this [debtors ledger].

I created my own insert command which had the [] and that got rid of the syntax error but then gave me the.
"System.Data.OleDb.OleDbException: No value given for one or more required parameters." error.

I can see what you are saying but I don't want to have to fill parameters I want to be able to fill my DataSet and the update the database using the DataAdapter.Update command.
I have tried using the commandbuilder and then replacing the commandtext with my own which includes the [] but it keeps setting the commandtext back to the original text.
0
 
LVL 1

Author Comment

by:Eamon
ID: 23682607
Do I have to run the Insertcommand for each record I am adding
0
 
LVL 9

Expert Comment

by:Ken Fayal
ID: 23684774
The idea of the DataAdapter is that you set the InsertCommand once and it is used to insert the data into the DataSet after you push it all in and do the update, but you still have to add rows to the dataset, so you have to run a loop to load in each row somewhere.  It doesn't just slurp the data in automatically.
0
 
LVL 1

Author Comment

by:Eamon
ID: 23690370
Thanks I think it is working now. I was filling the dataset but I was not declaring the paramaters. I usually use the commandbuilder so was not familiar with this. I will put the code up when finished.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month18 days, 17 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