Link to home
Start Free TrialLog in
Avatar of ac_davis2002
ac_davis2002Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

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)
Avatar of ac_davis2002

ASKER

thanks, but can you tell me where to put the datagrid1.datatasource = dsben.tables(0)?
after you filled it (so after authorsAdapter.Fill(DSBEN))
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?
You have to specify a table:
DGAdam.DataSource = DSBEN.Tables(0)
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?
You can give the index or the table name. Is it working?
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
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
Great thanks!

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

Thanks in advance for all you help!!!
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?
what do you have in : msgbox(dsben.tables(0).rows.count) ?
can msgbox(dsben.tables(0).rows.count) be found in the datagrid properties?

where should i look?
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?
cool, its showing 37
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.
its a datagridview
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Ignore that...being daft, its worked!!!!!

Thanks for staying with this!!!
Thanks again for your help, brillent