CODEWITHSUNDEEP
sqlvb.netms-accessvba
Aaron
Aaron

Reputation: 85

What is wrong with this VB/SQL query

This is the error I receive while debugging.

{"Syntax error in string in query expression ''Laptop);'."}

This is the SQL statement I have that isn't working properly. No matter what I do it seems to add a random . at the end of the statement, and I have no idea why it's doing it.

Sql = "INSERT INTO Devices ( [Asset Number], [Service Tag], Manufacturer, ModelName, Location, Room, Cart, [Purchase Date], Department, [Device Type] ) VALUES('" & AssetNum & "','" & ServTag & "','" & Manu & "','" & Model & "','" & Location & "','" & Room & "','" & Cart & "','" & PurchDate & "','" & Department & "','" & DeviceType & ");"

This is a statement that I've made that works just fine. It's just one long string value. The reason I need the variables above to work is because they're linked to textboxes so the user can input the data.

Sql = "INSERT INTO Devices ( [Asset Number], [Service Tag], Manufacturer, ModelName, Location, Room, Cart, [Purchase Date], Department, [Device Type] )VALUES (10, 1234, 'Dell', 'Latitude E6410', 'John McIntire', 100, 0, '5/17/2015', 'Technology', 'Laptop');"

So yeah basically I'm stuck and can't figure out why the first query doesn't work. I've been stuck on it for awhile now and could really use some assistance.

This is how I execute the queries.

cmd = New OleDb.OleDbCommand(Sql)
        cmd.Connection = dbConn
        cmd.ExecuteNonQuery()

Upvotes: 0

Views: 113

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

You're missing a trailing single quote for the last field in the query, but just adding the quote is really the wrong way to solve this. You should do this instead:

Sql = "INSERT INTO Devices (" &
         "[Asset Number], [Service Tag], Manufacturer, ModelName, Location, Room, Cart, [Purchase Date], Department, [Device Type]" &
         ") VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"

Using cmd AS New OleDb.OleDbCommand(Sql, dbConn)
    'Have to guess at column types/lengths here. Use actual types/lengths from your DB
    cmd.Parameters.Add("@AssetNum", OleDbType.VarChar, 10).Value = AssetNum
    cmd.Parameters.Add("@ServTag", OleDbType.VarChar, 10).Value = ServTag
    cmd.Parameters.Add("@Manu", OleDbType.VarWChar, 20).Value = Manu
    cmd.Parameters.Add("@Model", OleDbType.VarWChar, 30).Value = Model
    cmd.Parameters.Add("@Location", OleDbType.VarWChar, 50).Value = Location
    cmd.Parameters.Add("@Room", OleDbType.Integer).Value = Room
    cmd.Parameters.Add("@Cart", OleDbType.Integer).Value = Cart
    cmd.Parameters.Add("@PurchDate", OleDbType.Date).Value = PurchDate
    cmd.Parameters.Add("@Department", OleDbType.VarWChar, 20).Value = Department
    cmd.Parameters.Add("@DeviceType", OleDbType.VarChar, 20).Value = DeviceType

     cmd.ExecuteNonQuery()
End Using

This does four things for you:

  1. It fixes not only the the quoting issue from this question, but it makes it easier to get the quotes right for all queries you write in the future.
  2. It automatically handles data for fields like Make, Model, and Location that may include an apostrophe.
  3. It fixes issues with formatting dates for the query. For example, you can assign a VB.Net DateTime object directly to that PurchDate query parameter, and not need to care about the format.
  4. It closes a huge security hole in your existing code.

Upvotes: 3

ElGavilan
ElGavilan

Reputation: 6904

You are missing a single quote at the end to close out the one before DeviceType:

"','" & DeviceType & ");"

Add it in and it should resolve your issue:

"','" & DeviceType & "');"

At any rate, what you currently have is vulnerable to SQL injection and parameterizing your query is really the way that you should go about fixing this.

Upvotes: 4

Related Questions