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 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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,...
|
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:
|
by: Franko |
last post by:
I get the following error. please help
Server Error in '/WebApplication1' Application.
--------------------------------------------------------------------------------
Compilation Error...
|
by: Franko |
last post by:
I get the following error. Ihave no idea why? Pl help
Server Error in '/WebApplication1' Application.
--------------------------------------------------------------------------------
Compilation...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |