GO
-- =============================================
-- Author: aebea
-- Create date: 05/13/2011
-- Description: returns recordset based on parameters
-- =============================================
ALTER PROCEDURE [dbo].[sp_getRecIdsForProcessing]
-- Add the parameters for the stored procedure here
@act varchar(50),
@beginDate datetime,
@endDate datetime,
@processStep int,
@Rowcount int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @firstDate as datetime
DECLARE @lastDate as datetime
SET @firstDate = (SELECT CONVERT(DATETIME,@beginDate,101))
SET @lastDate = (SELECT CONVERT(DATETIME,@endDate,101))
-- Insert statements for procedure here
if @act = '*'
begin
SELECT tbl_ProcessTracking.RecID, tbl_ProcessTracking.OnThisProcStep, tbl_ProcessTracking.AsOf, tbl_MasterFile.PDFName, tbl_MasterFile.Act,
tbl_MasterFile.ActDate, tbl_MasterFile.FS_Name, tbl_MasterFile.DateReceived, tbl_MasterFile.CaseID, tbl_MasterFile.SheetComment,
tbl_MasterFile.Del_Rec
FROM tbl_MasterFile INNER JOIN
tbl_ProcessTracking ON tbl_MasterFile.RecID = tbl_ProcessTracking.RecID
WHERE [ActDate] between @firstdate and @lastdate
and [OnThisProcStep]=@processStep-1
end
else
begin
SELECT tbl_ProcessTracking.RecID, tbl_ProcessTracking.OnThisProcStep, tbl_ProcessTracking.AsOf, tbl_MasterFile.PDFName, tbl_MasterFile.Act,
tbl_MasterFile.ActDate, tbl_MasterFile.FS_Name, tbl_MasterFile.DateReceived, tbl_MasterFile.CaseID, tbl_MasterFile.SheetComment,
tbl_MasterFile.Del_Rec
FROM tbl_MasterFile INNER JOIN
tbl_ProcessTracking ON tbl_MasterFile.RecID = tbl_ProcessTracking.RecID
WHERE [ActDate] between @firstdate and @lastdate
and [Act] = @act
and [OnThisProcStep]=@processStep-1
end
set @rowcount = @@rowcount
RETURN @@rowcount
END
ASKER
ASKER
Public Function sp_getRecIdsForProcessing(sqlConnection, this_rst, this_act, this_fstDay, this_lstDay, this_process_step, this_rst_count)
Dim connectionString As String
Dim category As String
Dim category_searchString As String
'establish connection
Set sqlConnection = New ADODB.Connection
connectionString = "DRIVER={sql server};DATABASE=RDITS_build020310; SERVER=SBODWH\SQLEXPRESS; Trusted_Connection=Yes;"
sqlConnection.connectionString = connectionString
sqlConnection.Open
'set recordsets
Set this_rst = New ADODB.Recordset
'set commands
Set getRecordSet = New ADODB.Command
'open the recordset
Debug.Print category_searchString
Debug.Print actDate
Debug.Print fs_name
With getRecordSet
.ActiveConnection = sqlConnection
.CommandType = adCmdStoredProc
.CommandText = "sp_getRecIdsForProcessing"
.Parameters("@act").Value = this_act
.Parameters("@beginDate").Value = this_fstDay
.Parameters("@endDate").Value = this_lstDay
.Parameters("@processStep").Value = this_process_step
.Parameters("@rowcount").Value = 0
End With
' get recordset
Set this_rst = getRecordSet.Execute
End Function
ASKER
ASKER
ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
this line is not needed
anyhow, see here on how to run properly stored procedure, with output parameters:
http://msdn.microsoft.com/en-us/library/aa224819%28v=sql.80%29.aspx