Ans:-
1.Here i have taken one DropDownList,while selecting DDL value and 2. then Clicking the Button(Show Report) Report will Generate and then automatically another Button will visible and 3. then Clicking the Button(Export Report) it will export Report to Excel(.xls).
See Below UI Snaps to better understand..
1.
2.
3.
GetPlanPolicyProviderNm.aspx:-
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GetPlanPolicyProviderNm.aspx.vb" Inherits="GetPlanPolicyProviderNm" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Get Plan Policy Provider Name Page</title>
<script type="text/javascript" language="javascript">
function validate() {
var ddl = document.getElementById("DropDownList1")
if (ddl.selectedIndex == 0) {
ddl.focus();
alert("Plz Select UId..");
return false;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
<table>
<tr>
<td>
Select Uid :
</td>
<td align="left">
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
</td>
<td>
<asp:Button ID="btnShowRpt" runat="server" Text="Show Report" OnClientClick="return validate()" />
</td>
</tr>
<tr>
<td></td>
<td></td>
<td >
<asp:Button ID="btnExport" runat="server" Text="Export Report" />
</td>
</tr>
<tr>
<td colspan="3">
</td>
</tr>
<tr>
<td colspan="3">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" HasExportButton="True"
AutoDataBind="true" ReportSourceID="CrystalReportSource1" Width="901px" Visible="false" />
<CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
<Report FileName="CrystalReport.rpt">
</Report>
</CR:CrystalReportSource>
</td>
</tr>
</table>
</div>
</center>
</form>
</body>
</html>
1.Here i have taken one DropDownList,while selecting DDL value and 2. then Clicking the Button(Show Report) Report will Generate and then automatically another Button will visible and 3. then Clicking the Button(Export Report) it will export Report to Excel(.xls).
See Below UI Snaps to better understand..
1.
2.
GetPlanPolicyProviderNm.aspx:-
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GetPlanPolicyProviderNm.aspx.vb" Inherits="GetPlanPolicyProviderNm" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Get Plan Policy Provider Name Page</title>
<script type="text/javascript" language="javascript">
function validate() {
var ddl = document.getElementById("DropDownList1")
if (ddl.selectedIndex == 0) {
ddl.focus();
alert("Plz Select UId..");
return false;
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<center>
<div>
<table>
<tr>
<td>
Select Uid :
</td>
<td align="left">
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
</td>
<td>
<asp:Button ID="btnShowRpt" runat="server" Text="Show Report" OnClientClick="return validate()" />
</td>
</tr>
<tr>
<td></td>
<td></td>
<td >
<asp:Button ID="btnExport" runat="server" Text="Export Report" />
</td>
</tr>
<tr>
<td colspan="3">
</td>
</tr>
<tr>
<td colspan="3">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" HasExportButton="True"
AutoDataBind="true" ReportSourceID="CrystalReportSource1" Width="901px" Visible="false" />
<CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
<Report FileName="CrystalReport.rpt">
</Report>
</CR:CrystalReportSource>
</td>
</tr>
</table>
</div>
</center>
</form>
</body>
</html>
GetPlanPolicyProviderNm.aspx.vb:-
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.ReportAppServer.ClientDoc
Partial Class GetPlanPolicyProviderNm
Inherits System.Web.UI.Page
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim ds As New DataSet
Dim da As SqlDataAdapter
Dim spStr() As String
Dim obj As New Class1
Dim store() As Integer
Dim cryRpt As ReportDocument
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
cn = New SqlConnection(obj.GetConnection())
cn.Open()
If Not IsPostBack Then
DropDownList1.Items.Clear()
DropDownList1.Items.Add("")
obj.AddCombo(DropDownList1, "select cast(uid as varchar)+'-'+uname as uname from tbluser", cn)
DropDownList1.Items.Insert(1, "0-All")
btnExport.Visible = False
End If
'Call ShowReport()
End Sub
Private Sub ShowReport()
Try
If DropDownList1.SelectedIndex > 0 Then
cmd = New SqlCommand("spGetPlanPolicyProviderNm", cn)
cmd.CommandType = CommandType.StoredProcedure
spStr = Split(DropDownList1.SelectedValue, "-")
cmd.Parameters.Add(New SqlParameter("@uid", spStr(0)))
If DropDownList1.Text = "0-All" Then
spStr = Split(DropDownList1.Text, "-")
cmd.Parameters.Add(New SqlParameter("@allUser", spStr(0)))
End If
da = New SqlDataAdapter(cmd)
cmd.CommandTimeout = 0
da.Fill(ds, "tb1")
CrystalReportSource1.ReportDocument.SetDataSource(ds.Tables("tb1"))
CrystalReportViewer1.Visible = True
CrystalReportViewer1.ReportSourceID = "CrystalReportSource1"
CrystalReportViewer1.DataBind()
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Protected Sub btnShowRpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnShowRpt.Click
Try
ShowReport()
btnExport.Visible = True
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
Try
cmd = New SqlCommand("spGetPlanPolicyProviderNm", cn)
cmd.CommandType = CommandType.StoredProcedure
spStr = Split(DropDownList1.SelectedValue, "-")
cmd.Parameters.Add(New SqlParameter("@uid", spStr(0))) 'get record of one user..
If DropDownList1.Text = "0-All" Then 'get record of all users..
spStr = Split(DropDownList1.Text, "-")
cmd.Parameters.Add(New SqlParameter("@allUser", spStr(0)))
End If
da = New SqlDataAdapter()
da.SelectCommand = cmd
Dim datatable As New DataTable
da.Fill(datatable) 'getting value according to imageID and fill dataset
Dim crystalReport As New ReportDocument 'creating object of crystal report
crystalReport.Load(Server.MapPath("~/CrystalReport.rpt")) 'path of report
crystalReport.SetDataSource(datatable) 'binding datatable
CrystalReportViewer1.ReportSource = crystalReport
crystalReport.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.ExcelRecord, Response, True, "UserPolicyDetails")
'here i have use [ CrystalDecisions.Shared.ExportFormatType.ExcelRecord ] to Export in Excel
'crystalReport.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, True, "UserPolicyDetails")
'here i have use[CrystalDecisions.Shared.ExportFormatType.PortableDocFormat ] to Export in PDF
'crystalReport.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.WordForWindows, Response, True, "UserPolicyDetails")
'here i have use [ CrystalDecisions.Shared.ExportFormatType.WordForWindows ] to Export in Word
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
Class1.vb:-
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class Class1
Dim sqlcmd As SqlCommand
Dim sqldr As SqlDataReader
Public Function GetConnection() As String
Dim conStr As String = "Server=.;database=SubsDB;UID=sa;Password=123;Pooling=true;Min Pool Size=2;Max Pool Size=1000"
Return conStr
End Function
Public Sub AddCombo(ByVal CntName As DropDownList, ByVal SqlStr As String, ByVal sCon As SqlConnection)
'Which will fill the DropdownList Based on Query
Try
CntName.Items.Clear()
CntName.Items.Add("")
sqlcmd = New SqlCommand(SqlStr, sCon)
sqldr = sqlcmd.ExecuteReader()
While sqldr.Read
CntName.Items.Add(sqldr(0))
End While
sqldr.Close() : sqlcmd.Dispose()
Catch ex As Exception
'MsgBox(ex.Message)
End Try
End Sub
End Class
Table Store Procedure:-
create table tbluser(uid int constraint p primary key,uname varchar(30))
create table tblprovider(providerid int constraint p1 primary key,providername varchar(30),uid int constraint f11 foreign key references tbluser(uid)on delete cascade on update cascade)
create table tblpolicy(policyid int constraint p2 primary key,policyname varchar(30),uid int constraint f12 foreign key references tbluser(uid)on delete cascade on update cascade)
create table tblplan(planid int constraint p3 primary key,planname varchar(30),uid int constraint f13 foreign key references tbluser(uid)on delete cascade on update cascade)
insert into tbluser values(2,'pravin')
insert into tblprovider values(103,'eye hospital',2)
insert into tblpolicy values(1003,'policy3',2)
insert into tblplan values(10003,'plan3',2)
insert into tblplan(policyid) values(1001)
--for add column & datatype to the table
alter table tblpolicy
add providerid int
-- add foreign key to the already existing column
alter table tblpolicy
add constraint f15 foreign key(providerid) references tblprovider(providerid)
--for renaming column
sp_rename 'tblprovider.pname','providername'
alter proc spGetPlanPolicyProviderNm(@uid int,@allUser varchar(15)=null)
as
begin
if @allUser=0
Begin
select u.uname,pro.providerid,pro.providername,pol.policyid,pol.policyname,pla.planid,pla.planname from tblprovider pro inner join tblpolicy pol on pro.providerid=pol.providerid inner join
tblplan pla on pol.policyid=pla.policyid inner join tbluser u on pla.uid=u.uid
End
Else
Begin
select u.uname,pro.providerid,pro.providername,pol.policyid,pol.policyname,pla.planid,pla.planname from tblprovider pro inner join tblpolicy pol on pro.providerid=pol.providerid inner join
tblplan pla on pol.policyid=pla.policyid inner join tbluser u on pla.uid=u.uid where u.uid=@uid
end
end
exec spGetPlanPolicyProviderNm 1,0
select cast(uid as varchar)+'-'+uname as uname from tbluser
No comments:
Post a Comment