DeveloperBarn

Centurion · #1 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 · #2 June 23rd, 2009, 09:30 AM

try this:

Code:

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

Centurion · #3 June 23rd, 2009, 09:42 AM

Quote:

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 · #4 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 · #5 June 23rd, 2009, 10:01 AM

Quote:

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 · #6 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 · #7 June 23rd, 2009, 10:15 AM

Quote:

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 · #8 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 · #9 June 23rd, 2009, 10:27 AM

Quote:

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 · #10 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 Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SQL Job or stored procedure	todd2006	SQL Development	5	February 11th, 2009 02:20 PM
stored procedure	todd2006	ASP Development	7	February 5th, 2009 03:02 PM
stored procedure	todd2006	Microsoft SQL Server	1	February 5th, 2009 01:25 PM
If statement stored procedure	peebman2000	SQL Development	32	May 23rd, 2008 03:54 PM
Dynamic Stored Procedure	jmurrayhead	Microsoft SQL Server	16	March 26th, 2008 11:19 AM

DeveloperBarn

Recordset and Stored Procedure issues!

Recordset and Stored Procedure issues!

Similar Threads