Accessing SQL server data via a datagrid control

1)
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.

2)
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

Hmmm.

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)

            adapter.Fill(dataSet)

            '*****************************************************
            '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!

J

'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

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,

J

... 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??

J

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 it...it'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.

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!

J

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.