Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Help! SQLTransaction and Identity(?) column in SQL Server 2K

Reply
Thread Tools

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

 
 
.Net Newbie
Guest
Posts: n/a
 
      06-30-2004
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




 
Reply With Quote
 
 
 
 
George
Guest
Posts: n/a
 
      06-30-2004
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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




 
Reply With Quote
 
 
 
 
=?Utf-8?B?RGVlcA==?=
Guest
Posts: n/a
 
      07-09-2004
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on a SINGLE 2005 database sloan ASP .Net 2 08-26-2008 09:15 PM
SQLTransaction IsolationLevel questions eric.goforth@gmail.com ASP .Net 0 06-07-2006 04:21 PM
SqlTransaction problem Neven Klofutar ASP .Net 3 11-26-2005 01:49 PM
SqlTransaction Record Not Found Joe Rigley ASP .Net 0 11-11-2005 06:31 PM
SqlTransaction issue Piotr Strycharz ASP .Net 5 12-16-2004 08:28 AM



Advertisments