This is a discussion on Recordset and Stored Procedure issues! within the ASP Development forums, part of the Programming & Scripting category; Below is my stored procedure: Code: CREATE PROCEDURE spTestEdit1 (@username nvarchar(50) ) AS SELECT [TEST-1] ,[username] ,[FirstName] ,[Surname] FROM [dbTestSQL].[dbo].[TEST-1] ...
| |||||||
|
#1
| ||||
| ||||
| 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
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
%>
Im deseperate, any ideas?????? ![]() ![]() |
|
#2
| ||||
| ||||
| try this: Code: SQL="EXEC spTestEdit1 '" & spud(session("username")) & "'"
__________________ Quote of the Month: Mistakes: It could be that the purpose of your life is only to serve as a warning to others. 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
| ||||
| ||||
| Quote:
"Please close this page and try again" which tells me that the RS isnt filling up.. any ideas? |
|
#4
| ||||
| ||||
| 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
| ||||
| ||||
| Quote:
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
| ||||
| ||||
| 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"))? |
|
#7
| ||||
| ||||
| Quote:
Code: function spud(str) str=chr(34)+str+chr(34) spud=str end function |
|
#8
| ||||
| ||||
| 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. |
|
#9
| ||||
| ||||
| Quote:
|
|
#10
| ||||
| ||||
| 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. |
![]() |
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
| |
| ||||
| 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 |