I have an Access database with a contacts table and a location table that is linked to contacts. When i click the add button I want to add a new contact and add that contacts ID to a the location table. The ID is an Autonumber, so when i add a new contact it just assigns it a random value. First I was getting an error saying I cannot add to the location table because a matching record is required in the contacts table, now i am getting this error:
(the connection was not closed. the connection's current state is open.)

Here is my Add code:

Public Class AddPartner

    Dim cn As OleDbConnection
    Dim connString As String = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb"
    Dim myconn As OleDbConnection = New OleDbConnection
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim icount As Integer

    Private Sub AddParnter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim da As New OleDb.OleDbDataAdapter("select * from Location", con)
        Dim ds As New DataSet

        For i = 0 To ds.Tables(0).Rows.Count - 1
        Next i

    End Sub

    Private Sub btdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btdAdd.Click

    End Sub

    Public Sub AddPartner()

            Dim sql = "INSERT INTO Contacts(Company) Values ('" & txtcompadd.Text & "')"
            Dim cmd As New OleDb.OleDbCommand(sql, con)
            'Executes querey.


        Catch e As Exception

        End Try

    End Sub

    Public Sub addstate()

        Dim lendloc As New ArrayList()
        If lstDebtState.CheckedItems.Count <> 0 Then
            ' If so, loop through all checked items and print results.
            Dim x As Integer
            Dim sql As String
            Dim sql2 As String
            Dim cmd As OleDb.OleDbCommand

            sql2 = "Select Contact_ID from Contacts where Company = '" & txtcompadd.Text & "'"
            cmd = New OleDb.OleDbCommand(sql2, con)
            Dim dr As OleDb.OleDbDataReader
            dr = cmd.ExecuteReader

            Dim Contact_ID As Integer

            While dr.Read()
                Contact_ID = dr.GetInt32(0)
            End While

            For x = 0 To lstDebtState.CheckedItems.Count - 1
                sql = "Insert into Contact_Locn(Contact_ID, Location_Code) values ('" & Contact_ID & "', '"
                sql = sql & CStr(lstDebtState.CheckedItems(x)) & "')"
                cmd = New OleDb.OleDbCommand(sql, con)
                Dim temp As Integer
                temp = cmd.ExecuteNonQuery
            Next x

        End If

    End Sub

End Class
This article has been dead for over six months. Start a new discussion instead.