Execute Reader error on VB.net using MS ACCESS Database

Question

I just want to ask if where should i put the execute reader so that i can write a code that pops up a message when the function runs successfully. Here's the code.

    xprovider = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\Public\Downloads\dbInventoryManagementProgram.accdb")
    xconString = xprovider
    xmyConnection.ConnectionString = xconString
    xmyConnection.Open()

    Dim xstr As String
    xstr = "Insert into tblReports([Item],[Brand],[Quantity],[Transaction_Type],[Transaction_Date]) Values (?,?,?,?,?)"
    Dim xcmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(xstr, xmyConnection)
    'Dim xdr As OleDbDataReader = xcmd.ExecuteReader()
    xcmd.Parameters.Add(New OleDb.OleDbParameter("Item", CType(txtboxItem.Text, String)))
    xcmd.Parameters.Add(New OleDb.OleDbParameter("Brand", CType(txtboxBrand.Text, String)))
    xcmd.Parameters.Add(New OleDb.OleDbParameter("Quantity", CType(txtboxQuantity.Text, String)))
    xcmd.Parameters.Add(New OleDb.OleDbParameter("Transaction_Type", CType(txtboxTransactionType.Text, String)))
    xcmd.Parameters.Add(New OleDb.OleDbParameter("Transaction_Date", CType(dtpTransactionDate.Text, String)))

    Try
        xcmd.ExecuteNonQuery()
        xcmd.Dispose()
        xmyConnection.Close()
        txtboxTransactionType.Clear()
    Catch ex As Exception
        MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
    End Try

Try If txtboxItem.Text = "" Then MsgBox("Warning! Do not leave any fields empty. Please try again.", MsgBoxStyle.Critical) txtboxItem.Clear() txtboxBrand.Clear() txtboxQuantity.Clear() ElseIf txtboxBrand.Text = "" Then MsgBox("Warning! Do not leave any fields empty. Please try again.", MsgBoxStyle.Critical) txtboxItem.Clear() txtboxBrand.Clear() txtboxQuantity.Clear() ElseIf txtboxQuantity.Text = "" Then MsgBox("Warning! Do not leave any fields empty. Please try again.", MsgBoxStyle.Critical) txtboxItem.Clear() txtboxBrand.Clear() txtboxQuantity.Clear() ElseIf dtpTransactionDate.Text = "" Then MsgBox("Warning! Do not leave any fields empty. Please try again.", MsgBoxStyle.Critical) txtboxItem.Clear() txtboxBrand.Clear() txtboxQuantity.Clear() ElseIf xdr.HasRows Then MsgBox("Input success! Press ok to continue....", MsgBoxStyle.Information) txtboxItem.Clear() txtboxBrand.Clear() txtboxQuantity.Clear() Else MsgBox("Warning! There is some error detected. Please fill up the form correctly.", MsgBoxStyle.Critical) End If Catch ex As Exception MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical) End Try

Answer 1

As you asked for, here are some things I might do with your code. I can only base this on the code you have provided. I skipped your unformatted code. I didn't test this code. Use this as an example of how to write your code. Just about all of your code should be wrapped in Try/Catch. Users should only be notified when necessary. There are many times when you can recover from an error simply by know what the error is. When you have to notify the user, keep it simple and log details in a file they can send to you.

    Try
    xprovider = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\Public\Downloads\dbInventoryManagementProgram.accdb")
    xconString = xprovider
    'I don't know where you create this so I don't know you connection class
    Using xmyConnection As New WhatEverClassThisIs
        xmyConnection.ConnectionString = xconString
        xmyConnection.Open()

        Dim xstr As String
        xstr = "Insert into tblReports([Item],[Brand],[Quantity],[Transaction_Type],[Transaction_Date]) Values (?,?,?,?,?)"
        Using xcmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(xstr, xmyConnection)
            'Dim xdr As OleDbDataReader = xcmd.ExecuteReader()
            xcmd.Parameters.Add(New OleDb.OleDbParameter("Item", CType(txtboxItem.Text, String)))
            xcmd.Parameters.Add(New OleDb.OleDbParameter("Brand", CType(txtboxBrand.Text, String)))
            xcmd.Parameters.Add(New OleDb.OleDbParameter("Quantity", CType(txtboxQuantity.Text, String)))
            xcmd.Parameters.Add(New OleDb.OleDbParameter("Transaction_Type", CType(txtboxTransactionType.Text, String)))
            xcmd.Parameters.Add(New OleDb.OleDbParameter("Transaction_Date", CType(dtpTransactionDate.Text, String)))

            xcmd.ExecuteNonQuery()
            'You don't need to dispose now because End Using will take care of that
            'xcmd.Dispose()
        End Using

        xmyConnection.Close()
    End Using

    MsgBox("Process Completed Sucessfully")

'Exception catches everthing. You can catch others too so you can get more specific.
'When stacking them, you must go from most specific to least specific. So Exception will always be last
'I'm only adding these exceptions as examples. You can remove them.
'They are valid OleDbCommand exceptions
Catch exInvalidCast As InvalidCastException
    'Handle this
    MsgBox("Error: " & exInvalidCast.Message, MsgBoxStyle.Critical)
Catch exSQLException As SqlException
    'Handle this
    MsgBox("Error: " & exSQLException.Message, MsgBoxStyle.Critical)
Catch exIOException As IOException
    'Handle this
    MsgBox("Error: " & exIOException.Message, MsgBoxStyle.Critical)
Catch ex As Exception
    MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
Finally
    'always do this, even if error
    txtboxTransactionType.Clear()
End Try

Answer 2

You could use the Finally block in you Try/Catch like this. You should put your connection in a Try as well. Everything should be "Tried" and "Caught". Be good to your users. Don't let them see unnecessary errors or messages they don't understand. Log error details in a file they can send you and keep the messages simple for them.

You need to catch specific exceptions so you know how to address each. Not all errors require the user to be notified. You might be able to recover and try something again.

Try
    xcmd.ExecuteNonQuery()
    xcmd.Dispose()
    xmyConnection.Close()
    txtboxTransactionType.Clear()
Catch ex As Exception
    MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
Finally
    MsgBox("Process Completed")
End Try

I reread your question. To message upon successful then just move the msgbox into the Try.

Try
    xcmd.ExecuteNonQuery()
    xcmd.Dispose()
    xmyConnection.Close()
    txtboxTransactionType.Clear()
    MsgBox("Process Completed Sucessfully")
Catch ex As Exception
    MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
Finally
    'Cleanup
    'any lines of code after the errored line will not execute,
    'thus you might leave things hanging around not disposed of properly.
    'The Finally block will always execute, error or not.
End Try