Stored Procedure "failing"

Discussion in 'ASP General' started by Luis, Jan 5, 2004.

  1. Luis

    Luis Guest

    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
     
    Luis, Jan 5, 2004
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sarmad Aljazrawi

    New Stored Procedure Template in .Net

    Sarmad Aljazrawi, Dec 16, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    554
    Sarmad Aljazrawi
    Dec 16, 2003
  2. ES
    Replies:
    2
    Views:
    1,555
    jgy3183
    Aug 20, 2004
  3. Patrick
    Replies:
    2
    Views:
    5,990
    Gopinath S
    Feb 15, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,381
    Mike P
    Jun 19, 2006
  5. AlexWare
    Replies:
    2
    Views:
    789
    Paul Uiterlinden
    Oct 23, 2009
Loading...

Share This Page