Syntax error (missing operator) in query

Question

I'm trying to add a review to a site I'm building. As the content of the review is split into 3 tables I am trying to execute 3 inserts into the database for the one review. The page displays when I run it so I know most of it is working but when I click the submit button I get a:

Syntax error (missing operator) in query expression '3')'' error message.

It's saying that problem is in this line of code:

Line 86:         dbInsert.ExecuteNonQuery()

This is the code I have for this section:

Dim sql As String = "INSERT INTO MovieReviews (MovieID, MReviewID, ReviewerType, ReviewDate, UserID) "
    sql = sql & " VALUES ('" & movID & "','" & review_id & "','" & 2 & "','" & Date.Now & "'," & uID & "')'"
    Dim sql2 As String = "INSERT INTO MReviewRatings (MReviewID, ValueForMoney, ActingAbility, SpecialEffects, Plot, Total) "
    sql2 = sql2 & " VALUES ('" & movID & "','" & moneyStar(moneyStarRating) & "','" & actingStar(actingStarRating) & "','" & effectsStar(effectStarRating) & "','" & plotStar(plotStarRating) & "','" & totalStar(avg) & "')'"
    Dim sql3 As String = "INSERT INTO MReviewTexts (MReviewID, ReviewText) "
    sql3 = sql3 & " VALUES ('" & review_id & "','" & txtReviewText.Text & "')'"
    dbInsert.CommandText = sql
    dbInsert.CommandType = CommandType.Text
    dbInsert.Connection = aConnection

    dbInsert2.CommandText = sql2
    dbInsert2.CommandType = CommandType.Text
    dbInsert2.Connection = aConnection
    dbInsert3.CommandText = sql3
    dbInsert3.CommandType = CommandType.Text
    dbInsert3.Connection = aConnection
    dbInsert.ExecuteNonQuery()
    dbInsert2.ExecuteNonQuery()
    dbInsert3.ExecuteNonQuery()

I'm not sure what is causing the issue. Would anyone know how I can get the review to insert into the database?

if I were you, I'd log out the value of sql and look at it to determine the problem. — StevieG
– StevieG, Commented Dec 18, 2012 at 16:35

NYCdotNet · Accepted Answer · 2012-12-18 16:54:28Z

1

By implementing your code in this way, you are exposing yourself to SQL injection attacks as freefaller has noted.

You would be much better off writing your queries as such:

Dim sql As String = "INSERT INTO MovieReviews (MovieID, MReviewID, ReviewerType, ReviewDate, UserID) " & _
    " VALUES (@movID,@review_id,@reviewerType,@timestamp,@userid)"
dbInsert.CommandText = sql
dbInsert.CommandType = CommandType.Text
dbInsert.Connection = aConnection
dbInsert.Parameters.Add(New SQLParameter("@movID",movID))
dbInsert.Parameters.Add(New SQLParameter("@review_id",review_id ))
dbInsert.Parameters.Add(New SQLParameter("@reviewerType",2))
dbInsert.Parameters.Add(New SQLParameter("@timestamp",Date.Now))
dbInsert.Parameters.Add(New SQLParameter("@userid",uID))
dbInsert.ExecuteNonQuery()

The remaining queries can get similar treatment. This change not only protects you from SQL injection attacks but it also makes your data access layer code much more manageable.

answered Dec 18, 2012 at 16:54

NYCdotNet

4,6771 gold badge27 silver badges31 bronze badges

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

1 Comment

freefaller Over a year ago

I didn't have time to go into this much detail, but I have recommended to the OP that they follow your suggestion. +1

freefaller · Accepted Answer · 2012-12-18 16:38:00Z

0

You have an extra single quote at the end of your statements.

At the moment the string will result in something like...

insert into (x,y,z) values ('a','b','c')'

Instead of the line being like...

sql = sql & " VALUES ('" & movID .... uID & "')'"

You should have (note the missing ')...

sql = sql & " VALUES ('" & movID .... uID & "')"

As a side note, if your columns are numeric based, you don't need to place the value within a single quotes.

You should also look at trying to prevent SQL Injection attacks by use the stored procedures

answered Dec 18, 2012 at 16:38

freefaller

20.1k7 gold badges62 silver badges96 bronze badges

2 Comments

user1850189 Over a year ago

Thank you freefaller. I probably should have seen that

freefaller Over a year ago

You're welcome @user1850189. Although my answer does answer your specific question, I would thoroughly recommend you implement the answer suggested by NYCdotNet

Barry Kaye · Accepted Answer · 2012-12-18 16:35:00Z

0

This line looks like its missing a single quote near the end (just before uID):

sql = sql & " VALUES ('" & movID & "','" & review_id & "','" & 2 & "','" & Date.Now & "'," & uID & "')'"

s/b:

sql = sql & " VALUES ('" & movID & "','" & review_id & "','" & 2 & "','" & Date.Now & "','" & uID & "')'"

answered Dec 18, 2012 at 16:35

Barry Kaye

7,7696 gold badges46 silver badges65 bronze badges

1 Comment

Darren Wainwright Over a year ago

Or shouldn't have the very last ' before the ) if UID is an int

Adeel Ahmed · Accepted Answer · 2012-12-18 16:37:35Z

Supposing that all the fileds are Varchar...

Dim sql As String = "INSERT INTO MovieReviews (MovieID, MReviewID, ReviewerType, ReviewDate, UserID) "
sql = sql & " VALUES ('" & movID & "','" & review_id & "','" & 2 & "','" & Date.Now & "','" & uID & "')"
Dim sql2 As String = "INSERT INTO MReviewRatings (MReviewID, ValueForMoney, ActingAbility, SpecialEffects, Plot, Total) "
sql2 = sql2 & " VALUES ('" & movID & "','" & moneyStar(moneyStarRating) & "','" & actingStar(actingStarRating) & "','" & effectsStar(effectStarRating) & "','" & plotStar(plotStarRating) & "','" & totalStar(avg) & "')"
Dim sql3 As String = "INSERT INTO MReviewTexts (MReviewID, ReviewText) "
sql3 = sql3 & " VALUES ('" & review_id & "','" & txtReviewText.Text & "')"

Collectives™ on Stack Overflow

Syntax error (missing operator) in query

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related