Solved

Posted on 2009-02-19

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.

"System.Data.OleDb.OleDbEx

Here is some of the code

strSql = "Select Top 1 * From [debtors ledger];"

daDebtorsHead = New OleDb.OleDbDataAdapter(str

daDebtorsHead.Fill(dsDebto

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.InsertComman

I then fill dsDebtorsHead with all the records.

daDebtorsHead.Update(dsDeb

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()
```

5 Comments

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.OleDbEx

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.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

External app config and encryption of settings | 1 | 29 | |

Cannot convert sql query result in interger | 3 | 31 | |

Obtaining values from inside a HTMLDocument | 3 | 31 | |

Poor Man's .NET Framework Certification | 2 | 27 |

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

Connect with top rated Experts

**11** Experts available now in Live!