April 28, 2003 at 4:59 pm
Hi folks, I've worked with various SQL DBs for years in a bit lighter capacity than a full-fledged DBA, but not by much. I happen to be baffled by this however, and please pardon the simplicity of the question. Basically, I just want to know if anyone has come across a similar issue and how it was resolved. The pieces to the puzzle are simple:
ASP ->
ADODB Connection Object Execute ->
MSSQL 7.0 Table Insert (via a stored procedure with return select) =
Double Insertion!
If I can guarantee that the stored procedure is not being executed twice by ASP/ADO and ensure that it is happening by SQL's hand itself, can anyone enlighten me as to what could possibly be happening?
The ASP code and such is a bit involved as it is an integrated framework, but I'm happy to supply a DB trace if necessary. The trace does show it is being executed twice (as the resulting inserted data does as well of course), and the procedure itself is little more than something like this:
SET NOCOUNT ON
INSERT mytable (this, that, other) VALUES (@this, @that, @other)
SELECT something AS something
Thanks a LOT in advance,
Michael
April 28, 2003 at 5:26 pm
Maybe you have a trigger firing that calls it? Or hitting an error in code that bounces the current line back above where the insert happens initially? What happens if you put a stop or exit sub right after the proc call in the code?
Andy
April 28, 2003 at 9:58 pm
we had a similar problem in one of our ASP pages...our situation was a case wherein the user clicks on a button and the data from the ASP page is inserted into the table...this might(??) be the same case here...
a)The "submit" button was defined as a "submit" button...what that wierd statement means is that it was something like
<INPUT Type = "submit" Value = "Save Data" Name = "Save" onClick = "return ValidateSave();">
b)we also had a <FORM Method="Post" Action = "SaveData.asp"> in the asp page
c)And then -- The "submit" button had a corresponding javascript validation code which would validate the data entry page and then submit the form to the next page where the insertion is made...something like
function ValidateSave()
{
// all validations here
document.frm.action = "SaveData.asp";
document.frm.submit();
}
as a result when the button was clicked it called the SaveData.asp page twice(once by virtue of the Javascript and then again by virtue of the INPUT Type = "submit")and the data was being inserted twice...we had a lot of fun debugging this one and it is now in our official "best bugs" list... 🙂
April 29, 2003 at 7:20 am
I am having a similar problem. My double submit is not happening every time though. I can't seen to track a pattern to when/why it happens either. Is yours happening every time?
April 29, 2003 at 7:31 am
Thanks for your response folks. As far as the form submission is concerned, there isn't one to worry about with this one so we can rule out a validation or re-submission error. With regards to your suggestion Andy, I've placed a response.end statement directly following the call to the stored proc, ensuring that it is only sent once to SQL and the result is still a double insert. I've got to say that I'm completely baffled. I'm not using any triggers that would execute the statement more than once and the traces I've run simply show that the entire statement is being executed twice within SQL for some reason. Really starting to pull my hair out... but in a calm, collected go-figure sort of way. 🙂 Thanks for all your help,
M.
April 29, 2003 at 12:18 pm
I think some testing should (hopefully) help in figuring out what is happening...
a)create another table with the same structure as the table in which the double inserts are occuring and execute the same code on this new table and check if the double inserts are happening here too
b)get the code out from the stored procedure and embed it within the ASP page itself
c)a response.write to re-check out how many times the ADO command is being executed
April 29, 2003 at 3:23 pm
Well, this is certainly a pickle. First, thank you to everyone for your time. Much to my embarassment it seems to be an ASP issue after all. Being that the entire framework is for business logic abstraction, there's quite a few reference tosses of the ADO Connection object bouncing around and it ultimately lands on a single execute method and thus a single ConnectionObject.execute call. The weird thing is, as I am certain that the method in question is being called once and only once, it seems that there is a copy of the object floating in memory somewhere and is executing in bi-lateral fashion. For the purposes of explanation I'll throw some scenarios below, but I realize this is a SQL forum and so please don't feel obliged to pursue or comment on it:
(in a function called execute:)
Double-Insert:
set execute = ConnectionObject.execute("myStoredProc")
Single-Insert (even though this function is called once during a single HTTP request):
set execute = ConnectionObject.execute("myStoredProc")
response.end
Single-Insert (weird):
set execute = ConnectionObject.execute("myStoredProc")
set execute = ConnectionObject.execute("select * from table1")
The latter situation is particularly bizarre. All in all it seems that when the execute method on the connection object is called once, it is executing twice. This is why I assume that some object copy or reference is getting jumbled and executed in tandem. Not even sure how to continue debugging and using a response.end or immediate second query as shown above isn't an option. If I do find a solution I'll post it in the case that any of you are wondering.
Thanks much again!
M.
April 29, 2003 at 7:31 pm
Not sure how that could happen. If you had two different objects, calling a method on one would not execute it on the other (unless you have shared methods, only in .Net or other languages, not VB/VBS)(well, other than global objects, rarely used). If you have two different variables pointing to the same object - pretty common when you do set x = y, it's only one object and would execute only once.
I still recommend looking at your error handler. Very easy to have an error later on bounce you back so that a line happens twice. If you still can't find it, try moving the code into a compiled DLL so you get better debugging/trapping.
Andy
May 1, 2003 at 8:57 am
Update: Solved!
Just an update to all of those that helped me out. Once again, thank you much for your time. I really appreciate it.
Found this post on a site detailing tips for improving performance of SQL Server with Visual Basic:
Avoid using the MoveFirst method of the RecordSet object when using a Forward-Only cursor. In effect, when you use this method, it re-executes the entire query and repopulates the Forward-Only cursor, increasing server overhead. [6.5, 7.0, 2000]
My framework had a method in it that was traversing through the recordset and resetting the cursor using the MoveFirst method. The cursor was Forward-Only and reworking this bit of the code in accordance with what was above solved the issue. The MoveFirst call was re-executing the query without, of course, re-executing on the method in which the query originated.
Thanks again folks!
M.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply