468,738 Members | 2,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,738 developers. It's quick & easy.

Select from multiple tables

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
Nov 16 '05 #1
1 11227
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

Nov 16 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mfyahya | last post: by
6 posts views Thread by Eric Robinson | last post: by
3 posts views Thread by rallykarro | last post: by
1 post views Thread by nullGumby | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
xarzu
2 posts views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.