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

Tech-Archive recommends: Fix windows errors by optimizing your registry

On 1 Aug 2005 09:58:01 -0700, "Sean" <nbSean@xxxxxxxxx> wrote:

¤ 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)
.

Relevant Pages

  • Re: Passing of Parameter from Form to a Data Report
    ... I've tried it and it only works on a single command without a child command. ... "Paul Clement" wrote: ... > ¤ Thanks for the reply. ...
    (microsoft.public.vb.general.discussion)
  • Re: Probem: No size set for variable length data type: String, using OracleDataAdapter
    ... Thanks paul for your advice, I know using the command would solve the ... aspect me to do the changes to solve the empty string issue here. ... > ¤ Hi Paul, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Error on adding a record to an Access Database.
    ... Have you tried setting the OleDbCommandBuilder.QuotePrefix and QuoteSuffix ... access uses a backwards single quote for the QuotePrefix and normal single ... > I had to add the following line just before my da.update command though to ... >> ¤ I tried to write a record to an access database with the following code ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Creating Multiple MS Access Tables
    ... I have been working on the command text of my table creation command: ... When this runs I get a general Syntax error, ... ¤ I have given my users the ability to create a new MS Access database. ... multiple constraints would be greatly appreciated. ...
    (microsoft.public.dotnet.general)
  • Re: Error on adding a record to an Access Database.
    ... I originally had other fields in the database but I stripped them all out ... I had to add the following line just before my da.update command though to ... > ¤ I tried to write a record to an access database with the following code ... > ¤ Microsoft JET Database Engine ...
    (microsoft.public.dotnet.framework.adonet)