Visual Basic fill data set

Hi I am using Visual Studio (VBasic) that creates an application that executes a select statement and returns the code to a dataset grid, please see code below


Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim objConnection As SqlClient.SqlConnection = New _
        SqlClient.SqlConnection("Server=svricassql5;Database=pubs;Trusted_Connection=yes")

        ' Create a SqlDataAdapter for the Suppliers table.
        Dim authorsAdapter As SqlClient.SqlDataAdapter = _
        New SqlClient.SqlDataAdapter()

        ' A table mapping names the DataTable.
        authorsAdapter.TableMappings.Add("Table", "Authors")

        'open connect
        objConnection.Open()

        ' Create a SqlCommand to retrieve data.
        Dim productsCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand( _
                       "Select au_lname FROM authors", objConnection)
        authorsAdapter.SelectCommand = productsCommand

        ' Fill the DataSet.
        Dim DSBEN As DataSet = New DataSet("Authors")
        authorsAdapter.Fill(DSBEN)
        'Dim objCommand As SqlClient.SqlCommand
       

        productsCommand.ExecuteNonQuery()
        objConnection.Close()

    End Sub

End Class

My problem is the code runs with out error byt the results do not appear in the data grid! Could anyone take a look to see where I am going wrong please?
ac_davis2002Asked:
Who is Participating?
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.

Éric MoreauSenior .Net ConsultantCommented:
for sure, you don't have to run ExecuteNonQuery which is only good for INSERT/UPDATE/DELETE

You also need to keep the connection open

There is nothing here that displays datat into a grid. you need something like:
datagrid1.datatasource = dsben.tables(0)
0
ac_davis2002Author Commented:
thanks, but can you tell me where to put the datagrid1.datatasource = dsben.tables(0)?
0
Éric MoreauSenior .Net ConsultantCommented:
after you filled it (so after authorsAdapter.Fill(DSBEN))
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ac_davis2002Author Commented:
ok I have made the changes

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim objConnection As SqlClient.SqlConnection = New _
        SqlClient.SqlConnection("Server=svricassql5;Database=pubs;Trusted_Connection=yes")

        ' Create a SqlDataAdapter for the Suppliers table.
        Dim authorsAdapter As SqlClient.SqlDataAdapter = _
        New SqlClient.SqlDataAdapter()

        ' A table mapping names the DataTable.
        authorsAdapter.TableMappings.Add("Table", "Authors")

        'open connect
        objConnection.Open()

        ' Create a SqlCommand to retrieve data.
        Dim productsCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand( _
                       "Select au_lname FROM authors", objConnection)
        authorsAdapter.SelectCommand = productsCommand

        ' Fill the DataSet.
        Dim DSBEN As DataSet = New DataSet("Authors")
        authorsAdapter.Fill(DSBEN)
        DGAdam.DataSource = DSBEN.Tables

        'Dim objCommand As SqlClient.SqlCommand
       

        'productsCommand.ExecuteNonQuery()
        'objConnection.Close()

    End Sub

   
   
End Class


but still cant see any results, any ideas?
0
Éric MoreauSenior .Net ConsultantCommented:
You have to specify a table:
DGAdam.DataSource = DSBEN.Tables(0)
0
ac_davis2002Author Commented:
Am I being thick or do I just need and a zero

DGAdam.DataSource = DSBEN.Tables(0) or a table name?

sorry, you'll never guess I'm new to this?
0
Éric MoreauSenior .Net ConsultantCommented:
You can give the index or the table name. Is it working?
0
ac_davis2002Author Commented:
no..afraid not, i put a trace on the db and the select statement is reaching the database, it seems the results are not reaching the application
0
Éric MoreauSenior .Net ConsultantCommented:
Can you try this:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'open connect
        Dim objConnection As SqlClient.SqlConnection = New _
        SqlClient.SqlConnection("data source=svricassql5;initial catalog=pubs;Trusted_Connection=yes")
        objConnection.Open()

        ' Create a SqlCommand to retrieve data.
        Dim productsCommand As New SqlClient.SqlCommand("Select au_lname FROM authors", objConnection)

        ' Create a SqlDataAdapter for the Suppliers table.
        Dim authorsAdapter As New SqlClient.SqlDataAdapter()
        authorsAdapter.SelectCommand = productsCommand

        ' Fill the DataSet.
        Dim DSBEN As New DataSet
        authorsAdapter.Fill(DSBEN)

        DGAdam.DataSource = DSBEN.Tables(0)

    End Sub
0
ac_davis2002Author Commented:
Great thanks!

Got to leave now but will try first thing...

Thanks in advance for all you help!!!
0
ac_davis2002Author Commented:
mmmm gave it a try but no joy....

I am now looking at the properies of the datagridview and I can see the name as DGAdam and the tabindex = 1 does that sound coorect?
0
Éric MoreauSenior .Net ConsultantCommented:
what do you have in : msgbox(dsben.tables(0).rows.count) ?
0
ac_davis2002Author Commented:
can msgbox(dsben.tables(0).rows.count) be found in the datagrid properties?

where should i look?
0
Éric MoreauSenior .Net ConsultantCommented:
Common, msgbox is to display a MessageBox dialog on the screen, it should display a number (dsben.tables(0).rows.count) into this dialog. This number will be the number of rows into your datatable. What is this number?
0
ac_davis2002Author Commented:
cool, its showing 37
0
Éric MoreauSenior .Net ConsultantCommented:
then your 37 rows should be displayed into your grid?

what kind of control is DGAdam?

You can also try to place a brand new DataGridView control on your from without changing any properties and set its DataSource property in your button click event.
0
ac_davis2002Author Commented:
its a datagridview
0
Éric MoreauSenior .Net ConsultantCommented:
have you tried the other suggestion to place a new one on the from without changing any properties?
0

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
ac_davis2002Author Commented:
mmmmm I have put the control on the form but, and this may be the answer....

can you give me an example of the command to set the datasource property

the name of the control is DataGridView1
0
ac_davis2002Author Commented:
Ignore that...being daft, its worked!!!!!

Thanks for staying with this!!!
0
ac_davis2002Author Commented:
Thanks again for your help, brillent
0
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
.NET Programming

From novice to tech pro — start learning today.