CODEWITHSUNDEEP
asp.netvb.netms-access
user3245876
user3245876

Reputation: 11

Error: "No value given for one or more required parameters" on UPDATE query from ASP.NET

I receive the error

No value given for one or more required parameters

when I try to execute the following code

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

    Dim t2 = TextBox2.Text

    Dim dbcmd As OleDbCommand
    dbcmd = New OleDbCommand("UPDATE login1 SET height ='" + TextBox2.Text + "' WHERE username =" + str, dbcon) //This is the part where the error is ....
    dbcon.Open()
    dbcmd.ExecuteNonQuery()
    dbcon.Close()

    End Sub
End Class

I've tried making changes in this statement like using different methods with parameters...but all the code shows the same error.

Upvotes: 1

Views: 1158

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123654

Using a parameterized query is definitely the way to go, but using named parameters should be discouraged in this context because OleDbCommand objects ignore the parameter names when the CommandType is Text. They only rely on the order in which the parameters appear in the CommandText (ref: here).

Therefore, the preferred approach would be

Using dbcmd As New OleDbCommand(
            "UPDATE login1 SET height=? WHERE username=?",
            dbcon)
    dbcmd.Parameters.AddWithValue("?", TextBox2.Text)  ' height
    dbcmd.Parameters.AddWithValue("?", str)  ' username
    dbcmd.ExecuteNonQuery()
End Using

Upvotes: 2

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

You don't supply the user name properly at the end of the query. But that's not the only problem here. Let me edit the code a bit:

Using dbcon As New OleDbConnection(cString)
    dbcon.Open()

    Using dbcmd As New OleDbCommand(
        "UPDATE login1 SET height = @height WHERE username = @username",
        dbcon)
        dbcmd.Parameters.AddWithValue("@height", TextBox2.Text)
        dbcmd.Parameters.AddWithValue("@username", str)

        dbcmd.ExecuteNonQuery()
    End Using
End Using

NOTE: the using statement to ensure the objects are disposed property and also, do not share connections. When you need a connection, build it, open it, use it, and dispose it.

Upvotes: 1

Related Questions