SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't get a stored procedure to return any rows...

    I have a stored procedure in SQL Server 2000. It's as follows:
    Code:
    CREATE PROCEDURE tuAbs
     
     @date VARCHAR(255),
     @tugroup VARCHAR(255)
     
     AS
     
     SELECT 
     	Student.Stu_Reference, Student.Stu_Lastname, Student.Stu_Firstname, 
     	DStumMark.dstum_mark, DStumMark.dstum_starttime, DStumMark.dstum_endtime, DStumMark.dstum_weekstartdate, 
     	Staff.Sta_Lastname, Staff.Sta_Firstname, 
     	Module.Mod_Reference, Module.Mod_Description, 
     	Student.Stu_Tutorgroupkey, DStumMark.dstum_latest
     FROM ((((Amr INNER JOIN (Staff INNER JOIN Asr ON Staff.Sta_Syskey = Asr.Asr_Staffkey) ON Amr.Amr_Actkey = Asr.Asr_Actkey) INNER JOIN Msr ON Amr.Amr_Modkey = Msr.Msr_Modkey) INNER JOIN Student ON Msr.Msr_Stukey = Student.Stu_Syskey) INNER JOIN DStumMark ON (Amr.Amr_Actkey = DStumMark.dstum_actkey) AND (Student.Stu_Syskey = DStumMark.dstum_stukey)) INNER JOIN Module ON Msr.Msr_Modkey = Module.Mod_Syskey
     WHERE (((DStumMark.dstum_mark)='o') AND ((DStumMark.dstum_weekstartdate)=@date) AND ((Student.Stu_Tutorgroupkey)=@tugroup) AND ((DStumMark.dstum_latest)=1))
     ORDER BY Student.Stu_Lastname, Student.Stu_Firstname DESC;
     GO
    The query was built in acess, in which it runs fine returning 58 rows for the test date and tugroup values I'm using. The query also runs fine in Query Analyzer, giving the same 58 rows for the test data I'm giving it.

    When I try to run the procedure in an ASP (via an ODBC connection) it returns nothing! The query doesn't appear to fail, I get no error message (as I would if the query failed to run etc) it just returns no data. I have also tried putting the values into the procedure and running the query without sending any values to it - it still returns nothing.

    I have subsituted the query in the ASP page with another procedure from the same database, which executes fine returning the data I expected.

    I have been starting at this for a few hours now and I can't see why it runs in Query Analyzer but not in ASP

    Any suggestions would be very much appreciated.

    I've included the ASP code I'm attempting to test the query with below. The connection pc1 is establised via an include file and works fine for other queries run on the page.
    Code:
    <ul>
       <%
     
    sql2 = " tuabs '1/8/2006', 'Y12DF-AHN-3' "
     set cat2 = pc1.Execute(sql2)
    
     do until cat2.eof
     
     response.write("<li>" & cat2.fields("stu_reference") & "</li>")
     
     cat2.movenext
     loop
     
     cat2.close
     
     %></ul>
    Rick

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds to me like if the query runs fine in access, and runs fine in Query Analyzer, but doesn't run fine in ASP, then this is an ASP problem

    moving to ASP forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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