Error: Cast from type 'DBNull' to type 'Integer' is not valid.

[Sakana]

I don’t understand where possibly goes wrong. I can insert the members but I want it to redirect the members to another page to show their particulars which they have entered just now. The problem lies here: CustID = objCmd.Parameters("OUTPUT").Value

I’m creating a store procedure to insert customer. The coding in my asp.net goes something like this:

objParam = objCmd.Parameters.Add("OUTPUT", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output

objCmd.ExecuteNonQuery()

Dim retval As Integer
Dim CustID As Integer

retval = objCmd.Parameters("RETURN_VALUE").Value

If retval = -200 Then
lblError.Text = "User Name already exists!"
Else
'lblError.Text = "Record successfully added."
CustID = objCmd.Parameters("OUTPUT").Value
Session("Search") = CustID
Response.Redirect("Admin_Member.aspx")
End If

And this are the sql codes :

ALTER PROC spAddMember
(@CustName varchar(50), @CustNricNo char(9), @UserName varchar(20), @MemEmail varchar(50),
@MemPwd varchar(20), @MemDOB smalldatetime, @MemGender varchar(7), @MemJoinDate smalldatetime,
@CustID int OUTPUT)
AS

INSERT INTO CUSTOMER (CustName, CustNricNo) Values(@CustName, @CustNricNo)
INSERT INTO Member (CustID, UserName, MemEmail, MemPwd, MemDOB, MemGender, MemJoinDate, MemRewardPt)
Values(@@IDENTITY, @UserName, @MemEmail, @MemPwd, @MemDOB, @MemGender, @MemJoinDate, 1)

Select @@IDENTITY AS CustID

-- @@IDENTITY returns the last inserted identity value
SET @CustID = @@IDENTITY


RETURN

[Fishcake]

As far as i know functions such as @@rowcount, @@identity only apply to the Command directly before them. So in your case after this line is executed

Select @@IDENTITY AS CustID

the value of @@identity would then be null so then when reach this line

SET @CustID = @@IDENTITY

@@identity no longer has a value.

I would suggest simply deleting/commenting the line "Select @@IDENTITY AS CustID" as it doesn't appear to do anything anyway.

[pvb]

@@identity represents the most recently generated identity number in any table. It does not lose it's value after just being referenced, only another insert into any table with an identity column will affect the value(and truncate table will reset it). So long as the Member table does not have an identity you should be good to go. Maybe change the body of your proc to something like this to be sure:

INSERT INTO CUSTOMER (CustName, CustNricNo) Values(@CustName, @CustNricNo)
SET @CustID = @@IDENTITY
INSERT INTO Member (CustID, UserName, MemEmail, MemPwd, MemDOB, MemGender, MemJoinDate, MemRewardPt) 
VALUES ( @CustID, @UserName, @MemEmail, @MemPwd, @MemDOB, @MemGender, @MemJoinDate, 1) 

You don't need that select that's in there. And you don't really need that Return in there either unless you're gonna return a value and check it in your code. And make sure this line:

objParam = objCmd.Parameters.Add("OUTPUT", SqlDbType.Int)

is changed to refer to the output param in your proc:

objParam = objCmd.Parameters.Add("@CustID", SqlDbType.Int)