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

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.

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

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!

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

@@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:
Though that depends on just what your oData variable is and what the cmd property of that object is.