View Single Post
Old 04-12-2016, 11:12 AM
NicoCS29 NicoCS29 is offline
Join Date: Apr 2016
Posts: 1
Default Pass one value into INSERT statement using SQL CommandBuilder


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.


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

Imports MySql.Data.MySqlClient
Public Class Dashboard
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

        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
    End Try

End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    adapter.Update(data_set, "select_table") 'Use Update Method to store Info in DB
End Sub
End Class
Thanks for helping me learn
Reply With Quote