How can get data from multiple table in data table

bishoe nb

How can get data from multiple table in data table My Method DataTable dt = new DataTable(); dt = RetriveData.executequery("SELectTestmemberbuid",new Parameter ("@MemberID", int.Parse((comboBox1.SelectedValue.ToString())))); textBox1.Text = dt.Rows[0]["ID_user"].ToString(); This method support 1 result from one table

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar8y ago
Accepted answer
1. public static DataSet executequery(string stoedname, params Parameter[] paramarr)
2. {
3. SqlCommand cmd = new SqlCommand();
5. cmd.Connection = DBConnect.conn;
6. cmd.CommandType = CommandType.StoredProcedure;
7. cmd.CommandText = stoedname;
9. foreach (Parameter prm in paramarr)
10. {
11. cmd.Parameters.Add(prm.pname, prm.pvalue);
13. }
16. SqlDataAdapter adp = new SqlDataAdapter(cmd);
18. DataSet ds = new DataSet();
19. adp.Fill(ds);
21. return dt;
22. con.Close();
24. }
Please try this one
+2

bishoe nb8y ago

Finally the code worked successfully :D

public static DataSet executequery2(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adp.Fill(dt);
return dt;
con.Close();
}

DataSet DsUser = RetriveData.executequery2("Select_USerByID",
new Parameter("@ID_Member", ID_Member_)
,
new Parameter("@IDUser", Convert.ToInt32(LBLSaveIDuser.Text))
);
textBox1.Text = DsUser.Tables[0].Rows[0]["ID_Member"].ToString();
textBox2.Text = DsUser.Tables[2].Rows[0]["City_Name"].ToString();
txtadderss.Text = DsUser.Tables[3].Rows[0]["car_Name"].ToString();

The code is working now
I think I have a problem with this table
untiy
But other fields of

Queries successfully run

Thanks Jignesh Kumar for your time and effort

bishoe nb8y ago

CREATE proc [dbo].[Select_USerByIDs]
@IDUser int,
@ID_Member int
as
begin
select m.ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, [Address], Phone_Home, Mobile,
Email, Identity_Number, Date_OF_issue, Exp_Date, Profile_pic, Image_Regisrt,
DateUserAddMember,IDUnity_party,Memberof_partisanunit,IDUnity_party AS Expr1, up.Del_Flag, m.IDUser,
c.City_Name, c.IDCity AS Expr1, m.IDUser,
m.Del_Flag
from Member_Info m
inner join Untiy_Party up on m.IDUser = up.IDUser
inner join Citys c on m.IDUser = c.IDUser
where m.ID_Member = @ID_Member and m.IDUser = @IDUser and m.Del_Flag = 0
end

bishoe nb8y ago

i m sorry not IDUnity_par

right name col

IDUnity_party

and tb

Untiy_Party not

Untiy_Par

I made the appropriate adjustment to the names but the following errors appeared

Msg 207, Level 16, State 1, Procedure Select_USerByID, Line 15

Invalid column name 'IDUser'.
Msg 207, Level 16, State 1, Procedure Select_USerByID, Line 16
Invalid column name 'IDUser'.
Msg 209, Level 16, State 1, Procedure Select_USerByID, Line 11
Ambiguous column name 'IDUnity_party'.
Msg 209, Level 16, State 1, Procedure Select_USerByID, Line 11

Ambiguous column name 'IDUnity_party'.

Jignesh Kumar8y ago

Hi bishsoe,

Please use below sp it will return all value in one table

CREATE proc [dbo].[Select_USerByID]
@IDUser int,
@ID_Member int
as
begin
select m.ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, [Address], Phone_Home, Mobile,
Email, Identity_Number, Date_OF_issue, Exp_Date, Profile_pic, Image_Regisrt,
DateUserAddMember,IDUnity_par,Memberof_partisanunit,IDUnity_par AS Expr1, up.Del_Flag, m.IDUser,
c.City_Name, c.IDCity AS Expr1, m.IDUser,
m.Del_Flag
from Member_Info m
inner join Untiy_Par up on m.IDUser = up.IDUser
inner join Citys c on m.IDUser = c.IDUser
where m.ID_Member = @ID_Member and m.IDUser = @IDUser and m.Del_Flag = 0
end

textBox1.Text = DsUser.Tables[0].Rows[0]["Member_Name"].ToString();
textBox2.Text = DsUser.Tables[0].Rows[0]["City_Name"].ToString();

bishoe nb8y ago

Column 'City_Name' does not belong to table Table1.

method

public static DataSet executequery2(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adp.Fill(dt);
return dt;
con.Close();

DataSet DsUser = RetriveData.executequery2("Select_USerByID", new Parameter("@ID_Member", ID_Member_)
,
new Parameter("@IDUser", Convert.ToInt32(LBLSaveIDuser.Text))
);
textBox1.Text = DsUser.Tables[0].Rows[0]["Member_Name"].ToString();
textBox2.Text = DsUser.Tables[1].Rows[0]["City_Name"].ToString();

proc

ALTER proc [dbo].[Select_USerByID]
@IDUser int
,
@ID_Member int
as
begin
SELECT ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, [Address], Phone_Home, Mobile, Email, Identity_Number, Date_OF_issue, Exp_Date, Del_Flag, Profile_pic, Image_Regisrt, IDUser,
DateUserAddMember
FROM Member_Info
where ID_Member = @ID_Member and IDUser = @IDUser and Del_Flag =0
end
begin
------Untiy_Par
SELECT Untiy_Par.IDUnity_par, Untiy_Par.Memberof_partisanunit, Member_Info.ID_Member, Member_Info.IDUnity_par AS Expr1, Member_Info.Del_Flag, Member_Info.IDUser
FROM Untiy_Par INNER JOIN
Member_Info ON Untiy_Par.IDUnity_par = Member_Info.IDUnity_par
WHERE (Member_Info.ID_Member = @ID_Member) AND (Member_Info.IDUser = @IDUser) AND (Member_Info.Del_Flag = 0)
end
------Citys
begin
SELECT Citys.IDCity, Citys.City_Name, Member_Info.ID_Member, Member_Info.IDCity AS Expr1, Member_Info.IDUser, Member_Info.Del_Flag
FROM Citys INNER JOIN
Member_Info ON Citys.IDCity = Member_Info.IDCity
where Member_Info.ID_Member = @ID_Member and Member_Info.IDUser = @IDUser and Member_Info.Del_Flag = 0
end

I tried the following but did not succeed

textBox2.Text = DsUser.Tables[0].Rows[0]["City_Name"].ToString();

Err

Column 'City_Name' does not belong to table Table.

textBox2.Text = DsUser.Tables[0].Rows[1]["City_Name"].ToString();

err

There is no row at position 1.

bishoe nb8y ago

public static DataSet executequery2(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
return ds; <==<
con.Close();
}

DataSet dt_search =
RetriveData.executequery2("sp_select",
new Parameter("@MemberID", cmbsearch.SelectedValue)
);
textBox1.Text= dt_search.Tables[0].Rows[0].ToString();

but show in textbox

System.Data.DataRow

Jignesh Kumar8y ago

You have created dataset and you are retuning DataTable from your method that's why it is throwing error

DataSet dt =new DataSet()

You need to create

DataTable dt =new DataTable()

and return dt;

bishoe nb8y ago

when edit in my method

public static DataTable executequery(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adp.Fill(dt);
return dt;
con.Close();
}

ERR

Error CS0029 Cannot implicitly convert type 'System.Data.DataSet' to 'System.Data.DataTable'

Jignesh Kumar8y ago

Just need to change return dt; you are returning dt.Tables[0];

it will return only first table.

public static DataTable executequery(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adp.Fill(dt);
return dt.Tables[0];
con.Close();

bishoe nb8y ago

ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, Address, Phone_Home, Mobile, Email, Identity_Number, Date_OF_issue, Exp_Date, Del_Flag, Profile_pic, Image_Regisrt, IDUser

City_Name

Cit_Name

my method

RetriveData

public static DataTable executequery(string stoedname, params Parameter[] paramarr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnect.conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = stoedname;
foreach (Parameter prm in paramarr)
{
cmd.Parameters.Add(prm.pname, prm.pvalue);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet dt = new DataSet();
adp.Fill(dt);
return dt.Tables[0];
con.Close();

get data by this code but show first query

DataTable dt_search =
RetriveData.executequery("Select_USerByID",
new Parameter("@Member_Name", txtsearch.Text),
new Parameter("@IDUser", LBLSaveIDuser.Text)
);
if (dt_search.Rows.Count > 0)
{
ID_Member_ = dt_search.Rows[0][0].ToString();

i want use one datatable for get all query in my proc

Jignesh Kumar8y ago

Could you please list out which are columns you need in your output?

bishoe nb8y ago

this my proc

how can get all result from proc

ALTER proc [dbo].[Select_USerByID]
@Member_Name nvarchar(50) ,
@IDUser int
as
begin
SELECT ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, Address, Phone_Home, Mobile, Email, Identity_Number, Date_OF_issue, Exp_Date, Del_Flag, Profile_pic, Image_Regisrt, IDUser,
DateUserAddMember
FROM Member_Info
where Member_Name =@Member_Name and IDUser = @IDUser and Del_Flag =0
SELECT Citys.IDCity, Citys.City_Name, Member_Info.IDCity AS Expr1, Member_Info.ID_Member
FROM Citys INNER JOIN
Member_Info ON Citys.IDCity = Member_Info.IDCity where Member_Info.IDUser =1 and Member_Info.ID_Member = 1
SELECT Cit.IDCit, Cit.Cit_Name, Member_Info.IDCit AS Expr1, Member_Info.ID_Member
FROM Cit INNER JOIN
Member_Info ON Cit.IDCit = Member_Info.IDCit where Member_Info.IDUser =1 and Member_Info.ID_Member = 1
end

Jignesh Kumar8y ago

Hi,

First of all you should have same column in both the table which you can use for join. You can join like below

Select * from infouser i inner join

citys c on i.tableinfouserColumn = c.tableCitysCoulmnfrom

where c.idcity =1

bishoe nb8y ago

How us join I used cross join But data table get first select This my proc Select * from infouser Selcet cityname from citys where idcity =1

Sk Imad Fakir8y ago

Use join with multiple tables which will return data in a single table.

Jignesh Kumar8y ago

Pelase find below link and try to create procedure that returns data from multiple table

https://www.aspsnippets.com/Articles/Fill-Populate-DataSet-with-multiple-Tables-data-using-C-and-VBNet.aspx

https://www.aspsnippets.com/Articles/Load-Fill-Populate-DataTable-from-Stored-Procedure-in-ASPNet-using-C-and-VBNet.aspx