-
Dec 2nd, 2020, 11:25 AM
#1
Thread Starter
New Member
Syntaxe sql - multi table
Hi all,
I'm searching the SQL syntax en VB.BET to add data in 3 different tables.
I'm using a MySQL data base
Someone can help me to find an example?
All i' find it's just to add in one table.
Thanks
-
Dec 2nd, 2020, 11:42 AM
#2
Re: Syntaxe sql - multi table
Yes... you insert data into one table... then the next table, then the next.... one table at a time.
The INSERT syntax doesn't allow for inserting into multiple tables at once... There are ways using an updatable query, but even that also has its problems.
-tg
-
Dec 3rd, 2020, 03:52 AM
#3
Re: Syntaxe sql - multi table
It's probably worth asking what mechanism you're using to insert. Are you issue direct SQL, are you using the built in functionality of ADO.Net i.e. DataAdapters etc. or are you using any other sort of ORM e.g. LinqToSQL?
If you're issuing direct SQL then TG is right, you have to issue individual statements. You might want to consider wrapping them in a transaction to ensure they happen atomically.
If you're using DataAdpaters, ORMs etc then the chances are you can insert them as a single operation as long as you'd wired it all up correctly in the first place. The ORM will actually issue individual statements, manage transactions etc. but it will hide all that gubbins away from you. We'd need to know exactly what you were using before we could help further on that though.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 3rd, 2020, 09:04 AM
#4
Thread Starter
New Member
Re: Syntaxe sql - multi table
Originally Posted by FunkyDexter
It's probably worth asking what mechanism you're using to insert. Are you issue direct SQL, are you using the built in functionality of ADO.Net i.e. DataAdapters etc. or are you using any other sort of ORM e.g. LinqToSQL?
If you're issuing direct SQL then TG is right, you have to issue individual statements. You might want to consider wrapping them in a transaction to ensure they happen atomically.
If you're using DataAdpaters, ORMs etc then the chances are you can insert them as a single operation as long as you'd wired it all up correctly in the first place. The ORM will actually issue individual statements, manage transactions etc. but it will hide all that gubbins away from you. We'd need to know exactly what you were using before we could help further on that though.
Hi everyboy,
Thanks for the answers, il use a syntax like this :
Code:
Dim query = "INSERT INTO text_app (name, last_name)
VALUES(@namep, @last_namep)"
Dim MySqlCommand = New MySqlCommand(query, dbCon)
MySqlCommand.Parameters.AddWithValue("@namep", name.Text)
MySqlCommand.Parameters.AddWithValue("@last_namep", last_name.Text)
-
Dec 3rd, 2020, 09:16 AM
#5
Re: Syntaxe sql - multi table
In that case you just need to use the same kind of code for each of the tables you want to add data to (so for 3 tables, you would have 3 blocks of code like that, one for each table).
As FunkyDexter said you might want to think about using a Transaction around them, so that you can be sure that either they all succeed, or none of them do (so that you don't get partial data saved if there is an error with one of them).
-
Dec 3rd, 2020, 09:23 AM
#6
Re: Syntaxe sql - multi table
That is direct SQL... so as mentioned, it's not possible. You'll need to execute individual statements for each table you want to insert into.
-tg
-
Dec 6th, 2020, 04:14 PM
#7
Thread Starter
New Member
Re: Syntaxe sql - multi table
Thanks a lot, for all the answers.
-
Dec 13th, 2020, 11:21 PM
#8
Lively Member
Re: Syntaxe sql - multi table
Try it
Code:
Imports System.Data.OleDb
Public Class addNew
Dim conn As New OleDbConnection()
Dim sqlCom As New OleDbCommand()
Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
If txtFirstName.Text = "" Or txtLastName.Text = "" Or txtContact.Text = "" Or txtEmail.Text = "" Or comboMembershipType.Text = "" Then
MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Try
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\igcDatabase.accdb"
sqlCom.Connection = conn
conn.Open()
sqlCom.CommandText = "INSERT INTO [Members] ([Member First Name], [Member Last Name], [Gender], [Contact], [Email])" _
& " VALUES (@FirstName, @LastName, @Gender, @Contact, @Email)"
Dim gender As String
If rbtnMale.Checked = True Then
gender = "Male"
Else
gender = "Female"
End If
sqlCom.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
sqlCom.Parameters.AddWithValue("@LastName", txtLastName.Text)
sqlCom.Parameters.AddWithValue("@Gender", gender)
sqlCom.Parameters.AddWithValue("@Contact", txtContact.Text)
sqlCom.Parameters.AddWithValue("@Email", txtEmail.Text)
sqlCom.ExecuteNonQuery()
sqlCom.CommandText = "INSERT INTO [Membership Types] ([Membership Type])" & "VALUES (@MembershipType)"
sqlCom.Parameters.Clear()
sqlCom.Parameters.AddWithValue("@MembershipType", comboMembershipType.SelectedText)
sqlCom.ExecuteNonQuery()
MessageBox.Show("Successfully added member!")
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub
End Class
Refrence:https://stackoverflow.com/a/35646698/11954917
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|