Recordset and Stored Procedure issues!

**Centurion** · Centurion, June 23rd, 2009 09:08 AM

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??????

**mehere** · mehere, June 23rd, 2009 09:30 AM

try this:

Code:

SQL="EXEC spTestEdit1 '" & spud(session("username")) & "'"

**Centurion** · Centurion, June 23rd, 2009 09:42 AM

Originally Posted by mehere

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?

**Wolffy** · Wolffy, June 23rd, 2009 09:49 AM

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.

**Centurion** · Centurion, June 23rd, 2009 10:01 AM

Originally Posted by Wolffy

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.

**Wolffy** · Wolffy, June 23rd, 2009 10:07 AM

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"))?

**Centurion** · Centurion, June 23rd, 2009 10:15 AM

Originally Posted by Wolffy

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...

**Wolffy** · Wolffy, June 23rd, 2009 10:21 AM

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]

**Centurion** · Centurion, June 23rd, 2009 10:27 AM

Originally Posted by Wolffy

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

**Centurion** · Centurion, June 23rd, 2009 10:30 AM

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.

Thread: Recordset and Stored Procedure issues!

LinkBack

Thread Tools

Display

Recordset and Stored Procedure issues!

Similar Threads

SQL Job or stored procedure

stored procedure

stored procedure

If statement stored procedure

Dynamic Stored Procedure

Bookmarks

Bookmarks

Posting Permissions