Stored Procedure (parameters) :: recordset empty :

Results 1 to 2 of 2

Thread: Stored Procedure (parameters) :: recordset empty :

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedure (parameters) :: recordset empty :

    This problem has been dogging me for a week now:<BR><BR>I seem to be getting an empty recordset back from a stored procedure which has one parameter value called RegID. I used create.parameter to mirror the Database parameter. I get no errors until I open up a recordset. There appears to be nothing there.<BR><BR>I am going insane. In Access 2000, I get prompted for my parameter e.g. RegionID=3 and up pop the records but inside ASP the recordset comes back EOF OR BOF. It took me ages to get the parameter thing to work and now I appear to have no records:<BR> <BR>The stored procedure sits in A ccess 2000:<BR><BR>PARAMETERS RegID Short;<BR>SELECT DISTINCT YachtCrewed_PriceCategoryCabin.YachtCrewedID, YachtCrewed_PriceCategoryCabin.PCCabinID, YachtCrewed_Region.RegionID<BR>FROM (Region INNER JOIN (YachtCrewed INNER JOIN YachtCrewed_Region ON YachtCrewed.YachtCrewedID = YachtCrewed_Region.YachtCrewedID) ON Region.RegionID = YachtCrewed_Region.RegionID) INNER JOIN (PriceCategoryCabin INNER JOIN YachtCrewed_PriceCategoryCabin ON PriceCategoryCabin.PCCabinID = YachtCrewed_PriceCategoryCabin.PCCabinID) ON YachtCrewed.YachtCrewedID = YachtCrewed_PriceCategoryCabin.YachtCrewedID<BR>WH ERE (((YachtCrewed_Region.RegionID) Like [RegID] & &#039;*&#039;));<BR>--------------------------------------------------<BR><BR>My ASP CODE:<BR>&#060;% <BR> <BR>userRegionID=Request("RegionID")<BR>response.w rite userRegionID<BR>userRegionID=3 &#039;//FOR TESTING<BR><BR><BR>Set adoCon = Server.CreateObject("ADODB.Connection")<BR> strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/signature/signature4.mdb") &#039;//This one is for Access 2000/2002<BR>adoCon.Open strCon<BR>adoCon.CursorLocation = adUseclient<BR><BR>Set cmd = server.createobject("ADODB.command")<BR>Set cmd.ActiveConnection = adoCon<BR><BR> cmd.CommandText = "UserRegionQuery"<BR> cmd.CommandType = adCmdStoredProc<BR><BR><BR> &#039;Create parameters for your command object that<BR> &#039;match the stored procedure parameters.<BR><BR><BR> Set objPrm1 = cmd.CreateParameter("@RegID", adSmallInt, AdparamInput, 3)<BR> cmd.Parameters.Append objPrm1<BR> objPrm1.Value = userRegionID<BR><BR><BR> Set rx = Server.createobject("ADODB.Recordset")<BR> Set rx = cmd.Execute<BR><BR> <BR><BR> response.write rx(0) &#039;*************************************** ERROR - Either EOF or BOF = true<BR> <BR><BR><BR> rx.Close<BR> <BR> Set cmd = Nothing<BR><BR>adoCon.close <BR><BR>%&#062;<BR><BR> <BR><BR> <BR><BR><BR><BR> <BR> <BR> <BR><BR><BR><BR> <BR><BR> <BR>

  2. #2
    Join Date
    Dec 1969

    Default Ok this is assuming you have tested this within

    Access and that you actually have data in your table. ; )<BR><BR>First, you don&#039;t necessarily need to use a command object to simply execute a stored proc and return a recordset:<BR><BR>objRS = adoCon.Execute "exec UserRegionQuery "&userRegionID<BR><BR>However, if you really want to use a command object try this:<BR><BR>cmd.CommandText = "UserRegionQuery"<BR>cmd.CommandType = 1 &#039;***adCmdText<BR><BR>Set objPrm1 = cmd.CreateParameter("@RegID", adSmallInt, AdparamInput, 3, userregionid)<BR>cmd.Parameters.Append objPrm1<BR><BR>Good luck<BR>Pete<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts