VBScript:将带空值的参数传递给存储过程?

In VBScript (ASP environment), is it possible to pass a parameter with a null value to a stored procedure?

在VBScript (ASP环境)中，是否可能将一个带空值的参数传递给存储过程?

4 个解决方案

#1

Passing null to a stored procedure, using a command object.

使用命令对象将null传递给存储过程。

Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\Test"
Set cmd.ActiveConnection = cn
cmd.CommandText = "TestTable.sp_ModifyData"
cmd.CommandType = 4
cmd.NamedParameters = True

set cnParam = cmd.CreateParameter("@RowID",3,3,,-1)
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@AddRemoveModify",3,1,,0)
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@Value1",3,1,,0)
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@Value2",8,1,-1,"Test")
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@value3",5,1,,null)
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@value4",5,1,,0)
cmd.Parameters.Append cnParam
set cnParam = cmd.CreateParameter("@value5",8,1,-1,"")
cmd.Parameters.Append cnParam
cmd.Execute
cn.Close
Set cmd = Nothing
Set cn = Nothing

Sorry I didn't put much thought into naming the fields in my database.

对不起，我在命名数据库中的字段时没有考虑太多。

#2

Try vbNullString or vbNullChar. You may also need adParamNullable.

尝试vbNullString或vbNullChar。您可能还需要adparamlable。

set cnParam = cmd.CreateParameter("@value3",5,1,,vbNullString)  
cnParam.Attributes = adParamNullable  
cmd.Parameters.Append cnParam

Update:

更新:

Actually this worked for me:

事实上，这对我起了作用:

set cnParam = cmd.CreateParameter("@value3",5,1,,Null)  
cnParam.Attributes = adParamNullable  
cmd.Parameters.Append cnParam

Huh, this worked too:

嗯,这工作太:

set cnParam = cmd.CreateParameter("@value3",5,1,,Null)  
cmd.Parameters.Append cnParam

Go figure.

图。

#3

If you're building strings, and checking/guarding against SQL Injection, you can simply use the word null in your SQL string, or comma delimited EXEC statement.

如果您正在构建字符串，并检查/保护SQL注入，您可以在SQL字符串中使用null，或者使用逗号分隔的EXEC语句。

'calling a stored proc.
strSQL = "EXEC UpdateCustomer @CustomerID=" & iCustomerID + ",@PhoneNumber=null"

Here's how to use null in a manually built string to send to the DB

以下是如何在手动构建的字符串中使用null发送到DB

strSQL = "UPDATE Customer SET PhoneNumber = null WHERE CustomerID = " + iCustomerID

#4

Short answer: Set the parameter value to Null (VBScript keyword).

简短回答:将参数值设置为Null (VBScript关键字)。

#1