How t insert a data to the database using query

Question

so i have this codes but it gets a wrong syntax error.. i dont know whats wrong can anyone help me?? im a newbee in VB. programming

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


    Try
        Dim SqlQuery = "INSERT INTO sample (FirstName,MiddleName,LastName,Gender,age,Year Level,Date of birth,Date Enrolled,Citezenship,Religion,Address,Telephone NO,Average Grade,Father,Fathers Occupation,Fathers Address,Mother,Mothers Occupation,Mothers Address,Guardian,Guardians Address,Family Income,Payed Amount,Balance) VALUES ('" & txtfname.Text & "','" & txtmname.Text & "','" & txtlname.Text & "','" & combosex.Text & "','" & comboage.Text & "','" & comboyear.Text & "','" & txtdateofbirth.Text & "','" & txtdateenrolling.Text & "','" & txtcitezen.Text & "','" & txtreligion.Text & "','" & txtstudentadd.Text & "','" & txtnumber.Text & "','" & txtgrade.Text & "','" & txtfather.Text & "','" & txtfatherocc.Text & "','" & txtfatheradd.Text & "','" & txtmother.Text & "','" & txtmotherocc.Text & "','" & txtmotheradd.Text & "','" & txtguardian.Text & "','" & txtguardianadd.Text & "','" & txtincome.Text & "','" & txtpayment.Text & "','" & TextBox2.Text & "')"
        Dim sqlcommand As New OleDbCommand
        With sqlcommand
            .CommandText = SqlQuery
            .Connection = conn
            .ExecuteNonQuery()
        End With
        MsgBox("one record succesfull added")

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

System.data.oledb.oledbException:sysntax error in INSERT INTO statement. at System.data.oledb.oledbcommand.executeCommandTextErrorHandling(oledbHresult hr) at system.data.oledb.oledbcommand.executeCommandTextForSingleResult( tagDparams dbParams,Object&executeResult) at System.data.Oledb.OledbCommand.ExecuteCommand(command behavior behavior,Object&execute result) at system.data.oledb.oledbcommand.executeraderInternal(commandBehavior behavior,string method atsystem.oledb.oledbcommand.executenonquery() — user3242798
– user3242798, Commented Feb 3, 2014 at 12:36

David · Accepted Answer · 2014-01-31 15:03:15Z

1

This is most likely because of the SQL injection vulnerability you're exposing. Notice how you're treating user input:

"... VALUES ('" & txtfname.Text & "', ..."

This may look like you're just putting a data value in the query, but what you're actually doing is treating the user input as executable code. Any user input with database-reserved characters will cause problems. For example, if an input value has a single quote (such as the phrase "don't use SQL injectable code") then the resulting query you're building is:

"... VALUES ('don't use SQL injectable code', ..."

Naturally this will result in a syntax error because there's a t after a string literal, which isn't valid SQL.

Instead of treating user input as executable code, treat it as data values. Use SQL parameters to add those values to your query. Something like this:

' replace each concatenated string with a parameter placeholder:
Dim SqlQuery = "... VALUES (@fname, ..."

Dim sqlcommand As New OleDbCommand
With sqlcommand
    .CommandText = SqlQuery
    .Connection = conn

    ' add a parameter for each placeholder:
    .Parameters.AddWithValue("@fname", txtfname.Text)

    .ExecuteNonQuery()
End With

answered Jan 31, 2014 at 15:03

David

221k42 gold badges245 silver badges337 bronze badges

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

6 Comments

user3242798 Over a year ago

sir.. it still doesnt work.. :( i paramitised all but im still getting the error.. (sorry for the english)

David Over a year ago

@user3242798: What is the actual exception you're seeing? What is the text of the query that's causing the error?

user3242798 Over a year ago

this is the error System.data.oledb.oledbException:sysntax error in INSERT INTO statement. at System.data.oledb.oledbcommand.executeCommandTextErrorHandling(oledbHresult hr) at system.data.oledb.oledbcommand.executeCommandTextForSingleResult( tagDparams dbParams,Object&executeResult) at System.data.Oledb.OledbCommand.ExecuteCommand(command behavior behavior,Object&execute result) at system.data.oledb.oledbcommand.executeraderInternal(commandBehavior behavior,string method atsystem.oledb.oledbcommand.executenonquery()

David Over a year ago

@user3242798: It looks like your column names have spaces in them. Does your database engine allow that? I'm guessing it doesn't. You'll need to qualify those names as database objects. In MS SQL Server that would mean surrounding them with square brackets, such as: [Date of birth].

user3242798 Over a year ago

but im not using MS SQL ? im using Acces Databes

|

Mych · Accepted Answer · 2014-01-31 15:01:51Z

0

Without seeing the exception message its difficult say what is exactly wrong.... but at a guess I would say you are inserting stings where the db is expecting integers or dates. You need to cast your data in the type required by each field.

You are also open to sql injection. Try to paramatise your data.

answered Jan 31, 2014 at 15:01

Mych

2,5834 gold badges40 silver badges70 bronze badges

1 Comment

user3242798 Over a year ago

System.data.oledb.oledbException:sysntax error in INSERT INTO statement. at System.data.oledb.oledbcommand.executeCommandTextErrorHandling(oledbHresult hr) at system.data.oledb.oledbcommand.executeCommandTextForSingleResult( tagDparams dbParams,Object&executeResult) at System.data.Oledb.OledbCommand.ExecuteCommand(command behavior behavior,Object&execute result) at system.data.oledb.oledbcommand.executeraderInternal(commandBehavior behavior,string method atsystem.oledb.oledbcommand.executenonquery()

Collectives™ on Stack Overflow

How t insert a data to the database using query

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related