passing empty string to stored procedure -SQL Express 2005

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?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.
"Lisa Tanenbaum" <LisaTanenbaum@.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?|||> 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/|||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 faile
d
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.
"ML" wrote:

> Not quite true. Defaults get used when parameters are not set at all by th
e
> 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/|||"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.

Pass unicode to stored procedures

Hi,
I have an **urgent** need to pass chinese characters into an SQL Server
Express table using a stored procedure. The MSDN documentation informs me
that everything will work 'automatically' if I use NVARCHAR parameters but
all I am getting is question marks. I've found old threads where other
people have the same problem but the only answer available is 'it should
just work'. Hopefully someone here (PLEASE) has more idea why the following
would not work (the result for me is that every unicode character is
converted into a question mark instead of having the correct chinese
charater).
PS The same code works fine for Jet (Access).
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
SET @.iID = 1
SET @.sName = '''
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;
--
Result: 1 '
--
NOTE: Prefixing the variable name with an "N" does fix the problem:
SET @.sName = ''' // this inserts '
SET @.sName = N''' // this inserts '
However, this solution is not workable because eventually the parameter will
be set by an existing C# application that passes a string. BTW The
'automatic' detection of string variables should make this unecessary right?
This is an urgent problem for me. A fast response would be greatly
appreciated.
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.auIs it an omen that this newsgroup filtered unicode from my original post?
The following lines that contain question marks should actually have Chinese
characters:
SET @.sName = '''
SET @.sName = ''' // this inserts question marks
SET @.sName = N''' // this inserts '
Other instances of question marks in the original post are "as intended":
Result: 1 '
Chinese characters: '
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.au|||I've reworked the code in the original post slightly to better illustrate my
point (and because I'm desperate for help!). If you copy code below the
dotted line into studio manager and replace the 'QQQ' with Chinese
characters then the following results are obtained:
1 ? // This is failure
2 QQQ // This is success
---
DROP TABLE MyTable;
GO
DROP PROCEDURE MyTable_Insert;
GO
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS
INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
/* THIS INSERTS QUESTION MARKS */
SET @.iID = 1
SET @.sName = 'QQQ'
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
/* THIS INSERTS UNICODE */
SET @.iID = 2
SET @.sName = N'QQQ
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;|||> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
As Jesse mentioned, strings in C# are Unicode. You'll get the desired
behavior as long as you parameterize your SQL statements with the correct
datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hamilton Colyer-Long" <hamilton@.NOjanusysSPAM.com.au> wrote in message
news:uD3nDInAIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have an **urgent** need to pass chinese characters into an SQL Server
> Express table using a stored procedure. The MSDN documentation informs me
> that everything will work 'automatically' if I use NVARCHAR parameters but
> all I am getting is question marks. I've found old threads where other
> people have the same problem but the only answer available is 'it should
> just work'. Hopefully someone here (PLEASE) has more idea why the
> following would not work (the result for me is that every unicode
> character is converted into a question mark instead of having the correct
> chinese charater).
> PS The same code works fine for Jet (Access).
> CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
> GO
> CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
> AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
> GO
> DECLARE @.RC INT
> DECLARE @.iID INT
> DECLARE @.sName NVARCHAR(50)
> SET @.iID = 1
> SET @.sName = '''
> EXECUTE @.RC = MyTable_Insert @.iID, @.sName
> GO
> SELECT * FROM MyTable;
> --
> Result: 1 '
> --
> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
> This is an urgent problem for me. A fast response would be greatly
> appreciated.
> Regards,
> Hamilton Colyer-Long
> Janusys
> www.janusys.com.au
>