Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    23

    Unanswered: Need advise with stored proc + ASP issue.

    Wrote a simple stored proc to list out names which have one param to see whether is it a full listing or whether is it based on alphabetical

    Code:
    CREATE PROCEDURE usr_listcontractors(@sortby varchar(2))
    as
    If @sortby ='fl'
    select *  from contractor where deleted ='N' order by 'CName' 
    else
    select * from contractor where cname like @sortby+"%" and deleted ='n' order by 'CName'
    GO
    The above works fine in query analyser (exec usr_listcontractors)

    Under my ASP page,

    Code:
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString=mstrConnectionString 
    objConn.open
    strcontractors= "usr_listcontractors fl"
    Set objRS= Server.CreateObject("ADODB.Recordset")
    objRS.Open strcontractors,objConn,1,3
    	objRS.PageSize = 15		
    	objRS.CacheSize = objRS.PageSize
    	intPageCount = objRS.PageCount 
    	intRecordCount = objRS.RecordCount 
    .
    .
    .
    The above is supposed to show up the full listing of contractors. But nothing shows up on the ASP page.

    But if i were to execute "usr_listcontractors fl" in query analyser, the result displays promptly.

    What am I missing out? Please advise. I believe the issue is with the ASP scripts.

    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    60

    Re: Need advise with stored proc + ASP issue.

    Looks like fl needs to be in quotes:
    Code:
    strcontractors= "usr_listcontractors 'fl' "

  3. #3
    Join Date
    Sep 2003
    Posts
    14
    Try strcontractors= "usr_listcontractors ('fl') "
    if what has been suggested earlier does not work

  4. #4
    Join Date
    May 2003
    Posts
    23
    thanks guys. the issue is solved. the issue lies with the cursortype.

Posting Permissions

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