asked on

[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

Robert Schutt

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

peter-cooper

ASKER

@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

Robert Schutt

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

peter-cooper

ASKER

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

Robert Schutt

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.

Nasir Razzaq

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

     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

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter-cooper

ASKER

Thank you robert