Select from multiple tables

[Ahmet Karaca]

Hi.
myds.Reset();
mycommand.SelectCommand.CommandText=
"Select att1 from Ing as Ingredient, Pro as Product "+
"where Pro.ad='apple' and Pro.id=Ing.id";
mycommand.Fill(myds, "Product"); // Here is the problem
listbox.DataSource = myds.Tables["Product"].DefaultView; // Here again
listbox.DataTextField = "invid"; // Here again
listbox.DataBind();
mycon.Close()
////////////////////
As you see, I want to put data coming as a result of joining of tables into
a dataset. Then I want to display the result of the join in a list box;
because the result will be one column.

Please help me, I am just a beginner

[D.W. Warlock]

Ahmet,

What you are doing is not returning multiple tables. You are returning a
single table with the column att1. If you want to have multiple tables
returned, you need to use multiple select statements. Your best bet
would be to use a stored procedure in this case.

Stored Proc Example:
<code>
In SQL Server (or whatever RDBMS you are using):

CREATE PROCEDURE MultTableExample AS

SELECT col1, col2, col3 FROM Table1
SELECT col1, col2, col3 FROM Table2

GO
</code>

in C#:
<code>
SqlConnection connection = new SqlConnection("connection string");

DataTable table1 = new DataTable("Table1");
DataTable table2 = new DataTable("Table2");
DataSet multTables = new DataSet("MultTables");

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT col1, col2, col3
FROM Table1",connection);
dataAdapter.Fill(table1);

dataAdapter.SelectCommand.CommandText = "SELECT col1, col2, col3 FROM
Table2";
dataAdapter.Fill(table2);

multTables.Tables.Add(table1);
multTables.Tables.Add(table2);

listbox.DataSource = multTables.Tables["Table2"];
listbox.DataTextField = "col1";
listbox.DataBind();
</code>

If you don't want to use stored procedures, then you can do it this way.
<code>
SqlConnection connection = new SqlConnection("connection string");
DataSet multTables = new DataSet("MultTables");
SqlDataAdapter dataAdapter = new SqlDataAdapter("MultTableExample
",connection);
dataAdapter.SelectCommand.CommadType = CommandType.StoredProcedure;
dataAdapter.Fill(multTables);

listbox.DataSource = multTables.Tables["Table2"];
listbox.DataTextField = "col1";
listbox.DataBind();
</code>

HTH,
~d

Ahmet Karaca wrote:

Hi.
myds.Reset();
mycommand.SelectCommand.CommandText=
"Select att1 from Ing as Ingredient, Pro as Product "+
"where Pro.ad='apple' and Pro.id=Ing.id";
mycommand.Fill(myds, "Product"); // Here is the problem
listbox.DataSource = myds.Tables["Product"].DefaultView; // Here again
listbox.DataTextField = "invid"; // Here again
listbox.DataBind();
mycon.Close()
////////////////////
As you see, I want to put data coming as a result of joining of tables into
a dataset. Then I want to display the result of the join in a list box;
because the result will be one column.

Please help me, I am just a beginner