[VB2010] database insert query throwing error

Hi
I am a new user to VB.net ( of 2 weeks ) and coming from a php background, am finding it tough going. I have created a small form that should insert some data into an access mdb database. However, I keep getting an error of:

System.Data.OleDb.OleDbException

Open in new window


I have outlined in my pasted code where this error is occurring and would be grateful if someone could point out where I have gone wrong. many thanks.

Imports System.Data.OleDb

Public Class frmMain

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\domain\storage1.mdb"

    Dim cnnOLEDB As New OleDbConnection(strConnectionString)
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim cmd As OleDbCommand

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    
    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim first, last As String
        Dim age As Integer

        first = txtFirstName.Text
        last = txtLastName.Text
        age = txtAge.Text

        Dim InsertQuery As String

        InsertQuery = "INSERT INTO Details (first,last,age) VALUES ('" & first & "','" & last & "','" & age & "')"

        cnnOLEDB.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("first", txtFirstName.Text)
        cmd.Parameters.AddWithValue("last", txtLastName.Text)
        cmd.Parameters.AddWithValue("age", txtAge.Text)


        cmd.ExecuteNonQuery() <--- ERROR
        cnnOLEDB.Close()
        MessageBox.Show("Insert complete.")
    End Sub
End Class

Open in new window

peter-cooperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
You're using the nice way to add parameters to a query so you don't need the dangerous way to concatenate values into your SQL statement, use only placeholders there and it will use the parameters in the Command object:
InsertQuery = "INSERT INTO Details (first,last,age) VALUES (?,?,?)"

Open in new window

0
peter-cooperAuthor Commented:
@Robert

I have changed query but still getting same error. Not sure I understood your answer correctly, but coded like this. Thanks

InsertQuery = "INSERT INTO Details (first,last,age) VALUES (?,?,?)"

Open in new window

0
Robert SchuttSoftware EngineerCommented:
Can you try to find out a bit more about the error?

For example, change your line 38 to:
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

Open in new window

0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

peter-cooperAuthor Commented:
Hi Robert

Comes back as: Syntax error in INSERT INTO statement. I cannot see where the error could be i have checked and double checked. Thanks
0
Robert SchuttSoftware EngineerCommented:
Unfortunately I have seen that error in my testing as well for a number of reasons. I'm not able to test right now but could you post a screenshot of the table structure or a copy of the database with any data you don't want to share deleted from it? I noticed while testing that certain column types give this error, I would expect this insert statement to work with any type of text/string value but maybe the parameters need to be tweaked.

By the way, just as a test (I wouldn't recommend using it as the ultimate solution) you could try doing it the other way around: use the concatenated sql you had before but then comment out the command parameters for the moment.
0
CodeCruiserCommented:
Try changing following

     InsertQuery = "INSERT INTO Details (first,last,age) VALUES ('" & first & "','" & last & "','" & age & "')"

        cnnOLEDB.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("first", txtFirstName.Text)
        cmd.Parameters.AddWithValue("last", txtLastName.Text)
        cmd.Parameters.AddWithValue("age", txtAge.Text)

Open in new window


to

     InsertQuery = "INSERT INTO Details (first,last,age) VALUES (@first, @last, @age)"

        cnnOLEDB.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("@first", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@last", txtLastName.Text)
        cmd.Parameters.AddWithValue("@age", txtAge.Text)

Open in new window

0
Robert SchuttSoftware EngineerCommented:
Actually I found another problem altogether with the query, it seems to be using some reserved words, try changing to this:
InsertQuery = "INSERT INTO Details ([first],[last],age) VALUES (?,?,?)"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter-cooperAuthor Commented:
Thank you robert
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.