Hi
How do i assign an auto generated value in stored procedure?
In .NET what must we do to retrieve the auto generated value?
I have something like this in my stored procedure:
ALTER PROCEDURE spInsertRating(@RatingReviewID VARCHAR OUTPUT , @Title VARCHAR (50), @Rating INT ,
@Review VARCHAR ( MAX ), @ProductID VARCHAR (50), @Username VARCHAR (50))
AS
INSERT INTO ProductRatingAndReview(Title, Rating, Review, ProductID, Username)
VALUES (@Title, @Rating, @Review, @ProductID, @Username)
SET @RatingReviewID = SCOPE_IDENTITY ()
RETURN
In which RatingReviewID is the auto generated value.
Then in .NET, for RatingReviewID, i have this:
'Adds the specified parameter object to the SQL parameter collection
myParm = dbCmd.Parameters.Add( "@RatingReviewID" , SqlDbType.SmallInt)
myParm.Direction = ParameterDirection.Output
and when executing the stored procedure:
Try
dbConn.Open()
dbCmd.ExecuteNonQuery()
Dim RatingReviewID As String = dbCmd.Parameters( "@RatingReviewID" ).Value
MsgBox( "Opinion successfully added!" , MsgBoxStyle.Information, "Product Rating" )
dbConn.Close()
But, when i run the programming and try to key in values to add as a record into the database, it gives me the error:
Cannot insert the value NULL into column 'RatingReviewID', table 'C:\DOCUMENTS AND SETTINGS\TEACHER\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\MP WEB PORTAL\APP_DATA\DATABASE.MDF.dbo.ProductRatingAndReview'; column does not allow nulls. INSERT fails. The statement has been terminated.
How do i retrieve the auto generated value i created in the stored procedure in .NET? The way i do it above, is it correct? But, it doesn't seem to retrieve the value to be added into the database?
How should i modify my codings to do what i want? Help very much appreciated. I've been trying to figure this out for days, and i've tried to look for resource on the internet. I've tried them all, but it just doesn't seem to work. =(.
Btw, i'm using Visual Studio Web Developer Express Edition 2005 and i'm using VB.
Thanks! |