CODEWITHSUNDEEP
vb.netinsertodbcdataadapter
rfc1484
rfc1484

Reputation: 9857

Update database using ODBC

Here is my sub for updating a Database using ODBC:

Public Sub UpdateDatabase(ByVal sql As String, ByVal parameters() As OdbcParameter)
    Dim connectionString As String = "dsn=" & ODBC & ";uid=" & UID & ";pwd="

    Try
        Using conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim adapter As New OdbcDataAdapter(sql, conn)

            For Each parameter As OdbcParameter In parameters
                adapter.InsertCommand.Parameters.Add(parameter)
            Next

            conn.Open()

            adapter.InsertCommand.ExecuteNonQuery()
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Here is an example calling it with empty parameters:

            Dim query As String = "INSERT INTO F_ARTICLE (AR_Ref) VALUES ('test')"
            Dim parameters As OdbcParameter() =
            {
            }
            UpdateDatabase(query, parameters)

But the following exception is shown: 

NullReferenceException: Object reference not set to an instance of an object

Which seems to be triggered by this: 

adapter.InsertCommand.ExecuteNonQuery()

Any idea what am I doing wrong here?

Upvotes: 1

Views: 5811

Answers (1)

rfc1484
rfc1484

Reputation: 9857

I still don't know why the previous sub didn't work, but this is how I solved it:

Public Sub UpdateDatabase(ByVal query As String, ByVal parameters() As OdbcParameter)
    Dim connectionString As String = "dsn=" & ODBC & ";uid=" & UID & ";pwd="

    Try
        Using conn As OdbcConnection = New OdbcConnection(connectionString)
            Using command As New OdbcCommand(query, conn)

                For Each parameter As OdbcParameter In parameters
                    command.Parameters.Add(parameter)
                Next

                conn.Open()

                command.ExecuteNonQuery()
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Upvotes: 1

Related Questions