DataSet into DataGridView

[Scott Spence]

Hi all,

I have the below code I’m using to populate three DataGridView’s I
have on my form without much success at the moment as the DataGridView
only displays one column of data once it has run :/

In the example I am just trying to get it working for one DataGridView
with the intention of passing in SQL to define the DataSet’s once its
working.

I am unsure weather I have missed something because I can view all the
data in the visualiser but it only seems to populate the one column in
the DataGridView.

Please help.

Scott.

Private Sub cboUserName_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
cboUserName.SelectedIndexChanged

Dim lngUserID As Long

For i As Integer = 0 To ALWDBDataSet.Tables
("tblUser").Rows.Count - 1
If ALWDBDataSet.Tables("tblUser").Rows(i).Item("UserName")
= cboUserName.Text.ToUpper Then
lngUserID = ALWDBDataSet.Tables("tblUser").Rows(i).Item
("UserID")
Exit For
End If
Next

'// Initialize a new instance of the OleDbConnection Class
objConnection = New OleDbConnection(strConncectionString)

'// Build query string
Dim strSQL As String = "SELECT * FROM tblUserLogs WHERE UserID
= " & lngUserID
'// Initialize a new instance of the OleDbCommand Class
objCommand = New OleDbCommand(strSQL, objConnection)

Call PopulateGrid(dgvLogInfo, "tblUserLogs")


End Sub




Private Sub PopulateGrid(ByVal grd As DataGridView, ByVal strTable
As String)

'// Initialize a new instance of the OleDataAdapter class
objDataAdapter = New OleDbDataAdapter

'// Initialize a new instance of the DataSet Class
objDataSet = New DataSet

'// Set the SelectCommand for the OleDbDataAdapter
objDataAdapter.SelectCommand = objCommand

Try
'// Poplate the DataSet
objDataAdapter.Fill(objDataSet, strTable)

'// Bind the DataSet to the DataGrid
grd.DataSource = objDataSet
grd.DataMember = strTable

'// Set the AlternatingRowsDefaultCellStyle.BackColor
property
grd.AlternatingRowsDefaultCellStyle.BackColor =
Color.WhiteSmoke

'// Set the CellBorderStyle property
grd.CellBorderStyle = DataGridViewCellBorderStyle.None

'// Set the SelectionMode property
grd.SelectionMode =
DataGridViewSelectionMode.FullRowSelect

'// Set the AutoSizeColumnsMode property
'grd.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells

Catch OleDbException As OleDbException
MessageBox.Show(OleDbException.Message)
End Try

'// Cleanup
objCommand.Dispose()
objCommand = Nothing
objDataAdapter.Dispose()
objDataAdapter = Nothing
objDataSet.Dispose()
objDataSet = Nothing
objConnection.Dispose()
objConnection = Nothing

End Sub

 

[Rich P]

try something like this:

Initially:

dataAdapter1.SelectCommand.CommandText = "Select fld1, fld2, fld3 from
tbl1"
dataAdapter1.Fill(dataset1, "tbla")
datagridview1.datasource = dataset1.Tables("tbla")

Now a new query using a different table and a different number of
fields:

dataset1.Tables.Remove(dataset1.Tables("tbla"))
datagridview1.Columns.Clear

dataAdapter1.SelectCommand.CommandText = "Select fld5, fld6, fld7, fld8,
fld9 from tbl2"
dataAdapter1.Fill(dataset1, "tbla")
datagridview1.datasource = dataset1.Tables("tbla")

It seems like VS remembers the original table it creates and the
original number of columns. So you have to remove the table from the
dataset and remove the columns from the datagridview each time. Then
run the new query.



Rich

 

[Scott Spence]

try something like this:

Initially:

dataAdapter1.SelectCommand.CommandText = "Select fld1, fld2, fld3 from
tbl1"
dataAdapter1.Fill(dataset1, "tbla")
datagridview1.datasource = dataset1.Tables("tbla")

Now a new query using a different table and a different number of
fields:

dataset1.Tables.Remove(dataset1.Tables("tbla"))
datagridview1.Columns.Clear

dataAdapter1.SelectCommand.CommandText = "Select fld5, fld6, fld7, fld8,
fld9 from tbl2"
dataAdapter1.Fill(dataset1, "tbla")
datagridview1.datasource = dataset1.Tables("tbla")

It seems like VS remembers the original table it creates and the
original number of columns.  So you have to remove the table from the
dataset and remove the columns from the datagridview each time.  Then
run the new query.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

i also added grd.AutoGenerateColumns = True after the clear method
and it seems to be working ok now.

thanks for your help