VB2010 database insert query throwing exception error

Question

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

Thanks for telling us where the error was, this is helpful. In the future, tell us what the error is. The type of error "System.Data.OleDb.OleDbException" is only barely helpful, there should have been a message with the error that said something like "Data Type mismatch: Type of String could not be converted to Number", or something like that. If you aren't sure which message I am talking about, put a TRY/CATCH around your code, then give us the ex.ToString(). — Steve
– Steve, Commented Oct 18, 2013 at 15:30
@Steve thanks for the advice about try/catch. Will use in future. — user1532468
– user1532468, Commented Oct 18, 2013 at 17:15

Jack Pettinger · Accepted Answer · 2013-10-18 09:15:20Z

1

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

answered Oct 18, 2013 at 9:15

Jack Pettinger

2,7652 gold badges25 silver badges38 bronze badges

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

Comments

Makita · Accepted Answer · 2013-10-18 09:30:18Z

1

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.

answered Oct 18, 2013 at 9:30

Makita

7465 silver badges12 bronze badges

Collectives™ on Stack Overflow

VB2010 database insert query throwing exception error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related