New to ADO.net (AccessDB update question)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,
this is my first vb.net program.
I'm trying to update an Access database from a DataRow, see code below. It
keeps giving the Error : "Parameter @CustomerDNote has no default value".
Initially I tried without creating a separate oledbconnection.
I'm sure it's something simple that Ive done wrong, but just cant see it.
Thanks

Public Sub EditCreditDetails2(ByVal RowNum As Integer, ByVal ID As Integer,
ByVal strCustDnote As String, ByVal dblCreditValue As Double, ByVal
strCreditNoteNo As String)
Try
Dim con2 As New OleDbConnection

Dim editRow As DataRow =
dstInvoice.Tables("CreditDetails").Rows(RowNum)
Dim sSQL As String

con2.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings.Get("AccessConnString")
con2.Open()

editRow.BeginEdit()
editRow("CustomerDNote") = strCustDnote
editRow("AmountCredited") = dblCreditValue
editRow("CreditFlag") = True
editRow("DateCredited") = Now
editRow("CreditNote") = strCreditNoteNo
editRow.EndEdit()

sSQL = "UPDATE InvoiceLines SET "
sSQL = sSQL & " CustomerDNote=@CustomerDNote,
AmountCredited=@AmountCredited, "
sSQL = sSQL & "
CreditFlag=@CreditFlag,DateCredited=@DateCredited,"
sSQL = sSQL & " CreditNote=@CreditNote "
sSQL = sSQL & " WHERE InternalID=@InvLineID"

Dim commUpdate As New OleDbCommand(sSQL, con2)
commUpdate.Parameters.Add("@InvLineID", OleDbType.Integer,
Nothing, "InternalID")
commUpdate.Parameters.Add("@CustomerDNote", OleDbType.VarChar,
10, "CustomerDNote")
commUpdate.Parameters.Add("@AmountCredited", OleDbType.Currency,
Nothing, "AmountCredited")
commUpdate.Parameters.Add("@CreditFlag", OleDbType.Boolean,
Nothing, "CreditFlag")
commUpdate.Parameters.Add("@DateCredited", OleDbType.Date,
Nothing, "DateCredited")
commUpdate.Parameters.Add("@CreditNote", OleDbType.VarChar, 10,
"CreditNote")

commUpdate.Connection = New OleDbConnection(con2.ConnectionString)

adpSurcharge.UpdateCommand = commUpdate
adpSurcharge.Update(dstInvoice, "CreditDetails")
Catch eException As Exception
MessageBox.Show(eException.Message)

End Try
End Sub
 
The OLE DB .NET Data Provider does not support named parameters. Your
query:

UPDATE InvoiceLines
SET CustomerDNote=@CustomerDNote, AmountCredited=@AmountCredited,
CreditFlag=@CreditFlag, DateCredited=@DateCredited,
CreditNote=@CreditNote
WHERE InternalID=@InvLineID

should use the parameter marker "?" and look like:

UPDATE InvoiceLines
SET CustomerDNote=?, AmountCredited=?, CreditFlag=?,
DateCredited=?, CreditNote=?
WHERE InternalID=?

Since you're using parameter markers, you'll need to add parameters to
your Command's Parameters collection in the order they appear in the query.

The error you described doesn't ring a bell. It almost sounds like
one of the parameters you created did not match a column in your DataTable.
If using the ? parameter markers does not resolve your problem, you may
want to simplify the query to try to isolate the problem.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
 
ann,

Don't use @ symbol for Access databases for parameter queries, you only
really need to use the name of te parameter.

HTH

SpotNet.

: hi,
: this is my first vb.net program.
: I'm trying to update an Access database from a DataRow, see code below. It
: keeps giving the Error : "Parameter @CustomerDNote has no default value".
: Initially I tried without creating a separate oledbconnection.
: I'm sure it's something simple that Ive done wrong, but just cant see it.
: Thanks
:
: Public Sub EditCreditDetails2(ByVal RowNum As Integer, ByVal ID As
Integer,
: ByVal strCustDnote As String, ByVal dblCreditValue As Double, ByVal
: strCreditNoteNo As String)
: Try
: Dim con2 As New OleDbConnection
:
: Dim editRow As DataRow =
: dstInvoice.Tables("CreditDetails").Rows(RowNum)
: Dim sSQL As String
:
: con2.ConnectionString =
:
System.Configuration.ConfigurationSettings.AppSettings.Get("AccessConnString")
: con2.Open()
:
: editRow.BeginEdit()
: editRow("CustomerDNote") = strCustDnote
: editRow("AmountCredited") = dblCreditValue
: editRow("CreditFlag") = True
: editRow("DateCredited") = Now
: editRow("CreditNote") = strCreditNoteNo
: editRow.EndEdit()
:
: sSQL = "UPDATE InvoiceLines SET "
: sSQL = sSQL & " CustomerDNote=@CustomerDNote,
: AmountCredited=@AmountCredited, "
: sSQL = sSQL & "
: CreditFlag=@CreditFlag,DateCredited=@DateCredited,"
: sSQL = sSQL & " CreditNote=@CreditNote "
: sSQL = sSQL & " WHERE InternalID=@InvLineID"
:
: Dim commUpdate As New OleDbCommand(sSQL, con2)
: commUpdate.Parameters.Add("@InvLineID", OleDbType.Integer,
: Nothing, "InternalID")
: commUpdate.Parameters.Add("@CustomerDNote", OleDbType.VarChar,
: 10, "CustomerDNote")
: commUpdate.Parameters.Add("@AmountCredited",
OleDbType.Currency,
: Nothing, "AmountCredited")
: commUpdate.Parameters.Add("@CreditFlag", OleDbType.Boolean,
: Nothing, "CreditFlag")
: commUpdate.Parameters.Add("@DateCredited", OleDbType.Date,
: Nothing, "DateCredited")
: commUpdate.Parameters.Add("@CreditNote", OleDbType.VarChar, 10,
: "CreditNote")
:
: commUpdate.Connection = New
OleDbConnection(con2.ConnectionString)
:
: adpSurcharge.UpdateCommand = commUpdate
: adpSurcharge.Update(dstInvoice, "CreditDetails")
: Catch eException As Exception
: MessageBox.Show(eException.Message)
:
: End Try
: End Sub
:
 
David,

Maybe you can look once too it, as far as I have been able to see it, is all
on MSDN about the OleDbParameters almost completely written in the same way
as it is done with SQLparameters.

Also the fact that the OleDb parameter has to be added sequentially and that
when you reuse them in another direction should be done again is not (or
hard) to find.

By the way, it works well in that way, however the way you describe it, and
as it is build by the wizard, have I never found on MSDN.

Just a little thing that came up in my mind.

Cor
 
Thanks, Cor. I'll talk to the user education team about it.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.
 
Back
Top