Solved

Update SQL database table from vb.net datagridview

Posted on 2016-08-09
24
58 Views
Last Modified: 2016-08-15
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
Comment
Question by:smm6809
  • 13
  • 11
24 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41748835
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
 

Author Comment

by:smm6809
ID: 41748856
Got this error on changing:
Unhandled exception type system.invalidoperationexception: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749005
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
 

Author Comment

by:smm6809
ID: 41749051
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749246
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
 

Author Comment

by:smm6809
ID: 41749249
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749268
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
 

Author Comment

by:smm6809
ID: 41749296
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749355
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
 

Author Comment

by:smm6809
ID: 41749367
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
 

Author Comment

by:smm6809
ID: 41749407
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749413
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41749420
Please post the code as it is currently.
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41749754
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
 

Author Comment

by:smm6809
ID: 41750294
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41750308
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
 

Author Comment

by:smm6809
ID: 41750314
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41750344
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
 

Author Comment

by:smm6809
ID: 41750905
When you ask for the test database files what are you looking for?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41750914
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
 

Author Comment

by:smm6809
ID: 41751987
Right now I am using SQL server and I get to it through management studio so it's not an .mdb file.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41752360
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
 

Author Closing Comment

by:smm6809
ID: 41756588
Got it working based on this info. Thank you
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41756597
Not a problem smm6809, glad I was able to help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now