Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Question

Hi I have an invalid sql statement error. This is my code:

Imports System.Data.OleDb   'For OleDbConnection 
Imports System.Data         'For ConnectionState 


Public Class WebForm1

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
        '1 declare the variables
        Dim strName As String = txtName.Text
        Dim strAddress As String = txtAddress.Text


        '2. creates a new connection to your DB.
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\GT\Documents\Database11.accdb'")
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If

        '3. open the connection to your DB. 
        conn.Open()

        '4. assign your SQL statement into sqlString variable. 
        Dim sqlString As String
        sqlString = "INSERT INTO tblStuInfo (stuName, stuAddress) VALUES ('" & strName & "' , '" & strAddress & "')"

        '5. create a new command that links your SQL statement with your connection. 
        Dim sqlcommand As New OleDbCommand(sqlString, conn)

        '6. execute your command.
        sqlcommand.ExecuteNonQuery()
    End Sub
End Class

What is the problem? The path of the database and the table name of the DB is correct. Please help!

You have a sql injection error there. Use a parameterised SqlCommand. — Ben, Mar 27 '14 at 09:42
How do i do that? btw this is a tutorial i followed from a book — magicm00n, Mar 27 '14 at 09:44
Read this: http://jmcilhinney.blogspot.com.au/2009/08/using-parameters-in-adonet.html — jmcilhinney, Mar 27 '14 at 09:55
I dont want to use parameterised SqlCommand, this tutorial is from a book. What is wrong in my code? I followed it correctly but get that error — magicm00n, Mar 27 '14 at 10:00

score 0 · Answer 1 · edited Mar 27 '14 at 10:49

0

try to replace

sqlString = "INSERT INTO tblStuInfo (stuName, stuAddress) VALUES ('"
    & strName & "' , '" & strAddress & "')"

with

sqlString = "INSERT INTO tblStuInfo (stuName, stuAddress) VALUES ('"
    & strName.Replace("'", "''") & "' , '" & strAddress.Replace("'", "''") & "')"

this should solve any SQL injection issue, that happen when your string contain the ' character.

Anyway, I think you should add (or use) a key in the underlying table, otherwise how are you going to get these values back?

edited Mar 27 '14 at 10:49

Peter Lang

54,264
27
148
161

answered Mar 27 '14 at 10:04

Max

7,408
2
26
32

Ya that works but why in the tutorial it doesn't mention to use the "replace"? – magicm00n Mar 27 '14 at 10:15
Maybe because the issue will only happen if `strAddress` contains a single quote? The query seems to be OK in the case where `strAddress` doesn't have any quotes? – shree.pat18 Mar 27 '14 at 10:18
@user3414064 (wild guess) maybe because the tutorial is about vb.net, and not about SQL (so it take for granted that you already know tricks and quirks of SQL) - Or maybe it' because it's a tutorial, ant things get introduced a tiny bit at time, and managing string delimiter in sql is considered an 'advanced topic' by the tutorial – Max Mar 27 '14 at 10:24
No Im sure the tutorial is correct and im certain I followed it correctly. Here it is: http://www.sendspace.com/file/r2g7fq – magicm00n Mar 27 '14 at 10:29
What could be the problem? – magicm00n Mar 27 '14 at 11:04
@user3414064 nothing wrong on your side, the author of the tutorial didn't consider the management of string delimiter (not in this lesson at least), maybe he treated the management of SQL string delimiter is in another lesson :-) – Max Mar 27 '14 at 11:04
@user3414064 the issue caused by the ' character is explained here http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/ – Max Mar 27 '14 at 11:06
Oh I see, so the only way around this is to use "replace" right? If that is so then thanks a lot for your help. – magicm00n Mar 27 '14 at 11:07
1

@user3414064 the best way is to use "query parameters", but it's more complex, check here http://stackoverflow.com/a/542542/1057961 – Max Mar 27 '14 at 11:30

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

1 Answers1