Senin, 20 Desember 2010

call SQL stored procedure in VB.NET


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