11 replies to this topic

[jashsayani]

Ok, I have been working with AdoDC and displaying contents of a table in Access database. Now I have 20 tables in a database and want to display the contents of the Whole database file and not just the table. How do I do this ??

Thanks.

[WingedPanther]

The default assumption is to assume the tables are not related to each other. How are they related to enable them to be displayed at once? Are you envisioning them as one massive table, or a sequence of tables, or something else?

[jashsayani]

Well the tables are different categories with items and I want to display all items (from all categories/tables) into a drop-down box.

Hope it is more clear now. Thanks.

[debtboy]

jashsayani said:

Well the tables are different categories with items and I want to display all items (from all categories/tables) into a drop-down box.

Hope it is more clear now. Thanks.

Think again...
Typically you would execute a query like so...

        Dim sqlConnection1 As New SqlConnection

        Dim sqlInsertcommand1 As New SqlCommand

        Dim sqlUpdateCommand1 As New SqlCommand

        Dim sqlSelectCommand1 As New SqlCommand

        Dim sqlDeleteCommand1 As New SqlCommand

        Dim sqlDataAdapter1 As New SqlDataAdapter

        Dim sqlCommandBuilder1 As SqlCommandBuilder

        Dim sqlDataset1 As New DataSet


        sqlConnection1.ConnectionString = "Your DB connection string"

        sqlSelectCommand1.Connection = sqlConnection1

        sqlSelectCommand1.CommandText = ("SELECT FIRST_NAME, LAST_NAME FROM YOUR_TABLE")

        sqlDataAdapter1.SelectCommand.Connection = sqlConnection1

        sqlDataAdapter1.SelectCommand = sqlSelectCommand1

        sqlDataset1.Clear()

        sqlDataAdapter1.Fill(sqlDataset1)


        DropDownList1.DataSource = sqlDataset1

        DropDownList1.DataTextField = "FIRST_NAME"

        DropDownList1.DataValueField = "LAST_NAME"

        DropDownList1.Items.Add("Please Select Name")

        DropDownList1.DataBind()

        DropDownList1.SelectedIndex = 0

As you can see, a drop down box is typically used for one field
or a few fields concatenated together.

A datagrid is designed to display a complete dataset.
For example if your query was "SELECT * FROM TABLE"
and there were 30 fields in the DB table then those
30 fields would be displayed in the datagrid.

Displaying unrelated tables is not at all common.
What is common is creating a dataset from a query
of key joined tables.
Databases are not just containers, they are smart containers
with a powerful built in engine. We use this engine to extract
particular data based on our needs.
Dump everything in the database, then be selective pulling data out.

Good Luck ;)

[debtboy]

Just noticed your title Display "Access" database...

Basically the same using ODBC

        Dim var_connection As New OleDbConnection

        Dim var_insert_command As New OleDbCommand

        Dim var_select_command As New OleDbCommand

        Dim var_delete_command As New OleDbCommand

        Dim var_update_command As New OleDbCommand

        Dim var_dataadapter As New OleDbDataAdapter

        Dim var_dataset As New DataSet


        var_connection.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your Database.mdb;")

        var_select_command.Connection = var_connection

        var_select_command.CommandText = ("SELECT FIRST_NAME, LAST_NAME FROM YOUR_TABLE")

        var_dataadapter.SelectCommand = var_select_command

        var_dataset.clear

        var_dataadapter.Fill(var_dataset)

This section would be the same:

        DropDownList1.DataSource = var_dataset

        DropDownList1.DataTextField = "FIRST_NAME"

        DropDownList1.DataValueField = "LAST_NAME"

        DropDownList1.Items.Add("Please Select Name")

        DropDownList1.DataBind()

        DropDownList1.SelectedIndex = 0

To use a datagrid, define the datasource and bind just the same
as with the dropdownlist most "Data Objects are very similar"
How it's displayed is controlled via the properties

[jashsayani]

debtboy said:

Just noticed your title Display "Access" database...

Basically the same using ODBC.....

I am using MS Access with AdoDC.

[debtboy]

Sorry, didn't notice the "ADODC"
I haven't used an ADODC in years (vb5 or 6).

ADODC is actually a data control (DC) which simplifies
the database connection and navigation through data.
You have no flexibility using this control other than
local properties.
You would be much better off using the ADODB class
for database connectivity, something like this...
may have typos it's from memory.

set db_connection createobject("ADODB.connection")
set db_recordset createobject("ADODB.recordset")
set db_command createobject("ADODB.command")
db_connnection.provider (Microsoft.jet.oledb")
db_connection.open "your db file"
db_recordset.open "your query" db_connection

See how you are creating individual objects
(ActiveX Data Objects)

Can't remember if you can just plug a recordset field into
a pulldown type box or if you have to loop through
the recordset and insert/add each individual piece of data
into the control.
Sorry for the memory lapse...
(it's been about 8 years and lots of beer :P)

[WingedPanther]

It sounds like you may need to normalize your database. If you're saying that all the tables contain related data (hammers, saws, screwdrivers, each in a different table), then you should really have a master table with a field that indicates where the detail data is located.

[jashsayani]

WingedPanther said:

It sounds like you may need to normalize your database. If you're saying that all the tables contain related data (hammers, saws, screwdrivers, each in a different table), then you should really have a master table with a field that indicates where the detail data is located.

Yes, I guess that, or I will have to execute all commands. Table1.Add, Table2.Add, etc.

No problem, I guess I'll add all 10 tables manually.

[nop]

i agree with panther on the normalization, i have no idea why you'd want a raw dump of the whole db unless its design was poor or you were debugging something.

[debtboy]

I'm not sure I agree...,
He has said that the tables are categories which contain
part listings and he wants to display all part listings no matter
the category. This is not too uncommon in a catalog type situation
using flat files. I would expect the table structures to be similar
and there is no relation between the tables.

He is determined to use the data control...
He probably will not change his db design/schema...
He has not given us a table(s) schema or data sampling...

If his table structures are identical or close then a select
statement across all tables without a join might work, but
the proper way is to use a UNION statement to
create one uniform recordset to display.