如何使用 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.Connection 错误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.

到目前为止，空字符串的解决方法是将长度设置为 1 或将字符串设置为"(空格).但这不是很好，我更愿意发送 NULL.我还尝试将 paramValue 设置为 vbNull、Null、vbNullString 或设置 prmParamVal.value = Empty，但没有任何运气！

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!

推荐答案

这里的快速测试表明 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"，而 vbNull 则返回NOT 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'.其他任何东西都返回NOT NULL AND NOT 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屋！