How can get data from multiple table in data table

Question

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

Jignesh Kumar · Answer

public static DataSet 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 ds = new DataSet(); adp.Fill(ds); return dt; con.Close(); } Please try this one

Jignesh Kumar · Answer

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 nb · Answer

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 nb · Answer

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.

Sk Imad Fakir · Answer

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

bishoe nb · Answer

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 nb · Answer

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 Kumar · Answer

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 nb · Answer

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

bishoe nb · Answer

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 Kumar · Answer

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 nb · Answer

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 Kumar · Answer

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

bishoe nb · Answer

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 Kumar · Answer

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 nb · Answer

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

Jignesh Kumar · Answer

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