SQL Updating in VB.NET using OLEDB

Question

I am trying to update an Access database from an XML file using VB.NET and OleDB. I am using the following code but cannot get the update to work. I am not getting any errors but no update is happening - it seems to be treating CRSID as a string despite .DbType.Int32. Debug output shows all the correct values and a similar method for an Insert SQL works fine. If I hard code a value in CRSID the db is updated but the data is in the wrong columns LastName contains CRSID and FirstName contains LastName. Hope someone can help.

For Each drCurrent In dt.Rows
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText =
                    <SQL>
                        Update NewPMS SET LastName = @LastName, FirstName = @Firstname WHERE CRSID = @CRSID
                    </SQL>.Value
                }
                cmd.Parameters.AddRange(New OleDb.OleDbParameter() _
                    {
                      New OleDb.OleDbParameter With {.ParameterName = "@CRSID", .DbType = DbType.Int32},
                      New OleDb.OleDbParameter With {.ParameterName = "@LastName", .DbType = DbType.String},
                      New OleDb.OleDbParameter With {.ParameterName = "@FirstName", .DbType = DbType.String}
                    }
                )
                cmd.Parameters(0).Value = drCurrent("CRSID")
                cmd.Parameters(1).Value = drCurrent("LastName")
                cmd.Parameters(2).Value = drCurrent("FirstName")
                cn.Open()
                cmd.ExecuteNonQuery()
                cn.Close()
                cmd.Parameters.Clear()
            End Using
        Next

Steve · Accepted Answer · 2016-09-27 21:15:10Z

In OleDb parameters are positional. They are not recognized by their name. (See the Remarks section on OleDbCommand.Parameters collection)

The parameter placeholder for @CRSID appears as the last one in your query, but the first OleDbCommand.Parameter is, named for, and filled with the value for @CRSID.

This means that all your parameters are used for the wrong fields or condition and you end up with a where clause totally wrong. The command searches the CRSID field with the value of FirstName (and, pretty sure, it cannot find any match).

The correct code for your sql is

Update NewPMS SET 
    LastName = @LastName, 
    FirstName = @Firstname 
WHERE CRSID = @CRSID

....

cmd.Parameters.AddRange(New OleDb.OleDbParameter() _
    {
      New OleDb.OleDbParameter With {.ParameterName = "@LastName", .DbType = DbType.String},
      New OleDb.OleDbParameter With {.ParameterName = "@FirstName", .DbType = DbType.String}
      New OleDb.OleDbParameter With {.ParameterName = "@CRSID", .DbType = DbType.Int32},
    }
)
cmd.Parameters(0).Value = drCurrent("LastName")
cmd.Parameters(1).Value = drCurrent("FirstName")
cmd.Parameters(2).Value = drCurrent("CRSID")

As a side note, you should move the initialization of the OleDbCommand outside the loop, create the empty parameters and open the connection. Inside the loop, just set the values for the parameters from the current row and execute the command. (Don't close the connection until the end of the loop of course). This logic is better and without side effects, but it could give a slight better performance if you have many rows.

Collectives™ on Stack Overflow

SQL Updating in VB.NET using OLEDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related