need help - foreign key not populating

[gr8fasushi]

Hi everyone, novice here. I'm having some major issues trying to figure out what's going wrong with my code.

The general problem is that I can't get the foreign key in one of my child tables to populate the primary key from the parent table. I have a parent table named "ISSUES" and a child table named "ISSDESC". The primary key for ISSUES is ISS_ID which is an autonumber. The foreign key is ISS_ID in the ISSDESC table which is just a number. I'm using MS Access and an OLEDBConnection as the method. I've created the relationship in MS Access and also created the datarelation in the VB.NET code.

I am able to submit the information into the database into the two tables but the foreign key isn't being populated therefore not being linked to the other table.

I think it's probably a very simple fix, but it's been eluding me for a good 8 hours or so. I am using a dataset and 2 data adapters, one for each table.

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSubmit.Click
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        Dim ds As New DataSet
        Dim da, da2 As OleDb.OleDbDataAdapter
        Dim sql, sql2 As String
        Dim dsNewRow, dsNewRow2 As DataRow
        Dim x As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = C:\Users\John\Documents\UHITTracking\HelpDesk.mdb"
        con.ConnectionString = dbProvider & dbSource

        sql = "SELECT * FROM ISSUES;"
        sql2 = "SELECT * FROM ISSDESC;"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da2 = New OleDb.OleDbDataAdapter(sql2, con)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim cb2 As New OleDb.OleDbCommandBuilder(da2)

        con.Open()
        da.Fill(ds, "issues")
        da2.Fill(ds, "issuedesc")

        Dim dr As DataRelation
        Dim parentCol As DataColumn
        Dim childCol As DataColumn

        parentCol = ds.Tables("issues").Columns("ISS_ID")
        childCol = ds.Tables("issuedesc").Columns("ISS_ID")
        dr = New DataRelation("IssueRelation", parentCol, childCol)
        ds.Relations.Add(dr)

        dsNewRow = ds.Tables("issues").NewRow()
        dsNewRow2 = ds.Tables("issuedesc").NewRow()
        dsNewRow2.SetParentRow(dsNewRow)

        dsNewRow.Item("EMP_ID") = tbEmpID.Text
        dsNewRow.Item("ISSUE_TYPE") = cbIssType.Text
        dsNewRow.Item("PRIORITY") = cbPriority.Text
        dsNewRow.Item("SUBMIT_TIME") = Now
        dsNewRow.Item("STATUS") = "Open"
        dsNewRow2.Item("ISS_DESC") = tbIssDesc.Text

        If cbIssType.Text = "Hardware" Then
            dsNewRow.Item("TECH") = "John"
        ElseIf cbIssType.Text = "Software" Then
            dsNewRow.Item("TECH") = "Jason"
        ElseIf cbIssType.Text = "Interface" Then
            dsNewRow.Item("TECH") = "Russell"
        ElseIf cbIssType.Text = "Printer" Then
            dsNewRow.Item("TECH") = "Shawn"
        ElseIf cbIssType.Text = "Other" Then
            dsNewRow.Item("TECH") = "Dewey"
        End If

        ds.Tables("issues").Rows.Add(dsNewRow)
        ds.Tables("issuedesc").Rows.Add(dsNewRow2)
        da.Update(ds, "issues")
        da2.Update(ds, "issuedesc")
        con.Close()

        MsgBox("Thank you.  We will contact you shortly if the issue requires additional information.")

        x = vbNullString
        tbEmpID.Text = ""
        cbIssType.Text = ""
        cbPriority.Text = ""
        tbIssDesc.Text = ""
    End Sub
End Class

[lolafuertes]

The FK population from issues to issuesdesc is not automatic, even you define a relation. This info is on ly used in to select info, update existing info or delete related records, but, unfortunatelly, not for insert.

In order to solve your problem you need, first to do the insert for the issues table. Then retrieve from this table the highest index and use it to populate the record to be inserted into the issue desc table. And do all three actions inside a unique transaction, to ensure that no other user, concurrent insert in the issues table, harms your transaction.

Hope this helps

[gr8fasushi]

thanks for clearing that up for me. That was one of the burning questions that I was uncertain of. Looks like I'll need to re-work my code. Thanks for your help

[lolafuertes]

you are welcome

[gr8fasushi]

alright, I crossed one hurdle and ran straight into another. As you recommended, I'm trying to wrap my insert statements into a transaction. I've changed my code around so that I manually insert and retrieve the identity values sequentially and this works fine. However, when I introduce the transaction with multiple @@identity statements, I'm getting an error: "Executescalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The transaction property of the command has not been initialized." Here's my code

Dim trans As OleDb.OleDbTransaction = _
        con.BeginTransaction(IsolationLevel.ReadCommitted)
        sqlcmd.Transaction = trans

        Try
            sqlcmd.CommandText = "INSERT INTO ISSTYPE(ISS_TYPE, TECH) VALUES(" & """" & cbIssType.Text & """, """ & techID & """)"
            sqlcmd.ExecuteNonQuery()
            ID1 = cmdID1.ExecuteScalar()

            sqlcmd.CommandText = "INSERT INTO ISSUES(EMP_ID, PRIORITY, ISS_TYPE_ID, STATUS) VALUES(" & _
            tbEmpID.Text & ", """ & cbPriority.Text & """, """ & ID1 & """, 'Open');"
            sqlcmd.ExecuteNonQuery()
            ID2 = cmdID2.ExecuteScalar()

            sqlcmd.CommandText = "INSERT INTO ISSDESC(ISS_ID, ISS_DESC) VALUES(" & ID2 & ", """ & tbIssDesc.Text & """)"
            sqlcmd.ExecuteNonQuery()

            sqlcmd.CommandText = "INSERT INTO ISSNOTES(ISS_ID) VALUES(" & ID2 & ")"
            sqlcmd.ExecuteNonQuery()

            trans.Commit()
            MsgBox("Thank you.  We will contact you shortly if the issue requires additional information.  Your reference number is " & ID2)
        Catch ex As Exception
            trans.Rollback()
            MsgBox("Transaction failed" & vbNewLine & ex.Message)
        End Try

Any thoughts?

[lolafuertes]

This is because the transaction for cmdID1 and cmdID2 has not been set.

I'll suggest that as you assign the transaction to the sqlcmd in line 3 of this example, you should add in line 4:

cmdID1.Transaction = trans
cmdID2.Transaction = trans

Hope this helps

[gr8fasushi]

you sir, are a genius! thank you thank you thank you. I can't believe I overlooked such a simple step and caused myself hours of unnecessary detours