Return value parameter error

David C · Sep 16, 2009

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

arameter Name="PersonID" Type="Int32" DefaultValue="0" />
<asp

arameter Name="EnteredBy" Type="String" />
<asp

arameter Name="ApprovedDate" DbType="Date" />
<asp

arameter Name="CreatedDate" DbType="Date" />
<asp

arameter Name="FirstName" Type="String" />
<asp

arameter Name="LastName" Type="String" />
<asp

arameter Name="MiddleName" Type="String" />
<asp

arameter Name="Address1" Type="String" />
<asp

arameter Name="Address2" Type="String" />
<asp

arameter Name="City" Type="String" />
<asp

arameter Name="State" Type="String" />
<asp

arameter Name="ZipCode" Type="String" />
<asp

arameter Name="Gender" Type="String" />
<asp

arameter Name="SSN" Type="Int32" />
<asp

arameter Name="Phone" Type="String" />
<asp

arameter Name="Email" Type="String" />
<asp

arameter Name="MedicaidID" Type="String" />
<asp

arameter Name="Birthdate" DbType="Date" />
<asp

arameter Name="PartTypeID" Type="Int32" />
<asp

arameter Name="AnnualAllocation" Type="Decimal" />
<asp

arameter Name="BudgetEffectiveDate" DbType="Date" />
<asp

arameter Name="BudgetEndingDate" DbType="Date" />
<asp

arameter 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

Scott M. · Sep 16, 2009

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

Jesse Houwing · Sep 16, 2009

* 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>
<asparameter Name="PersonID" Type="Int32" DefaultValue="0" />
<asparameter Name="EnteredBy" Type="String" />
<asparameter Name="ApprovedDate" DbType="Date" />
<asparameter Name="CreatedDate" DbType="Date" />
<asparameter Name="FirstName" Type="String" />
<asparameter Name="LastName" Type="String" />
<asparameter Name="MiddleName" Type="String" />
<asparameter Name="Address1" Type="String" />
<asparameter Name="Address2" Type="String" />
<asparameter Name="City" Type="String" />
<asparameter Name="State" Type="String" />
<asparameter Name="ZipCode" Type="String" />
<asparameter Name="Gender" Type="String" />
<asparameter Name="SSN" Type="Int32" />
<asparameter Name="Phone" Type="String" />
<asparameter Name="Email" Type="String" />
<asparameter Name="MedicaidID" Type="String" />
<asparameter Name="Birthdate" DbType="Date" />
<asparameter Name="PartTypeID" Type="Int32" />
<asparameter Name="AnnualAllocation" Type="Decimal" />
<asparameter Name="BudgetEffectiveDate" DbType="Date" />
<asparameter Name="BudgetEndingDate" DbType="Date" />
<asparameter 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">

and keep the rest of your code the same.

David C · Sep 17, 2009

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

Scott M. · Sep 17, 2009

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

-Scott

David C · Sep 17, 2009

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

Scott M. · Sep 17, 2009

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

Andrew Morton · Sep 18, 2009

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">

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

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

Andrew

David C · Sep 18, 2009

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

It fails on too many parameters message.

David

Andrew Morton · Sep 18, 2009

David said:
It fails on too many parameters message.

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

Scott M. · Sep 18, 2009

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

David C · Sep 18, 2009

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

David

David C · Sep 18, 2009

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

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

ObjectDataSource not calling BLL	3	Apr 25, 2008
SqlDataSource UpdateCommand problem	1	Aug 24, 2007
Insert using sp error	2	Nov 1, 2009
formview update problem	0	Jan 19, 2006
DetailsView not updating	2	Jan 5, 2007
DetailsView Update command working, but it wipes out my record with null values	0	Apr 30, 2007
Inserting from DetailsView and associated Child Records	1	Dec 7, 2007
Insert Parameter using .DefaultValue	2	Apr 10, 2009

Return value parameter error

David C

Scott M.

Jesse Houwing

David C

Scott M.

David C

Scott M.

Andrew Morton

David C

Andrew Morton

Scott M.

David C

David C

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads