Results 1 to 8 of 8

Thread: Syntaxe sql - multi table

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2020
    Posts
    12

    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

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,394

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,703

    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

  4. #4

    Thread Starter
    New Member
    Join Date
    Mar 2020
    Posts
    12

    Re: Syntaxe sql - multi table

    Quote Originally Posted by FunkyDexter View Post
    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)

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,660

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

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,394

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2020
    Posts
    12

    Re: Syntaxe sql - multi table

    Thanks a lot, for all the answers.

  8. #8
    Lively Member
    Join Date
    Jan 2020
    Posts
    121

    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
  •  



Click Here to Expand Forum to Full Width