The Stored Procedure to call.
ALTER PROCEDURE [dbo].[uspCountRecurInvoice] @RecurCount float OUTPUT
AS
SET NOCOUNT ON;
--monthly
SELECT @RecurCount=COUNT(ARHeaderID)
FROM dbo.tblARHeaderRecur
WHERE DateDiff(mm,StartDate,GetDate())>=1 and RecurringInterval='Monthly'
--weekly
SELECT @RecurCount=@RecurCount+COUNT(ARHeaderID)
FROM dbo.tblARHeaderRecur
WHERE DateDiff(wk,StartDate,GetDate())>=1 and RecurringInterval='Weekly'
RETURN @RecurCount
Calling a stored produre in VB.NET
Private Function CountRecurringDue() As Boolean
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim lreturn As Boolean = True
Try
con.ConnectionString = Settings.ConnectionString
con.Open()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "uspCountRecurInvoice"
cmd.Parameters.Add("@RecurCount", SqlDbType.Int)
cmd.Parameters("@RecurCount").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim intCount As Integer = 0
Integer.TryParse(cmd.Parameters("@RecurCount").Value, intCount)
If intCount > 0 Then MessageBox.Show("There is " & intCount.ToString() & " recurring invoice to run", "Recurring Invoice")
Catch ex As Exception
lreturn = False
MessageBox.Show("Error while counting due recurring invoices." & ex.Message, "Recurring Due Counter")
Finally
con.Close()
End Try
Return lreturn
End Function
Tidak ada komentar:
Posting Komentar