[RESOLVED] Datagridview update from query

I have a datagridview and i want it to display the results from a query. How would i go about doing this. Here is my code so far:

Code:

---

        Dim searchStr As String = S_date_menuItem.Text.Remove(0, S_date_menuItem.Text.IndexOf("'") + 1)
        searchStr = searchStr.TrimEnd("'")

        adapter = New OleDb.OleDbDataAdapter("select * from account where Date= @Date", acctconstr)
        adapter.SelectCommand.Parameters.AddWithValue("@Date", searchStr)

---

that is my query but i don't know how to get my datagrid to display the results.

This can all be done automatically using datasets, all the work is done for you. (Depends on which Ver of Visual Studio your running)

First, add the database you wish to run the command against to the data sources (Shift+Alt+D), go through the wizard specifying the tables that you need and then you'll be able to add a query to the dataset which can then be used to fill your datatable.

Or, you could put just a little more effort in, and learn to do it with code (you're almost there . . .):

Option #1 (the way you are doing it, with an oleDbDataAdatper (I am not a fan of the DataAdapter, but here goes):

Code:

---


      'Create a reference to your DataGridView Control:
        Dim dgv As DataGridView = Me.DataGridView1
       
        'This is a SELECT Command from one of my local Access Databases:
        Dim strSQL As String = "SELECT * FROM tblProperty"

        'Create a DataTable which can then be bound to the DataGridview:
        Dim dt As New DataTable

        'I adapted some code to use the DataAdapter. I don't ordinarily use the DataAdapter, so if
        'this looks a little strange, that's why:
        Using cn As New OleDb.OleDbConnection(My.Settings.acCOnnection)
            Using cmd As New OleDb.OleDbCommand(strSQL, cn)
                Dim da As New OleDb.OleDbDataAdapter(cmd)

                'Here's the key: FIll the DataTable:
                da.Fill(dt)
            End Using
        End Using

        'Then bind the DataTable as the DataGridView Datasource:
        dgv.DataSource = dt

---

Option #2: Use a DataReader + DataTable: (I Prefer this way):

Code:

---


        'Create a reference to your DataGridView Control:
        Dim dgv As DataGridView = Me.DataGridView1

        'This is a SELECT Command from one of my local Access Databases:
        Dim strSQL As String = "SELECT * FROM tblProperty"

      'Create a DataTable which can then be bound to the DataGridview:
        Dim dt As New DataTable

        Using cn As New OleDb.OleDbConnection(My.Settings.acCOnnection)
            Using cmd As New OleDb.OleDbCommand(strSQL, cn)
                cn.Open()
                Dim dr As OleDb.OleDbDataReader
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                dt.Load(dr)
            End Using
        End Using

        'Then bind the DataTable as the DataGridView Datasource:
        dgv.DataSource = dt

---

Resolved thanks so much for your help RunsWithScissors!

To 'RunsWithScissors':

What do you have against the DataAdapter? Just curious.

Quote:

---

Originally Posted by RunsWithScissors

I am not a fan of the DataAdapter

---

Guess what: the DataTable.Load method internally creates a LoadAdapter, whose base class is System.Data.Common.DataAdapter. Apparently you are a fan after all. The DataTable.Load and OleDbDataAdapter.Fill methods end up using the exact same methods of the DataAdapter class to populate the DataTable from a DataReader.

Quote:

---

Originally Posted by jmcilhinney

Guess what: the DataTable.Load method internally creates a LoadAdapter, whose base class is System.Data.Common.DataAdapter. Apparently you are a fan after all. The DataTable.Load and OleDbDataAdapter.Fill methods end up using the exact same methods of the DataAdapter class to populate the DataTable from a DataReader.

---

Uh, of COURSE that's why I am not a fan . . . ;-)

Really, it's just my personal preference, for no real reason I can explain. Probably due to nothing more than my comfort zone.

However, thanks to JMC, I will now, on general principle, have to stop using the DAtaTable.Load Method . . . (kidding).

You should choose the right tool for the job, whatever job and tool that might be. In my CodeBank thread of ADO.NET examples I use a DataReader to populate a DataTable that will be displayed but not updated, because a DataReader is lighter weight than a DataAdapter. That said, I use a DataAdapter to populate a DataTable that will be displayed AND updated, because a DataAdapter has the functionality to do the updating as well as the population. The DataReader is lighter weight for a reason: it can't do as much as the DataAdapter. If the job you want done requires the functionality that the DataAdapter provides then you should use a DataAdapter. If the job requires only the functionality that the DataRaeder provides then you should use a DataReader.

I was (obviously, I hope) being humorous.

I tend to Create, use, and discard my commands and connections at the time of use. Also, I am USUALLY using sprocs against SQL Server, and rarely using in-line sql.

Having absorbed your always valuable input, I can totally see why the dataAdapter would be the proper choice for the Display + Update paradigm.

My reason for not having used it enough would be that I usually avoid data-bound controls (at least, in the updatable sense - I have no fear of binding a list or table for display).

It could very well be that I should open my brain to some new ways of doing things. However, when I set out to learn this stuff, I am stubborn, and try to learn the hard, old-fashioned way before I let the slick VS IDE and Framework do the work for me.

I am coming to realize that Programming is addictive, and that the more I learn, the more there is that I didn't know. I may not be satisfied until I know how to do it all in assembly . . . (OK, THAT may be pushing it a bit). :wave:

Quote:

---

Originally Posted by RunsWithScissors

I was (obviously, I hope) being humorous.

I tend to Create, use, and discard my commands and connections at the time of use. Also, I am USUALLY using sprocs against SQL Server, and rarely using in-line sql.

Having absorbed your always valuable input, I can totally see why the dataAdapter would be the proper choice for the Display + Update paradigm.

My reason for not having used it enough would be that I usually avoid data-bound controls (at least, in the updatable sense - I have no fear of binding a list or table for display).

It could very well be that I should open my brain to some new ways of doing things. However, when I set out to learn this stuff, I am stubborn, and try to learn the hard, old-fashioned way before I let the slick VS IDE and Framework do the work for me.

I am coming to realize that Programming is addictive, and that the more I learn, the more there is that I didn't know. I may not be satisfied until I know how to do it all in assembly . . . (OK, THAT may be pushing it a bit). :wave:

---

It's starting to sound to me that your issue is with TableAdapters, not DataAdapters. DataAdapters have nothing to do with data-binding or any wizards or IDE features. They are a standard Framework class, just like commands and data readers. They are used exclusively in code and all they do is tie together four commands that are used to retrieve, delete, insert and update data. If your issue is with the Data Source wizard and typed DataSets then that's nothing to do with DataAdapters.

Even if you do use the Data Source wizard to generate a typed DataSet, that's still got nothing specifically to do with data-binding. Just because you use a typed DataSet doesn't mean that you have to drag tables and columns to a form and have the IDE do all the work for you. You can still create your instances in code if you want. You can still use data-binding without using drag and drop in the designer too. You can also use stored procedures with a typed DataSet and TableAdapters.

There are lots of different concepts here and many people seem to get them confused. I happily use the Data Source wizard to create typed DataSets because they are easier to work with in code, but I pretty much never drag items from the Data Sources window and have the IDE generate the controls and data-bindings. You can pick and choose which part syou want to use which you don't, so don't throw out the baby with the bath water.

Quote:

---

It's starting to sound to me that your issue is with TableAdapters, not DataAdapters. DataAdapters have nothing to do with data-binding or any wizards or IDE features. They are a standard Framework class, just like commands and data readers. They are used exclusively in code and all they do is tie together four commands that are used to retrieve, delete, insert and update data. If your issue is with the Data Source wizard and typed DataSets then that's nothing to do with DataAdapters.

---

Nope. And I probably overstated my position, along with lacking some understanding of the proper use of the tools. By "Not a fan of . . . " I meant "Not what I tend to use".

I messed with the vs data sources for a minute, and a little with the table adapter. But I tend to do all of it in code, and connect as needed. I was resistent to having vs handle my data connectivity, and just became accustomed to doing it myself.

Most of my Data Access is handled thusly:

Code:

---


DIm dt As New DataTable

Using SomeConnection As New SQLConnection(My.Settings.SomeConnectionString)
    Using cmd As New SQLCommand("SprocName", ComeCOnnection)
        Dim prm1 As New SQLParameter("@Parameter1Name", otherinitialization)
        cmd.Paramters.Add(prm1)

        '. . . Etc. For more params

        SomeConnection.Open
       
        'If There is a resultSet:
        Dim dr As SQLDataReader
        cmd.ExecuteReader
        dt.Load(dr)

      'Otherwise (INSERT or UPDATE sprocs):
      cmd.ExecuteNonQuery
     
    End Using
ENd Using

---

I probably put some redundant effort into creating my own classes which replicates some of the functionality offered by the DataAdapter (Standard SELECT, INSERT, UPDATE methods, for example).

Your explanation above was very instructive, however, and has me re-thinking how I do some things. With so many classes in the framework, it is often difficult to discern what the proper tool is for the job.

JMC, you are font of know-how, and a patient man. Thanks for your feedback and comments!

-J

As a further aside, I have completed my first stab at a code-generator, which constructs my da classes just the way I like them.

Having done that, I realized that what I had done was create a cruder, less-efficient means of the smae functionaliity provided by several existing framework classes (DataAdapter and/or Table Adapter, for example . . . )

That said, creating the project was a VERY valuable and instructive acedemic excercise. I can point it at any SQL Server or MS Access Database, and it will create a class to represent each table in the Database, complete with SELECT, INSERT, UPDATE, and DELETE methods.

It ALSO will create SQL code for sprocs for each DML above, and insert them into the database. You can tell it to do that, or to use in-line SQL in the vb code, depending on preference/needs. Obviously, for MS Access, it uses in-line SQL in the vb DCode.

You should have seen my face when it WORKED!