Communication Mixup

  • Thread starter Thread starter Glen Wilkin via DotNetMonster.com
  • Start date Start date
G

Glen Wilkin via DotNetMonster.com

I have a ASP.net applcation, written in C#, that is producing some very
strange behaviour.
Information is stored in a SQL Server 2000 database. The page that records
and stores information to the database functions correctly if only one user
is using the application. However, if there is more than one user the
system seems to get confused.
The stored procedure that saves the new items returns the ID of the new
item, via @@IDENTITY, this is then used by the page to populate itself from
the database. If two people are using the system the one who submits his
data second gets the first persons id back and his page populates with the
other persons data. The system functions correctly for the first person.
I simply do not understand how the @@IDENTITY value can be passed from one
SQL Server session to an ASP.net session that did not create it.
I've removed all session variables and record the @@IDENTITY value in a
hidden field on the page.
Anyone have any suggestions?

Thanks
 
This is the part of the stored procedure that creates the return value.
I've tried SCOPE_IEDNTITY() but it made no difference, there aren't any
triggers that could affect the @@IDENTITY value anyway.

INSERT INTO dbo.tblSite(Status)
VALUES(NULL)
SET @n_SiteID = @@IDENTITY

This is the C# code that saves a new site and gets the returned value

private void cmdSave_Click(object sender, System.EventArgs e)
{
// If a new site
if(this.ltxtHidden.Value.Equals("0"))
{
string lstrSiteIdentifier =
this.lcboNeighbourhoods.SelectedItem.Text + "/" +
this.lcboAreas.SelectedItem.Text + "/" +
this.ltxtID.Text;
if(this.thisIDUnique(lstrSiteIdentifier).Equals(true))
{
System.Data.SqlClient.SqlDataReader ldrReader;
ldrReader = mobjData.OpenDataReader("spSaveNewSite",
"@vch_SiteIdentifier", lstrSiteIdentifier);
// If save worked
if(null != ldrReader)
{
ldrReader.Read();
this.ltxtHidden.Value = ldrReader[0].ToString();
ldrReader.Close();
fillSiteDetails();
}
mobjData.CloseConnection();
}
}
 
I certainly don't see anything in the code that reveals any problems. Where
is it getting confused? i take it the INSERT statement is correctly
inserting a new ID for both users? When you're the 2nd user, does the
hidden field have the correct ID or the first persons?

I don't see how this will help, but you should try using ExecuteScalar() if
you are only retrieving a single field...and change your sproc to read
SELECT SCOPE_IDENTITY()

Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
Still getting the errors.
Thanks for your suggestions, I'll keep trying and let you know if I solve
it.
 
I'm an idiot, the code I showed earlier works fine, but the DataSet that
holds the information to display on the page was declared as static, so its
shared between all sessions. Apologies.
Any ideas how to declare a DataSet that can be populated on the first load
event of an aspx page and have its value maintained for subsecquent loads.
 
Is it better to use a Session variable or a set of keyed Application
variables?
 
Back
Top