fillbystoredprocedure with multiple parameters

StrataFrame Forum

Back

fillbystoredprocedure with multiple parameters

Author	Message
Michael R Poirier	Michael R Poirier posted 15 Years Ago Post Details Share Post
Group: StrataFrame Users Posts: 9, Visits: 131	Edhy, worked like a charm - thanks much. Mike
	Reply Like 0
Edhy Rijo	E Edhy Rijo posted 15 Years Ago Post Details Share Post
E Group: StrataFrame Users Posts: 2.4K, Visits: 23K	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: 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 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! Edhy Rijo
	Reply Like 0
Michael R Poirier	Michael R Poirier posted 15 Years Ago Topic Details Share Topic
Group: StrataFrame Users Posts: 9, Visits: 131	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 GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- 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 AS 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)
	Reply Like 0