VB.net SQL Syntax

Question

For some school work I was given sourcecode in order to update data in an access database. However, when trying to use it I literally have no clue what the correct syntax is.

Here's the source code function to update it:

Sub UpdateData(ByVal dataToUpdate As String, ByVal updateCriteria As String)
    'Assemble SQL query to update the specified record(s) with the specified value(s)
    Dim sql As String = "UPDATE " & DBtable & " SET " & dataToUpdate & " WHERE " _
        & updateCriteria

    'Create an instance of data adapter (if not created already)
    myDataAdapter = New OleDb.OleDbDataAdapter()

    'Add command to update data (using data adaptor) based on SQL query above.
    myDataAdapter.UpdateCommand = New OleDb.OleDbCommand(sql, myCon)
    'Execute command to update data in the relevant database record(s)
    myDataAdapter.UpdateCommand.ExecuteNonQuery()

    MsgBox(sql)
End Sub

And this is what I'm trying to use to execute it, but I can't get the syntax right:

UpdateData("first_name = '" & Firstnamebox.Text & "' AND last_name = '" _
    & Lastnamebox.Text & "' AND middle_name = '" & Middlenamebox.Text _
    & "' AND age = '" & Agebox.Text & "' AND AdditionalInfo = '" & AddInfoBox.Text _
    & "' AND User_level = '" & UserLevelBox.Text & "' AND username =' " _
    & Usernamebox.Text & "' AND [password] = '" & Passwordbox.Text & "'",
    "ID = '" & id & "'")

Any idea's on where i'm going wrong?

Thanks,

You should use Prepared Statements instead of patching the query together like this — juergen d
– juergen d, Commented Mar 2, 2014 at 16:07
You really should, see my answer in this post: stackoverflow.com/a/22130028/1842065 — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Mar 2, 2014 at 16:22
I cannot stress the importance of using parameters. It simplifies assigning values and prevents injection attacks. For example, you are placing single quotes around the Age field, which is probably numeric and will cause a type mismatch error. — ron tornambe
– ron tornambe, Commented Mar 2, 2014 at 16:34

Olivier Jacot-Descombes · Accepted Answer · 2014-03-03 14:15:23Z

You don't need the data adapter. A connection and a command is all you need. Also make sure the connection is open. Maybe you do it somewhere else in your code.

Dim command = New OleDb.OleDbCommand(sql, myCon)
myCon.Open()
command.ExecuteNonQuery()

The UPDATE command goes like this:

UPDATE myTable
SET col1 = value1, col2 = value2, col3 = value3, ...
WHERE ...

You are stiching the SQL command together with string concatenation. Better use command parameters.

UPDATE myTable
SET col1 = @1, col2 = @2, col3 = @3
WHERE ID = @4

Dim command = New OleDb.OleDbCommand(sql, myCon)
command.Parameters.AddWithValue("@1", value1)
command.Parameters.AddWithValue("@2", value2)
command.Parameters.AddWithValue("@3", value3)
command.Parameters.AddWithValue("@4", id)
myCon.Open()
command.ExecuteNonQuery()

There are several advantages in doing so:

You don't have to care about the right formatting of your data for SQL. Especially date values can be tricky to format.
You don't have problems with texts containing string delimiters.
It prevents you from SQL injection attacks where hackers enter a nasty SQL-statement instead of a user name, for instance.
It is easier to read.

E Mett · Accepted Answer · 2014-03-03 08:08:17Z

0

Replace the AND in the SQL statement with a comma ,

UpdateData("first_name = '" & Firstnamebox.Text & "', last_name = '" & Lastnamebox.Text & "', middle_name = '" & Middlenamebox.Text & "', age = " & Agebox.Text & ", AdditionalInfo = '" & AddInfoBox.Text & "', User_level = '" & UserLevelBox.Text & "', username =' " & Usernamebox.Text & "', [password] = '" & Passwordbox.Text & "'", "ID = '" & id & "'")

edited Mar 3, 2014 at 8:08

answered Mar 2, 2014 at 16:39

E Mett

2,3223 gold badges19 silver badges39 bronze badges

2 Comments

Naffel Over a year ago

So how would I do this with the syntax?

Naffel Over a year ago

I'm getting a mismatch error, I think it's because Age is an integer, not a string; how would I change this?

Collectives™ on Stack Overflow

VB.net SQL Syntax

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related