Accessing SQL server data via a datagrid control

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
jfellAuthor Commented:

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!

'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

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

jfellAuthor Commented:
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,

jfellAuthor Commented:
... 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??

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.

jfellAuthor Commented:
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!

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfellAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.