how to insert into two tables from vb.net

Question

i want to insert two values into two tables of a sql database which i had created. In my vb.net code my problem is if i insert it get insterted but only in one table else sometimes it's not getting inside.

here is my code which i had used:

 c = TextBox1.Text sh = TextBox2.Text ph = Val(TextBox3.Text) ad = RichTextBox1.Text ob = Val(TextBox4.Text) con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\SHOPPROJECT\SHOPPROJECT\shop.mdf;Integrated Security=True;User Instance=True") con.Open() str1 = " INSERT INTO CUSTOMER VALUES('" & c & " ' , '" & sh & "' ," & ph & ",'" & ad & "' ,'" & TextBox5.Text & "' ) " str2 = "INSERT INTO BALANCE VALUES ('" & c & "', " & ob & ")" cmd = New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = str1 cmd.ExecuteNonQuery() cmd.CommandText = str2 cmd.ExecuteNonQuery() MsgBox("ITEM IS INSERTED", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "CUSTOMER ADDED") TextBox1.Clear() TextBox2.Clear() TextBox3.Clear() TextBox4.Clear() TextBox5.Clear() RichTextBox1.Clear()

Paul Sasik · Accepted Answer · 2011-03-18 18:22:00Z

You can actually do it in a single command and even wrap it in a transaction like this:

str1 = "begin tran; " str1 &= "INSERT INTO CUSTOMER VALUES('" & c & " ' , '" & sh & "' ," & ph & ",'" & ad & "' ,'" & TextBox5.Text & "' ); " str1 &= "INSERT INTO BALANCE VALUES ('" & c & "', " & ob & "); " str1 &= "commit tran; " cmd = New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = str1 cmd.ExecuteNonQuery()

Next you need to use try/catch on a SqlServerException to see what is going wrong. Something like:

try ' all your sql code catch (sqlex as SqlException) MessageBox.Show(sqlex.Message)

Also read up on SQL injection.

Sang Suantak · Accepted Answer · 2011-03-18 18:24:55Z

You don't need to use different string variable to insert the values. You can do it like this:

str1 = " INSERT INTO CUSTOMER VALUES('" & c & " ' , '" & sh & "' ," & ph & ",'" & ad & "' ,'" & TextBox5.Text & "' );" str1 & = "INSERT INTO BALANCE VALUES ('" & c & "', " & ob & ")" cmd = New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = str1 cmd.ExecuteNonQuery()

hey it shows a erroe under the str1& it says type character '&' does not match the declared datatype string string

Collectives™ on Stack Overflow

how to insert into two tables from vb.net

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related