ac_davis2002
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("S erver=svri cassql5;Da tabase=pub s;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.TableMappin gs.Add("Ta ble", "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.SelectComma nd = productsCommand
' Fill the DataSet.
Dim DSBEN As DataSet = New DataSet("Authors")
authorsAdapter.Fill(DSBEN)
'Dim objCommand As SqlClient.SqlCommand
productsCommand.ExecuteNon Query()
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?
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("S
' Create a SqlDataAdapter for the Suppliers table.
Dim authorsAdapter As SqlClient.SqlDataAdapter = _
New SqlClient.SqlDataAdapter()
' A table mapping names the DataTable.
authorsAdapter.TableMappin
'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.SelectComma
' Fill the DataSet.
Dim DSBEN As DataSet = New DataSet("Authors")
authorsAdapter.Fill(DSBEN)
'Dim objCommand As SqlClient.SqlCommand
productsCommand.ExecuteNon
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?
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) )
ASKER
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("S erver=svri cassql5;Da tabase=pub s;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.TableMappin gs.Add("Ta ble", "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.SelectComma nd = productsCommand
' Fill the DataSet.
Dim DSBEN As DataSet = New DataSet("Authors")
authorsAdapter.Fill(DSBEN)
DGAdam.DataSource = DSBEN.Tables
'Dim objCommand As SqlClient.SqlCommand
'productsCommand.ExecuteNo nQuery()
'objConnection.Close()
End Sub
End Class
but still cant see any results, any ideas?
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("S
' Create a SqlDataAdapter for the Suppliers table.
Dim authorsAdapter As SqlClient.SqlDataAdapter = _
New SqlClient.SqlDataAdapter()
' A table mapping names the DataTable.
authorsAdapter.TableMappin
'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.SelectComma
' Fill the DataSet.
Dim DSBEN As DataSet = New DataSet("Authors")
authorsAdapter.Fill(DSBEN)
DGAdam.DataSource = DSBEN.Tables
'Dim objCommand As SqlClient.SqlCommand
'productsCommand.ExecuteNo
'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)
DGAdam.DataSource = DSBEN.Tables(0)
ASKER
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?
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?
ASKER
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("d ata source=svricassql5;initial catalog=pubs;Trusted_Conne ction=yes" )
objConnection.Open()
' Create a SqlCommand to retrieve data.
Dim productsCommand As New SqlClient.SqlCommand("Sele ct au_lname FROM authors", objConnection)
' Create a SqlDataAdapter for the Suppliers table.
Dim authorsAdapter As New SqlClient.SqlDataAdapter()
authorsAdapter.SelectComma nd = productsCommand
' Fill the DataSet.
Dim DSBEN As New DataSet
authorsAdapter.Fill(DSBEN)
DGAdam.DataSource = DSBEN.Tables(0)
End Sub
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("d
objConnection.Open()
' Create a SqlCommand to retrieve data.
Dim productsCommand As New SqlClient.SqlCommand("Sele
' Create a SqlDataAdapter for the Suppliers table.
Dim authorsAdapter As New SqlClient.SqlDataAdapter()
authorsAdapter.SelectComma
' Fill the DataSet.
Dim DSBEN As New DataSet
authorsAdapter.Fill(DSBEN)
DGAdam.DataSource = DSBEN.Tables(0)
End Sub
ASKER
Great thanks!
Got to leave now but will try first thing...
Thanks in advance for all you help!!!
Got to leave now but will try first thing...
Thanks in advance for all you help!!!
ASKER
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?
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).row s.count) ?
ASKER
can msgbox(dsben.tables(0).row s.count) be found in the datagrid properties?
where should i look?
where should i look?
Common, msgbox is to display a MessageBox dialog on the screen, it should display a number (dsben.tables(0).rows.coun t) into this dialog. This number will be the number of rows into your datatable. What is this number?
ASKER
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.
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.
ASKER
its a datagridview
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
can you give me an example of the command to set the datasource property
the name of the control is DataGridView1
ASKER
Ignore that...being daft, its worked!!!!!
Thanks for staying with this!!!
Thanks for staying with this!!!
ASKER
Thanks again for your help, brillent
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)