I have two tables data and sort with same column names.
what is the code to write for insert using transaction so that if data inserted in 1st table automatically gets inserted in 2nd sort table also.
[Edit - added code from OP's comments]
this is the code i have written for 1st table-data and its working fine.
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
cmd.Connection = cn
cn.Open()
cmd.CommandText = "insert into data(lot_no,type,shape,size,place,weight) values (' " & TextBox1.Text & " ',' " & TextBox2.Text & " ',' " & TextBox3.Text & " ',' " & TextBox4.Text & " ',' " & TextBox5.Text & " ',' " & TextBox6.Text & " ')"
cmd.ExecuteNonQuery()
MsgBox("Data successfully added", MsgBoxStyle.Information)
gencatid()
Me.DataTableAdapter.Insert(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text)
Me.DataTableAdapter.Fill(Me.RecordDataSet.data)
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox3.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.TextBox6.Text = ""
next in 2nd table sort, i am inserting data with same column name as in data table with on selection of combo box and retreiving values from data table and putting in textbox in form2.
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable
cmd.Connection = cn
cn.Open()
Dim da As New SqlDataAdapter("select * from data", cn)
dt = New DataTable
ds = New DataSet
da.Fill(ds, "data")
For i As Integer = 0 To ds.Tables("data").Rows.Count - 1
If ComboBox1.SelectedItem = ds.Tables("data").Rows(i).Item("lot_no").ToString() Then
TextBox3.Text = ds.Tables("data").Rows(i).Item("type").ToString()
TextBox4.Text = ds.Tables("data").Rows(i).Item("shape").ToString()
TextBox5.Text = ds.Tables("data").Rows(i).Item("size").ToString()
TextBox6.Text = ds.Tables("data").Rows(i).Item("place").ToString()
TextBox7.Text = ds.Tables("data").Rows(i).Item("weight").ToString()
End If
Next
cn.Close()
now my form 2 has two another fields name and date which i will manually enter in runtime,so that all 8 fields get updated in 2nd sort table. but my 2nd sort table has no values,and i have written code for update before writing insert query.
so i want transaction code so that i can insert values in both data and sort table together simultaneously
i am giving the code for update which i wrote
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand
Dim ds As New DataSet
cmd.Connection = cn
cn.Open()
cmd.CommandText = "Update Sort set " & "type = '" & TextBox3.Text & "', " & "shape='" & TextBox4.Text & " '," & "size='" & TextBox5.Text & " '," & "place='" & TextBox6.Text & " ' ," & "weight='" & TextBox7.Text & " '," & "name = '" & TextBox2.Text & "', " & "issue_dt = '" & TextBox8.Text & "'" & "Where lot_no = '" & ComboBox1.SelectedItem & "'"
cmd.ExecuteNonQuery()
MsgBox("Data successfully updated", MsgBoxStyle.Information)
Me.SortTableAdapter.Insert(ComboBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text)
Me.SortTableAdapter.Fill(Me.RecordDataSet.sort)
Me.TextBox2.Text = "" // comment- this is the name field
Me.TextBox8.Text = "" // this is the date field