passing empty string to stored procedure -SQL Express 2005

31 Mar 2006 10:13 AM

Lisa Tanenbaum

I am taking data from a form and passing it to a stored procedure to insert
into a table. If there is nothing entered in the field I receive the
following error message:
"Parameter object is improperly defined. Inconsistent or incomplete
information was provided"

The stored procedure is:

ALTER PROCEDURE [dbo].[AddNewContract]
    @strContractorName nVARCHAR(50),
    @strOrderNumber nVARCHAR(50) = null,
    @strWorkLocation ntext = null,
    @Report datetime = null,
    @NewContractID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
WorkLocation, SubmitDate,)
        SELECT @strContractorName, @strOrderNumber, @strWorkLocation,
@dtReport;
    SELECT @NewContractID = SCOPE_IDENTITY();
END

The VB code is:

With MyCmd
    .ActiveConnection = conn
    .CommandText = "dbo.AddNewContract"
    .CommandType = adCmdStoredProc

    .Parameters.Append .CreateParameter("@ContractorName", adVarChar,
adParamInput, Len(strContractorName), strContractorName)
    .Parameters.Append .CreateParameter("@OrderNumber", adVarChar,
adParamInput, Len(strOrderNumber), strOrderNumber)
    .Parameters.Append .CreateParameter("@WorkLocation", adLongVarChar,
adParamInput, Len(strWorkLocation), strWorkLocation)
    .Parameters.Append .CreateParameter("@dtReport", adDate, adParamInput,
Len(dtReport), dtReport)
    .Parameters.Append .CreateParameter("@NewContractID", adInteger,
adParamOutput)
End With

MyCmd.Execute
lContractID = MyCmd.Parameters("@NewContractID").Value()

I don't know if nothing is passed to the stored procedure if there is no
data in the field or a null is passed.

I though I could just set the default to null in the stored procedure, but
the above error message is displayed. I'd appreciate it if you could let me
know how I pass the empty/null string?

31 Mar 2006 5:31 PM

Jim Underwood

Just some ideas, not sure if they will solve the problem or not...

I think that setting your parameter defaults to null is redundant, as the
default only gets used if a null parameter is passed to begin with. To pass
a null rather than an empty string, set the parameter to VBNull.Value (at
least that is what you use in VB.Net). You can pass an empty string for
varchar parameters, but not for date or numberic parameters.

Also, you could run into issues with your NTEXT value if it is very long
(over 4000 characters?).

Before you make any changes, however, step through your VB code and verify
that your variables are populated with the data you expect. If you can't
step through the code, at least print out the values of your variables.
Once you have confirmed what the values are that you are passing, you will
have a better idea of what is going wrong.

Show quoteHide quote

"Lisa Tanenbaum" <LisaTanenb***@discussions.microsoft.com> wrote in message
news:70464E51-9AB4-4DD8-873A-DC6427188B8E@microsoft.com...
> I am taking data from a form and passing it to a stored procedure to
insert
> into a table. If there is nothing entered in the field I receive the
> following error message:
> "Parameter object is improperly defined. Inconsistent or incomplete
> information was provided"
>
> The stored procedure is:
>
> ALTER PROCEDURE [dbo].[AddNewContract]
>     @strContractorName nVARCHAR(50),
>     @strOrderNumber nVARCHAR(50) = null,
>     @strWorkLocation ntext = null,
>     @Report datetime = null,
>     @NewContractID INT OUTPUT
> AS
> BEGIN
>     SET NOCOUNT ON;
>     INSERT dbo.tblAcceptContract (ContractorName, OrderNumber,
> WorkLocation, SubmitDate,)
>         SELECT @strContractorName, @strOrderNumber, @strWorkLocation,
> @dtReport;
>     SELECT @NewContractID = SCOPE_IDENTITY();
> END
>
> The VB code is:
>
> With MyCmd
>     .ActiveConnection = conn
>     .CommandText = "dbo.AddNewContract"
>     .CommandType = adCmdStoredProc
>
>     .Parameters.Append .CreateParameter("@ContractorName", adVarChar,
> adParamInput, Len(strContractorName), strContractorName)
>     .Parameters.Append .CreateParameter("@OrderNumber", adVarChar,
> adParamInput, Len(strOrderNumber), strOrderNumber)
>     .Parameters.Append .CreateParameter("@WorkLocation", adLongVarChar,
> adParamInput, Len(strWorkLocation), strWorkLocation)
>     .Parameters.Append .CreateParameter("@dtReport", adDate, adParamInput,
> Len(dtReport), dtReport)
>     .Parameters.Append .CreateParameter("@NewContractID", adInteger,
> adParamOutput)
> End With
>
> MyCmd.Execute
> lContractID = MyCmd.Parameters("@NewContractID").Value()
>
> I don't know if nothing is passed to the stored procedure if there is no
> data in the field or a null is passed.
>
> I though I could just set the default to null in the stored procedure, but
> the above error message is displayed. I'd appreciate it if you could let
me
> know how I pass the empty/null string?

1 Apr 2006 9:50 AM

ML

> I think that setting your parameter defaults to null is redundant, as the
> default only gets used if a null parameter is passed to begin with.

Not quite true. Defaults get used when parameters are not set at all by the
caller, or if the caller specifies DEFAULT as the parameter value.

Lisa,
Validate user data before calling a procedure, and only append parameters
(to the command object) that have no defaults - i.e. make sure the user
enters all necessary values or create some defaults in the application.

The error message you see is not a SQL Server error.

ML

---
http://milambda.blogspot.com/

5 Apr 2006 8:29 AM

Lisa Tanenbaum

Jim, ML
Thank you for your feedback, unfortunately I haven't been able to try it out
as I had the insane idea to upgrade my SQL Express to SP1. The upgrade failed
and I now cannot uninstall or reinstall, so I'm only left with the option of
setting up another PC. Thankfully I did backup my database before I started
the upgrade!!! (Thanks Microsoft for giving me such challenges.)
I'll let you know how I got on when I'm able to resume.

Show quoteHide quote

"ML" wrote:

> > I think that setting your parameter defaults to null is redundant, as the
> > default only gets used if a null parameter is passed to begin with.
>
> Not quite true. Defaults get used when parameters are not set at all by the
> caller, or if the caller specifies DEFAULT as the parameter value.
>
> Lisa,
> Validate user data before calling a procedure, and only append parameters
> (to the command object) that have no defaults - i.e. make sure the user
> enters all necessary values or create some defaults in the application.
>
> The error message you see is not a SQL Server error.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

19 Apr 2006 11:08 PM

david.erwin

"I think that setting your parameter defaults to null is redundant, as
the
default only gets used if a null parameter is passed to begin with. To
pass
a null rather than an empty string, set the parameter to VBNull.Value
(at
least that is what you use in VB.Net). You can pass an empty string
for
varchar parameters, but not for date or numberic parameters. "

I just wanted to quickly mention that the parameter is DBNull.Value,
not VBNull.Value.

Thread options