Re: Problem refreshing recordset

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Jeff -

Thanks for your response.

Can anyone see what may be causing this?

A couple of things which have nothing to do with your problem:

You're doing this twice:

Set rsRefresh = New ADODB.Recordset
THANKS - I'm getting tired, obviously.

Also, there's no need to set prmRefresh to a New Parameter when you're going
to turn right around and set it to the result of CreateParameter().

Set prmRefresh = New ADODB.Parameter
Set prmRefresh = cmdRefresh.CreateParameter("@Agent", adVarChar,
adParamInput, 20, frmAdminFilterCalls.txtAgtName.Text)

AGAIN, THANKS
Now your problem may lie here:

'Open recordset
rsRefresh.Open cmdRefresh, , adOpenDynamic, adLockOptimistic

Call cmdPrevious_Click
End Sub

rsRefresh is local to the FormRefresh() procedure, so I don't see what good
it does to open it, call another method (and be prepared to get jumped on by
others for directly calling an event handler), and then exit the sub. In

TESTING PURPOSES ONLY - I'LL MAKE IT PRETTY LATER

other words, it doesn't appear like you're actually doing anything with
rsRefresh....unless....wait, does the spCallsAllAdminRefresh SP perchance do
an INSERT statement or something? If so, AAAAGGGGGHHHHH!!!! Do NOT use
Recordsets to run non-SELECT statements. Just use the Execute() method of
the Command.

Thanks for clarifying this. I've been unclear on when to use recordsets
and when to just use the execute method. My tendency is to use execute
anyways. I am not too swift about working with recordsets, as you can see.
I did not include the code for the insertion because I know they're
inserting - when I run my sproc below, it returns the correct records.
My stored procedure is a select sproc:

CREATE PROCEDURE [dbo].[spCallsAllAdminRefresh]
@Agent nvarchar (20),
@FromDate datetime
AS
SELECT c.CallID, c.Agent, c.CallerName,
c.Phone, c.CallDate, c.LoanType, c.NumbApps,
c.Comment, c.CallTypeID, ct.CallType AS CallTypeText,
c.DeclarationID, d.Declaration AS DeclarationText,
d.DeclarationNo, d.StateID, s.State
FROM tblStates s
RIGHT JOIN (tblDeclaration d
RIGHT JOIN (tblCallType ct
RIGHT JOIN tblCalls c
ON ct.CallTypeID = c.CallTypeID)
ON d.DeclarationID = c.DeclarationID)
ON s.StateID = d.StateID
Where c.Agent = @Agent And c.CallDate Between @FromDate And GetDate() + 1
Order by c.CallDate;

I did make all the changes above. It still is not returning the last
record. (Actually, I just ran it and inserted a couple of records and when I
refreshed it, none of those showed up either.) Any other suggestions? This
has to be a textbook solution (only problem, none of my books has this in
them). Do you know of any internet sources that may shed some light on this?

--
Sandy


"Jeff Johnson" wrote:


"Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5EDFA1C5-
Can anyone see what may be causing this?

A couple of things which have nothing to do with your problem:

You're doing this twice:

Set rsRefresh = New ADODB.Recordset

Also, there's no need to set prmRefresh to a New Parameter when you're going
to turn right around and set it to the result of CreateParameter().

Set prmRefresh = New ADODB.Parameter
Set prmRefresh = cmdRefresh.CreateParameter("@Agent", adVarChar,
adParamInput, 20, frmAdminFilterCalls.txtAgtName.Text)

Now your problem may lie here:

'Open recordset
rsRefresh.Open cmdRefresh, , adOpenDynamic, adLockOptimistic

Call cmdPrevious_Click
End Sub

rsRefresh is local to the FormRefresh() procedure, so I don't see what good
it does to open it, call another method (and be prepared to get jumped on by
others for directly calling an event handler), and then exit the sub. In
other words, it doesn't appear like you're actually doing anything with
rsRefresh....unless....wait, does the spCallsAllAdminRefresh SP perchance do
an INSERT statement or something? If so, AAAAGGGGGHHHHH!!!! Do NOT use
Recordsets to run non-SELECT statements. Just use the Execute() method of
the Command.



.