@@Identity returning Null
-
Hi:
I have an ASP.NET/SQLServer application. I INSERT to one table and then use @@Identity to grab the autogenerated primary key from the record. The INSERT is working but I get the following error, when I try to convert the returned value.
Object cannot be cast from DBNull to other types
My code is as follows:
SqlCommand cmd = new SqlCommand( "SELECT @@IDENTITY", Conn );
object iD = cmd.ExecuteScalar();
Session["TestID"] = iD;
int thisTestID = Convert.ToInt32(iD);
The application works when I use an Access database. I created the SQLServer database by upsizing from Access.
-
Hallo
Why don't you try the method in the following example
SqlCommand cmd = new SqlCommand( " Insert into Customer (FirstName) Values ('Bruce');select @@identity as 'iD'", Conn );
object iD = cmd.ExecuteScalar();
Session["TestID"] = iD;
int thisTestID = Convert.ToInt32(iD);
Regards
Joseph
-
Well, @@IDENTITY is not always safe to use
...Read the BOL. Instead you should use...Select Ident_Current('TableName')
...which will give you the last inserted ID on that specific table.
However, my suggestion is this. There is always a big margin for error when you have hundreds of people using the same table and inserted rows all the time. Your best bet is to take some time to Develop a Stored Procedure for this particular table with the "ID" Parameters as Output. It's awesome how .NET can map Parameters to DataFields in a DataTable. This will allow you to Receive the ID back into your DataTable from the Stored Proc.
However, doing this method, you'll need to turn off any Identity Columns on your in-memory DataTable before Saving, and then turn them back on later. Also, follow the suggestion that when Inserted new Rows in the DataTable, that your Identity Columns in Memory are incremented starting from "-1" and de-incrementing by one (read MS Help on this).
-
Joseph:
I tried that approach with the same results already. I realise now that @@Identity grabs the primary key in an Access table, but grabs the last identity in a SQLServer database.
I hadn't realised that there is a difference between an identity and a primary key. Too bad. It was so simple with Access.
When I examine my SQLServer table, my primary key is not an identity column. I can't even change it to one, because apparantly an identity cannot have a default value.
When I designed my table in Access, I made the primary key a random autonumber. So it seems that there is no straightforward way to retrieve it.
-
What kind of table is this? What is it used for or what data does it contain?
-
I wrote this application for my students to take my tests online. When they submit a test I insert their StudentID and DateTime into a table named 'Tests'. MS Access assigns a random autonumber in the primary key field 'TestID'.
I grab this TestID value and use it as a foreign key in a related table 'TestDetails' table which records the QuestionNum and StudentAnswer for each question.
The student is then sent to a confirmation page that displays the TestID as their confirmation number, and calculates their results.
The application has been working perfectly with MS Access, but I thought perhaps I should upsize to SQLServer.
Thanks
-Bruce
-
Since I hate Identity Columns (i.e. Autonumbers) I will suggest a new Key for your Tables... It sounds like you can get away with this. However, you can always go back and redesign your tables to use Autonumbers (Identity Columns) and then re-import your data into them.
The "Tests" table can have "StudentID" and "TestDate" as a Composite Key in which case you won't even need the "TestID" field. Or you can do concatenation in your Code if you feel you want these together into one PriKey Field (ex. TestID = StudentID & "-" & Format(TestDate, "mmddyyyy")).
If you choose to go the Composite Key route, the "TestDetails" should have the "StudentID" and "TestDate" and an additional column as the Key. This would also be a Composite Key. The additional column I would choose to either be the "QuestionNo" or something like that. Because if you think about it, the combination of StudentID, TestDate, and QuestionNo will ALWAYS be unique! Except if the student can take the same test more than once in a given day. I personally wouldn't allow that, but I might allow editing the same test??
If you didn't choose the Composite Key, then you could do two things on "TestDetails". One is to make a Composite Key on "TestID" and "QuestionNo". The other is to concatenate the "QuestionNo" into the "TestDetailID" (ex. TestDetailID = TestID & "-" & Format(QuestionNo, "0000")).
The disadvantage to Composite Keys is you have to do more work to determine the Records on any sub tables (i.e. TestDetails).
Select * From Tests T Inner Join TestDetails TD Where (T.StudentID = TD.StudentID) And (T.TestDate = TD.TestDate) Order By T.TestDate, QuestionNoWhere as with the concatenation, it's a lot more straight forward. I personally like the concatenation method for some things (ex. ProductCategories & Products: ProductID = ProdCat & Format(SeqNo, "0000")).
Select * From Tests T Inner Join TestDetails TD ON T.TestID = TD.TestID -
I guess whether I use the Composite key route or the concatenation method, I won't need to retrieve the primary key from my Tests table before inserting into my TestDetails table. I can grab it from my code.
I will try your suggestions.
Thanks for the help Tymberwyld
-
oh please stay with CODD' s rules !
http://qa.sqlservercentral.com/columnists/fkalis/coddsrules.asp
Basics of any RDBMS :
- know your relationships.
- use meaningless keys. It makes schema-knowlage mandatory ! and will avoid symantic errors !
- OLTP : store once (unless you can prove it hurts)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
alzdba
Is there a problem?
-
Don't store concatenated stuff. It's not as adaptable as you might think.
Composite keys, don't have that huge downside.
If you want to have your newly inserted key returned by sqlserver (i.e. right after you have inserted your row), you could use @@idendity or SCOPE_IDENTITY( ). Scope_identity is preferred. check BOL.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
"If you want to have your newly inserted key returned by sqlserver (i.e. right after you have inserted your row), you could use @@idendity ..."
But that was my problem in the first place. I used @@Identity in the first place and it returned Null. @@Identity doesn't return the primary key but returns the last changed identity value.
As tymberwyld suggested, I could use "StudentID" and "TestDate" as a Composite Key in my 'Tests' table. Then I wouldn't have to ask SQLServer to return it because I would know it.
But if you do want SQLServer to return a newly inserted Composite key, is there a way to do that? Is there a way to return a newly inserted primary key that is not an identity column?
-
Why would you want to? You'll already have that Key in your DataTable. Unless you've got a Student who is logged into two machines at the same time, and somehow manages to click "Submit" / "Grade" at the exact same time, you should not have any conflicts at all!
If you even remotely think that what I just mentioned is possible, then you should build something into the "Save" routine that would check for an Existing Record, if it finds it, tell the user they've already taken the test. This would eliminate that possibility.
My ultimate suggestion is to use Stored Procs. They are not that hard to learn and are amazing helpful fr storing your Business Logic on the Server, instead of in your Code. You might have something like "spSaveTest" with all the Parameters neccessary for a Test, then map those Parameters (using a SqlDataAdapter) to your DataTable. Voila! No complicated logic, your stored procs handle everything! Then if later you decide you need to change that logic, you only need to edit it at the Server Level and not have to recompile your code / ASP pages.
Whew!
-
This all seems a bit messy.
Could You please post the definition for Your table, with PK etc.
To me it does not make sense to have a default value on the TestID column, why?
/rockmoose
You must unlearn what You have learnt -
As you did find out, @@identity <> primary-key-info
afaik there is no way to have the "just inserted" primary key info returned, unless you write a query,... This however is not transaction-safe.
Like tymberwyld mentioned, you must already have the pk-data in your application.
Using a stored procedure seems the best way to solve your problem, if there is one.
Even the example tymberwyld mentioned, will be safe because only one of the transactions will complete. The other one will have a primary key violation and will not be accepted.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply