473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

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


Nov 18 '05 #1
2 2733
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" <jc******@hotmail.com> wrote in message news:ud**************@TK2MSFTNGP12.phx.gbl...
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


Nov 18 '05 #2
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" <jc******@hotmail.com> wrote in message news:ud**************@TK2MSFTNGP12.phx.gbl...
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

Nov 18 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Sells, Fred | last post by:
I have a legacy system with data stored in binary files on a remote server. I need to access and modify the content of those files from a webserver running on a different host. (All Linux) I...
0
by: Michael Bourgon | last post by:
I've been having the same problem for 2 weeks now. If anyone has any ideas, I'd love to hear them. We are using both SQL and Windows Authentication. I was running a Profiler Trace at the time,...
0
by: Roberto Ortolano | last post by:
Hello on our win2k adv server, I have enabled asp.net for a domain: http://www.testdomain.xx/test/demo everything works fine. Ee make available to the customer another URL which is:
2
by: Franko | last post by:
I get the following error. please help Server Error in '/WebApplication1' Application. -------------------------------------------------------------------------------- Compilation Error...
3
by: Franko | last post by:
I get the following error. Ihave no idea why? Pl help Server Error in '/WebApplication1' Application. -------------------------------------------------------------------------------- Compilation...
0
by: rooster575 | last post by:
I have a strange situation whereby I must un-install and re-install ASP.NET every time I reboot the server. When the server is rebooted and I try to access a web application it reads: "Server...
1
by: Nobody | last post by:
<!--#include file="CommonServer.aspx"--> <script language="VB" runat="server"> Sub Page_Load(Src As Object, E As EventArgs) CheckConnection() end sub </script> <HTML> <HEAD> <META...
1
by: dasein fiasco | last post by:
I'm sure this is a design question that is commonly encountered, but I can't find any resources which explain how to solve it. I would deeply appreciate any advice. Basically, when building a...
4
by: frog | last post by:
Hi, all: I tried to call Server.Execute in Application_Start and got exception. Any help is appreciated You might ask why I want to do that in the first place. Well, I need to start a...
0
by: Chris Davoli | last post by:
Test Server Environment is: Win server 2003, SP1, Installed both Framework 1.1 and Framework 2.0. I've got a 2005 web site (converted from VS2003). When I deploy to test server it gives me the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.