[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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
0
smm6809
Asked:
smm6809
  • 13
  • 11
1 Solution
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
Please post the code as it is currently.
0
 
Fernando SotoCommented:
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
 
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
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 SotoCommented:
Not a problem smm6809, glad I was able to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now