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

[.Net Newbie]

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]

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
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

 

[Guest]

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.