Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Go Back  Xtreme Visual Basic Talk > > > Pass one value into INSERT statement using SQL CommandBuilder


Reply
 
Thread Tools Display Modes
  #1  
Old 04-12-2016, 12:12 PM
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
  #2  
Old 08-26-2016, 10:30 PM
CheeseShard CheeseShard is offline
Newcomer
 
Join Date: Aug 2016
Posts: 1
Default

Quote:
Originally Posted by NicoCS29 View Post
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!
Reply With Quote
  #3  
Old 09-05-2016, 05:42 AM
Dennis DVR's Avatar
Dennis DVRPass one value into INSERT statement using SQL CommandBuilder Dennis DVR is offline
Back in the Game

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Manila Philippines
Posts: 3,576
Default

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

[Posting Guidelines] [Standards & Practices Tutorial] [Participate here effectively]
Our knowledge can only be finite, while our ignorance must necessarily be infinite. Karl Popper
Reply With Quote
Reply

Tags
user_id, id_usuario, dim, datagridview, notas, adapter, email, nombre, connectionstring, public, string, insert, data, tbl_clientes, mysql, column, query, inserted, mysqldataadapter, dataset, pass, form, apellido, called, select


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
 
Pass one value into INSERT statement using SQL CommandBuilder
Pass one value into INSERT statement using SQL CommandBuilder
 
-->