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
con.Open()
Dim da As New OleDb.OleDbDataAdapter("select * from Location", con)
Dim ds As New DataSet
da.Fill(ds)
For i = 0 To ds.Tables(0).Rows.Count - 1
lstDebtState.Items.Add(ds.Tables(0).Rows(i)(0))
Next i
End Sub
Private Sub btdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btdAdd.Click
AddPartner()
addstate()
End Sub
Public Sub AddPartner()
Try
con.Open()
Dim sql = "INSERT INTO Contacts(Company) Values ('" & txtcompadd.Text & "')"
Dim cmd As New OleDb.OleDbCommand(sql, con)
'Executes querey.
cmd.ExecuteNonQuery()
con.Close()
Catch e As Exception
MsgBox(e.Message)
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
con.Open()
Dim cmd As OleDb.OleDbCommand
sql2 = "Select Contact_ID from Contacts where Company = '" & txtcompadd.Text & "'"
cmd = New OleDb.OleDbCommand(sql2, con)
'cmd.ExecuteNonQuery()
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)
'cmd.ExecuteNonQuery()
lendloc.Add(CStr(lstDebtState.CheckedItems(x)))
MessageBox.Show(CStr(lstDebtState.CheckedItems(x)))
Dim temp As Integer
temp = cmd.ExecuteNonQuery
con.Close()
Next x
End If
End Sub
End Class