• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

Stored Proc Newbie: cant use .movefirst method

I'm converting an asp intranet site from using access and embedded sql to using sql server and stored procedures, partly to ensure longevity for the site, partly as a learning experience for myself.

old embedded sql:
      'Set rs = Server.CreateObject("ADODB.Recordset")
      'sql = "select tblReminder.*, tblBrand.id as BrandId, shortname, icon from tblReminder, tblBrand where tblBrand.id=tblReminder.brand and dateRemind <= '" & InsertDateStr(date) & "' and bIsDeleted=0 and username='" & lcase(username) & "' order by dateRemind"
      'rs.open sql, cnCRM

new "use a stored proc" code
      Set cmd = Server.CreateObject("ADODB.Command")

      cmd.activeconnection = cnCRM
      cmd.commandtext = "crmGetRemindersForUser"
      cmd.commandtype = adCmdStoredProc
      cmd.Parameters.Append  cmd.createParameter("username", adChar, adParaminput, 40, sUsername)
      Set rs = cmd.execute      


Stored Proc:
CREATE PROCEDURE crmGetRemindersForUser
      @username varchar(40)

AS
SELECT r.*, b.id AS BrandId, b.shortname, b.icon
FROM tblReminder r INNER JOIN tblBrand b ON r.brand = b.id
WHERE
      r.dateRemind <= getdate()
      and r.bIsDeleted=0
      and r.username=@username
ORDER BY r.dateRemind
GO

now, a s amatter of course in prettyy much all my code, I do this:
      if rs.eof then
            response.write("<tr><td colspan='5'>No Reminders for user " & sUsername & "</td></tr>" & vbcrlf)
      else
            rs.movefirst
            do until rs.eof
                  'stuff
                  rs.movenext
            loop
      end if
      'etc

Now the question is, confirming I was definately at the right point by using rs.movefirst generated an error:
Error Number: -2147217896
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description: Rowset position cannot be restarted.

Why can't I use movefirst?  How can I?

I'm also having problems with dates, but thats another question. ^_^
0
Hecatonchires
Asked:
Hecatonchires
4 Solutions
 
mokuleCommented:
I must claim that I don't know VB but as uptill now nobody answers.
Here are some guess.

1. It may be unnecessary. simply remove it
  rs.movefirst

2.
 maybe You should try
     Set rs = cmd.open    
in place of
     Set rs = cmd.execute    
0
 
mokuleCommented:
also You can try to set cursor location to client
0
 
rafranciscoCommented:
Do you get an error when you remove the rs.movefirst?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HecatonchiresAuthor Commented:
mokule: i cant seem to do that with the command object
rafrancisco: no error when its commented out
0
 
appariCommented:
try setting the cursor location and cursortype properties of recordset object.


change your code to something like this

     Set cmd = Server.CreateObject("ADODB.Command")
     Set rs  = Server.CreateObject("ADODB.RecordSet")
     rs.CursorLocation = adUseClient
     rs.CursorType = adOpenStatic

     cmd.activeconnection = cnCRM
     cmd.commandtext = "crmGetRemindersForUser"
     cmd.commandtype = adCmdStoredProc
     cmd.Parameters.Append  cmd.createParameter("username", adChar, adParaminput, 40, sUsername)
     Set rs = cmd.execute    

0
 
HecatonchiresAuthor Commented:
Havent tried that yet appari, and I will, but a bit worried.

My understanding is that Set [object] = Server.create... is something like c's malloc or c++'s new, ie: allocating memory.  So I'd be allocating to rs, but then changing the pointer to point to a new rs thats been returned by the command object, losing the reference to the first allocated memory.

Do i need to set the cursor properties before I get the recordset from the execute method, or can I do it after?
0
 
AmiiitCommented:
create SP again with this and then try..

Stored Proc:
CREATE PROCEDURE crmGetRemindersForUser
     @username varchar(40)

AS
set nocount ON
SELECT r.*, b.id AS BrandId, b.shortname, b.icon
FROM tblReminder r INNER JOIN tblBrand b ON r.brand = b.id
WHERE
     r.dateRemind <= getdate()
     and r.bIsDeleted=0
     and r.username=@username
ORDER BY r.dateRemind
set nocount OFF
GO
0
 
HecatonchiresAuthor Commented:
Amiiit: Sorry, change makes no difference.  

How do I specify that the recordset returned by the sp allows a moveable cursor?
0
 
HecatonchiresAuthor Commented:
I guess it must be unneccesary.  All my fiddling, nothing seems to matter.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now