SQl Server doesnt update new values from Textbox Bind to Datatable

lmalave

New member
Joined
Apr 21, 2016
Messages
4
Programming Experience
1-3
Original Post: http://www.vbdotnetforums.com/sql-server/62234-need-update-sql-server-values-txtbox.html

Using: SQL Server 2014, VS2015, VB



  1. I have a Datagridview that fills with a DataTable (Table).
  2. I have several Textboxes that are bind with their corresponding values from the binding Source (bsAllData)

This works perfect thanks to jmcilhinney. It fills the Datagridview and when I click on a row it populates the corresponding textboxes.
What is not working is that when I make changes in the textboxes I press a command button to update the SQL Server with the new values and nothing happens, the data doesn't update. This is where I am stuck.


The following Code is working to populate the Datagridview and Textboxes:

?Button to display all records in the datagridview
Private Sub btnViewAll_Click(sender As Object, e As EventArgs) Handles btnViewAll.Click

?Connection to the Server
Using connection3 As New SqlConnection("SERVER=x.x.x.x;database=ITDatabase;trusted_connection=yes;")

?Variables declarations
Dim cmd As New SqlCommand("SELECT AssetNo [ASSET NO], t2.TYPE
From ASSETINFO t1
INNER Join TypeInfo t2 on t1.TYPEID = t2.TYPEID
Order By AssetNo ASC;", connection3)

Dim Adpt As New SqlDataAdapter(cmd)
Dim table As New DataTable

Adpt.Fill(table)
bsAllData.DataSource = table
dataGridAssetEdit.DataSource = bsAllData

txtAssetNo2.DataBindings.Clear()
txtAssetNo2.DataBindings.Add("Text", Me.bsAllData, "ASSET NO")

cboAssetType2.DataBindings.Clear()
cboAssetType2.DataBindings.Add("Text", Me.bsAllData, "Type")

End Sub


?Button to Update
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

?Code used but doesn?t do anything.
bsAllData.EndEdit()
Adpt.Update(table)

End Sub


Regards,
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,457
Location
Sydney, Australia
Programming Experience
10+
Firstly, when you post code snippets, please just post it as plain text within appropriate formatting tags, i.e.

[xcode=vb]your code here[/xcode]

As for your issue, I see that you're creating a new data adapter and assigning it to a local variable named Adpt in your btnViewAll.Click event handler and then you are using a variable of the same name in your btnUpdate.Click event handler. That suggests that you might think that you're using the same data adapter but you're not. You should be using a single variable declared at the class level, creating one data adapter object and assigning it to that one variable, then using that same data adapter to retrieve and save the data.
 

lmalave

New member
Joined
Apr 21, 2016
Messages
4
Programming Experience
1-3
Using: SQL Server 2014, VB2015 WINFORMS, DataGridview, Textboxes, ComboboxesI was able to update the SQL Server with a new SQL Update Query since I also had Identity Fields from other tables that needed to be updated. At the Class level I declared an ODBC Connection (connODBC) and an ODBC Command (Query_SQL_Update) variables. Yes I am using an ODBC connection. This will work also with a SQL Connection just make the changes from ODCBCOMMAND to SQLCOMMAND and ODBCCONNECTION to SQLCONNECTION).Working Code:

Class level Code:


Public Class frmAssetEdit

Place in Class Level
Dim connODBC As New OdbcConnection("DSN=IT_INVENTORY")   'Asigns the ODBC connecton
Dim Query_SQL_Update As OdbcCommand                                 'Declares the Query_SQL_Update as an OdbcCommand command



Update Button Code:

This is the Button used for the Update
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
 
'Assigns the variable Query_SQL_Update the SQL command to update the fields
Query_SQL_Update = New OdbcCommand("UPDATE ASSETINFO 
 
SET SerialNO = '" & txtSerialNo2.Text & "', 
LNAME = '" & txtlname2.Text & "' , 
FNAME = '" & txtfname2.Text & "' ,  
TypeID = '" & cboType2.SelectedValue & "', 
MakeID = '" & cboMake2.SelectedValue & "' , 
ModelID = '" & cbomodel2.SelectedValue & "', 
OwnerID = '" & cboAssetOwner2.SelectedValue & "', 
SITEID = '" & cboSite2.SelectedValue & "', 
BLDGID = '" & cboBLDG2.SelectedValue & "', 
DEPTID = '" & cboDept2.SelectedValue & "', 
  
WHERE ASSETNO = '" & txtAssetNo2.Text & "'")

'Assigns the connecton to the command
Query_SQL_Update.Connection = connODBC
 
'Executes the Command Query for the update
Query_SQL_Update.ExecuteNonQuery()
 
End Sub


Please view the attachment for details.

Regards and thank you, Updating_SQL_SERVER_ QUERY_WINFORMS.jpeg
 
Last edited:
Top Bottom