how to insert into two tables from vb.net

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.Informati开发者_如何学JAVAon + MsgBoxStyle.OkOnly, "CUSTOMER ADDED")
    TextBox1.Clear()
    TextBox2.Clear()
    TextBox3.Clear()
    TextBox4.Clear()
    TextBox5.Clear()
    RichTextBox1.Clear()

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.

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()