CODEWITHSUNDEEP
asp.netsqlvb.net
user1850189
user1850189

Reputation: 41

Syntax error (missing operator) in query

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?

Upvotes: 0

Views: 2252

Answers (4)

NYCdotNet
NYCdotNet

Reputation: 4647

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.

Upvotes: 1

freefaller
freefaller

Reputation: 19963

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

Upvotes: 0

Adeel Ahmed
Adeel Ahmed

Reputation: 1601

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 & "')"

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7759

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 & "')'"

Upvotes: 0

Related Questions