Click here to Skip to main content
15,890,973 members
Search within:


code to insert data in two different tables with same column names and values in vb.net 2008
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
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.
VB
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
        'Dim dr As SqlDataReader
        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.
VB
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

        'Dim strcon As String = ("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
        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
        Dim dt As DataTable

        'Try

        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

        'Catch ex As Exception
        'MsgBox(ex.Message)
        'Finally
        cn.Close()

        ' End Try

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
VB
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
        'Dim dr As SqlDataReader
        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
Posted
Updated 5-Jul-13 7:43am
v2
Add a Solution
Comments
[no name] 5-Jul-13 12:35pm    
If you are expecting someone to write this code for you then you are going to have to supply way more information than just this. Otherwise, you need to show us the code that you have written and clearly describe the problem that you are having.
sudeshna from bangkok 5-Jul-13 12:42pm    
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
'Dim dr As SqlDataReader
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 = ""
sudeshna from bangkok 5-Jul-13 12:44pm    
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 strcon As String = ("Data Source=.\INSTANCE;initial catalog=record;user=sa;password=gariahat")
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
Dim dt As DataTable


'Try

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

'Catch ex As Exception
'MsgBox(ex.Message)
'Finally
cn.Close()

' End Try
sudeshna from bangkok 5-Jul-13 12:48pm    
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
'Dim dr As SqlDataReader
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
sudeshna from bangkok 5-Jul-13 12:54pm    
now can you help me? i have given the full code whatever i have done so far and where i am stuck

1 solution

You can't do an insert into two tables simultaneously - you need two statements as INSERT only operates on a single table. You can do the two INSERTS as a single transaction, or even issue two INSERT statements as one SqlCommand:
VB
cmd.CommandText = "INSERT INTO T1 (Id) VALUES 111;INSERT INTO T2 (Id) VALUES 111";

However, it would be better to create a Stored Procedure on SQL to do the job:
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DoubleInsert
   @ID int,
   @DT varchar(50) 
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRAN T1
	INSERT INTO Table1 (ID, Data) VALUES (@ID, @DT)
	INSERT INTO Table2 (ID, Data) VALUES (@ID, @DT)
	COMMIT TRAN T1
END
GO
You would then call the SP from your VB code with a single set of parameters, and either both will succeed or no changes will be made.

BTW: While we are on the subject of parameters: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead at all times.


"how to call the sp in vb.net 2008 code?

where will i call and what code to write?

i have written this sp code in sql server stored procedure section under programmability.

now next what to do?"
Along the lines of:
VB
Using con As New SqlConnection(strConnect)
	con.Open()
	Using com As New SqlCommand("proc_userinfo", con)
		com.CommandType = CommandType.StoredProcedure
		com.Parameters.AddWithValue("@uid", "My new uid")
		com.Parameters.AddWithValue("@uname", "My new uname")
		com.Parameters.AddWithValue("@uip", "My new uip")
		com.ExecuteNonQuery()
	End Using
End Using
 
Share this answer
 
Posted
Updated 5-Jul-13 22:13pm
v2
Comments
sudeshna from bangkok 5-Jul-13 22:22pm    
Hi, thank you for the code but this stored procedure where will i write?. i never have used stored procedure so dont know.

Can you just help me. I am using sql server 2005 and vb.net 2008
sudeshna from bangkok 5-Jul-13 23:12pm    
how to call the sp in vb.net 2008 code?

where will i call and what code to write?

i have written this sp code in sql server stored procedure section under programmability.

now next what to do?
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to pass same date value in two different masked text boxes in VB.NET?
How to insert into two different table in same stored procedure
How to divide the single column of a database table in two different columns? with same ID
Same Database Two Different Tables
How to generate a same UID for a row which will load multiple time and update with different data
Find the difference from two table in two different database, if Column name is same ?
i want to insert same data in two tables in vb.net and mysql
Same data has to be saved in two different table
How to insert data in one table from two different forms in VB.NET?
Values are same but names are different using linq

Advertise
Privacy
Cookies
Terms of Use
Last Updated 6 Jul 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900