StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » fillbystoredprocedure with multiple parameters

fillbystoredprocedure with multiple parameters

Michael R Poirier — Sun, 01 Nov 2009 09:08:48 GMT

I created a stored procedure - code to follow - I can exec the procedure from within the sql management studio and it works. I'm trying to call to fill a bo - that code also below. I get an error saying the stored procedure expects a parameter '@startdate' - which I think I'm supplying - Looking for help as to where I've gone wrong.

SQL Stored Procedure:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: MRP

-- Create date: 10/2009

-- Description: return table needed for pay error report

-- =============================================

ALTER PROCEDURE [dbo].[sdsi_ExtractPayrollErrors]

-- Add the parameters for the stored procedure here

@startdate datetime,

@enddate datetime

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select pd.*,co.companies_name from payrolldata PD

left join companies co on co.companies_pk = pd.pay_hiringco,

(select pay_hiringco,pay_ssn,

max(pay_paydate) as lastpay,

max(pay_pk) as paypk from payrolldata

where pay_paydate > = @startdate and pay_paydate <= @enddate

group by pay_hiringco, pay_ssn) PS

where pd.pay_pk = ps.paypk

order by companies_name,pay_fullname

END

VB code to fillbystoredprocedure:

'-- Create a new parameter that matches the stored procedure

Dim loParm1 As SqlParameter

loParm1 = New SqlParameter()

loParm1.ParameterName = "@startdate"

loParm1.DbType = DbType.DateTime

loParm1.Direction = ParameterDirection.Input

loParm1.Value = rptstartdt

'-- Create a new parameter that matches the stored procedure

Dim loParm2 As SqlParameter

loParm2 = New SqlParameter()

loParm2.ParameterName = "@enddate"

loParm2.DbType = DbType.DateTime

loParm2.Direction = ParameterDirection.Input

loParm2.Value = rptenddt

'-- Fill the business object by calling the stored procedure

Me.PayrollBORPT.FillByStoredProcedure("sdsi_ExtractPayrollErrors", loParm1, loParm2)

RE: fillbystoredprocedure with multiple parameters

Michael R Poirier — Sun, 01 Nov 2009 09:08:48 GMT

Edhy,

worked like a charm - thanks much.

Mike

RE: fillbystoredprocedure with multiple parameters

Edhy Rijo — Fri, 30 Oct 2009 21:27:35 GMT

Hi Michael and welcome to the forums.

I don't know what may be wrong with your procedure, but the way I fill my BO with Stored Procedure is like this:

[codesnippet]
Public Sub FillByCustomDates(ByVal startdate As DateTime, ByVal enddate As DateTime)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.sdsi_ExtractPayrollErrors"

'-- Create the parms
cmd.Parameters.AddWithValue("@startdate", startdate).SqlDbType = SqlDbType.DateTime
cmd.Parameters.AddWithValue("@enddate", enddate).SqlDbType = SqlDbType.DateTime

'-- Return the results
Me.FillDataTable(cmd)
End Sub
[/codesnippet]

You can copy and paste this code in your business object and then you may call it from any instance of your BO like any other method passing the start and end dates.

Good luck!