Help! SQLTransaction and Identity(?) column in SQL Server 2K

Discussion in 'ASP .Net' started by .Net Newbie, Jun 30, 2004.

  1. .Net Newbie

    .Net Newbie Guest

    Hello,

    I am somewhat new to .Net and currently working on an intranet site using C#
    going against SQL Server 2k. I am accepting personal information on a
    single webform and trying to insert the information into three separate
    tables (all in a single aspx page -- without using stored procedures, yet).

    The first SQL Statement accepts the persons most detailed information, like
    name, address, phone, etc and inserts the single record into the table.
    This main table contains an Identity column and an ntext column (so I am
    adding parameters to account for the ntext column).

    The next SQL Statement(s) are contained in a a series of if blocks that
    basically check some textboxes and dropdownlist for children's information.
    If a child's name is found in any of five textboxes, I build an SQL
    Statement and push a record into the appropriate table after I have picked
    up the Max(Identity column) from the main table.

    The last statement collects information from a series of checkboxes (product
    and services the person is interested in) and builds a comma list where I am
    building a single statement that does an Insert into table
    select..from..where field in (comma list). For this I am also picking up the
    Max(Identity column) from the main table.

    All of this works fine when I don't use a Transaction statment (except for
    when there's an error on one of the inserts of course), however when I try
    to use this scenerio within an SQLTransaction, I get a time out message and
    it does not appear to be closing my connection. People I've discussed this
    problem with seem to think it might have something to do with the Identity
    column not being commited prior to trying to read it in the next two
    sections of inserts, but so far I have been unable to find a solution with
    anything I've tried. It seems like this would be a fairly common scenerio
    to deal with but I haven't been able to find any examples any where so far.

    Does anyone have any suggestions as to how to address this problem, examples
    of similar scenerios, or a different approach I might take to accomplish the
    same goal?

    Any help would be greatly appreciated.

    Thanks,
    ..NetNewbie
     
    .Net Newbie, Jun 30, 2004
    #1
    1. Advertising

  2. .Net Newbie

    George Guest

    I am not sure what is the problem with time out.
    But here is couple points.

    1. Do not use MAX(Identity Column).
    Use @@IDENTITY or SCOPE_IDENTITY(). Since there is a time frame between you read the MAX and insert new record. And during that time frame some one could insert a new record.
    Also it's probably is the reason of time out since MAX probably locks the table for INSERT. (depends on your transaction level).

    2. You connection is not closed because you do not catch exception and connection is left open which is a big "no" in a Webdevelopmen enviroment.

    It must be something like that

    con.Open()
    try
    {
    .....run my SQL
    }
    finally
    {
    con.Close();
    }


    George
    My Site - Body Jewelry
    ".Net Newbie" <> wrote in message news:...
    Hello,

    I am somewhat new to .Net and currently working on an intranet site using C#
    going against SQL Server 2k. I am accepting personal information on a
    single webform and trying to insert the information into three separate
    tables (all in a single aspx page -- without using stored procedures, yet).

    The first SQL Statement accepts the persons most detailed information, like
    name, address, phone, etc and inserts the single record into the table.
    This main table contains an Identity column and an ntext column (so I am
    adding parameters to account for the ntext column).

    The next SQL Statement(s) are contained in a a series of if blocks that
    basically check some textboxes and dropdownlist for children's information.
    If a child's name is found in any of five textboxes, I build an SQL
    Statement and push a record into the appropriate table after I have picked
    up the Max(Identity column) from the main table.

    The last statement collects information from a series of checkboxes (product
    and services the person is interested in) and builds a comma list where I am
    building a single statement that does an Insert into table
    select..from..where field in (comma list). For this I am also picking up the
    Max(Identity column) from the main table.

    All of this works fine when I don't use a Transaction statment (except for
    when there's an error on one of the inserts of course), however when I try
    to use this scenerio within an SQLTransaction, I get a time out message and
    it does not appear to be closing my connection. People I've discussed this
    problem with seem to think it might have something to do with the Identity
    column not being commited prior to trying to read it in the next two
    sections of inserts, but so far I have been unable to find a solution with
    anything I've tried. It seems like this would be a fairly common scenerio
    to deal with but I haven't been able to find any examples any where so far.

    Does anyone have any suggestions as to how to address this problem, examples
    of similar scenerios, or a different approach I might take to accomplish the
    same goal?

    Any help would be greatly appreciated.

    Thanks,
    .NetNewbie
     
    George, Jun 30, 2004
    #2
    1. Advertising

  3. Deepankar Wrote:
    I agree with what george has to say regarding closing of database connections.
    One suggestion from my end is if you can move the code to a stored procedure it would simplify your task.

    > I am not sure what is the problem with time out.
    > But here is couple points.
    >
    > 1. Do not use MAX(Identity Column).
    > Use @@IDENTITY or SCOPE_IDENTITY(). Since there is a time frame between you read the MAX and insert new record. And during that time frame some one could insert a new record.
    > Also it's probably is the reason of time out since MAX probably locks the table for INSERT. (depends on your transaction level).
    >
    > 2. You connection is not closed because you do not catch exception and connection is left open which is a big "no" in a Webdevelopmen enviroment.
    >
    > It must be something like that
    >
    > con.Open()
    > try
    > {
    > .....run my SQL
    > }
    > finally
    > {
    > con.Close();
    > }
    >
    >
    > George
    > My Site - Body Jewelry
    > ".Net Newbie" <> wrote in message news:...
    > Hello,
    >
    > I am somewhat new to .Net and currently working on an intranet site using C#
    > going against SQL Server 2k. I am accepting personal information on a
    > single webform and trying to insert the information into three separate
    > tables (all in a single aspx page -- without using stored procedures, yet).
    >
    > The first SQL Statement accepts the persons most detailed information, like
    > name, address, phone, etc and inserts the single record into the table.
    > This main table contains an Identity column and an ntext column (so I am
    > adding parameters to account for the ntext column).
    >
    > The next SQL Statement(s) are contained in a a series of if blocks that
    > basically check some textboxes and dropdownlist for children's information.
    > If a child's name is found in any of five textboxes, I build an SQL
    > Statement and push a record into the appropriate table after I have picked
    > up the Max(Identity column) from the main table.
    >
    > The last statement collects information from a series of checkboxes (product
    > and services the person is interested in) and builds a comma list where I am
    > building a single statement that does an Insert into table
    > select..from..where field in (comma list). For this I am also picking up the
    > Max(Identity column) from the main table.
    >
    > All of this works fine when I don't use a Transaction statment (except for
    > when there's an error on one of the inserts of course), however when I try
    > to use this scenerio within an SQLTransaction, I get a time out message and
    > it does not appear to be closing my connection. People I've discussed this
    > problem with seem to think it might have something to do with the Identity
    > column not being commited prior to trying to read it in the next two
    > sections of inserts, but so far I have been unable to find a solution with
    > anything I've tried. It seems like this would be a fairly common scenerio
    > to deal with but I haven't been able to find any examples any where so far.
    >
    > Does anyone have any suggestions as to how to address this problem, examples
    > of similar scenerios, or a different approach I might take to accomplish the
    > same goal?
    >
    > Any help would be greatly appreciated.
    >
    > Thanks,
    > .NetNewbie
    >
    >
    >
    >
     
    =?Utf-8?B?RGVlcA==?=, Jul 9, 2004
    #3
    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. Piotr Strycharz

    SqlTransaction issue

    Piotr Strycharz, Dec 15, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    614
    Piotr Strycharz
    Dec 16, 2004
  2. Joe Rigley

    SqlTransaction Record Not Found

    Joe Rigley, Nov 11, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    381
    Joe Rigley
    Nov 11, 2005
  3. Frederick D'hont
    Replies:
    0
    Views:
    323
    Frederick D'hont
    Jul 25, 2005
  4. Replies:
    6
    Views:
    465
  5. Popezilla
    Replies:
    2
    Views:
    952
    Popezilla
    Mar 18, 2007
Loading...

Share This Page