将空白文本从文本框中插入为null

Question

我正在使用VB6，如果文本框空白，则尝试将空值插入数据库中。当我这样做时，" null"将插入仅null所设置的数据库中。我该如何解决？

Dim ctl As Control
For Each ctl In Me.Controls
   If TypeOf ctl Is TextBox Then
       If ctl.Text = "" Then
       ctl.Text = "NULL"
       End If
  End If
Next
 Set prm = cmdDlrID.CreateParameter("@ContractNumberField", adVarChar, adParamInput,  50, txtContNum.Text)
 cmdDlrID.Parameters.Append prm 
 Set prm = cmdDlrID.CreateParameter("@ContractSuffix", adVarChar, adParamInput, 50,   txtContSfx.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@CustomerLastName", adVarChar, adParamInput, 50,  txtCustLstNme.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@CustomerFirstName", adVarChar, adParamInput, 50, txtCustFstNme.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@Last6OfVin", adVarChar, adParamInput, 10, txtVin6.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@DealershipName", adVarChar, adParamInput, 100,   cmbDealerName.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@ClaimNumber", adVarChar, adParamInput, 50,   txtClaimNumber.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@PortalClaimNumber", adVarChar, adParamInput, 50, txtPortalClaimNum.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@RONumber", adVarChar, adParamInput, 50, txtRONumber.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@ContractID", adVarChar, adParamInput, 50, txtContCode.Text)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@LookUpUser", adVarChar, adParamInput, 50,  username)
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@LookUpTime", adVarChar, adParamInput, 50, "CurrentDate")
 cmdDlrID.Parameters.Append prm
 Set prm = cmdDlrID.CreateParameter("@Department", adVarChar, adParamInput, 50, "Department")
 cmdDlrID.Parameters.Append prm

 cmdDlrID.Execute

存储的proc

 ALTER PROCEDURE [dbo].[InsertLookupLog] 
-- Add the parameters for the stored procedure here
@ContractNumberField varchar(50) ,
@ContractSuffix varchar(50),
@CustomerLastName varchar(50), 
@CustomerFirstName varchar(50),
@Last6ofVIN varchar(10), 
@DealerShipName varchar(100),
@ClaimNumber varchar(50), 
@PortalClaimNumber varchar(50),
@RONumber varchar(50), 
@ContractId varchar(50),
@LookUpUser varchar(50), 
@LookUpTime varchar(50),
@Department varchar(50)
 AS
 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert Into tblContractLookupLog (ContractNumberField,ContractSuffix, CustomerLastName, CustomerFirstName,Last6ofVin,DealershipName,ClaimNumber, PortalClaimNumber, RONumber, ContractID, LookUpUser,LookUpTime,Department) Values (@ContractNumberField,@ContractSuffix,@CustomerLastName,@CustomerFirstName,@Last6ofVin,@DealershipName,@ClaimNumber,@PortalClaimNumber,@RONumber, @ContractID, @LookUpUser,@LookUpTime,@Department)
 END

Answer 1

这里的问题是，您将TextBox设置为字符串文字"NULL"而不是值NULL。而不是使用String，您需要使用Nothing。您可以将其抽象到功能

Function GetDbValue(ByVal tb) 
  If tb.Text = "" Then
    Return Nothing
  Else
    Return tb.Text  
  End If
End Function

然后删除For循环，然后更改参数创建代码如下

Set prm = cmdDlrID.CreateParameter("@ContractNumberField", adVarChar, adParamInput,  50, GetDbValue(txtContNum)

注意：我不是100％确定Nothing会转化为VB6中的NULL dB值，但我很确定就是这样。使用字符串字面的"NULL"绝对无法正常工作

Answer 2

由于您使用的是存储过程，因此可以修改过程以插入null如果值为空字符串。这样：

Insert Into tblContractLookupLog (ContractNumberField,More columns here) 
Values (NullIf(@ContractNumberField, ''),More columns here)

如果两个参数的值相同，则nullif函数将返回null。在这种情况下，如果您有一个为@ContractNumberfield的空字符串，则NULLIF函数将返回null。

将此更改为存储过程后，您只需将一个空字符串传递给存储过程。