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

Question

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

Look into using SQL parameters and use one for this. A quick fix would be to change your command to "INSERT INTO Archives ( ArchUser ) VALUES ('" & strDummy & "');" but that's liable to SQL Injection security issues, so only use it for testing. — Jon Roberts, Jun 30 '20 at 15:51
@RossfromBrooklin, *I read somewhere that I don't need to worry about that in my Insert statement* : **Yes, you don't need to worry about then (means, you don't need to write them in your Sql-Statement.** — evry1falls, Jun 30 '20 at 22:39

score 2 · Answer 1 · answered Jun 30 '20 at 16:23

2

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

answered Jun 30 '20 at 16:23

Mary

14,926
3
18
27

As I said, I'm fairly new to VB and have never heard of Using/End Using. I can try that. As far as parameters go, if I was inserting 10 fields (columns) would I need 10 statements adding parameters? Also, I hear people saying I SHOULD do it this way but not saying what was causing the problem with the way I was doing it. Any comments on that? – Ross from Brooklin Jun 30 '20 at 17:44
1

The biggest reason for using parameters is prevention of SQL injection vulnerabilities. There is a famous XKCD about it, you should be able to find it if you do a search for "Bobby Tables". – Craig Jun 30 '20 at 18:00
@RossfromBrooklin No. Just one _insert_ statement and separate the fields and parameters by commas. Ex: `"INSERT INTO Archives (ArchUser, col2, col3) VALUES (@strDummy, @col2, @col3);"`. and add the parameters `myInsertCommand.Parameters.Add("@col2", OleType.Whatever).Value = ..`. – Jun 30 '20 at 18:03
@RossfromBrooklin Important! In Access the names of the parameters are not important. What is important is that the order the parameters are added to the parameters collection must match the order that they appear in the sql query statement. – Mary Jun 30 '20 at 18:10
@RossfromBrooklin strDummy is not a variable inside a string. It is just part of the string. String values for fields are enclosed in single quotes in an sql string. This would be a literal, not a variable. To see why we use parameters check https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements… – Mary Jun 30 '20 at 18:14
@RossfromBrooklin Yes, you will need a parameter for each field. You can use With...End With block to add them and save typing With myInsertCommand.Parameters and then .Add... – Mary Jun 30 '20 at 18:14
@RossfromBrooklin, also consider using [Configuration Files](https://learn.microsoft.com/en-us/dotnet/api/system.configuration.configurationmanager.connectionstrings?view=dotnet-plat-ext-3.1) this is about performance tuning and is fun to read about. – evry1falls Jun 30 '20 at 22:50

preciousbetine · Answer 2 · 2020-06-30T16:33:04.450

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

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

2 Answers2