如何使用ADO和VB将NULL或空字符串传递给存储过程输入参数? [英] How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?
问题描述
我在Sql Server 2005中有一个存储过程,其varchar输入参数定义为:
I have a stored procedure in Sql Server 2005 with a varchar input parameter defined as:
@Value varchar(24) = NULL
在我的VB6应用程序中,我尝试使用ADO函数设置参数:
in my VB6 application I try to set the parameter with an ADO function:
Set prmParamVal = cmdChkParam.CreateParameter(, adVarChar, adParamInput, Len(paramValue), paramValue)
如果我尝试传递的值是一个空(零长度)字符串,则出现以下错误:
If the value I try to pass is an empty (zero length) string, then I get the following error:
ADODB。连接错误'800a0e7c'
参数对象定义不正确。提供的信息不一致或不完整。
ADODB.Connection error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided.
我试图传递NULL值而不是空字符串,但这会导致不同的错误。
I tried to pass a NULL value instead of the empty string, but this lead to different errors.
如何将空字符串或NULL值传递给存储过程?
How can I pass empty strings or NULL values to the stored procedure?
我已经读了很多文章和搜索的论坛(甚至多次找到我的问题),但没有正确的答案。
I already read a lot of articles and searched forums (even found my question several times) but without the right answer.
到目前为止,对于空字符串的解决方法是将length设置为1或set字符串=(空格)。但这并不是很好,我希望发送NULL。我还尝试将paramValue设置为vbNull,Null,vbNullString或将prmParamVal.value =空设置为没有运气!
The workaround so far for the empty strings is to set the length = 1 or set the string = " " (a blank space). But thats not really nice and I would favour to send NULL. I also experimented with setting paramValue to vbNull, Null, vbNullString or to set prmParamVal.value = Empty without any luck!
推荐答案
A这里的快速测试表明NULL应该可以完成工作。我曾经测试的示例代码(到一个带有一个按钮和一个文本框的简单表单中):
A quick test here shows that's NULL ought to do the job. Sample code I used to test (onto a simple form with one button and one textbox):
Private Sub Command1_Click()
Dim dbConn As ADODB.Connection
Dim dbComm As ADODB.Command
Dim dbRS As ADODB.Recordset
Set dbConn = New ADODB.Connection
With dbConn
.ConnectionString = "...REPLACE THIS ACCORDINGLY..."
.ConnectionTimeout = 10
.Open
End With
Set dbComm = New ADODB.Command
With dbComm
.ActiveConnection = dbConn
.CommandType = adCmdStoredProc
.CommandText = "usp_Bob"
.Parameters.Append .CreateParameter("b", adVarChar, adParamInput, 10, Null)
Set dbRS = .Execute
End With
Text1.Text = dbRS.Fields.Item(0).Value
dbRS.Close
dbConn.Close
End Sub
它调用了此存储过程:
ALTER PROCEDURE usp_Bob
@b VARCHAR(10)
AS
IF @b IS NULL
SELECT 'NULL' AS '1'
ELSE
IF @b = ''
SELECT 'EMPTY' AS '1'
ELSE
SELECT 'NOT NULL AND NOT EMPTY' AS '1'
usp_Bob由于使用VB值 Null
(根据上面的示例)而返回'NULL',而'NOT' vbNull
为NULL。如果 Null
对您不起作用,那么我无法评论可能是什么问题...!
usp_Bob returned 'NULL' for using the VB value Null
(as per the sample above), and 'NOT NULL' for vbNull
. If Null
doesn't work for you, then I can't comment on what might be wrong...!
类似地,应该像这样传递空字符串-一个空字符串,即 str =
-这会使usp_Bob返回'EMPTY'。
Similarly, empty strings should be passed just as that -- an empty string, i.e. str = ""
-- which makes usp_Bob return 'EMPTY'. Anything else has it return 'NOT NULL AND NOT EMPTY' (as expected).
如果您无法通过NULL,那么另一种选择是将其强制转换为空。在程序中将字符串转换为NULL-即
If you can't get NULL passed through, then another option is to cast an empty string to NULL in the sproc -- i.e.,
IF @param = ''
SET @param = NULL
请注意,经过的长度并不太重要。这反映了SQL Server中定义的参数的最大长度,而不是您正在传递的数据的长度。
Note that the length you pass through shouldn't matter too much. It's a reflection of the maximum length of the parameter as defined in SQL Server rather than the length of the data you're passing through.
这篇关于如何使用ADO和VB将NULL或空字符串传递给存储过程输入参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!