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

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


Code:
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
    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
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