Using Conn.Execute() in an ASP file
-
Happy Friday Everyone,
I am having trouble finding an answer to this question so hopefully someone has it, can answer it, and we can move on.
I have created an ASP document that interacts with my SQL2000 server database.
At the top of the document I do the following:
dim sql
sql="Select (not important what goes here I know it works)
Set notes = Conn.Execute(sql)
Now towards the bottom of the code, I want to perform an Update
So I have the following:
dim (variables go here)
fixNotes = "EXEC dbo.usp_UpdateProductionNotes @programID = '" & programID
Conn.Execute(fixNotes)
When I perform this second Conn.Execute statement via Submit button, it throws back an error stating that "
either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."
I'm thinking that since I did the first Conn.Execute statement and did a select and Set, I can't use Conn.Execute again to perform any other operations in this ASP file. Is this the case? What can I do to get around it? Am I doing something else wrong?
Thanks and I apologize for the very "newbie" question
Andrew
-
I don't think the issue is with Conn.Execute where it is calling that stored proc.
I think you have a statement after Conn.execute that is referencing a recordset (notes maybe?) that does not exist/ has not been created via the Set Recordset= Conn.Execute. if the Conn failed, you would get a different error (ie "procedure does not exist, cant find object, etc)
the error you are getting is related to an ADODB.Recordset specifically, so it's gotta be something you are glancing over in the code.
add a Response.Write ("conn executed the UpdateProductionNotes command") directly after the command, and see if it renders before you get the BOF/EOF error.
if you are referencing the notes recordset, do you have something like
if Not (notes.EOF) Then
Response.Write(notes(0).Value
Else
Response.Write "No Notes Found"
End If
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the insight on that, I see what the problem is now. Not sure how I'm going to solve it though -
So I have my notes() array containing notes(0) and notes(1)
Then I am referencing those in a form like this
<form action ="EditNotes.asp" method="post">
Program Name: <br /><textarea rows = "1" cols = "30" name = "program"> <%=notes(1)%></textarea>
Program Note: <br /><textarea rows = "3" cols = "70" name = "programNote"> <%=notes(0)%></textarea>
<input type = "submit" value = "Update Notes"
<%dim note = Request.Form("programNote")
dim name = Request.Form("program")
dim fixNote = "my sql statement"
Conn.Execute(fixNote)
%>
/>
So it seems that once I click the submit button, the references to notes(0) and notes(1) do not exist anymore. This is causing the EOF or BOF error but what can I do to get around this then?
For some reason I can't think of any other way to do this.
-
I'm assuming that on this document, it looks something like this based on your original post.
You would want to add something similar in blue; note how i removed the shortcut for Response.Write (<%=) and turned it into a logical block to decide whether to print it or not.
At the top of the document said you do the following:
<%dim sql
sql="Select (not important what goes here I know it works)
Set notes = Conn.Execute(sql)%>
<form action ="EditNotes.asp" method="post">
Program Name: <br /><textarea rows = "1" cols = "30" name = "program"> <%If Not notes.EOF Then Response.Write notes(1)%></textarea>
Program Note: <br /><textarea rows = "3" cols = "70" name = "programNote"> <%If Not notes.EOF Then Response.Write notes(0)%></textarea>
<input type = "submit" value = "Update Notes"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Oh I see...that is a much better way to do that. I never thought of doing a response.write like that. It seems to be working how I want it to now. Ah.. it feels good to learn!
Thanks again for your help I really appreciate it.
Sincerely,
Andrew
-
glad I could help.
I was wondering if your stored procedure will save your notes correctly if no notes exist yet...it wasn't obvious to me whether the notes get saved based on an ID (program?)
Good luck on your project.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I am customizing and modifying a program that my company has purchased. The only way to get to this page I have created is from a page that already contains the notes. I have added a link to the bottom of that page to click on so that notes can be modified or updated when criteria changes.
I'm passing the primary key ProgramID to my page and using that number to query for the ProductionNotes field which is what is being modified. I can't think of any way for the notes to not exist and I will never be inserting a new record into the database.
This is sloppy right now and I just learned about parameter passing but here is what I have for code when the user clicks the submit button
still inside the form
<input type = "submit" value = "Update Production Notes"
<%
dim productionNotes
dim revisedNotes
dim fixNotes
dim progID
progID = Request.Form("progID")
productionNotes = REquest.Form("productionNote")
!-- Both of these requests come from textareas within my created form --!
revisedNotes = Replace(productionNotes, "'", "' '"
fixNotes = "EXEC dbo.usp_UpdateProductionNotes @programID = '" & progID & "', @notes = '" & revisedNotes & "'"
Conn.Execute(fixNotes)
%> />
The stored procedure just does a simple UPDATE table SET notesfield = @notes WHERE id = @programID
Now that I know it works I will be "attempting" to revamp this so it uses parameters via sqlCommand. I'm sure I'll have many more questions with that.
Any comments or suggestions you have are always appreciated.
-
this is a prototype example, because your field names, as welll as other manditory (NOT NULL) columns are not mentioned here.
you want to be able to insert a note from the page if one didn't exist yet. I would change your procedure to do either an insert or an update; something like this:
CREATE PROCEDURE usp_UpdateProductionNotes (
@programID int,
@Notes varchar(2000) )
AS
BEGIN --PROC
--create if it doesn't exist
IF NOT EXISTS(SELECT ID FROM TABLE WHERE ID = @programID)
BEGIN
INSERT INTO TABLE(ID,NOTESFIELD) VALUES(@PROGRAMID,@Notes)
END
--update if it does exist
ELSE
BEGIN
UPDATE TABLE SET NOTESFIELD = @NOTES WHERE ID = @PROGRAMID
END
END --PROC
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Always good to use Command object when u execute a stored procedure.Just create an object of ADODB.Command and you can set its properties like its a stored procedure or text/table , ur procedure name,
parameters name ,value and then you can execute the procedure.
Good luck..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply