Accessing SQL server data via a datagrid control

Posted on 2006-04-20
Last Modified: 2010-04-23
I have just started using VB.NET having come from an Access background.  I'm sure this is a simple question ....

I have created a form, on which there is a datagrid control (dataGridView1) which gets it's data from a DataSet (AddressSQLDataSet) linked to a SQL database.  The data appears on the datagrid control as I expect.

Problem 1
I want to Add, Delete and Edit the data in the datagrid and to change the corresponding data in the SQL database, but any changes I make in the datagrid are not kept.  If possible I would like to manipulate data with commands and methods similar to what is found in VBA.  e.g. Addnew, Edit, Update etc. but this is not essential.

Problem 2
In addition I would like to search the underlying dataset as I would a recordset in VBA, i.e.

'I know this doesn't work...
Dim rec as Recordset
Set rec = AddressSQLDataSet.OpenRecordSet

rec.movefirst "LastName = Jon*"

If rec.nomatch
    Msgbox "Can't find that"
    Msgbox "AddressID for the record is..." & rec!AddressID
End If

Suggestions would be gratefully received.

Question by:jfell
    LVL 2

    Expert Comment

    Create a sqldataadapter that gets your data from the database and store it in a datatable. The datatable acts as the source for the datagrid.

    The adapter has different methods that you can define (selectcommand, deletecommand, insertcommand, updatecommand).

    After the changes have been made in your grid, just call the desired method to update your database.

    You can search the datatable like this:
    Dim selRow() As DataRow
    selRow = datatable.Select("id=123")          'your searching column "id" for value "123"
    selRow(0)           'selRow is an array of the results...access each array row by index

    Author Comment


    I've been working at this for hours now and can't seem to get it to work.  Because of migrating from VBA I am having problems with the syntax of the language.  I will give the code that I have done so far with comments as to what is not working.  

            Dim dataSet As New DataSet
            Dim connectionString As String
            Dim queryString As String
            Dim table As DataTable
            Dim selrow As DataRow

            connectionString = "Data Source=HP;Initial Catalog=PupilReportsSQL;Integrated Security=True"

            queryString = "SELECT TOP (100) PERCENT dbo.tblStudents.FirstName, dbo.tblStudents.LastName"
            queryString = queryString & " FROM dbo.tblstudents;"

            'queryString = "dbo.tblstudents"

            Using connection As New SqlConnection(connectionString)
                Dim adapter As New SqlDataAdapter()
                adapter.SelectCommand = New SqlCommand( _
                    queryString, connection)


                'So far, so good, the code runs fine up to this point.

                table = dataSet.Tables("tblStudents")

                'Following line gives a build error for 'table.firstname' and 'table.lastname'
                Debug.Print("First record on the list is:" & table.firstname & " " & table.lastname)

                'Following line gives a build error for 'table.sel....John'")
                selrow = table.Select("FirstName = 'John'")

                'Search for firstName "John", take first record and change the lastname to "Smith"

                selrow(0).lastname = "Smith"

                'Save changes back to SQL Database

                table.AcceptChanges()     '<=I have a feeling this won't work

            End Using

    As you see, there are just a few lines where I need the correct syntax to get the thing to work.  Your help is appreciated!

    LVL 2

    Expert Comment

    'Following line gives a build error for 'table.firstname' and 'table.lastname'
                Debug.Print("First record on the list is:" & table.firstname & " " & table.lastname)

    -you cannot access the values that way.
        1st row, firstname column value can be accessed this way:  table.rows(0).item("firstname")
        1st row, lastname column value: table.rows(0).item("lastname")

    -to select the row w/ first name john
        Dim selrow() = new datarow
        selRow = table.Select("firstname=John")
        selRow(0).item("lastname") = "Smith"

    adapter.updatecommand.executenonquery 'you must define your update command with the correct query to update your database

    if you're using visual studio .net, there is a wizard that will automatically create all of your commands


    Author Comment

    Great, almost there.

    My apologies in the delay in writing, I have other things which are urgent.

    With a bit of tweaking the code works.

    One last thing, I'm using Visual studio, so how do I invoke the wizard to create the commands?

    All I have to do now is to get the data table to write the data back to the SQL database.

    Thanks again,


    Author Comment

    ... OK I have the code which was automatically generated BUT I can't find the command which updates the underlying table in the SQL server database.  I searched for 'updatecommand' and 'executenonquery' to no avail.

    I think I simply need an example of code which updates the SQL server database- it seems like a simple task but it has taken me a disporportionate amount of time to do it :-(

    Can you enlighten me??

    LVL 2

    Expert Comment

    In your toolbox in VS, click the data tab. Drag a SqlDataAdapter to your form in the design window (that will start the wizard). You have to define a connection, if you haven't done so yet. Select, "Use SQL Statements". From there, you you need to specify your select statement. It will automatically create all 4 commands. You can look at the properties of the dataadapter and see what strings were created for each of the commands.

    don't worry, it's hard when you first learn it, but once you get'll save you much time.

    Another thing you can do is create Stored Procedures and in your properties, specify their names for each of the commands you want to do. Don't forget to change the commandtype to stored procedure.


    Author Comment

    We must be getting close to the solution ... but not yet.

    On the toolbox, under the Data tab there are the following controls: Pointer, Dataset,  DataGridView, BindingSource, BindingNavigator, ReportViewer

    There is no SqlDataAdapter

    I apolgise in advance if I have missed something blindingly obvious!

    LVL 2

    Accepted Solution

    Ok, go to your "Tools" menu at the top and select, "Add/Remove Toolbox Items". Scroll down and make sure those tools are checked. Then try again.

    Author Comment

    I'm sorry for the delay in replying- I had to finish off some important work.

    I put the item on the toolbar, and now I need to go through the steps you outlined.  I don't have much time to try it at the moment, but it is looking good so far, so I will accept you answer in faith that it will work.

    Thanks for your comments.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 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