How to do a simple update command with ado.net?

[Sean]

I am trying to execute a simple SQL Update command on an Access
database. I have an OleDBConnection (gDatabaseConn) that has
successfully been executing select commands, but I cannot get an update
command to work. Here is the code in question:

Dim cmdUpdate As New OleDb.OleDbCommand("", gDatabaseConn)

sSQL = "UPDATE NoteTable SET AcctID = '" & Account.sAcctID
sSQL = sSQL & "' WHERE AcctNo = '" & Account.sAccountNo & "'"

cmdUpdate.CommandText = sSQL
cmdUpdate.ExecuteNonQuery()

The call to ExecuteNonQuery throws an exception with the message "No
value given for one or more required parameters". Both of the
variables used in the string have values. Is ADO.NET capable of
running a simple SQL command like this, or do I need to investigate
OleDBParameter objects? This way would be preferrable, since I am not
using DataTables or DataRows.

 

[William \(Bill\) Vaughn]

90% of the time it's best to create a Parameter-based Command to handle
queries--even action commands. The Command deals with a litany of issues
that you might never think of until you try to figure out what's wrong.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Earl]

I believe your SQL is incorrect. Note the single parens differences. Set a
breakpoint past your SQL line and then pass your cursor over the top to
check the string syntax. But as Bill says, command objects make it much
easier to find these sorts of issues.

sSQL = "UPDATE NoteTable SET AcctID = "'" & Account.sAcctID & "'"
sSQL = sSQL & " WHERE AcctNo = " & "'" & Account.sAccountNo & "'"

 

[Paul Clement]

¤ I am trying to execute a simple SQL Update command on an Access
¤ database. I have an OleDBConnection (gDatabaseConn) that has
¤ successfully been executing select commands, but I cannot get an update
¤ command to work. Here is the code in question:
¤
¤ Dim cmdUpdate As New OleDb.OleDbCommand("", gDatabaseConn)
¤
¤ sSQL = "UPDATE NoteTable SET AcctID = '" & Account.sAcctID
¤ sSQL = sSQL & "' WHERE AcctNo = '" & Account.sAccountNo & "'"
¤
¤ cmdUpdate.CommandText = sSQL
¤ cmdUpdate.ExecuteNonQuery()
¤
¤ The call to ExecuteNonQuery throws an exception with the message "No
¤ value given for one or more required parameters". Both of the
¤ variables used in the string have values. Is ADO.NET capable of
¤ running a simple SQL command like this, or do I need to investigate
¤ OleDBParameter objects? This way would be preferrable, since I am not
¤ using DataTables or DataRows.

See if the following example helps:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<database path goes here>"

Dim gDatabaseConn As New System.Data.OleDb.OleDbConnection(ConnectionString)
gDatabaseConn.Open()

Dim cmdUpdate = New System.Data.OleDb.OleDbCommand("UPDATE NoteTable SET AcctID = ? WHERE
AcctNo = ?", gDatabaseConn)

Dim QueryParameter As New OleDbParameter("@Param1", OleDbType.VarChar)
QueryParameter.Value = Account.sAcctID
cmdUpdate.Parameters.Add(QueryParameter)

QueryParameter = New OleDbParameter("@Param2", OleDbType.VarChar)
QueryParameter.Value = Account.sAccountNo
cmdUpdate.Parameters.Add(QueryParameter)

cmdUpdate.CommandType = CommandType.Text

cmdUpdate.ExecuteNonQuery()

gDatabaseConn.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)