Hi, I am beginner. I have a question about VB.net. Kindly help me.
1) UserDB.vb file
Public Shared Function GetCountDashboard(userid As Integer) As DataSet
Dim ds As New DataSet
Dim spname As String
spname = "sp_R_CNTDASHBOARD"
Dim arPrms() As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter(1) {}
arPrms(0) = New System.Data.SqlClient.SqlParameter("@usrID", SqlDbType.Int)
arPrms(0).Value = userid
Try
ds = SQLHelper.db.SqlHelper.ExecuteDataset(SQLHelper.db.SqlHelper.CONN_STRING, CommandType.StoredProcedure, spname, arPrms)
Catch ex As Exception
Trace.Write("Exception retrieving Code: " & ex.Message, "Count Dashboard")
ds = Nothing
End Try
Return ds
End Function
2) stored procedure
USE [TRUST]
GO
/****** Object: StoredProcedure [dbo].[sp_R_CNTDASHBOARD] Script Date: 16/07/2018 8:41:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_R_CNTDASHBOARD]
(
@usrID int
)
AS
SET NOCOUNT ON
Begin
DECLARE @cntBank int, @cntDoc int, @cntReceipt int, @cntSchedule int, @cntPAyment int, @cntPAymentAppr int, @cntAstValue int, @cntLandSch int
DECLARE @cntTrustTerm int, @role_id char(1),@isVerifyBankAcc char(1),@isApprBankAcc char(1),@isVerifyDoc char(1),@isApprDoc char(1)
DECLARE @isVerifyReceipt char(1),@isApprReceipt char(1),@isVerifySchedule char(1),@isApprSchedule char(1),@isVerifyPayment char(1)
DECLARE @isApprPayment char(1),@isVerifyAstValue char(1),@isApprAstValue char(1),@isVerifyLandSch char(1),@isApprLandSch char(1)
DECLARE @isVerifyTerm char(1), @isApprTerm char(1), @cdBranch int
SELECT @cdBranch = vw_DEPT.dept_branch
FROM U_USER LEFT OUTER JOIN
vw_DEPT ON U_USER.usr_dept = vw_DEPT.dept_id
WHERE (U_USER.usr_id = @usrID)
SELECT @isVerifyBankAcc = isVerifyBankAcc, @isApprBankAcc = isApprBankAcc,@isVerifyDoc = isVerifyDoc ,@isApprDoc = isApprDoc
,@isVerifyReceipt = isVerifyReceipt ,@isApprReceipt = isApprReceipt ,@isVerifySchedule = isVerifySchedule ,@isApprSchedule = isApprSchedule
,@isVerifyPayment = isVerifyPayment ,@isApprPayment = isApprPayment ,@isVerifyAstValue = isVerifyAstValue ,@isApprAstValue = isApprAstValue
,@isVerifyLandSch = isVerifyLandSch ,@isApprLandSch = isApprLandSch ,@isVerifyTerm = isVerifyTerm ,@isApprTerm = isApprTerm
FROM U_USER
INNER JOIN U_USER_ROLE ON U_USER_ROLE.usr_id = U_USER.usr_id
INNER JOIN CD_U_ROLE_VERIFY ON CD_U_ROLE_VERIFY.role_id = U_USER_ROLE.role_id
WHERE U_USER.usr_id = @usrID
IF @isVerifyBankAcc = '1'
BEGIN
--select @cntBank = count(*) FROM TRU_CIF_ACCNO
SELECT @cntBank = count(*) FROM (select accno_id FROM TRU_CIF_ACCNO
LEFT JOIN GENERIC.dbo.C_PERSON person ON person.per_id = TRU_CIF_ACCNO.per_id
LEFT JOIN GENERIC.dbo.C_COMPANY company ON company.co_id = TRU_CIF_ACCNO.co_id
LEFT JOIN TRU_DONOR ON TRU_DONOR.donor_perID = person.per_id OR TRU_DONOR.donor_co_id = company.co_id
LEFT JOIN TRU_BENE ON TRU_BENE.per_id = person.per_id OR TRU_BENE.bene_co_id = company.co_id
LEFT JOIN TRU_GRD ON TRU_GRD.grd_per_id = person.per_id OR TRU_GRD.grd_co_id = company.co_id
LEFT JOIN TRU_FILE ON TRU_FILE.tru_file_id =TRU_DONOR.tru_file_id OR TRU_FILE.tru_file_id = TRU_BENE.tru_file_id
OR TRU_FILE.tru_file_id = TRU_GRD.tru_file_id
WHERE verifyby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by accno_id) as tblAcc
END
IF @isVerifyDoc = '1'
BEGIN
SELECT @cntDoc = count(*) FROM (select doc_id FROM TRU_DOC
LEFT JOIN GENERIC.dbo.C_PERSON person ON person.per_id = TRU_DOC.per_id
LEFT JOIN GENERIC.dbo.C_COMPANY company ON company.co_id = TRU_DOC.co_id
LEFT JOIN TRU_DONOR ON TRU_DONOR.donor_perID = person.per_id OR TRU_DONOR.donor_co_id = company.co_id
LEFT JOIN TRU_BENE ON TRU_BENE.per_id = person.per_id OR TRU_BENE.bene_co_id = company.co_id
LEFT JOIN TRU_GRD ON TRU_GRD.grd_per_id = person.per_id OR TRU_GRD.grd_co_id = company.co_id
LEFT JOIN TRU_FILE ON TRU_FILE.tru_file_id =TRU_DONOR.tru_file_id OR TRU_FILE.tru_file_id = TRU_BENE.tru_file_id
OR TRU_FILE.tru_file_id = TRU_GRD.tru_file_id OR TRU_FILE.tru_file_id = TRU_DOC.tru_id
WHERE verifyby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by doc_id) as tblDoc
END
IF @isVerifyReceipt = '1'
BEGIN
SELECT @cntReceipt = count(*) FROM (select rcpt_id FROM TRU_RECEIPT_MASTER
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_RECEIPT_MASTER.tru_file_id
WHERE verifyby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by rcpt_id) as tblDoc
END
IF @isVerifySchedule = '1'
BEGIN
SELECT @cntSchedule = count(*) FROM (select scdule_id FROM TRU_SCHEDULE
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_SCHEDULE.tru_id
WHERE verifyby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by scdule_id) as tblDoc
END
IF @isVerifyPayment = '1'
BEGIN
SELECT @cntPAyment = count(*) FROM (select paym_id FROM TRU_PAYMENT
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_PAYMENT.tru_id
WHERE paym_verifyby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by paym_id) as tblDoc
END
IF @isApprPayment = '1'
BEGIN
SELECT @cntPAymentAppr = count(*) FROM (select paym_id FROM TRU_PAYMENT
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_PAYMENT.tru_id
WHERE paym_approveby IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by paym_id) as tblDoc
END
IF @isVerifyAstValue = '1'
BEGIN
SELECT @cntAstValue = count(*) FROM (select astd_id FROM TRU_ASSET_DTL
INNER JOIN TRU_ASSET ON TRU_ASSET.ast_id = TRU_ASSET_DTL.ast_id
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_ASSET.tru_file_id
WHERE valuation_verifyBy IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by astd_id) as tblDoc
END
IF @isVerifyLandSch = '1'
BEGIN
SELECT @cntLandSch = count(*) FROM (select astd_id FROM TRU_ASSET_DTL
INNER JOIN TRU_ASSET ON TRU_ASSET.ast_id = TRU_ASSET_DTL.ast_id
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_ASSET.tru_file_id
WHERE landSch_verifyBy IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by astd_id) as tblDoc
END
IF @isVerifyTerm = '1'
BEGIN
SELECT @cntTrustTerm = count(*) FROM (select term_id FROM TRU_TERM
INNER JOIN TRU_FILE ON TRU_FILE.tru_file_id = TRU_TERM.tru_id
WHERE verifyBy IS NULL AND TRU_FILE.cd_branch = @cdBranch
group by term_id) as tblDoc
END
SELECT @cntBank as cntBank, @cntDoc as cntDoc, @cntReceipt as cntReceipt, @cntSchedule as cntSchedule,
@cntPAyment as cntPayment, @cntPAymentAppr as cntPaymentApprove,@cntAstValue as cntAstValue,
@cntLandSch as cntLandSch, @cntTrustTerm as cntTrustTerm
End
3) Label name for profile.aspx file, just give few example
lbBnkAcc
lbDocUpload
lbReceipt
lbSchPayment
lbPayment
lbPaymntAppr
lbAsstVal
lbLandSearch
lbTrustTerm
<div class="row">
<div class="col-lg-3" style="width: 220px;">
<div class="panel panel-info">
<div class="panel-heading">
<div class="row">
<div class="col-xs-6">
<i class="fa fa-book fa-5x"></i>
</div>
<div class="col-xs-6 text-right">
<p class="announcement-text">Bank Account</p>
</div>
</div>
</div>
<a href="#">
<div class="panel-footer announcement-bottom">
<div class="row">
<div class="col-xs-6">
View
</div>
<div class="col-xs-6 text-right">
<i class="fa fa-arrow-circle-right"></i> <!--total number here-->
<asp:Label ID="lbBnkAcc" runat="server" Font-Bold="True"></asp:Label>
</div>
</div>
</div>
</a>
</div>
</div>
<div class="col-lg-3" style="width: 220px;">
<div class="panel panel-warning">
<div class="panel-heading">
<div class="row">
<div class="col-xs-6">
<i class="fa fa-upload fa-5x"></i>
</div>
<div class="col-xs-6 text-right">
<p class="announcement-text"> Document Upload</p>
</div>
</div>
</div>
<a href="#">
<div class="panel-footer announcement-bottom">
<div class="row">
<div class="col-xs-6">
View
</div>
<div class="col-xs-6 text-right">
<i class="fa fa-arrow-circle-right"></i>
<asp:Label ID="lbDocUpload" runat="server" Font-Bold="True"></asp:Label>
</div>
</div>
</div>
</a>
</div>
</div>
<div class="col-lg-3" style="width: 220px;">
<div class="panel panel-danger">
<div class="panel-heading">
<div class="row">
<div class="col-xs-6">
<i class="fa fa-list-alt fa-5x"></i>
</div>
<div class="col-xs-6 text-right">
<p class="announcement-text">Receipt</p>
</div>
</div>
</div>
<a href="#">
<div class="panel-footer announcement-bottom">
<div class="row">
<div class="col-xs-6">
View
</div>
<div class="col-xs-6 text-right">
<i class="fa fa-arrow-circle-right"></i>
<asp:Label ID="lbReceipt" runat="server" Font-Bold="True"></asp:Label>
</div>
</div>
</div>
</a>
</div>
</div>
<div class="col-lg-3" style="width: 220px;">
<div class="panel panel-success">
<div class="panel-heading">
<div class="row">
<div class="col-xs-6">
<i class="fa fa-calendar fa-5x"></i>
</div>
<div class="col-xs-6 text-right">
<p class="announcement-text"> Schedule Payment</p>
</div>
</div>
</div>
<a href="#">
<div class="panel-footer announcement-bottom">
<div class="row">
<div class="col-xs-6">
View
</div>
<div class="col-xs-6 text-right">
<i class="fa fa-arrow-circle-right"></i>
<asp:Label ID="lbSchPayment" runat="server" Font-Bold="True"></asp:Label>
</div>
</div>
</div>
</a>
</div>
</div>
</div>
4) my question is how to call parameter or value from stored procedure to get the value in the file below? I know there has mistake there and need your help...
profile.aspx.vb file
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
uDM = UserDB.GetUserInfo(Session("UserAD"))
'ListTeamMember()
Dim ds As New DataSet
ds = UserDB.GetCountDashboard(Session("userid"))
If Not IsNothing(ds.cntBank) Then
Session("cntBank") = ds.cntBank
End If
If Not IsNothing(ds.cntReceipt) Then
Session("cntReceipt") = ds.cntReceipt
End If
'***need code from here....................
End Sub
