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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
have you tried the other suggestion to place a new one on the from without changing any properties?
0
 
É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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Éric MoreauSenior .Net ConsultantCommented:
after you filled it (so after authorsAdapter.Fill(DSBEN))
0
 
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
 
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
All Courses

From novice to tech pro — start learning today.