How use Stored Procedures Returning Multiple Result in crs

Question

i use sp Returning Multiple Result

but how can use this sp in crs

data set Get Fitst Query From SP but another query don't show

this proc but not full

@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
------Untiy_Party
SELECT Untiy_Party.IDUnity_party, Untiy_Party.Memberof_partisanunit, Member_Info.ID_Member, Member_Info.Del_Flag, Member_Info.IDUser
FROM Untiy_Party INNER JOIN
Member_Info ON Untiy_Party.IDUnity_party = Member_Info.IDUnity_party
WHERE (Member_Info.ID_Member = @ID_Member) AND (Member_Info.IDUser = @IDUser and Member_Info.Del_Flag = 0)
------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

procedure contains 260 line = all queries

how can use sp in crystal report

Jignesh Kumar · Answer

Please try below link. http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-multiple-tables.htm

bishoe nb · Answer

Msg 207, Level 16, State 1, Procedure Select_USerByIDa, Line 15 Invalid column name 'IDUser' . Msg 207, Level 16, State 1, Procedure Select_USerByIDa, Line 16 Invalid column name 'IDUser' . Msg 209, Level 16, State 1, Procedure Select_USerByIDa, Line 11 Ambiguous column name 'IDUnity_party' . Msg 209, Level 16, State 1, Procedure Select_USerByIDa, Line 11 Ambiguous column name 'IDUnity_party' . alter proc [dbo].[Select_USerByIDa] @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 not work some error

Jignesh Kumar · Answer

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 You can use inner join in your query as I had given in last post and you can create datatable with all filed as you created

bishoe nb · Answer

thank you but not work Get First query only My Proc ex.. alter proc testRep @IDUser int , @ID_Member int as SELECT ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion FROM Member_Info where ID_Member = @ID_Member and IDUser = @IDUser and Del_Flag =0 ------Untiy_Party SELECT Untiy_Party.IDUnity_party, Untiy_Party.Memberof_partisanunit, Member_Info.ID_Member, Member_Info.Del_Flag, Member_Info.IDUser FROM Untiy_Party INNER JOIN Member_Info ON Untiy_Party.IDUnity_party = Member_Info.IDUnity_party WHERE (Member_Info.ID_Member = @ID_Member) AND (Member_Info.IDUser = @IDUser and Member_Info.Del_Flag = 0) ------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 ------Villages begin SELECT Villages.Village_Name, Member_Info.ID_Member, Member_Info.Del_Flag, Member_Info.IDUser FROM Member_Info INNER JOIN Villages ON Member_Info.IDvillage = Villages.IDvillage where Member_Info.ID_Member =@ID_Member and Member_Info.Del_Flag = 0 and Member_Info.IDUser = @IDUser end using CrystalDecisions.CrystalReports.Engine; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace WindowsFormsApplication4 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private Member GetData() { SqlCommand cmd = new SqlCommand( "testRep" ); using (SqlConnection con = new SqlConnection( "Data Source=.;Initial Catalog=name;Integrated Security=True" )) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue( "@IDUser" , 1); cmd.Parameters.AddWithValue( "@ID_Member" , 25); sda.SelectCommand = cmd; using (Member dsCustomers = new Member()) { sda.Fill(dsCustomers, "DataTable11" ); return dsCustomers; } } } } private void Form1_Load( object sender, EventArgs e) { ReportDocument crystalReport = new ReportDocument(); string filename = Application.StartupPath.Substring(0, Application.StartupPath.Substring(0, Application.StartupPath.LastIndexOf( "\")).LastIndexOf(" \ ")) + @" \MemberReport.rpt"; crystalReport.Load(filename); Member dsMembe_IN = GetData(); crystalReport.SetDataSource(dsMembe_IN); crystalReportViewer1.ReportSource = crystalReport; } } } and try this code string query = "SELECT ID_Member, Member_Name, Birth_Date, MaleORFamle FROM Member_Info where ID_Member = 25 and IDUser = 1 and Del_Flag = 0" ; string query2 = "SELECT Untiy_Party.IDUnity_party, Untiy_Party.Memberof_partisanunit,Member_Info.ID_Member, Member_Info.Del_Flag,Member_Info.IDUser FROM Untiy_Party INNER JOIN Member_Info ON Untiy_Party.IDUnity_party = Member_Info.IDUnity_party WHERE(Member_Info.ID_Member = 25) AND(Member_Info.IDUser = 1 and Member_Info.Del_Flag = 0)" ; string q3 = query + query2; SqlCommand cmd = new SqlCommand(q3); Get First Query only

Jignesh Kumar · Answer

Hi Bishoe, Please try below forum it will help you to get it done your things. In this link only one table is there but same way you can use second table. https://www.aspsnippets.com/Articles/Create-Crystal-Report-using-Stored-Procedure-in-ASPNet-with-C-and-VBNet.aspx