Update SQL database table from vb.net datagridview

I load my datagridview correctly into a vb.net project but I don't know how to update the data. I looked online but nothing is clicking for me. Below is how I load the grid which works fine, I just need the code for the button.
 Public Sub psFillDatagrid()
        Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tvwIP Where strDealer='" & strSelected & "'"
            Dim dbadp As New SqlDataAdapter(sSQL, sqlComm)
            Dim dTable As New DataTable
            dbadp.Fill(dTable)
            dbadp.Dispose()
            dgIP.DataSource = dtable
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

??? Help below
    Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
        Dim dbadp As New SqlDataAdapter("Select * from tvwIP", sqlComm)
        Dim dTable As New DataTable
    End Sub
smm6809Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi smm6809;

OK, I used the last code you posted. I made changes to all the methods except the cmdUpdate_Click event. I do not think I missed anything so give it a try.
Public Class frmIP
    Dim sqlComm As String = "server=XXXXX;Database=CCCCC; Trusted_Connection=Yes;"
    Dim sqlcmd As SqlCommand
    Dim dbadp As SqlDataAdapter
    Dim dTable As New DataTable
    
    Private Sub frmIP_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tblDealers Where strDealer='" & strSelected & "'"
            Dim dbadp2 = New SqlDataAdapter(sSQL, sqlComm)
            Dim dTable2 As New DataTable
            dbadp2.Fill(dTable2)
            dbadp2.Dispose()
            cboDealer.DisplayMember = "strDealer"
            cboDealer.ValueMember = "numDealerID"
            cboDealer.DataSource = dTable2
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    Private Sub cboDealer_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboDealer.SelectedIndexChanged
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tblDealers Where strDealer='" & strSelected & "'"
            Dim dbadp3 = New SqlDataAdapter(sSQL, sqlComm)
            dbadp3.Fill(dTable)
            dbadp3.Dispose()
            txtIP.Text = dTable.Rows(0)("strCode").ToString()
            psFillDatagrid()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub psFillDatagrid()
        '' The variable conn is defined but never used
        '' Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tvwIP Where strDealer='" & strSelected & "'"
            dbadp = New SqlDataAdapter(sSQL, sqlComm)
            '' Add this line to your code to add CRUD commands for a single table query
            '' otherwise your database will not get updated
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dbadp)
            dbadp.Fill(dTable)
            '' You do not want to do this because when you go to update the database in the cmdUpdate event 
            '' it will not work
            '' dbadp.Dispose()
            dgIP.DataSource = dtable
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
        dbadp.Update(dTable)
    End Sub
End Class

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi smm6809;

You do not want to Dispose of objects that will be needed again as with the SqlDataAdapter and DataTable objects. Try making the following changes.
Dim dbadp As SqlDataAdapter  
Dim dTable As New DataTable

Public Sub psFillDatagrid()
    Dim conn As New SqlConnection(sqlComm)
    Try
        Dim sSQL As String
        Dim strSelected = cboDealer.Text
        sSQL = "Select * from tvwIP Where strDealer='" & strSelected & "'"
        dbadp = New SqlDataAdapter(sSQL, sqlComm)
        dbadp.Fill(dTable)
        dbadp.Dispose()
        dgIP.DataSource = dtable
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub


Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
    dbadp.Update(dTable)
End Sub

Open in new window

0
 
smm6809Author Commented:
Got this error on changing:
Unhandled exception type system.invalidoperationexception: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Fernando SotoRetiredCommented:
Hi smm6809;

Sorry since I have been using Entity Framework I overlooked this.
dbadp = New SqlDataAdapter(sSQL, sqlComm)
'' Add this line to your code to add CRUD commands for a single table query
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dbadp)
dbadp.Fill(dTable)

Open in new window

0
 
smm6809Author Commented:
Unfortunately, now nothing works. Comboboxes and textboxes not filling either This is all my code:

Imports System.ComponentModel
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class frmIP
    Public sqlComm As String = "server=XXXXX;Database=CCCCC; Trusted_Connection=Yes;"
    Public sqlcmd As SqlCommand
    Public dbadp As SqlDataAdapter
    Public dTable As New DataTable

    Private Sub frmIP_Load(sender As Object, e As EventArgs) Handles Me.Load
        Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tblDealers Where strDealer='" & strSelected & "'"
            dbadp = New SqlDataAdapter(sSQL, sqlComm)
            dbadp.Fill(dTable)
            dbadp.Dispose()
            cboDealer.DisplayMember = "strDealer"
            cboDealer.ValueMember = "numDealerID"
            cboDealer.DataSource = dTable
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


    End Sub
    Private Sub cboDealer_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboDealer.SelectedIndexChanged
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tblDealers Where strDealer='" & strSelected & "'"
            dbadp = New SqlDataAdapter(sSQL, sqlComm)
            dbadp.Fill(dTable)
            dbadp.Dispose()
            txtIP.Text = dTable.Rows(0)("strCode").ToString()
            psFillDatagrid()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub psFillDatagrid()
        Dim conn As New SqlConnection(sqlComm)
        Try
            Dim sSQL As String
            Dim strSelected = cboDealer.Text
            sSQL = "Select * from tvwIP Where strDealer='" & strSelected & "'"
            dbadp = New SqlDataAdapter(sSQL, sqlComm)
            dbadp.Fill(dTable)
            dbadp.Dispose()
            dgIP.DataSource = dtable
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub


    Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click
        dbadp.Update(dTable)
    End Sub
End Class
0
 
Fernando SotoRetiredCommented:
OK, I do not see that you made the last change I posted so what caused it to stop working? Are you getting run time exceptions? If so can you post them.
0
 
smm6809Author Commented:
I didn't get any exceptions. The problem is, I fill the combobox first and then populate the datagrid after something is selected from the combo. Do I have to put "Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dbadp)" in the combobox fill as well because it worked before?
0
 
Fernando SotoRetiredCommented:
No this line of code, "Dim builder As SqlCommandBuilder = New SqlCommandBuilder(dbadp)", only creates a simple Create, Update and Delete commands for the SqlDataAdapter to use for updating the SQL database. If it is not updating the database it is not in play.
0
 
smm6809Author Commented:
Funny when I added it to all of the comboboxes they started working again, however, the SQL database still not updating and no errors thrown. Also, when I change the combo, the grid doesn't update and a duplicate value gets added to the combo the next time I go in. When I change the code back to the way I had it before, the combo doesn't add duplicates and the grid changes as well. So I'm back to what isn't working which is the update.
0
 
Fernando SotoRetiredCommented:
Hi smm6809;

Here is your current issue. At the top of your code you have the following declarations.
Public sqlcmd As SqlCommand
Public dbadp As SqlDataAdapter
Public dTable As New DataTable

Open in new window

These objects being at class level causes them to be available throughout the class. This is normally not a problem but in your methods you are using these same variables so you overwrite them. So when you update the database when clicking the button who knows what is in the object dbadp, may no longer have the CRUD commands.  If you were to use the same datasource it should keep the controls in sync with each other, clicking a line in the DataGridView should change the items displayed in the other controls sharing the same datasource.
0
 
smm6809Author Commented:
I apologize for my ignorance, new to .net. What does CRUD mean? Also, if I change to Dim, like you said above, do they still go here:
Public Class frmIP
' HERE
0
 
smm6809Author Commented:
That's where I left it but the code keeps duplicating my combobox and putting the wrong information into the datagrid. The columns are different in the datagrid than what I expect and the combo has about 50 duplicates the first time I open it.
0
 
Fernando SotoRetiredCommented:
Not a problem smm6809. To your question, "What does CRUD mean?", it si an acronym for Create, Retrieve, Update, and Delete. You wrote the retrieve part when you created the SQL Select statement and when you created the object SqlCommandBuilder it created the other three statements for you.

To your question, "if I change to Dim, like you said above, do they still go here: Public Class frmIP", yes for class level variables. Be aware that these two statements are exactly the same at class level.
Private dbadp As SqlDataAdapter
Dim dbadp As SqlDataAdapter

Open in new window

The default for Dim at class level is Private. Also note that the following statement will make the variable accessible from anywhere in and out of the class when defined at class level
Public dbadp As SqlDataAdapter

Open in new window

0
 
Fernando SotoRetiredCommented:
Please post the code as it is currently.
0
 
smm6809Author Commented:
Thank you for your patience. I think I might be close now. I am using a view to show the data and the datagrid is somehow picking up fields that aren't even in the view. How could that be possible?
0
 
Fernando SotoRetiredCommented:
If you are using your original code you are more then likely overwriting the objects that are assigning to the DataSource of a control.
0
 
smm6809Author Commented:
I am using your changes, I even wrote another query to see if that made a difference, I also just listed the fields I wanted in the select string and didn't use *
0
 
Fernando SotoRetiredCommented:
Well that does not make sense seeming I'm using different DataAdapter and DataTables for each of the controls. If you can please zip up the following 1 The folder that contains the complete project 2 The test database files so that I can see first hand what is going on. Once you have done that upload them to a location on the internet where it can be download from such as Microsoft OneDrive, Google Docs, ... remember to place them in a public folder.
0
 
smm6809Author Commented:
When you ask for the test database files what are you looking for?
0
 
Fernando SotoRetiredCommented:
Hopefully you are using a Test database that has fictitious data in it or real data if that is permissible. The SQL database files such as databaseFileName.mdb as well as the databaseFileName.log if one exist but not necessary for the .log.
0
 
smm6809Author Commented:
Right now I am using SQL server and I get to it through management studio so it's not an .mdb file.
0
 
Fernando SotoRetiredCommented:
Hi, To find the location of the database files you can do the following. In Microsoft SQL Server Management Studio expand the database node select the database name you want to find the location of the files right click on that name and select the Properties. In the Database Properties window that opens click on Files. In the center pane you will find the two files there names is in the first column Database Files. You will find a column called Path which has the file path and the file name in the next column. as it is in the directory.
Voila_Capture-2016-08-11_10-37-10_AM.png
0
 
smm6809Author Commented:
Got it working based on this info. Thank you
0
 
Fernando SotoRetiredCommented:
Not a problem smm6809, glad I was able to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.