Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 26th, 2010, 03:22 PM
Authorized User
Points: 406, Level: 7
Points: 406, Level: 7 Points: 406, Level: 7 Points: 406, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2005
Location: Florida
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default @@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
Reply With Quote
  #2 (permalink)  
Old November 26th, 2010, 04:30 PM
Authorized User
Points: 406, Level: 7
Points: 406, Level: 7 Points: 406, Level: 7 Points: 406, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2005
Location: Florida
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old November 26th, 2010, 05:30 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Originally Posted by sirmilt View Post
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.
Reply With Quote
  #4 (permalink)  
Old November 26th, 2010, 07:15 PM
Authorized User
Points: 406, Level: 7
Points: 406, Level: 7 Points: 406, Level: 7 Points: 406, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2005
Location: Florida
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default @@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
Reply With Quote
  #5 (permalink)  
Old November 29th, 2010, 05:26 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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!
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
sirmilt (November 29th, 2010)
  #6 (permalink)  
Old November 30th, 2010, 12:12 PM
Authorized User
Points: 406, Level: 7
Points: 406, Level: 7 Points: 406, Level: 7 Points: 406, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2005
Location: Florida
Posts: 88
Thanks: 4
Thanked 0 Times in 0 Posts
Default @@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
Reply With Quote
  #7 (permalink)  
Old November 30th, 2010, 02:26 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

@@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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 13 - Cannot insert explicit value for identity column in table 'Picture' when hahoa BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 10 November 6th, 2009 01:29 PM
Chapter 4 - Membership and Identity cindeost BOOK: Beginning ASP.NET 2.0 and Databases 0 November 17th, 2008 10:55 PM
chapter 11 figure 11-7 relative positioning pelopito BOOK: Beginning CSS: Cascading Style Sheets for Web Design ISBN: 978-0-7645-7642-3 2 November 29th, 2007 06:11 AM
Chapter 11 zaidqais Visual Basic 2005 Basics 0 June 2nd, 2006 04:09 AM
Chapter 4: Membership and Identity. (Access 2003) shirley65 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 7 March 23rd, 2006 03:09 PM



All times are GMT -4. The time now is 04:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.