oledb datatable update using paramaters failing

Question

I am trying to write the update command for a oledb dataadapter. I have had a variety of errors. I think my stumbling point is the stupid field names that I can do nothing about. Perhaps someone can see my mistake?

The Database is access The table has many columns but I only want to update one. the column [Transaction Seq] is an AutoNumber the column to be updated [Code (IAO)] is text - 255 long

Error: Parameter [@Code (IAO)] has no default value.

my code:

oDAtblBound.SelectCommand = New OleDb.OleDbCommand(strSql, oCon)
Dim builder As OleDb.OleDbCommandBuilder = New OleDbCommandBuilder(oDAtblBound)
builder.QuotePrefix = "["
builder.QuoteSuffix = "]"
oDAtblBound.MissingSchemaAction = MissingSchemaAction.AddWithKey
oDAtblBound.Fill(oTables, "tblBound")

cmd = New OleDbCommand("UPDATE tblBound SET [Code (IAO)] = [@Code (IAO)] WHERE [Transaction Seq] = [@Transaction Seq]", oCon)

cmd.Parameters.Add("[Code (IAO)]", OleDbType.Char, 255, "[Code (IAO)]")
parameter = cmd.Parameters.Add("[Transaction Seq]", OleDbType.Char, 255, "[Transaction Seq]")
parameter.SourceVersion = DataRowVersion.Original
oDAtblBound.UpdateCommand = cmd

Your use of parameters is messed up. You need to set a .Value for the first one to what you want to the update value. If [Transaction Seq] is an AutoNumber, why do you define the it as Char? That one has no `.Value` either. see http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparametercollection(v=vs.110).aspx — Ňɏssa Pøngjǣrdenlarp, Jul 21 '14 at 13:02
Why you want to make your life difficult using column names and parameter names with spaces is beyond me. — Steve, Jul 21 '14 at 13:03

score 1 · Accepted Answer · answered Jul 21 '14 at 12:55

Change your code to.....

cmd = New OleDbCommand("UPDATE tblBound SET [Code (IAO)] = @Code_IOA WHERE [Transaction Seq] = @Transaction_Seq", oCon)

cmd.Parameters.Add("Code_IAO", OleDbType.Char, 255, "Code (IAO)")
parameter = cmd.Parameters.Add("Transaction_Seq", OleDbType.Char, 255, "Transaction Seq")
parameter.SourceVersion = DataRowVersion.Original
oDAtblBound.UpdateCommand = cmd

oledb datatable update using paramaters failing

1 Answers1