Learn how to a build a cloud-first strategyRegister Now

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

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"
Else
    Msgbox "AddressID for the record is..." & rec!AddressID
End If

Suggestions would be gratefully received.

J
0
jfell
Asked:
jfell
  • 5
  • 4
1 Solution
 
af4643Commented:
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
0
 
jfellAuthor Commented:
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
0
 
af4643Commented:
'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


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.

 
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,

J
0
 
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??

J
0
 
af4643Commented:
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.

0
 
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!

J
0
 
af4643Commented:
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.
0
 
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.
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!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now