Syntax error in INSERT INTO statement in vb.net and ms access-CodePudding

Sql="INSERT INTO BooksReturned  
     (BookId,Booktitle,Student,Form)
      Values('" & txtid.Text & '",'" & bookname.Text  
      & "','" & txtstudent.Text & "','" & txtform.Text & "');"

CodePudding user response：

While you no doubt will get scolded for sql injection issues?

In fact, that scolding is of LITTLE help unless a good, nice, clean, easy alternative is offered to you.

the problem with trying place values into the sql string has many problems. One missed quote around text, will fail.

And accidently putting quotes around a number - again often fails. So, MORE then just sql injection issues, is the simple issue that such sql statements are MESSAY and hard to maintain, hard to write, and in fact often hard to read!

so, lets clean this up.

first up, I assume you built a conneciton to the access database. And avoid the tempattion to strew all over the place connection strings in your code. In fact, NEVER put connection strings in code - since if you ever have to change the data - for testing, or even for deployment, you be sorry you did.

So, lets setup the connection to access - one place, one easy place, and in fact we can use the Visual Studio connection builder for you!!! - nice and easy!

So, in your project, (Project->"your project name properties), and then go to settings.

So, we have/get to this:

From the drop down choose connection string, and then next drop down use "application"

So, we have this:

Now, click on the builder [...], and we can now "build" the connection that for use in our code.

(you use the change in above - since it defaults to sql server).

Ok, so now that we have the above - "accessDB" is our connection we can use in code.

So, now lets write the code you have.

I would suggest this code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Using conn = New OleDbConnection(My.Settings.AccessDB)

        Dim Sql As String =
            "INSERT INTO BooksReturned  
            (BookId,Booktitle,Student,Form) 
            Values(@BID, @Btitle, @BStudent, @Form)"

        Using cmdSQL = New OleDbCommand(Sql, conn)

            With cmdSQL.Parameters
                .Add("@BID", OleDbType.VarWChar).Value = txtID.Text
                .Add("@Btitle", OleDbType.VarWChar).Value = bookname.Text
                .Add("@BStudent", OleDbType.VarWChar).Value = txtStudent.Text
                .Add("@Form", OleDbType.VarWChar).Value = txtForm.Text
            End With

            conn.Open()
            cmdSQL.ExecuteNonQuery()

        End Using

    End Using

End Sub

So, now this is a "wee bit" more code. However, when I typed in this line of code:

.Add("@BID", OleDbType.VarWChar).Value = txtID.Text

Well, while my cursor was on that line, I hit ctrl-d (duplicate).

So, in fact I ONLY had to replace the "BID" and the txtID for the next line of code.

Also, note how we used our "settings" for the connection to the database.

So, we

by using the editor to help me type code - did not type much
the sql was really clean, clear, easy to write
the sql does not have a bunch of "quotes" and all that jazz
with clean sql, then it easy to read, but MORE important add to the sql.

So, give the above approach a try.

I had for that form at the start these two settings:

Imports System.Data.OleDb
Imports System.Data

However, since darn near every page will need that, then check box above both in the Project settings "references" area - imported name spaces - will save you lots of typing.

Last but not least?

consider using a data bound form. This works like access, and thus you can navigate, edit, delete, and insert new records - all done without having to write code.

CodePudding user response：

l found another way you can put row names in [] like below;

Sql="INSERT INTO BooksReturned  
 ([BookId],[Booktitle],[Student],[Form])
  Values('" & txtid.Text & '",'" & bookname.Text  
  & "','" & txtstudent.Text & "','" & txtform.Text & "');"

It will work perfectly without errors