[RESOLVED] ADO.NET Update Code with Parameter - How?

[Mark Gambo]

Here is a snippet of code that I am using to update a row in a DB:

       Dim MyCONNECTIONSTRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
        Dim DBConnection As New OleDb.OleDbConnection(MyCONNECTIONSTRING)
        Dim strSQL As String = "UPDATE Customers SET Rank = + @SearchFor + WHERE ID=1;"
        Dim OLECmd As New OleDb.OleDbCommand(strSQL)

        Dim SelectParameter As OleDb.OleDbParameter = OLECmd.CreateParameter
        SelectParameter.ParameterName = "@SearchFor"
        SelectParameter.Value = "Test Rank"
        OLECmd.Parameters.Add(SelectParameter)

        DBConnection.Open()
        OLECmd.ExecuteNonQuery()

I can't seem to be able to get this code to work by using the Parameter, any ideas?

Thanks!

[Aspnot]

I have not coded statements in this manner, but I would guess that you should change strSQL to take out the + signs. I usually just use a ? in mine and then make sure that if there are multiple parameters that you put them in the correct order.

I usually create my parameters as follows:

cmd.Parameters.Add(New OldDb.OleDbParameter("@CodeNo", SqlDbType.SmallInt, 2, "CodeNo")).Value = "Test Rank"

That just saves you from having to create and deal with a Parameter object. It is all created and added in one line.

[dee-u]

Shouldn't it be...

       Dim MyCONNECTIONSTRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
        Dim DBConnection As New OleDb.OleDbConnection(MyCONNECTIONSTRING)
        Dim strSQL As String = "UPDATE Customers SET Rank = ? WHERE ID=1;"
        Dim OLECmd As New OleDb.OleDbCommand(strSQL)

        Dim SelectParameter As OleDb.OleDbParameter = OLECmd.CreateParameter
        SelectParameter.ParameterName = "Rank"
        SelectParameter.Value = "Test Rank"
        OLECmd.Parameters.Add(SelectParameter)

        DBConnection.Open()
        OLECmd.ExecuteNonQuery()

? or @Rank

[Aspnot]

I don't think so. If you name the parameter @SearchFor in the SQL statement, then you have to set the command object's ParameterName property equal to the same name.

[dee-u]

That's how I did it here though I maybe missing something since I am just starting with C#...

[Mark Gambo]

dee-u,

I tried your code but I had to add "@Rank" in order to get it to work.

 Dim MyCONNECTIONSTRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test1.mdb"
        Dim DBConnection As New OleDb.OleDbConnection(MyCONNECTIONSTRING)
        Dim strSQL As String = "UPDATE Customers SET Rank = @Rank WHERE ID=1;"
        Dim OLECmd As New OleDb.OleDbCommand(strSQL)

        Dim SelectParameter As OleDb.OleDbParameter = OLECmd.CreateParameter
        SelectParameter.ParameterName = "@Rank"
        SelectParameter.Value = "Test Rank"
        OLECmd.Parameters.Add(SelectParameter)

        DBConnection.Open()
        OLECmd.ExecuteNonQuery() '<===Error

But I am getting an error on the last line: "ExecuteNonQuery: Connection property has not been initialized."

Any ideas?

[jmcilhinney]

At no point have you assigned your connection object to the Connection property of the command object. You must either pass it as a parameter in the constructor or set the property explicitly. Also, you can use un-named parameters (?) with any ParameterName you like. As was said, you just need to make sure they are added in the same order as they appear in the SQL code. In fact, if you access SQL Server via OleDb then you cannot used named parameters and you must use "?" place-holders.

[Shuja Ali]

Modify this statement to look like this

Dim OLECmd As New OleDb.OleDbCommand(strSQL, DBConnection)

[Mark Gambo]

Modify this statement to look like this

Dim OLECmd As New OleDb.OleDbCommand(strSQL, DBConnection)

Shuja,
Thank, that did the trick!

Mark