SQL 如何使用 ADO 和 VB 将 NULL 或空字符串传递给存储过程输入参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1747641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?
提问by r?ph
I have a stored procedure in Sql Server 2005 with a varchar input parameter defined as:
我在 Sql Server 2005 中有一个存储过程，其 varchar 输入参数定义为：
@Value varchar(24) = NULL
in my VB6 application I try to set the parameter with an ADO function:
在我的 VB6 应用程序中，我尝试使用 ADO 函数设置参数：
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.Connection error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided.
ADODB.Connection 错误 '800a0e7c' 参数对象定义不正确。提供了不一致或不完整的信息。
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?
如何将空字符串或 NULL 值传递给存储过程？
I already read a lot of articles and searched forums (even found my question several times) but without the right answer.
我已经阅读了很多文章并搜索了论坛（甚至多次找到我的问题）但没有正确答案。
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!
到目前为止，空字符串的解决方法是设置长度 = 1 或设置字符串 = " "（空格）。但这不是很好，我更愿意发送 NULL。我还尝试将 paramValue 设置为 vbNull、Null、vbNullString 或设置 prmParamVal.value = Empty，但没有任何运气！
回答by Chris J
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):
这里的快速测试表明 NULL 应该可以完成这项工作。我用来测试的示例代码（在一个带有一个按钮和一个文本框的简单表单上）：
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
And it called this stored proc:
它调用这个存储过程：
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 returned 'NULL' for using the VB value Null(as per the sample above), and 'NOT NULL' for vbNull. If Nulldoesn't work for you, then I can't comment on what might be wrong...!
usp_Bob 为使用 VB 值返回“NULL” Null（根据上面的示例），为vbNull. 如果Null对您不起作用，那么我无法评论可能有什么问题......！
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).
类似地，空字符串应该像那样传递——一个空字符串，即str = ""——这使得 usp_Bob 返回 'EMPTY'。其他任何东西都返回“NOT NULL AND NOT EMPTY”（正如预期的那样）。
If you can't get NULL passed through, then another option is to cast an empty string to NULL in the sproc -- i.e.,
如果您无法通过 NULL 传递，则另一种选择是在 sproc 中将空字符串转换为 NULL——即，
IF @param = ''
SET @param = NULL
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.
请注意，您通过的长度应该无关紧要。它反映了 SQL Server 中定义的参数的最大长度，而不是您传递的数据的长度。
回答by phillip voyle
if you want to pass a blank string to a parameter us adBSTR instead of adVarChar. It works for strings longer than zero chars too
如果您想将空字符串传递给参数，请使用 adBSTR 而不是 adVarChar。它也适用于长度超过零字符的字符串
eg (where oCmd is the command object in question):
例如（其中 oCmd 是有问题的命令对象）：
oCmd.Parameters.Append oCmd.CreateParameter("@Parm", adBSTR, adParamInput, len(sParm), sParm)
回答by Mathew
I always pass DBNULL.Value as the parameter value when inserting null into a column
将空值插入列时，我总是将 DBNULL.Value 作为参数值传递
