Question Need to update the SQL Server with the values FROM txtBOX

lmalave

New member
Joined
Apr 21, 2016
Messages
4
Programming Experience
1-3
Hello to all,

I am Using:
SQL Server 2014, VS2015, VB, WIN FORMS.

1. I need to update the SQL Server with the values that are entered into the Textbox.
2. I have a textbox where I enter an Asset Number and I click a Search Button and it populates the Datagridview control with the results.

The Code Used for #2 is:

Using connection As New SqlConnection("SERVER=x.x.x.x;database=ITDatabase;trusted_connection=yes;")
Dim cmd As New SqlCommand("SELECT AssetNo [ASSET NO.], t2.TYPE [TYPE], t3.Status [STATUS]

From ASSETINFO t1

INNER Join TypeInfo t2 on t1.TYPEID = t2.TYPEID
INNER Join STATUSINFO t3 on t1.STATUSID = t3.STATUSID
WHERE AssetNo Like '%" + txtSearchData.Text + "%' Order By AssetNo ASC;", connection)

Dim Adpt As New SqlDataAdapter(cmd)
Dim ds As New DataSet()

If (Adpt.Fill(ds, "EQUIPINFO")) Then
dataGridAssetEdit.DataSource = ds.Tables(0)

End If

Else

MessageBox.Show("Record(s) not found.")

End If
End Using


3. The Datagridview populates correctly with the data.
4. I Click on a row in the Datagridview and it populates several Textboxes with the corresponding values.

The Code Used for # 4 is:

If e.ColumnIndex >= 0 AndAlso e.RowIndex >= 0 Then

txtAssetNo2.Text = dataGridAssetEdit.Rows(e.RowIndex).Cells(0).Value.ToString
cboAssetType2.Text = dataGridAssetEdit.Rows(e.RowIndex).Cells(1).Value.ToString
cbostatus2.Text = dataGridAssetEdit.Rows(e.RowIndex).Cells(2).Value.ToString

End If

5. I would like to make changes in the Textboxes and be able to Click an Update Button to update the new values to the SQL Server. This is where I am stuck.


Regards and Thank you,
 

Attachments

  • UPDATE_SQL_WITH_TEXTBOX.jpg
    UPDATE_SQL_WITH_TEXTBOX.jpg
    80.5 KB · Views: 33

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,580
Location
Sydney, Australia
Programming Experience
10+
You're doing it wrong. You don't need any code to populate the TextBox and ComboBoxes. If you use data-binding then the controls will be populated automatically when you select a row in the grid and you then don't need any code to push the changes back again. When you bind the grid, you bind the TextBoxes too:
'If all you need is a DataTable then don't create a DataSet.
Dim table As New DataTable

Adpt.Fill(table)

'Use a BindingSource that you added in the designer.
myBindingSource.DataSource = table

dataGridAssetEdit.DataSource = myBindingSource
txtAssetNo2..DataBindings.Add("Text", myBindingSource, "AssetNo")
cboAssetType2.DataBindings.Add("Text", myBindingSource, "Type")
cbostatus2.DataBindings.Add("Text", myBindingSource, "Status")
Now, when it's time to save, you simply commit the current edit via the BinsingSource and then use a data adapter to save the changes. It makes sense that it be the same data adapter that you used to retrieve the data in the first place, so you'd need to adjust that:
myBindingSource.EndEdit()
myDataAdapter.Update(table)
 

lmalave

New member
Joined
Apr 21, 2016
Messages
4
Programming Experience
1-3
Thank you jmcilhinney!

Your solution worked as I needed it and your explanation help me understand the reasoning behind the code.

Regards
 
Top Bottom