vb.net - OleDbException was unhandled.......Syntax error in UPDATE statement

Question

I get that Error when i debug please can someone help please... Below is the code:

 Private Sub UpdateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateToolStripMenuItem.Click
    If MsgBox("Save Changes?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "WARNING") = MsgBoxResult.Yes Then
        Dim oleDC As New OleDbCommand
        With oleDC
            .Connection = conn
            .CommandText = "UPDATE tblPatientsRecord SET Names='" & txtNames.Text & _
        "',Licensenumber='" & txtLicensenumber.Text & _
        "',Address='" & txtAddress.Text & _
        "',Fullname='" & txtFullname.Text & _
        "',Birthday='" & txtBase.Text &
        "',Age='" & txtAge.Text & _
        "',Country='" & cmbCountry.Text & "'  WHERE PatientID='" & txtPatientID.Text & "'"
            .ExecuteNonQuery()
            MsgBox("Record Updated!", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "SUCCESS")
            Disable()
            Clear()
            SaveToolStripMenuItem.Enabled = False
            NewToolStripMenuItem.Enabled = True
            LoadPatientsRecord()
            getLastPatientID()
        End With
    End If
End Sub

help please

Answer 1

You should use SQL parameters. These will not only simplify your code, they will make certain kinds of errors regarding syntax and data types extremely unlikely and protect against SQL injection attacks:

Dim sql = <sql> 
        UPDATE tblPatientsRecord SET [Names] = @p1,
                Licensenumber = @p2, 
                Address = @p3,
                Fullname = @p4,
                Birthday = @p5, 
                [Age] = @p6,
                Country = @p7
        WHERE PatientID = @p8
        </sql>.Value

Using conn = New OleDbConnection(myConnStr),
        cmd As New OleDbCommand(sql, conn)

    conn.Open()

    cmd.Parameters.Add("@p1", OleDbType.VarChar).Value = txtNames.Text
    cmd.Parameters.Add("@p2", OleDbType.VarChar).Value = txtLicensenumber.Text
    ' ...etc
    cmd.Parameters.Add("@p6", OleDbType.Integer).Value = intVar
    cmd.Parameters.Add("@p7", OleDbType.VarChar).Value = strVar

    'the last one is the WHERE
    cmd.Parameters.Add("@p8", OleDbType.VarChar).Value = Convert.ToInt32(lblPatientID.Text)

    cmd.ExecuteNonQuery()
    '... etc

End Using

There are several other commonly seen issues which should be tended to.

1. DBConnection objects are intended to be created, used and disposed of rather than the same one used over and over. However, you can use a global connection string so you don't have the same connection string all over the place.

2. Many of the DBObjects should be disposed of. Using blocks will close and dispose of the connection and command objects. Generally, if something has Dispose method, wrap them in a Using block. The above shows how to "stack" 2 objects (OleDbConnection and OleDbCommand) into one Using statement which reduces indentation.

3. Use the Add method rather than AddWithValue. This allows you to specify the datataype for each parameter. Without it, the DB Provider must guess which can result in Datatype mismatch or even corrupt the database in some instances.

4. The WHERE clause is just another parameter. Often people will use Parameters for the first part of the SQL but concatenate for the WHERE clause needlessly.

5. The above also uses an XML literal to construct the SQL. This is handy for long, complex SQL because you can format and indent it as you like. You can also just use multiple lines to make it readable :

   Dim sql = "UPDATE tblPatientsRecord SET [Names] = @p1, " _ & "Licensenumber = @p2, " _ & "Address = @p3, "

6. If you use SQL reserved words or spaces in table or column names, you must escape the names using [Square Brackets] as shown. It is best not to use either in the names. User, Password Names and Values are commonly seen words used as column or table names which result in SQL syntax errors.

7. Ticks are not all-purpose SQL field delimiters, they actually indicate that the value being passed is string/text: & "Age ='" & txtAge.Text & "'". If the DB is set to store Age as a number, your SQL is passing it as text/string which can result in a data type mismatch. The same is true of PatientID and Birthday if it is a date field.

   A common problem concatenating strings for a SQL statements is too many or too few ticks (') in the result. This cant happen with SQL Parameters.

The main purpose for SQL Parameters, though is to prevent an error if the name is "O'Brian" or "O'Reilly" or "Bobby';DROP TABLE tblPatientsRecord"

These principles apply for other DB providers such asMySql, SQLite and SQLServer. The details such as the exact escape character however will vary.

Note that Access/OleDB doesn't actually use named parameters as such (@FirstName or even @p2), so will often see params in the form of "?". This means that you must add the parameter values (Add/AddWithValue) in the same exact order as those columns appear in the SQL.

For more information see:

• Using Statement
• Connection Pooling