Cannot view or insert MS Access oledb via VB.NET

Question

I was trying to insert values to the MS ACCESS DB but I can't insert any data to ACCESS. I have CustomerID as primary key with auto increment. After running the code it always gives me Total Customers 0* Please help me figure out whats going wrong here!

This is my code

Public Const settings As String = "Provider=Microsoft.ACE.OLEDB.12.0;" + 
                                  "Data Source=Provider=Microsoft.ACE.OLEDB.12.0;" + 
                                  "Data Source=C:\Users\sha\Documents\Visual Studio 2010\Projects\Assign2_24\Assign2_24\db24.accdb;"

Dim vConnStr As New OleDbConnection(settings)

If vboo = "" Then

        Try
             vConnStr.Open()
            Dim vSQL As String = "Insert into Customer "
            vSQL = vSQL & " (FirstName, LastName, Telephone, Email)"
            vSQL = vSQL & " Values (?, ?, ?, ?)"
            Dim cmd As New Data.OleDb.OleDbCommand(vSQL, vConnStr)
            cmd.Parameters.AddWithValue("@p1", cusFName)
            cmd.Parameters.AddWithValue("@p2", cusLName)
            cmd.Parameters.AddWithValue("@p3", cusTP)
            cmd.Parameters.AddWithValue("@p4", cusEmail)


            Dim vRowsUpdated As Integer = cmd.ExecuteNonQuery
            MessageBox.Show("Successfully saved to the system" & vRowsUpdated)


            '*************************************** Retrieve the CusID that have inserted *******************************

            Dim vSQL2 As String = "SELECT @@IDENTITY"
            Dim cmd2 As New Data.OleDb.OleDbCommand(vSQL2, vConnStr)

            Dim vCusId As Integer = CInt(cmd2.ExecuteScalar())
            MessageBox.Show("*** ID: " & vCusId)

            'Dim vCusId As Integer = CInt(cmd2.ExecuteScalar())
            'MessageBox.Show("*** ID: " & vCusId)

            Dim vSQL3 As String = "Select count (*) from Customer"
            Dim cmd3 As New Data.OleDb.OleDbCommand
            cmd3.CommandText = vSQL3

            Dim vCount3 As Integer = cmd.ExecuteScalar()
            MessageBox.Show("***** Total Customers " & vCount3)



        Catch ex As Data.Odbc.OdbcException

            Dim vErMsg As String = " *** Error occured while registering the customer ***"
            vErMsg = vErMsg & ex.ErrorCode & "***" & ex.Message & "***"


        Finally
            vConnStr.Close()
        End Try

Steve · Accepted Answer · 2013-09-25T23:20:41.750

0

There is no LAST_INSERT_ID functionality in MSAccess. Try instead

"SELECT @@IDENTITY"

Said that, I strongly suggest to change you orginal command to use a parameterized query. It always a safe approach to database commands because you don't have to worry to correctly quote your strings, numerics and dates, but, most important, you avoid Sql Injection

Dim vSQL As String = "Insert into Customer "
vSQL = vSQL & " (FirstName, LastName, Telephone, Email)"
vSQL = vSQL & " Values (?, ?, ?, ?)"
Dim cmd As New Data.OleDb.OleDbCommand(vSQL, vConnStr)
cmd.Parameters.AddWithValue("@p1", cusFName)
cmd.Parameters.AddWithValue("@p2", cusLName)
cmd.Parameters.AddWithValue("@p3", cusTP)
cmd.Parameters.AddWithValue("@p4", cusEmail)

As a side note. You are catching an ODBC exception, but your code uses OleDb. (leftover from previous version?)

GOT IT

This line executes the first query, not the second one

Dim cmd2 As New Data.OleDb.OleDbCommand(vSQL2, vConnStr)
                                            ^

and finally use the cmd3 instance to count the customers

Dim vCount3 As Integer = cmd3.ExecuteScalar()
MessageBox.Show("***** Total Customers " & vCount3)

edited Sep 25 '13 at 23:20

answered Sep 25 '13 at 11:30

Steve

213,761
22
232
286

Thanks for pointing that out! Tried your code still the same ;( – Ayesha Amarasinghe Sep 25 '13 at 12:05
There is a typo in your second query. You pass again the first sql instead of `SELECT @@IDENTITY` – Steve Sep 25 '13 at 12:40
Hi Steve Thanks for bringing that up now I got the vCusId part working ;) But the main issue is it doesn't store in my database! when I run Dim vCusId As Integer = CInt(cmd2.ExecuteScalar()) MessageBox.Show("***** Total Customers " & vCusId) it throws me ***** Total Customers 0 and nothing is on the db – Ayesha Amarasinghe Sep 25 '13 at 21:52
Try with `Dim vCusId = cmd2.ExecuteScalar()` (not forcing the output of ExecuteScalar to integer. If you haven't inserted the record the vRowsUpdated should be zero. What value do you have for vRowsUpdated ? – Steve Sep 25 '13 at 21:59
I got the vCusId part working ;) But the main issue is it doesn't store in my database! – Ayesha Amarasinghe Sep 25 '13 at 23:13
because you use cmd instead of cmd3 – Steve Sep 25 '13 at 23:18
Oh dear... Got everything smoothly running now! (Feel like a silly head!! ;() Do u have any idea why my Access table in MS Access is still empty while I get the total customer = 89 ???? – Ayesha Amarasinghe Sep 25 '13 at 23:33
Probably you are not looking at the same database defined by your connection string. – Steve Sep 25 '13 at 23:35

score 0 · Answer 2 · edited May 23 '17 at 10:25

Check out this article, autoincremented-value, appearently it works different in 2000 or greater.

Also, I think you you have a problem with your variables. You are use vSQL with cmd but you also use vSQL with cmd2. So, when you do your CInt(cmd2.ExecuteScalar()), you are actually running your insert again. Try grouping your stuff together so it is easier to follow, like this:

Dim vSQL As String = "  Insert into Customer "
vSQL = vSQL & " (FirstName, LastName, Telephone, Email)"
vSQL = vSQL & " Values ("
vSQL = vSQL & "'" & cusFName & "'"
vSQL = vSQL & "," & "'" & cusLName & "'"
vSQL = vSQL & "," & cusTP
vSQL = vSQL & "," & "'" & cusEmail & "'"
vSQL = vSQL & ");"

Dim cmd As New Data.OleDb.OleDbCommand(vSQL, vConnStr)
Dim vRowsUpdated As Integer = cmd.ExecuteNonQuery
MessageBox.Show("Successfully saved to the system" & vRowsUpdated)

Dim vSQL2 As String = "SELECT LAST_INSERT_ID()"
Dim cmd2 As New Data.OleDb.OleDbCommand(vSQL2, vConnStr)
Dim vCusId As Integer = CInt(cmd2.ExecuteScalar())
MessageBox.Show("*** ID: " & vCusId)

This will help so you can't use vSQL2 or cmd2 before they are declared and removing 1/2 of your possible errors.

Hi Steve Thanks for bringing that up now I got the vCusId part working ;) But the main issue is it doesn't store in my database! when I run Dim vCusId As Integer = CInt(cmd2.ExecuteScalar()) MessageBox.Show("***** Total Customers " & vCusId) it throws me ***** Total Customers 0 and nothing is on the db — Ayesha Amarasinghe, Sep 25 '13 at 21:51
Assuming you did all the things mentioned in these comments and answers, I think you are missing the colon required by access. You have `vSQL = vSQL & " Values (?, ?, ?, ?)"` but it should be `vSQL = vSQL & " Values (?, ?, ?, ?);"` — Steve, Sep 25 '13 at 22:04

Cannot view or insert MS Access oledb via VB.NET

2 Answers2