David

David

  • NA
  • 1
  • 0

Database with ASP.NET Question (Transactions)

Dec 4 2005 9:24 PM
I am using the MSDE2000a database server to run a web calendar program. I am having a problem where I need information created by actually inserting a record into the first table I am altering. However, I am working inside a transaction (SqlTransaction object on ASP.NET 2.0 with a C# backend) and when I try to retrieve an ID number (the information that the DB auto generates) created by the first query I execute in the transaction, I get a nasty Exception about the data field being Null (I am using an SqlDataReader to read this information). I am not sure if it is how the transaction works (Data is only accessable once commited) or if it is just somthing crazy with the way I am doing it. I start by opening the connection and begining the transaction. Then I make a try catch block where I do all processing. There are 2 insert queries and 1 select query that will add a user record to one table, then the ID that is created by the first query is selected. After this, it is used in the 3rd query to an record to a second table. The point where the problem comes up is when I run the Select query and try to get data out of it. I change the CommandText for the connection I used for inserting and then ran the datareader. Once that was up, I tried to getInt32(0) and get the Exception about an SQL null datatype. The user record should exist as that is the first statement issued. Is there anything I have to do to make part of a transaction where the data is readable so I can get the required info while leaving it where I can roll back the entire transaction?