0

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()
Improve this question
7
  • Remove the leading single quote here: '" & DeviceType Commented May 26, 2015 at 15:18
  • 1
    @jarlh He should probably add a trailing single quote to close the first one out since DeviceType is likely a string. Commented May 26, 2015 at 15:19
  • Add a single quote after DeviceType like this: "','" & DeviceType & "');" Or use a parameter query and don't bother about quoted string values. Commented May 26, 2015 at 15:20
  • 7
    What is wrong... you are concatenating SQL rather than using Parameters. Extra/missing ticks cant happen (among other more devastating things) with Parameters. Commented May 26, 2015 at 15:20
  • 2
    Did someone say Little Bobby Tables? Commented May 26, 2015 at 15:29

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your eyes sir. I will accept your answer as soon as I get back from lunch, since there's a 4 minute wait.
could you direct me to any more documentation on parameterizing like that? I'm trying to get a better understanding of what they actually are doing for me.
3

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.
Improve this answer

3 Comments

I'll definitely look into this for future use. One good thing here is this will all be local, and only accessible from inside the network it's going on. If you have time could we maybe set up a chat on here. If you can't tell I'm not the most experienced coder, and I'm not 100% sure what all is happening in the above code.
The answer is based on features of ADO.Net msdn.microsoft.com/en-us/library/aa286484.aspx You are using a subset of ADO.Net when you use dynamic SQL from your code.
Okay so I went ahead and ran this through a backup of my project to see how it worked and I have it inputting the data correctly, but I still don't understand what exactly it's doing for me. On the other hand, I do see the major vulnerabilities with the way I had it originally. If possible I'd like an explanation, or a link to one on how exactly this is solving problems like the little bobby tables example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.