Datagrid (RESOLVED)

[Hutty]

My button_click event does not show datagrid when execute command. Instead I have to click on plus sign, then the word table to see the results in grid format. How can I modifiy code to
show grid format went command is executed?

The code below is driven by the combobox selection. However, I'm able to get it to execute properly on first try. Subsequent click event result in error "cannot creat child list for Apr04". Any ideas what's wrong with the code?

thanks


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Inetpub\wwwroot\intelis.MDB")

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from APR04 where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)

DS = New System.Data.DataSet
DS.Clear()

MyCommand.Fill(DS)

DataGrid1.DataMember = "APR04"
DataGrid1.DataSource = DS
'MyConnection.Close()
End Sub

[CyberHawke]

A dataset is a container for one or more datatables.
The methods that attach a dataset to a datagrid do not know the number of tables your datagrid contains, so it just attaches the entire dataset to the grid, this is actually very nice behavior if you have multiple tables contained in your set and want to be able to view them in the grid without looping through the tables.

Your solution is to create a datatable object and assign it to the table in your dataset, then assign the datagrids datasource to the datatable:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim DS As System.Data.DataSet
    Dim DT As System.Data.DataTable
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection
 
    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=C:\Inetpub\wwwroot\intelis.MDB")
 
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
    "select * from APR04 where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)
 
    DS = New System.Data.DataSet
    DS.Clear()
 
    MyCommand.Fill(DS)
    DT = DS.Tables(0)
 
    DataGrid1.DataMember = "APR04"
    DataGrid1.DataSource = DT
    MyConnection.Close()
End Sub

Alternatively, you can fill your table instead of a dataset directly from your command:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim DT As System.Data.DataTable = New System.Data.DataTable
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection
 
    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=C:\Inetpub\wwwroot\intelis.MDB")
 
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
    "select * from APR04 where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)
 
    MyCommand.Fill(DT)
 
    DataGrid1.DataMember = "APR04"
    DataGrid1.DataSource = DT
    MyConnection.Close()
End Sub

[Hutty]

Thanks CYBER! The datatable displays properly with the addition.

Now, I still have the second issue still where when I click to execute the button_click on subsequent command I get the error"cannot create child list for field Apr04". Apr04 is the table name.

[CyberHawke]

how do you know that your table is name Apr04?

When you fill a dataset, the dataadapter automatically assigns names to tables like "Table", "Table2", etc...

If you did not explicitly tell the dataadapter that your table name is "Apr04" then it isn't.

try this code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim DS As New System.Data.DataSet
    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    Dim MyConnection As System.Data.OleDb.OleDbConnection
 
    DS.TableMappings.Add("Table", "APR04")
    MyConnection = New System.Data.OleDb.OleDbConnection( _
    "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source=C:\Inetpub\wwwroot\intelis.MDB")
 
    MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
    "select * from APR04 where Entity = '" & ComboBox1.Text.Trim & "'", MyConnection)
 
    MyCommand.Fill(DS)
 
    DataGrid1.SetDataBinding(DS, "APR04")
    MyConnection.Close()
End Sub

[Hutty]

Thanks Cyber. It makes sense in logic. I just assumed the table name from trying it using the datagrid properties on the form. I took out the line "Datagrid1.DataMember ="apr04" and it works.

thanks again.