How to save multiple data in multiple rows in a column

[monching]

I want to add 12 players in 1 transaction only. My codes here can only save 1 player.
Here's my codes:

Private Sub savebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles savebtn.Click
    Dim conn As MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim commandB As MySqlCommandBuilder
    Dim data As MySqlDataAdapter
    Dim query As String = "SELECT * FROM basketplayer b;"
    Dim myconnstring As String
    Dim comm As String

        Dim value As String
        value = TeamNameTextBox.Text & "', '" & Player1NumberTextBox.Text & "', '" & Player1NameTextBox.Text

'11 more textboxes that contains players

        myconnstring = "datasource=localhost;username=root;password=monchito;database=newscoring;"
        comm = "INSERT INTO `basketplayer` (`team`, `plnum`, `playernam`, `pts`, `throw`, `foul`) VALUES ('" & value & "', '0', '0', '0');"
        conn = New MySqlConnection(myconnstring)

        Try
            conn.Open()
            data = New MySqlDataAdapter(query, conn)
            commandB = New MySqlCommandBuilder(data)
            Dim res As New MySqlParameter
            res.Value = commandB
            myCommand.CommandText = comm
            myCommand.Connection = conn
            Try

'1 Player that has saved in this command. Only the first entry.

                myCommand.ExecuteNonQuery() 
                MessageBox.Show("Players successfully added!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                newbtn.PerformClick() 'all textbox are cleared
            Catch myerror As MySqlException
                MessageBox.Show("Error Connecting to Database: " & myerror.Message)
                conn.Dispose()
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
            conn.Dispose()
        End Try
End Sub

[gon1387]

Although there are numerous ways to insert multiple records in a database, the easiest way is this:

INSERT 
    INTO [TABLE] ( [COLUMN1], [COLUMN2], [COLUMN3])
    VALUES
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE]),
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE]),
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE]),
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE]),
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE]),
        ( [COLUMN1_VALUE], [COLUMN2_VALUE], [COLUMN3_VALUE])

[M.Waqas Aslam]

well it is better to first add all the records of your all players in a datagrid then insert them in db by using loop . if you are not able to use datagrid then use datatable , insert all records in it then then save them using loop.

If you have query regarding above mentioned methods ,Please Post your question here

Best Regards

[monching]

gon1387,
I am using vb.net, Is that vb? I was thinking that is PHP.

[gon1387]

Hi monching,

It's an SQL statement. Multiple insertion in one execution.

Though, you can use .Net's stringbuilder like this one, to execute the above mentioned command: Sample

[monching]

Thanks gon,
It works!

[monching]

Hi M.Wagas Aslam,
I prefer to use gon's suggestion, because as what he have said it is easier. Thank you for your concern.

[gon1387]

Walang anu man. Anytime.

[icebergman]

Hi GON, pa help nman. Meron akong Invoice number, Fname, Lname, at datagridview with multiple rows and column. (Product ID, Price, Qty, Amount ). Gusto ko sana i-save within 1 record. Unique value is Invoice num. Mysql gamit ko