Return value parameter error

  • Thread starter Thread starter David C
  • Start date Start date
D

David C

I am getting the following error after inserting a record in a SqlDataSource
and cannot figure out why.

Object cannot be cast from DBNull to other types.

I have below at the end of my stored procedure

RETURN SCOPE_IDENTITY();

In my SqlDataSource I have the Insert Command below

InsertCommand="EXEC mc_insBudgets @PersonID, @EnteredBy,
@ApprovedDate, @CreatedDate, @FirstName, @LastName, @MiddleName,
@MedicaidID, @Birthdate, @PartTypeID, @AnnualAllocation,
@BudgetEffectiveDate, @BudgetEndingDate, @Address1, @Address2, @City,
@State, @ZipCode, @Gender, @SSN, @Phone, @Email"
InsertCommandType="StoredProcedure">

In my InsertParameters I have below.

<InsertParameters>
<asp:Parameter Name="PersonID" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="EnteredBy" Type="String" />
<asp:Parameter Name="ApprovedDate" DbType="Date" />
<asp:Parameter Name="CreatedDate" DbType="Date" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="MiddleName" Type="String" />
<asp:Parameter Name="Address1" Type="String" />
<asp:Parameter Name="Address2" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="State" Type="String" />
<asp:Parameter Name="ZipCode" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="SSN" Type="Int32" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="MedicaidID" Type="String" />
<asp:Parameter Name="Birthdate" DbType="Date" />
<asp:Parameter Name="PartTypeID" Type="Int32" />
<asp:Parameter Name="AnnualAllocation" Type="Decimal" />
<asp:Parameter Name="BudgetEffectiveDate" DbType="Date" />
<asp:Parameter Name="BudgetEndingDate" DbType="Date" />
<asp:Parameter Name="NewBudgetID" Type="Int32"
Direction="ReturnValue" />
</InsertParameters>

In my SqlDataSource ItemInserted event I have the following.

Protected Sub SqlBudgets_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
SqlBudgets.Inserted
Dim newBudgetID As Integer =
Convert.ToInt32(e.Command.Parameters("@NewBudgetID").Value)
txtBudgetID.Text = newBudgetID.ToString
.....etc
End Sub

The error is occurring in the DIM line because apparently the parameter
value is null and I don't know why.
Any help is appreciated.

David
 
Have you tried getting any other parameters to see if they give you the same
problem? Have you tried using an ordinal instead of the parameter name?

Also, have you tried getting the data directly through the SQLDataSource
InstertCommand parameters collection instead of the e argument to the event
handler?

This will help narrow down the problem to just that parameter, or a problem
in general.

-Scott
 
* David C wrote, On 16-9-2009 21:43:
I am getting the following error after inserting a record in a SqlDataSource
and cannot figure out why.

Object cannot be cast from DBNull to other types.

I have below at the end of my stored procedure

RETURN SCOPE_IDENTITY();

In my SqlDataSource I have the Insert Command below

InsertCommand="EXEC mc_insBudgets @PersonID, @EnteredBy,
@ApprovedDate, @CreatedDate, @FirstName, @LastName, @MiddleName,
@MedicaidID, @Birthdate, @PartTypeID, @AnnualAllocation,
@BudgetEffectiveDate, @BudgetEndingDate, @Address1, @Address2, @City,
@State, @ZipCode, @Gender, @SSN, @Phone, @Email"
InsertCommandType="StoredProcedure">

In my InsertParameters I have below.

<InsertParameters>
<asp:Parameter Name="PersonID" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="EnteredBy" Type="String" />
<asp:Parameter Name="ApprovedDate" DbType="Date" />
<asp:Parameter Name="CreatedDate" DbType="Date" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="MiddleName" Type="String" />
<asp:Parameter Name="Address1" Type="String" />
<asp:Parameter Name="Address2" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="State" Type="String" />
<asp:Parameter Name="ZipCode" Type="String" />
<asp:Parameter Name="Gender" Type="String" />
<asp:Parameter Name="SSN" Type="Int32" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="MedicaidID" Type="String" />
<asp:Parameter Name="Birthdate" DbType="Date" />
<asp:Parameter Name="PartTypeID" Type="Int32" />
<asp:Parameter Name="AnnualAllocation" Type="Decimal" />
<asp:Parameter Name="BudgetEffectiveDate" DbType="Date" />
<asp:Parameter Name="BudgetEndingDate" DbType="Date" />
<asp:Parameter Name="NewBudgetID" Type="Int32"
Direction="ReturnValue" />
</InsertParameters>

In my SqlDataSource ItemInserted event I have the following.

Protected Sub SqlBudgets_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
SqlBudgets.Inserted
Dim newBudgetID As Integer =
Convert.ToInt32(e.Command.Parameters("@NewBudgetID").Value)
txtBudgetID.Text = newBudgetID.ToString
.....etc
End Sub

The error is occurring in the DIM line because apparently the parameter
value is null and I don't know why.
Any help is appreciated.
try

InsertCommand="mc_insBudgets"
InsertCommandType="StoredProcedure">
Click to expand...

and keep the rest of your code the same.
 
I tried to follow the 4 Guys article 050207-1 but when I looked at it again,
the article showed the InsertParameters as ControlParameter and not plain
Parameter entries. Should that make a difference? Also, this is a ListView
so I am using the workaround for 2 DropDownList controls by populating them
in the Inserting event. Thanks.

David
 
But what about the questions I asked (since I don't know what the 4 Guys
article says)?

-Scott
 
I tried the Ordinal position instead of the actual parameter name and it
gave me the same error. Then I tried returning a different ordinal position
of the same data type and it gave me the error below.

Procedure or function mc_insBudgets has too many arguments specified

Then I checked the parameter count and it is the exact same as the stored
procedure.

David
 
Well, there's got to be something wrong with the insert command, which is
causing the results to be null.

If you look at the SQLDataSource's properties in the Property window, you
can bring up the query designer by clicking on the InsertQuery property and
manually execute the query with dummy data to see what's happening.

-Scott
 
David said:
I am getting the following error after inserting a record in a
SqlDataSource and cannot figure out why.

Object cannot be cast from DBNull to other types.

I have below at the end of my stored procedure

RETURN SCOPE_IDENTITY();

In my SqlDataSource I have the Insert Command below

InsertCommand="EXEC mc_insBudgets @PersonID, @EnteredBy,
@ApprovedDate, @CreatedDate, @FirstName, @LastName, @MiddleName,
@MedicaidID, @Birthdate, @PartTypeID, @AnnualAllocation,
@BudgetEffectiveDate, @BudgetEndingDate, @Address1, @Address2, @City,
@State, @ZipCode, @Gender, @SSN, @Phone, @Email"
InsertCommandType="StoredProcedure">
Click to expand...

I see 22 parameters there, but 23 parameters listed in your
InsertParameters.

What happens if you include the NewBudgetID parameter in the InsertCommand?

Andrew
 
Andrew Morton said:
I see 22 parameters there, but 23 parameters listed in your
InsertParameters.

What happens if you include the NewBudgetID parameter in the
InsertCommand?

Andrew
Click to expand...

It fails on too many parameters message.

David
 
David said:
It fails on too many parameters message.
Click to expand...

Oh. If you iterate over all the keys in e.Command.Parameters after the call,
does it include NewBudgetID? Doesn't the SP need NewBudgetID declared as an
OUTPUT parameter into which you select the value, in the style of

ALTER PROCEDURE [dbo].[getDaysSinceCleanup]
@DaysAgo INTEGER OUTPUT
AS
SELECT @DaysAgo=DATEDIFF(d, lastRunDate, getdate()) FROM lastCleanup;

So you'd use SELECT @NewBudgetID=SCOPE_IDENTITY();

I looked up "RETURN [integer]" in BOL and it's an unconditional exit from a
query with an [optional] exit code (and it cannot return a null value),
rather than SELECT, which is used to return values (don't blame me, I didn't
define SQL).

I could, of course, be way out.

Andrew
 
Could it be that you are inserting a unique Person Id (via the @PersonID)
attribute, when that field is automatically populated by the database, in
which case, you are passing 1 parameter too many?

You usually don't send an ID to the database on an insert (because the DB
manages unique id's for you). You usually only have to send an ID to the DB
when you are doing updates, filtered selects, or deletes.

-Scott
 
The PersonID is not the identity column and it can have multiples as it just
identifies the person for the budget.

David
 
Andrew Morton said:
David said:
It fails on too many parameters message.
Click to expand...

Oh. If you iterate over all the keys in e.Command.Parameters after the
call, does it include NewBudgetID? Doesn't the SP need NewBudgetID
declared as an OUTPUT parameter into which you select the value, in the
style of

ALTER PROCEDURE [dbo].[getDaysSinceCleanup]
@DaysAgo INTEGER OUTPUT
AS
SELECT @DaysAgo=DATEDIFF(d, lastRunDate, getdate()) FROM lastCleanup;

So you'd use SELECT @NewBudgetID=SCOPE_IDENTITY();

I looked up "RETURN [integer]" in BOL and it's an unconditional exit from
a query with an [optional] exit code (and it cannot return a null value),
rather than SELECT, which is used to return values (don't blame me, I
didn't define SQL).

I could, of course, be way out.

Andrew
Click to expand...

I may try the OUTPUT parameter because the stored proc is working when I
include all parameters in the InsertCommand line, e.g. InsertCommand="EXEC
mc_insBudgets @PersonID, ......" and InsertCommandType="Text"

David
 
Back
Top