How use Stored Procedures Returning Multiple Result in crs

bishoe nb

8y767Visual Basic .NET

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

5 Replies

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

Jignesh Kumar8y ago
Accepted answer
Please try below link.

http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-multiple-tables.htm
+1
bishoe nb8y ago
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
0
Jignesh Kumar8y ago
1. CREATE proc [dbo].[Select_USerByID]
3. @IDUser int,
4. @ID_Member int
5. as
6. begin
8. select m.ID_Member, Member_Name, Birth_Date, MaleORFamle, Religion, [Address], Phone_Home, Mobile,
9. Email, Identity_Number, Date_OF_issue, Exp_Date, Profile_pic, Image_Regisrt,
10. DateUserAddMember,IDUnity_par,Memberof_partisanunit,IDUnity_par AS Expr1, up.Del_Flag, m.IDUser,
11. c.City_Name, c.IDCity AS Expr1, m.IDUser,
12. m.Del_Flag
13. from Member_Info m
14. inner join Untiy_Par up on m.IDUser = up.IDUser
15. inner join Citys c on m.IDUser = c.IDUser
16. where m.ID_Member = @ID_Member and m.IDUser = @IDUser and m.Del_Flag = 0
17. 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
0

bishoe nb8y ago

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 Kumar8y ago

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