CODEWITHSUNDEEP
visual-studiostored-proceduresvb6null
Zingo
Zingo

Reputation: 291

Inserting blank text from textbox as NULL

I'm using VB6 and im trying to insert a NULL value into the database if a textbox is left blank. When i do this, 'NULL' gets inserted into the database insted of just NULL. How can i fix this?

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

Stored 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

Upvotes: 0

Views: 949

Answers (2)

George Mastros
George Mastros

Reputation: 24498

Since you are using a stored procedure, you could modify the procedure to insert null if the value is an empty string. Like this:

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

The NullIf function will return NULL if the value of both parameters are the same. In this case, if you have an empty string for @ContractNumberField, the NULLIF function will return NULL.

Once you make this change to the stored procedure, you can simply pass an empty string to the stored procedure.

Upvotes: 0

JaredPar
JaredPar

Reputation: 755179

The problem here is you are setting the TextBox to the string literal "NULL" not the value NULL. Instead of using a String you will need to use Nothing. You could abstract this out to a function 

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

Then remove the For loop and change the parameter creation code as follows

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

Note: I'm not 100% sure that Nothing will translate to a NULL DB value in VB6 but I'm pretty sure that is the case. Using a string literal "NULL" definitely won't work

Upvotes: 2

Related Questions