Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Database and Reporting (http://www.xtremevbtalk.com/-net-database-and-reporting/)
-   -   Pass one value into INSERT statement using SQL CommandBuilder (http://www.xtremevbtalk.com/-net-database-and-reporting/328159-pass-value-insert-statement-using-sql-commandbuilder.html)

NicoCS29 04-12-2016 11:12 AM

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

CheeseShard 08-26-2016 09:30 PM

Quote:

Originally Posted by NicoCS29 (Post 1409702)
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 :)

Hey,

I am working on something very similar and bit stuck, did you end up achieving that?

Thanks!

Dennis DVR 09-05-2016 04:42 AM

You should start your own thread and post your current code with detailed explanation of your problem.


All times are GMT -6. The time now is 05:30 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.