+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 39

Thread: Recordset and Stored Procedure issues!

  1. #1
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    Recordset and Stored Procedure issues!

    Below is my stored procedure:


    Code:
    CREATE PROCEDURE spTestEdit1
    
    (@username nvarchar(50) 
    
    )
    
    AS
    
    SELECT [TEST-1]
          ,[username]
          ,[FirstName]
          ,[Surname]
    FROM [dbTestSQL].[dbo].[TEST-1]
      WHERE username=@username
    
    and below is my asp page:

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <!--#include file= "../connection/conn.asp" -->
    
    <%
    
    
    'Checking to see what request is being submitted
    	'IF  Request.Form("submit")="submit" Then
    	
    	username=request.form("username")    ' Get the unique Ref being searched for
    	
    	'create a record set
    set rs = Server.CreateObject("ADODB.RecordSet") 
    	'Query the database
    	
    	
    		SQL="spTestEdit1 " + spud(session("username"))
    		
    				
    		rs.open sql, conn, 1, 1
    		IF NOT rs.eof THEN
    		  
    		  response.write sql
    		
    
    %>
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title></title>
    
    <link rel="stylesheet" href="../includes/calendar-blue.css" type="text/css"/>
    <link rel="stylesheet" href="../includes/new_amended1.css" type="text/css"/>
    <script src="../includes/calendar.js" language="javascript" type="text/javascript"></script>
    <script src="../includes/calendar-en.js" language="javascript" type="text/javascript"></script>
    <script src="../includes/calendar-setup.js" language="javascript" type="text/javascript"></script>
    
    </head>
    
    
    
    <body>
    
    
        <form id="form2" runat="server">
        </form>
        
        <form id="form1" runat="server">
        </form>
        
        <div id="frame">
        
        <div id="topHeader"></div>
        
        <div id="wrappermain">
            
            <div id="contentcenter">
               
                <form action="../sql/update/update3_extra.asp" 
                method="POST">
                
                  
            <p class="emailtop">Email Address 
    		<input class="emailbox formpush formText" name="username" size="30" type="text" value="<%Response.Write(Session("username"))%>" /></p>
            
                <fieldset class="header">
                   
                    <legend>User Information</legend>
                    
                    <p class="floatp">First Name</p>
                    
                    <input class="floatv formText formpush" name="FirstName" size="30" type="text"  value="<%=response.write(rs("FirstName"))%>" />
                    
                    <p class="floatp">Surname</p>               
                    <input class="floatv formText formpush" name="Surname" size="30" type="text" value="<%=response.write(rs("Surname"))%>" />
                    
     <%
    ELSE response.write("Please close this page and try again")
    
    
    'free the buffer by closing all the recordsets and the connection
    rs.close
    set rs= Nothing
    conn.close
    set conn= Nothing
    END IF
    'END IF
    %>
    
    my problem is, i cant get the asp page to return the UserName, FirstName, Surrname, from the recordset.

    Im deseperate, any ideas??????

  2. #2
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    436
    Rep Power
    7

    try this:
    Code:
    SQL="EXEC spTestEdit1 '" & spud(session("username")) & "'"
    
    Quote of the Month:
    Leaders: Leaders are like eagles. We don't have either of them here.

    Questions to Ponder:
    Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.

  3. #3
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    Quote Originally Posted by mehere View Post
    try this:
    Code:
    SQL="EXEC spTestEdit1 '" & spud(session("username")) & "'"
    
    thank you for your help, but the page now is calling my ELSE statement which says :

    "Please close this page and try again"

    which tells me that the RS isnt filling up.. any ideas?

  4. #4
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    1,037
    Blog Entries
    2
    Rep Power
    13

    Check to ensure that your session variable "username" actually contains a value and that value is indeed in your database. Try putting a Response.Write in your Else section to write back the username.

    Also, rather than setting up an sql string in memory like this, you really should be using parameters in the command object to pass the username value. Then, set the command text to "spTestEdit1" and the command type to be a stored procedure.
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

  5. #5
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    Quote Originally Posted by Wolffy View Post
    Check to ensure that your session variable "username" actually contains a value and that value is indeed in your database. Try putting a Response.Write in your Else section to write back the username.

    Also, rather than setting up an sql string in memory like this, you really should be using parameters in the command object to pass the username value. Then, set the command text to "spTestEdit1" and the command type to be a stored procedure.
    Hi Wolffy,

    i get this error when i response.write the username in the ELSE section :

    Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

    i am aware that i should be using parameters and a query string for a session variable. but i just want to get this working first.

  6. #6
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    1,037
    Blog Entries
    2
    Rep Power
    13

    OK, that's strange. Before I go too far down a wrong road, what the heck is the spud function?

    In your code you have username = request.form("username") but then in your sql you have session("username"). So is it a session variable or a form variable? Also, when you did the resposne.write did you Response.Write(session("username"))?
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

  7. #7
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    Quote Originally Posted by Wolffy View Post
    OK, that's strange. Before I go too far down a wrong road, what the heck is the spud function?

    In your code you have username = request.form("username") but then in your sql you have session("username"). So is it a session variable or a form variable? Also, when you did the resposne.write did you Response.Write(session("username"))?
    LOL spud function:

    Code:
    function spud(str)
    str=chr(34)+str+chr(34)
    spud=str
    end function
    
    sorry it displays the username correctly...

  8. #8
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    1,037
    Blog Entries
    2
    Rep Power
    13

    Ah -- ok, So i think your sql command is becomming exec spTestEdit1 "jqpublic" while I think is should be exec spTestEdit1 'jqpublic' as SQL uses single quotes for strings. Not sure if ADO mungs it around or not. Single quote is chr(39)

    [edit]OH never mind, now I see the dang quotes[/edit]

    [edit2] OK, that's what I get for posting during a meeting. Anyway, if you are using what mehere gave you, your sql string is now (i hope) exec spTestEdit1 '"jqpublic"' which isn't going to work. Why are you putting double quotes around the user name? [/edit]
    Last edited by Wolffy; June 23rd, 2009 at 10:25 AM.
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

  9. #9
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    Quote Originally Posted by Wolffy View Post
    Ah -- ok, So i think your sql command is becomming exec spTestEdit1 "jqpublic" while I think is should be exec spTestEdit1 'jqpublic' as SQL uses single quotes for strings. Not sure if ADO mungs it around or not. Single quote is chr(39)

    [edit]OH never mind, now I see the dang quotes[/edit]

    [edit2] OK, that's what I get for posting during a meeting. Anyway, if you are using what mehere gave you, your sql string is now (i hope) exec spTestEdit1 '"jqpublic"' which isn't going to work. Why are you putting double quotes around the user name? [/edit]
    spud is supposed to sort the quotes out... :s

  10. #10
    Barn Enthusiast Centurion is on a distinguished road Centurion's Avatar
    Join Date
    Dec 2008
    Posts
    429
    Rep Power
    2

    if i use my original SQL= line, then i get this error :

    Microsoft OLE DB Provider for SQL Server (0x80040E14)
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

+ Reply to Thread
Page 1 of 4 1 2 3 ... LastLast

Similar Threads

  1. SQL Job or stored procedure
    By todd2006 in forum SQL Development
    Replies: 5
    Last Post: February 11th, 2009, 02:20 PM
  2. stored procedure
    By todd2006 in forum ASP Development
    Replies: 7
    Last Post: February 5th, 2009, 03:02 PM
  3. stored procedure
    By todd2006 in forum Microsoft SQL Server
    Replies: 1
    Last Post: February 5th, 2009, 01:25 PM
  4. If statement stored procedure
    By peebman2000 in forum SQL Development
    Replies: 32
    Last Post: May 23rd, 2008, 03:54 PM
  5. Dynamic Stored Procedure
    By jmurrayhead in forum Microsoft SQL Server
    Replies: 16
    Last Post: March 26th, 2008, 11:19 AM

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

SEO by vBSEO