CODEWITHSUNDEEP
vb.netvisual-studio-2010
user1532468
user1532468

Reputation: 1753

VB2010 database insert query throwing exception error

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

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

Upvotes: 0

Views: 175

Answers (2)

Makita
Makita

Reputation: 746

First and Last are reserved words for Jet 4.0. They should not be used as column names. Access itself will let you get away with it, SQL code is a little less forgiving. If you can't avoid using those words, try putting them in square brackets [first] when addressing them. For more information on reserved words, see http://support.microsoft.com/kb/248738.

Upvotes: 1

Jack Pettinger
Jack Pettinger

Reputation: 2755

The problem is here:

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

cmd.Parameters.AddWithValue("first", txtFirstName.Text)
cmd.Parameters.AddWithValue("last", txtLastName.Text)
cmd.Parameters.AddWithValue("age", txtAge.Text)

Try this:

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

Then for the Parameters:

cmd.Parameters.AddWithValue("@first", txtFirstName.Text)
cmd.Parameters.AddWithValue("@last", txtLastName.Text)
cmd.Parameters.AddWithValue("@age", txtAge.Text)

You can then remove these lines as you don't need them:

Dim first, last As String
Dim age As Integer

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

Upvotes: 1

Related Questions