asked on

Object cannot be cast from DBNULL to other types

I used this below in VB flavor and it worked perfectly.

                SqlCommand cmd = new SqlCommand();
                int taxPayerID = 0;
                cmd.CommandText = "usp_TaxPayer";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@taxPayerFullName", txtfname.Text);
                cmd.Parameters.AddWithValue("@address", txtfaddress.Text);
                cmd.Parameters.AddWithValue("@city", txtcity.Text);
                cmd.Parameters.AddWithValue("@state", ddlstates.SelectedValue);
                cmd.Parameters.AddWithValue("@zip", txtfzip.Text);
                cmd.Parameters.AddWithValue("@pid", accountnumber.Text);
                cmd.Parameters.AddWithValue("@phone", txtOwnerPhone.Text);
                cmd.Connection = conn;

                SqlParameter tID = cmd.Parameters.Add("@taxPayerID", SqlDbType.Int);
                tID.Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();

                taxPayerID += Convert.ToInt32(tID.Value);

Open in new window

I converted to C# and now, I am getting an error that this:

taxPayerID += Convert.ToInt32(tID.Value);

Open in new window

Object cannot be cast from DBNULL to other types

Any ideas how to resolve?

Basically, I have one stored procedure that inserts records into the database and grabs the last inserted ID:

SET @taxPayerID = SCOPE_IDENTITY()

Open in new window

Then I am using that in another stored procedure:

ALTER PROCEDURE [dbo].[sp_saveMarineInfo] 
 @pid       int,
 @eID       int,
 @txYr        smallint,
 @marineRegno       varchar(50),
 @vesselRegno       varchar(50),
 @txretval          Money,
 @vtxretval         Money,
 @tID               int
AS
BEGIN
  SET NOCOUNT ON;
  begin tran
  if exists (select * from MarineInfo where pid = @pid)
   begin
    UPDATE MarineInfo SET TaxYear = @txYr
      ,marineRegNo = @marineRegno
	  ,vesselRegNo = @vesselRegno
      ,TaxPyrRetdVal = @txretval
	  ,VesselTaxPyrRetdVal =@vtxretval
       where pid = @pid
	end
    else
    begin
    INSERT INTO [dbo].MarineInfo
           (pid
           ,TaxYear
           ,marineRegNo
		   ,vesselRegNo
		   ,EquipmentTypeID
           ,TaxPyrRetdVal
		   ,VesselTaxPyrRetdVal
		   ,TaxPayerID
           )
     VALUES
           (@pid
           ,@txYr
           ,@marineRegno
		   ,@vesselRegno
		   ,@eID
           ,@txretval
		   ,@vtxretval
		   ,@tID
           )

Open in new window

With a parameter of tID and I am trying to assign a value from that parameter to tID in another insert statement but I can't get there because of the error from the first INSERT statement.

Please let me know if I need to clarify.
Thank you

HainKurt

try

taxPayerID += (tID.Value==DBNull.Value)?0:Convert.ToInt32(tID.Value);

Open in new window

if (! DBNull.Value.Equals(tID.Value)) taxPayerID += Convert.ToInt32(tID.Value);

Open in new window

Pavel Celba

Is the NULL values correct? It should mean no records were inserted or a bug.
So first of all you should make sure the value is returned correctly from the SP. Output parameters do not work sometimes...

If taxPayerID allows NULL values and then you have to use int? data type as the int does not allow nulls.

Does the taxPayerID in [dbo].MarineInfo table allow null values? Does this column have foreign key defined?

HainKurt

what does your usp_TaxPayer code look like?
maybe you have problem here
maybe you dont insert any record, or maybe the table does not have an identity record...

so post the code for usp_TaxPayer + structure of the table involved here...

sammySeltzer

ASKER

Hi HainKurt,

Thanks again for your assistance.

Good news, bad news.

Good news is the error is gone.

Bad news is that it is insert 0 instead of value from the lastInserted ID from the Taxpayer table.

I probably don't have enough information for you but any ideas?

sammySeltzer

ASKER

pcelba,

No, the table does not allow nulls.

HainKurt, here is the SP.

Please note that the UPDATE portion of the SP is not updating TaxpayerID.

You can see that SET @taxPayerID = SCOPE_IDENTITY() is grabbing the last inserted record. I teste this in SSMS and it works.

ALTER PROCEDURE [dbo].[usp_TaxPayer] 
 @taxPayerFullName       varchar(75),
 @address     varchar(100),
 @city        varchar(50),
 @state       varchar(50),
 @zip         varchar(50),
 @pid              int,
 @phone       varchar(50),
 @taxPayerID  int = 0 OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  if exists (select * from TaxPayer where pid = @pid)
   begin
    UPDATE TaxPayer SET [TaxPayerFullName] = @taxPayerFullName
      ,[Address] = @address
      ,[City] = @city
      ,[State] = @state
      ,[Zip] = @zip 
	  ,[Phone] = (ISNULL(@phone, ''))
	  where pid = @pid
	end
  else
    begin
    INSERT INTO [dbo].[TaxPayer]
           ([TaxPayerFullName]
           ,[Address]
           ,[City]
           ,[State]
           ,[Zip]
		   ,[PID]
		   ,[Phone])
     VALUES
           (@taxPayerFullName
           ,@address
           ,@city
           ,@state
           ,@zip
		   ,@pid
		   ,(ISNULL(@phone, '')))
		     SET @taxPayerID = SCOPE_IDENTITY()
    end
END

Open in new window

HainKurt

add this on top

SET @taxPayerID = @pid;

Open in new window

so when there is update you get @PID

but actually you dont need to do this!
if it is 0, you know that it is an update! not insert!

HainKurt

or maybe not :)

put this on top

SET @taxPayerID = 0;

Open in new window

and you will get

0: update, check @PID parameter
any other number: Insert
null ? failed I guess...

* I see it is initialized with 0, so no need to add any code...

Kevin Cross

Not sure if this was already said but I do not believe the return value from cmd.Parameters.Add(...) is the SqlParameter object. If you want to keep a reference to it, it should be:

SqlParameter tID = new SqlParameter ("@taxPayerID", SqlDbType.Int);
tID.Direction = ParameterDirection.Output;
cmd.Parameters.Add(tID);

Open in new window

Ref: https://msdn.microsoft.com/en-us/library/h8f14f0z(v=vs.110).aspx

If someone already posted that, please forgive the duplication.

HainKurt

maybe you need the id that is updated in case if there is a record!

select @taxPayerID=taxPayerID  from TaxPayer where pid = @pid;
if (@taxPayerID<>0)
  update here
else
  insert here
  SET @taxPayerID = SCOPE_IDENTITY();
end;

Open in new window

not sure what column is the identity column, I used "taxPayerID"
if it is something, modify the code...
so it will return existing taxPayerID if @pid exists, otherwise it will insert and a new taxPayerID will be returned...

sammySeltzer

ASKER

A couple of clarifications good people.

PID is not the identity column. PID is a reference ID that this table must have and users can use it to reference their records.

But the identity column that I am trying to get is not the update statement.

HainKurt, you said I should set @TaxpayerID at top but at top of what the SP?

Why?
pcelba, I will try what you suggested although as stated at the beginning, I used similar code in VB and it worked great. It could be a C# issue?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

I wrote it already but you didn't test it yet so again:

Output parameters do not work in C# sometimes and you should test this possibility.

Update the SP to return just the number 5

SET @taxPayerID = 5
RETURN

and test whether this value was returned into the calling program.

sammySeltzer

ASKER

HainKurt,

Your solution is working for me.

Thank you very much