I'm using a SQL Server 2000 stored procedure similar to the one below
to upload data to a database. This data is collected from the user on
a number of asp pages and stored in session variables (that's the way
I inherited the system...). When the user has captured all the info
and clicks the submit button the stored procedure is called and it
uploads the data to the database.
This normally works perfectly except for a few occassions when the
stored procedure has "failed" while loading the data onto the last
table (TestTable3) - the data in the session variables is not loaded
onto the database and the respective fields on the respective tables
are blank.
The most recent time it happened the user was using IE6.0 on Windows
XP.
Why is the stored procedure failing to complete?
The system uses code similar to the following to pass the information
that the user has captured to the stored procedure:
<!--#include file='dbConnect.inc'-->
<%
sqlstring = "EXEC dbo.InsertData @Test1 = '" & session("Test1") & "',
@Test2 = '" & session("Test2") & "', @Test3 = '" & session("Test3") &
"', " &_
"@Data1 = '" & session("TestData1") & "', @Data2 = '" &
session("TestData2") & "', @Data3 = '" & session("TestData3") & "', "
&_
"@SomeData1 = '" & session("Test1") & "', @SomeData2 = '" &
session("Test2") & "', @SomeData3 = '" & session("Test3") & "', "'"
conn.execute(sqlstring)
conn.close
Set conn = nothing
%>
The stored procedure is similar to:
CREATE PROCEDURE dbo.InsertData
@Test1 NVARCHAR(10),
@Test2 NVARCHAR(10),
@Test3 NVARCHAR(10),
@Data1 NVARCHAR(50),
@Data2 NVARCHAR(50),
@Data3 NVARCHAR(50),
@SomeData1 NVARCHAR(10),
@SomeData2 NVARCHAR(10),
@SomeData3 NVARCHAR(10),
AS
BEGIN
SET NOCOUNT ON
DECLARE @ID INT
INSERT TestTable1
(Field1,Field2,Field3)
VALUES
(@Test1,@Test12,@Test3)
SELECT @ID = SCOPE_IDENTITY()
SELECT ID = @ID
INSERT TestTable2
(ID,FieldAA,FieldBB,FieldCC)
VALUES
(@ID,@Data1,@Data2,@Data3)
INSERT TestTable3
(ID,FieldX1,FieldX2,FieldX3)
VALUES
(@ID,@SomeData1,@SomeData2,@SomeData3)
END
GO
