CODEWITHSUNDEEP
mysqlvb.net
user2172157
user2172157

Reputation: 35

INSERT INTO 2 Tables

I have this two tables which is the account and the people, people contains information about a person while account is their online account.

Now I have this code, but I can't seem to figure out how to get the value from the online form and insert the value into two tables in SQL. 

Dim SQLStatement As String = "INSERT INTO people(Firstname, Middlename, 
Lastname, uname, pass, type)" & " SELECT ('" & firstname.Value & "',
'" & middlename.Value & "','" & lastname.Value & "', '" & username.Value & 
"','" & password.Value & "','" & type.Value & "') LEFT JOIN people ON account  "

As you can see, it's unfinished. I would like to ask how to use INSERT INTO to two tables. I did some researched found some LEFT JOIN but I can't understand quite a bit.

EDIT

  Protected Sub btnRegister_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRegister.Click
    Const SQLStatement As String = "START TRANSACTION; INSERT INTO people(Firstname, Middlename, Lastname) VALUES ( @p0, @p1, @p3); INSERT account(uname, pass, type) VALUES( @p4, @p5,@p6); COMMIT;"
    Using connection As New SqlConnection()
        Dim command As New SqlCommand(SQLStatement, connection)
        command.Parameters.AddWithValue("@p0", firstname.Value)
        command.Parameters.AddWithValue("@p1", middlename.Value)
        command.Parameters.AddWithValue("@p2", lastname.Value)
        command.Parameters.AddWithValue("@p3", username.Value)
        command.Parameters.AddWithValue("@p4", password.Value)
        command.Parameters.AddWithValue("@p5", type.Value)

        connection.Open()
        command.ExecuteNonQuery()
    End Using
    lblNotice.Text = "Successfully Registered!"
    firstname.Value = ""
    middlename.Value = ""
    lastname.Value = ""
    username.Value = ""
    password.Value = ""
End Sub

Error at Connection.open() The ConnectionString property has not been initialized.

Upvotes: 0

Views: 105

Answers (1)

Jodrell
Jodrell

Reputation: 35706

An SQL INSERT statement can only insert into one object, it can insert data selected from many into one.

If you want to maintain integrity when updating/inserting/deleting across multiple tables then you need to use a transaction.

Const statement As String = @"START TRANSACTION;

INSERT `People` (
    `Firstname`,
    `Middlename`,
    `Lastname`,
    `uname`,
    `pass`,
    `type`)
VALUES (
    @p0,
    @p1,
    @p2,
    @p3,
    @p4,
    @p5);

INSERT `Account` (
    `Firstname`,
    `Middlename`,
    `Lastname`,
    `uname`,
    `pass`,
    `type`)
VALUES (
    @p0,
    @p1,
    @p2,
    @p3,
    @p4,
    @p5);

COMMIT;"

Then you can use this string with an DbCommand and pass the parameters in to avoid injection attacks.

Using connection As New DbConnection()
    Dim command As New DbCommand(statement, connection)
    command.Parameters.AddWithValue("@p0", firstname.Value)
    command.Parameters.AddWithValue("@p1", middlename.Value)
    command.Parameters.AddWithValue("@p2", lastname.Value)
    command.Parameters.AddWithValue("@p3", username.Value)
    command.Parameters.AddWithValue("@p4", password.Value)
    command.Parameters.AddWithValue("@p5", type.Value)

    connection.Open()
    command.ExecuteNonQuery()
End Using

Upvotes: 3

Related Questions