 |
BOOK: Beginning Microsoft SQL Server 2008 Programming ISBN: 978-0-470-25701-2 Discuss the book Beginning Microsoft SQL Server 2008 Programming, Robert Vieira, ISBN: 978-0-470-25701-2 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the BOOK: Beginning Microsoft SQL Server 2008 Programming ISBN: 978-0-470-25701-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

November 26th, 2010, 03:22 PM
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
|
|
@@Identity Chapter 11
I have beentrying to get this to work in a program I'm writing for a solid week with no success, I tried to follow the examplee in chapter 11 with no success. Any help would be reallly appreciated.
I must first state that I am an absolute novice with SQL Server. Having said that, here's my problem. My program is in Visual Basic 2008 Professional, the Database is SQL Server 2008 Express R2. The tables and stored procedures were all prepared using the SSMS.
My query in SQL Server is:
@Title varchar(50)= a,
@Authors varchar(50)= a,
@Series varchar(30)= a,
@Issued smallint= 0,
@Price decimal(6,2)=0.00,
@ISBN varchar(20)= a,
@Category varchar(50)= a,
@Condition varchar(15)= a,
@Type varchar(25)= a,
@Signed bit=NO,
@BookLoanStatus varchar(5)= a,
@Comments varchar(200)= a
INSERTINTO tblBooks( Title, Authors, Series, Issued, Price, ISBN, Category, Condition,Type, Signed, BookLoanStatus, Comments)
VALUES ( @Title, @Authors, @Series, @Issued, @Price, @ISBN, @Category, @Condition, @Type, @Signed,@BookLoanStatus, @Comments )
This works perfectly in Visual Basic,except that I need to capture the program generated ID number in a variable when the new record is created.
The code related to the @@IDENTITY looks relatively simple, but I can't get it to produce anything other then 0. Does this code go in the SQL statement or in the Visual Basic code? If in the SQL query the database ID field name is LoanID and I'm not sure how this fits into the process.
Suggestions would be greatly appreciated.
Milt
|

November 26th, 2010, 04:30 PM
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
|
|
When I added this line of code to Visual Basic:
oData.cmd.Parameters.AddWithValue("@LoanID", intBookLoanID)
I get the error "Cannot insert explicit value for identity column in table 'tblOnLoan' when IDENTITY_INSERT is set to OFF."
The book doesn't tell me how to set it to ON.
|

November 26th, 2010, 05:30 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Quote:
Originally Posted by sirmilt
When I added this line of code to Visual Basic:
oData.cmd.Parameters.AddWithValue("@LoanID", intBookLoanID)
I get the error "Cannot insert explicit value for identity column in table 'tblOnLoan' when IDENTITY_INSERT is set to OFF."
The book doesn't tell me how to set it to ON.
|
DO *NOT* DO THIS!!!!
Let SQL Server generate LoanID for you.
All you have to do is make a *SEPARATE* QUERY that simply does "SELECT @@IDENTITY" to get the value.
BUT! But you must do so on the SAME OPEN CONNECTION!! If you are closing the connection and then reopening it to get the @@IDENTITY, then you *will* always get 0.
You don't show your code, but I'm guessing that indeed the problem is that you are closing and reopening the connection.
|

November 26th, 2010, 07:15 PM
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
|
|
@@IDENTITY
Thank you, for the speedy reply.
Just to be sure that I understand what you are suggesting:
I am to remove the line of code "SET @LoanID =@@IDENTITY"
and the VALUES parameter value "@@IDENTITY" from the Stored Procedure
inSQL
Following which I will place the following in my Visual Basic code
SQL = âSELECT @@IDENTITY FROM tblOnLOanâ following the
Command.ExecuteNonQuery and then the Command.ExecuteNonQuery again.
Following that I willassign the value to a variable.
Hopefully I got the gist of what you are suggesting, please let me know.
Thanks again for the help
Milt
|

November 29th, 2010, 05:26 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Ummm....you didn't say you were using a Stored Procedure in your first post.
I took your code to mean you were just using an ad hoc query to do that INSERT.
If you are using a SP, you can do it either way: You can put the SELECT @@IDENTITY as the last thing in the SP and then you can use ExecuteScalar to get its value.
But yes, it works just as well to make the separate query. Do ExecuteNonQuery to perform the INSERT and then use ExecuteScalar to get the @@IDENTITY with a separate query.
But in either case, you want to use ExecuteScalar, *NOT* ExecuteNonQuery, to get that @@IDENTITY value! ExecuteNonQuery will simply *IGNORE* any SELECT you do in your query!
|
The Following User Says Thank You to Old Pedant For This Useful Post:
|
|

November 30th, 2010, 12:12 PM
|
Authorized User
|
|
Join Date: Aug 2005
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
|
|
@@IDENTITY
I Think that we are almost home, and do appreciate your help. Here's the code I added:
oData.SQL = "SELECT @@IDENTITY FROM tblOnLoan"
oData.OpenConnection()
oData.cmd.ExecuteScalar()
intLoanID = @@IDENTITY
When testing, the code seems to work fine up to the line "intLoanID = @@IDENTITY"
This throws the error "Expression Expected" with the first @ underlined in my code.
But, this is as close as I've come...thanks again.
Milt
|

November 30th, 2010, 02:26 PM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
@@IDENTITY *only* exists *INSIDE* of SQL Server. It does not exist the VB.NET or C# or even ADO.NET world.
You need to read the manual on how to use ExecuteScalar.
Briefly:
Code:
intLoanID = oData.cmd.ExecuteScalar()
Though that depends on just what your oData variable is and what the cmd property of that object is.
|
|
 |