? about getting Primary Key for a batch insert RESOLVED

Printable View

? about getting Primary Key for a batch insert *RESOLVED*

ok guys and girls...i finally got my question figured out that i asked a few days ago, but now i have another one. if i add a record to the one table that has a PKey that autoincrements...is there a way to get the PKey to that record that i just added so that i can add records to another table where PKey is a FKey in that table....i hope that makes sense...i've posted the code as well so that you can see what is happening up to the point of where i want to perform this action that i'm asking about....sorry about the newbie questions....just so use to vb6 and old ado. also i delete some of the code that doesn't have anything to do with what i'm asking.

VB Code:

Private Sub SaveNewShipLog()
        Dim LibInfoAdp As OleDbDataAdapter 'For Library Information Table access
        Dim LibProjAdp As OleDbDataAdapter 'For Project Table access
        Dim ShipLogAdp As OleDbDataAdapter 'For Shipping Log Table access
        Dim ShipListAdp As OleDbDataAdapter 'For Shipping Log Lists Table access
        Dim adoDs As DataSet
 
        Try
            'Setup the adapters
            LibInfoAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("library information"), adoCnn)
            LibProjAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("lib_proj_tbl"), adoCnn)
            ShipLogAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("ship_log_tbl"), adoCnn)
            ShipListAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("shp_log_lstn_tbl"), adoCnn)
 
            'Make sure the adapters that the primary key.
            LibInfoAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            LibProjAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipLogAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipListAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
 
            'Fill the dataset.
            adoDs = New DataSet
            LibInfoAdp.Fill(adoDs, "[Library Information]")
            LibProjAdp.Fill(adoDs, "lib_proj_tbl")
            ShipLogAdp.FillSchema(adoDs, SchemaType.Mapped, "ship_log_tbl")
            ShipListAdp.FillSchema(adoDs, SchemaType.Mapped, "shp_log_lstn_tbl")
 
            Try
                'Test for library information change
                Me.LibraryInfoUpdateTest(adoDs)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            Try
                'Test for Project budget change.
                If adoDs.Tables("lib_proj_tbl").Rows(0)("proj_budget") <> CDbl(Me.txtProjectAmt.Text.Replace(",", "")) Then
                    adoCmd = New OleDbCommand("UPDATE lib_proj_tbl " & _
                                              "SET proj_budget = " & _
                                              CDbl(Me.txtProjectAmt.Text.Replace(",", "")) & _
                                              " WHERE LIB_ID = " & lngLIB_ID & _
                                              " AND PROJ_ID = " & lngPROJ_ID, adoCnn)
 
                    adoCmd.ExecuteNonQuery()
                    adoCmd.Dispose()
                    adoCmd = Nothing
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            Try
                'Add Shipping Log and List
                AddNewShippingLog(ShipLogAdp, adoDs)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            'Set up a command builder
            Dim cBldr = New OleDbCommandBuilder(ShipLogAdp)
 
            ShipLogAdp.Update(adoDs, "ship_log_tbl")
            cBldr = Nothing
        Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            adoDs.Dispose()
            LibInfoAdp.Dispose()
            LibProjAdp.Dispose()
            adoDs = Nothing
            LibInfoAdp = Nothing
            LibProjAdp = Nothing
    End Sub
 
    Private Sub AddNewShippingLog(ByVal adp As OleDbDataAdapter, ByVal ds As DataSet)
        Dim dRow As DataRow = ds.Tables("ship_log_tbl").NewRow
 
        dRow("PROJ_ID") = lngPROJ_ID
        dRow("SHP_MTH_ID") = CInt(Me.cboShipMethod.Text.Substring(0, Me.cboShipMethod.Text.IndexOf(" ", 0)))
        If Me.optListTypeNSO.Checked Then
            dRow("NSO_ID") = CInt(Me.cboListTypeNSO.Text.Substring(0, Me.cboListTypeNSO.Text.IndexOf(" ", 0)))
        End If
        If Me.optListTypeODC.Checked Then
            dRow("ODC_ID") = CInt(Me.cboListTypeODC.Text.Substring(0, Me.cboListTypeODC.Text.IndexOf(" ", 0)))
        End If
        If Me.optListTypeReg.Checked Then
            dRow("REG_COLLCT_BLDR_LST_ID") = CInt(Me.cboListTypeReg.Text.Substring(0, Me.cboListTypeReg.Text.IndexOf(" ", 0)))
        End If
        If Me.optListTypeCompleat.Checked Then
            dRow("COMP_BKLST_ID") = 1
        End If
        If Me.optListTypeBID.Checked Then
            dRow("BID_CB_LST_ID") = 1
        End If
        dRow("ship_date") = CDate(Me.DateTimePicker1.Value.ToShortDateString)
        dRow("attetion_of") = Me.txtAttention.Text.Trim()
        dRow("cd_cs_person") = Me.txtCDCSPerson.Text.Trim()
        dRow("lst_pricing") = IIf(CInt(Me.cboListPrice.Text.Substring(0, Me.cboListPrice.Text.IndexOf(" ", 0))) = 1, True, False)
        dRow("spec_notes") = Me.txtSpecialNotes.Text.Trim()
        dRow("fmt_paper") = IIf(Me.chkFormatPaper.Checked, True, False)
        dRow("fmt_disk") = IIf(Me.chkFormatDisk.Checked, True, False)
        dRow("fmt_FTP") = IIf(Me.chkFormatFTP.Checked, True, False)
        dRow("fmt_BIBZ") = IIf(Me.chkFormatBIBZ.Checked, True, False)
        dRow("fmt_email") = IIf(Me.chkFormatEmail.Checked, True, False)
        dRow("fmt_spd_sht") = IIf(Me.chkFormatSpreadSh.Checked, True, False)
 
        ds.Tables("ship_log_tbl").Rows.Add(dRow)
        dRow = Nothing
    End Sub
 
    Private Function BulidShippingLogSQL(ByVal DB_Table_Name As String) As String
        Select Case DB_Table_Name.ToLower
            Case "library information"
                Return "SELECT " & _
                            "ID, " & _
                            "[Library Name], " & _
                            "Address1, " & _
                            "Address2, " & _
                            "City, " & _
                            "State, " & _
                            "Zip, " & _
                            "[Library Type] " & _
                       "FROM " & _
                            "[Library Information] " & _
                       "WHERE " & _
                            "ID = " & lngLIB_ID
            Case "lib_proj_tbl"
                Return "SELECT * " & _
                       "FROM " & _
                            "lib_proj_tbl " & _
                       "WHERE " & _
                            "PROJ_ID = " & lngPROJ_ID & " AND LIB_ID = " & lngLIB_ID
            Case "ship_log_tbl"
                Return "SELECT * FROM ship_log_tbl WHERE PROJ_ID = " & lngPROJ_ID
            Case "shp_log_lstn_tbl"
                Return "SELECT * FROM shp_log_lstn_tbl"
            Case Else
 
        End Select
    End Function

Jul 2nd, 2004, 08:56 AM
venerable bede

If you where using a sql server procedure you could return the new PK but seeing as your using something else the only way I can think of achieving this is to, after insertion select the last record inserted by dooing something like

"Select top 1 from myTbl order desc"

Ok this may not be the best way...and if anyone else has a suggestion i'm all ears however here's what i did in order to get the PKey after adding the new record so that i can do the batch insert

MODIFIED CODE:

VB Code:

Private Sub SaveNewShipLog()
        Dim LibInfoAdp As OleDbDataAdapter 'For Library Information Table access
        Dim LibProjAdp As OleDbDataAdapter 'For Project Table access
        Dim ShipLogAdp As OleDbDataAdapter 'For Shipping Log Table access
        Dim ShipListAdp As OleDbDataAdapter 'For Shipping Log Lists Table access
        Dim adoDs As DataSet
 
        Try
            'Setup the adapters
            LibInfoAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("library information"), adoCnn)
            LibProjAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("lib_proj_tbl"), adoCnn)
            ShipLogAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("ship_log_tbl"), adoCnn)
            ShipListAdp = New OleDbDataAdapter(Me.BulidShippingLogSQL("shp_log_lstn_tbl"), adoCnn)
 
            'Make sure the adapters that the primary key.
            LibInfoAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            LibProjAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipLogAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
            ShipListAdp.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
 
            'Fill the dataset.
            adoDs = New DataSet
            LibInfoAdp.Fill(adoDs, "[Library Information]")
            LibProjAdp.Fill(adoDs, "lib_proj_tbl")
            ShipLogAdp.Fill(adoDs, "ship_log_tbl")
            ShipListAdp.Fill(adoDs, "shp_log_lstn_tbl")
 
            Try
                'Test for library information change
                Me.LibraryInfoUpdateTest(adoDs)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            Try
                'Test for Project budget change.
                If adoDs.Tables("lib_proj_tbl").Rows(0)("proj_budget") <> CDbl(Me.txtProjectAmt.Text.Replace(",", "")) Then
                    adoCmd = New OleDbCommand("UPDATE lib_proj_tbl " & _
                                              "SET proj_budget = " & _
                                              CDbl(Me.txtProjectAmt.Text.Replace(",", "")) & _
                                              " WHERE LIB_ID = " & lngLIB_ID & _
                                              " AND PROJ_ID = " & lngPROJ_ID, adoCnn)
 
                    adoCmd.ExecuteNonQuery()
                    adoCmd.Dispose()
                    adoCmd = Nothing
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
 
            Try
                'Add Shipping Log and List
                AddNewShippingLog(ShipLogAdp, adoDs)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            'Set up a command builder
            Dim cBldr = New OleDbCommandBuilder(ShipLogAdp)
 
            ShipLogAdp.Update(adoDs, "ship_log_tbl")
            
            'ADDED NEW CODE HERE
            'Refresh the Dataset, making sure it has PKey for the new record.
            ShipLogAdp.Fill(adoDs, "ship_log_tbl")
            'Testing to check if correct PKey, will be replaced with routine to do batch insert.
            MsgBox(adoDs.Tables("ship_log_tbl").Rows(adoDs.Tables("ship_log_tbl").Rows.Count - 1).Item("SLT_ID"))
 
            cBldr = Nothing
        Catch ex As Exception
                MsgBox(ex.Message)
            End Try
 
            adoDs.Dispose()
            LibInfoAdp.Dispose()
            LibProjAdp.Dispose()
            adoDs = Nothing
            LibInfoAdp = Nothing
            LibProjAdp = Nothing
    End Sub

:thumb: :wave: