CODEWITHSUNDEEP
databasevb.netoledb
Ross from Brooklin
Ross from Brooklin

Reputation: 303

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

I'm getting the error above when I try to insert a row into an Access table using VisualStudio/VisualBasic. I've made the Insert statement as simple as I could to eliminate the possibility of a data error. The Sub to open the connection is wrapped in a try/catch so I know it's working ok. The only thing that worries me is that the Archives table has an autonum primary key (ArchID). I read somewhere that I don't need to worry about that in my Insert statement but maybe that's not correct. I'm new to VB so please keep any help simple. THanks.

Here is the code for the Insert sub:

    Try
        Dim myInsertCommand As New OleDbCommand
        Dim strDummy As String = "Ross"
        ArchiveOpenConnection()
        myInsertCommand.Connection = myConn
        myInsertCommand.CommandText = "INSERT INTO Archives (
        ArchUser
    ) VALUES (
        strDummy
    );"

        myInsertCommand.ExecuteNonQuery()
        myConn.Close()

        MessageBox.Show("IT WORKED!")

    Catch ex As Exception

        MessageBox.Show("DIDNT WORK")

    End Try

Upvotes: 0

Views: 785

Answers (2)

Precious Uwhubetine
Precious Uwhubetine

Reputation: 3007

Use using blocks when dealing with connections to dispose of resources as quick as possible. You are to open a connection using a using block and the End Using statement automatically closes and disposes the connection. Also use Parameters to avoid errors and SQL injections.

Using conn As New Oledbconnection(your connection string here)
    Using myInsertCommand As New OleDbCommand(conn)
        myInsertCommand.CommandText = "INSERT INTO Archives (ArchUser) VALUES (@strDummy)"
        myInsertCommand.Parameters.Add("@strDummny", OleDbType.VarChar, 100).Value = "Ross"
        myConn.Open()
        myInsertCommand.ExecuteNonQuery()
        myConn.Close()
    End Using
End Using

Upvotes: 1

Mary
Mary

Reputation: 15091

You can use a Form level variable to hold your connection string so you can use it anywhere in your Form.

Use Using blocks to make sure your command and connection are closed and disposed even if there is an error

You can pass the connection string directly to the constructor of the connection. You can pass the command text and the connection directly to the constructor of the command.

Always use parameters to avoid errors and prevent sql injection. https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparametercollection.add

Add(String, OleDbType, Int32)

Adds an OleDbParameter to the OleDbParameterCollection given the parameter name, data type, and column length.

Private ConStr As String = "Your connection string"

Private Sub InsertArchinves()
    Using myConn As New OleDbConnection(ConStr),
            myInsertCommand As New OleDbCommand("INSERT INTO Archives (ArchUser) VALUES (@strDummy);", myConn)
        myInsertCommand.Parameters.Add("@strDummny", OleDbType.VarChar, 100).Value = "Ross"
        myConn.Open()
        myInsertCommand.ExecuteNonQuery()
    End Using
End Sub

Upvotes: 2

Related Questions