Question Pass one value into INSERT statement using SQL CommandBuilder

[NicoCS29]

Hello,
I'm new to VB.Net.
I'm using MySqlCommandBuilder to automatically generate Insert and Update queries when some data is changed in a DataGridView.
I already have a variable called user_id that stores a numerical value.
THIS IS THE GOAL:
I want to insert the value contained in the user_id variable into a column called id_usuario in my MySQL Database along with whatever data was inserted in a new row of the DataGridView.
Here's my code: or if you prefer Paste ofCode

[FONT=verdana]


Imports MySql.Data.MySqlClient[/FONT]
[FONT=verdana]Public Class Dashboard[/FONT]
Public user_id As String ' This value has been set from another Form it contains the id of the current user

Dim connectionstring As String = "Server=REMOVED;user id=REMOVED; password=REMOVED; database=REMOVED;"
Dim commandtext As String = "SELECT id, id_usuario, Nombre AS 'Nombre', apellido_p AS 'Apellido Paterno', apellido_m AS 'Apellido Materno', cel AS 'Celular', email AS 'Email', notas AS 'Notas' FROM tbl_clientes WHERE id_usuario = @id_usu ORDER BY id DESC"
Dim adapter As New MySqlDataAdapter ' Declare MySql Adapter 
Dim data_set As New DataSet ' Create DataSet




Public Sub Dashboard_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'Load Info into DataGridView when form loads
    Try

        adapter = New MySqlDataAdapter(commandtext, connectionstring) 'Pass connectionstring and select query to MysqlDataAdapter
        adapter.SelectCommand.Parameters.AddWithValue("@id_usu", MySqlDbType.Int16).Value = user_id ' Add values to parameter in commandtext query

        'My Useless attempt at Inserting the user_id into the id_usuario column along with the rest of the data inserted in the DataGridView :(
        'adapter.InsertCommand = New MySqlCommand("INSERT INTO tbl_clientes (id_usuario, Nombre, apellido_p, apellido_m, cel, email, notas) VALUES (@id_usua, , , , , , )") '<--What values would go here?... if this is even how it's done
        'adapter.InsertCommand.Parameters.AddWithValue("@id_usu", MySqlDbType.Int16).Value = user_id 'Pass Paramter value to Insert Query

        Dim cmd_blder = New MySqlCommandBuilder(adapter) 'Create a new instance of MySqlCommandBuilder. Not sure exactly why but it's required

        adapter.Fill(data_set, "select_table") 'Fill the dataset
        DataGridView1.DataSource = data_set.Tables("select_table") 'Set Datasource for DataGridView
        DataGridView1.Columns("id").Visible = False 'Hide the ID column so it is not displayed in the DataGridView

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub
[FONT=verdana]Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click[/FONT]
    adapter.Update(data_set, "select_table") 'Use Update Method to store Info in DB
[FONT=verdana]End Sub[/FONT]
[FONT=verdana]End Class[/FONT]

Thanks for helping me learn

 

[jmcilhinney]

When you call Update on a data adapter, it will execute the InsertCommand for each Added DataRow in the DataTable in order to insert them into the database, plus it will execute the UpdateCommand for each Modified row to update them in the database and the DeleteCommand for each Deleted row to delete them from the database. Those command shave to exist in order for them to be executed. For them to exist, either you must create them yourself or a command builder must be associated with the data adapter to create them for you.

The changes are saved from the DataTable to the database. If you want specific data saved then you need to add that data to the DataTable. Alternatively, you could create the commands yourself and write the SQL to include a literal value for one of the columns instead of a parameter if you want the same value used for every row.

To see the difference between using a command builder and creating your own commands, take a look at the fourth and fifth code snippets here.