fill ado recordset using stored procedure with 3 parameters

[shavon]

Good day:

I am having the most difficult time with what should be a simple task.

I want to fill a recordset using a stored procedure.

Dim rs as new adodb.recordset
rs.open "SP_GetTriplicate '10/30/2006 09:00:01', '10/31/2006 09:00:00', 3",cnn, adOpenKeyset, adLockOptimistic, adCmdStoredProc

do while not rs.eof
something
rs.movenext
loop

I have also tried the techniques with the ado command object and parameters to no avail.

My Stored procedure works in sql query analyzer.

Your assistance is greatly appreciated.

Thank you

 

[shavon]

Additional Info: The Stored Procedure being used

CREATE PROCEDURE SP_GetTriplicate
@pDateFrom varchar(20), -- This is the input parameter.
@pDateTo varchar(20),
@iDupCount int
AS
BEGIN

CREATE TABLE #AllAcc (transit varchar(5), box int, imageid char(8), account varchar(7))

insert into #AllAcc
select track.transit, images.box, images.imageid, images.account from images inner join track on images.box = track.box where releasetime between @pDateFrom and @pDateTo and images.account <> '0000000'
UNION
select track.transit, images.box, images.imageid, images.account2 from images inner join track on images.box = track.box where releasetime between @pDateFrom and @pDateTo and not (images.account2 is null or images.account2 = '' or images.account2 = '0000000')
union
select track.transit, images.box, images.imageid, images.account3 from images inner join track on images.box = track.box where releasetime between @pDateFrom and @pDateTo and not (images.account3 is null or images.account3 = '' or images.account3 = '0000000')
union
select track.transit, images.box, images.imageid, images.account4 from images inner join track on images.box = track.box where releasetime between @pDateFrom and @pDateTo and not (images.account4 is null or images.account4 = '' or images.account4 = '0000000')
union
select track.transit, images.box, images.imageid, images.account5 from images inner join track on images.box = track.box where releasetime between @pDateFrom and @pDateTo and not (images.account5 is null or images.account5 = '' or images.account5 = '0000000')

SELECT transit, account, Count(account) as Totals
FROM #AllAcc
GROUP BY transit, account
HAVING COUNT(account) >= @iDupCount
ORDER BY transit, count(account), account

--drop table #AllAcc

END
RETURN
GO

 

[gmmastros]

You don't mention what the problem is.

A couple things come to mind. First, I assume this is a microsoft sql server database that you are dealing with. If so, I suggest you modify the stored procedure so that SET NOCOUNT ON is the first statement. Something like...

Alter Procedure SP_GetTriplicate 
  @Date1 DateTime,
  @Date2 DateTime,
  @Int1 Integer
AS
[!]SET NOCOUNT ON[/!]
(the rest of the query here)

Second... You shouldn't name your stored procedures with [!]sp_[/!] at the beginning of the name. When SQL Server executes your query, it will search the master database for the stored procedure and then the user database (that you are connected to). By changing your naming convention, you will get better performance (although not much and you will probably never notice). Of bigger concern is that Microsoft may come along and create a procedure with the same name that you are using, and then your application will break. I suggest you prefix your stored procedure names with USP (User Stored Procedure).

Third...
You should change the way you deal with dates. This is one of those habit things that you should get used to. The problem is that most of the worlds deal with dates differently than americans. For example 1/4/2006 can be Jan 4 or April 1. Since this ambiguity exists, you could potentially have problems in the future. To prevent this from being a problem, you should use the ISO unseperated date format, which is YYYYMMDD. Using this format, your query call would look like this...

[tt][blue]
rs.open "SP_GetTriplicate '20061030 09:00:01', '20061031 09:00:00', 3",cnn, adOpenKeyset, adLockOptimistic, adCmdStoredProc[/blue][/tt]





-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shavon]

Resolved!!

This works for whatever reason. Thank you.

Dim objCmd As New ADODB.Command
Dim rs As New ADODB.Recordset

With objCmd
.ActiveConnection = d.c 'You can also just specify a connection string here
.CommandText = "SP_GetTriplicate"
.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag
'Add Input Parameters
.Parameters.Append .CreateParameter("@pDateFrom", adVarChar, adParamInput, 20, "10/30/2006 09:00:01")
.Parameters.Append .CreateParameter("@pDateTo", adVarChar, adParamInput, 20, "10/31/2006 09:00:00")
.Parameters.Append .CreateParameter("@iDupCount", adInteger, adParamInput, , 3)
rs.Open objCmd, , adOpenStatic, adLockOptimistic, adCmdStoredProc
End With
Report.Database.SetDataSource rs, 3, 1
CRViewer1.ReportSource = Report
CRViewer1.ViewReport
CRViewer1.Zoom 100
Set rs = Nothing
Set Report = Nothing