Receiving an error when attempting to update a record

Question

In my program I have a function titled runSQL, here it is:

Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TrainingLog.accdb")
    Dim DT As New DataTable
    Dim DataAdapter As OleDb.OleDbDataAdapter

    Connection.Open()
    DataAdapter = New OleDb.OleDbDataAdapter(query, Connection)
    DT.Clear()
    DataAdapter.Fill(DT)
    Connection.Close()
    Return DT

And I'm trying to update a record in a database using the update string, sourced from this code:

    Dim sqlString As String

    sqlString = "UPDATE UserRecords set FirstName = '" & txtName.Text
    sqlString = sqlString & "', LastName = '" & txtSurname.Text

    If ChkSmoker.Checked = True Then
        sqlString = sqlString & "', Smoker = true"
    ElseIf ChkSmoker.Checked = False Then
        sqlString = sqlString & "', Smoker = false"
    End If

    sqlString = sqlString & ", Weight = " & txtWeight.Text

    If RdoMale.Checked = True Then
        sqlString = sqlString & ", Gender = 'm'"
    ElseIf RdoFemale.Checked = True Then
        sqlString = sqlString & ", Gender = 'f'"
    End If

    sqlString = sqlString & " WHERE UserName = '" & LstUsers.SelectedItem.ToString & "'"

    runSQL(sqlString)

However once I click the save button, I get an error from line 7 of the runSQL function (not including empty line, so that's the DataAdapter.Fill(DT) line) which says "No value given for one or more required parameters."

I wondered if anyone knew why this is or how to fix it.

One thing I did think of is that, in the table being updated, there are fields other than those being mentioned in my UPDATE statement. For example there is a Yes/no field titled "TeamMember", which I don't mention in the update statement. When using the update function, do I have to give values for every field, even those not being changed?

Thanks for reading, and hopefully helping!

Answer 1

You should never composea SQL query yourself. It much easies and safer (to vaoid SQL injection) to create a parameterized query, or use an stored procedure. And then execute it by pasing the query or stored procedure name and the parameter values.

Besides, in this way, you don't have to take care of what the right format is for a particular value. For example, how do you format a date? And, how do you format a boolean value? Most probably the problem with your query is the false or true value that you're trying to set for the Smoker column, because in TSQL that's a bit value, and can only be 0 or 1.

Check this to see samples of using parameters: ADO.NET Code Examples (Click the VB tab to see it in VB). You'll see that you define a parameter specifying a name with an @ prefix in the query, and then you simply pass a value for each parameter in the query, and it will be passed to the server in the correct format without you taking care of it.

Taken from one of the samples:

Dim queryString As String = _
        "SELECT ProductID, UnitPrice, ProductName from dbo.Products " _
        & "WHERE UnitPrice > @pricePoint " _
        & "ORDER BY UnitPrice DESC;"
Dim command As New SqlCommand(queryString, connection)
command.Parameters.AddWithValue("@pricePoint", paramValue)

'' command.ExecuteXXX 

NOTE that you can execute the command in different ways, depending on your need to simply execute it or get an scalar value or a full dataset as a result.