September 22, 2005 at 9:51 am
I am trying to insert rows in two tables related by primary key-foreign key relationship using ADO .Net. I have to take the values of the primary key, ID (which is auto generated)from the parent table MACHINEINFORMATION and insert it in the foreign key field MACHINEID of table MACHINESETTINGS. I am using ADO.Net for this and the tables are in a SQL Server database. I am getting an error which says "variable @ID not declared". Please help. Thanks in advance. I am attaching the code below.
Thanks
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Class1
Inherits System.Windows.Forms.Form
' TODO: Add code to start application here
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
'
'Class1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Name = "Class1"
Me.Text = "Form1"
End Sub
#End Region
Private Sub Class1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Create the DataSet object
Dim miDataSet As New DataSet
Dim conn As New SqlConnection("Data Source=NVPROTOSRV\PROTOTYPE;Initial Catalog=AILIS5PROTOTEST;User ID=developer2;Password=proto;")
conn.Open()
miDataSet.Tables.Add("MACHINEINFORMATION")
miDataSet.Tables.Add("MACHINESETTINGS")
Dim miAdapter As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT * FROM MACHINEINFORMATION", conn))
miAdapter.InsertCommand = New SqlCommand("INSERT INTO MACHINEINFORMATION (MachineName,Active)" & _
"VALUES(@MACHINENAME,@ACTIVE)", conn)
Dim cmdInsertmi As New SqlCommand
cmdInsertmi = miAdapter.InsertCommand()
cmdInsertmi.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int, 4, "ID"))
cmdInsertmi.Parameters("@ID").Direction = ParameterDirection.Output
'cmdInsertmi.Parameters("@ID").SourceColumn = "ID"
cmdInsertmi.Parameters.Add(New SqlParameter("@MACHINENAME", SqlDbType.VarChar, 50, "MachineName"))
cmdInsertmi.Parameters.Add(New SqlParameter("@ACTIVE", SqlDbType.Char, 1, "Active"))
cmdInsertmi.UpdatedRowSource = UpdateRowSource.OutputParameters
'miAdapter.FillSchema(miDataSet, SchemaType.Source)
miAdapter.Fill(miDataSet.Tables(0))
Dim msAdapter As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT * FROM MACHINESETTINGS", conn))
'Dim msDatacol As New DataColumn
msAdapter.InsertCommand = New SqlCommand("INSERT INTO MACHINESETTINGS (machineid, AccountPrinter, DefaultPrinter,BarcodePrinter, IsBarcodeUSB, CommPort, AdvanceInterval, ReportPath, Checkendorser1, Checkendorser2)" & _
"VALUES(@machineid, @ACCOUNTPRINTER,@DEFAULTPRINTER,@BARCODEPRINTER,@ISBARCODEUSB,@COMMPORT,@ADVANCEINTERVAL,@REPORTPATH,@CHECKENDORSER1,@CHECKENDORSER2)", conn)
Dim cmdInsertms As New SqlCommand
cmdInsertms = msAdapter.InsertCommand()
cmdInsertms.Parameters.Add(New SqlParameter("@MACHINEID", SqlDbType.Int, 4, "MachineID"))
cmdInsertms.Parameters.Add(New SqlParameter("@ACCOUNTPRINTER", SqlDbType.VarChar, 100, "AccountPrinter"))
cmdInsertms.Parameters.Add(New SqlParameter("@DEFAULTPRINTER", SqlDbType.VarChar, 100, "DefaultPrinter"))
cmdInsertms.Parameters.Add(New SqlParameter("@BARCODEPRINTER", SqlDbType.VarChar, 100, "BarcodePrinter"))
cmdInsertms.Parameters.Add(New SqlParameter("@ISBARCODEUSB", SqlDbType.Char, 1, "IsBarcodeUSB"))
cmdInsertms.Parameters.Add(New SqlParameter("@COMMPORT", SqlDbType.VarChar, 50, "CommPort"))
cmdInsertms.Parameters.Add(New SqlParameter("@ADVANCEINTERVAL", SqlDbType.Int, 4, "AdvanceInterval"))
cmdInsertms.Parameters.Add(New SqlParameter("@REPORTPATH", SqlDbType.VarChar, 100, "ReportPath"))
cmdInsertms.Parameters.Add(New SqlParameter("@CHECKENDORSER1", SqlDbType.VarChar, 100, "Checkendorser1"))
cmdInsertms.Parameters.Add(New SqlParameter("@CHECKENDORSER2", SqlDbType.VarChar, 100, "Checkendorser2"))
'msAdapter.FillSchema(miDataSet, SchemaType.Source)
msAdapter.Fill(miDataSet.Tables(1))
' Create the relationship between the two tables
Dim ParentChild As DataRelation
ParentChild = New DataRelation("Parent2Child", miDataSet.Tables("MACHINEINFORMATION").Columns("ID"), miDataSet.Tables("MACHINESETTINGS").Columns("MACHINEID"))
miDataSet.Relations.Add(ParentChild)
' Insert the Data
Dim miRow As DataRow
miRow = miDataSet.Tables("MACHINEINFORMATION").NewRow()
miRow("MACHINENAME") = "ABC1"
miRow("ACTIVE") = "Y"
miDataSet.Tables("MACHINEINFORMATION").Rows.Add(miRow)
Dim msRow As DataRow
msRow = miDataSet.Tables("MACHINESETTINGS").NewRow()
msRow("ACCOUNTPRINTER") = "ABC1"
msRow("DEFAULTPRINTER") = "XYZ"
msRow("BARCODEPRINTER") = "AC2"
msRow("ISBARCODEUSB") = "Y"
msRow("COMMPORT") = "23C6781"
msRow("ADVANCEINTERVAL") = "1"
msRow("REPORTPATH") = "CCC"
msRow("CHECKENDORSER1") = "AKL"
msRow("CHECKENDORSER2") = "KJH"
'msRow.SetParentRow(miRow)
miDataSet.Tables("MACHINESETTINGS").Rows.Add(msRow)
miAdapter.Update(miDataSet, "MACHINEINFORMATION")
Dim myCommand As SqlCommand = conn.CreateCommand()
Try
myCommand.CommandText = "SELECT @ID = @@IDENTITY FROM MACHINEINFORMATION"
Dim iid As Integer = Convert.ToInt32(myCommand.ExecuteScalar())
msRow("machineid") = iid
msAdapter.Update(miDataSet, "MACHINESETTINGS")
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
End Class
September 22, 2005 at 9:57 am
I'd suggest you encapsulate all the logic in a single stored proc where both inserts would be executed and the new identity easily fetched ::
create proc dbo.demo param1 int, param2 int...
AS
set nocount on
Declare @NewID as int
insert into dbo.Machines (cols) values (@params)
set @Newid = SCOPE_IDENTITY()
insert into dbo.MachineParts (FK, cols) (@Newid, @Params2)
set nocount off
September 22, 2005 at 10:21 am
Hey, Thanks! I actually tried the stored procedure earlier but for my application, we use different databases and I need to do the querying without using the stored procedure. Is there a way to fix it without using a stored procedure?
September 22, 2005 at 11:30 am
Create a single script much like the proc where all 3 statements would be executed at the same time.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply