Passing NULL into SQL Server 7

[NevG]

hi

Im trying to pass a null value depending on the value of text box into a stored procedure to update a table in SQL server. I cannot work out how to pass the vlue NULL into a date field without getting errors. Please help

Thanks

Nev G

 

[NickISD]

Nev,
Have you set the fields property to allow NULL's ? if not then you will get an error.

Nick W

 

[dubeaum]

I had the same problem. I didn't found a way to do it from within VB6. So I now use a Stored Procedure on the server. You can assign a NULL value from there. Here is an example of what I did. [REGLEE] is a date, it is not in the parameters of the procedure, I assign it NULL.

CREATE PROCEDURE dbo.COL_UPD_COLLECTION_OUV

(
@ref char(8),
@LeurRef varchar (50) ,
@Devise varchar (4) ,
@Montant money ,
@Succ varchar (10) ,
@NumCompte varchar (10) ,
@NumCheque varchar (10) ,
@Tire varchar (50) ,
@Tireur varchar (50) ,
@Taux float ,
@DeviseFrais varchar (4) ,
@Details varchar (400) ,
@InfoCheque varchar (100) ,
@NomBanq varchar (100) ,
@AdrBanq varchar (200) ,
@VilleBanq varchar (150) ,
@PaysBanq varchar (100) ,
@CPBanq varchar (50) ,
@Attn varchar (100) ,
@Amex int ,
@ModRegl varchar (50)
)
AS

UPDATE [dbo].[COL_COLLECTION]
SET [LeurRef] =@LeurRef,
[Devise] =@Devise,
[Montant] =@Montant,
[Succ] =@Succ,
[NumCompte] =@NumCompte,
[NumCheque] =@NumCheque,
[Tire] =@Tire,
[Tireur] =@Tireur,
[Reglee] =NULL,
[Taux] =@Taux,
[DeviseFrais] =@DeviseFrais,
[Details] =@Details,
[InfoCheque] =@InfoCheque,
[NomBanq] =@NomBanq,
[AdrBanq] =@AdrBanq,
[VilleBanq] =@VilleBanq,
[PaysBanq] =@PaysBanq,
[CPBanq] =@CPBanq,
[Attn] =@Attn,
[Amex] =@Amex,
[ModRegl] =@ModRegl
WHERE [Ref] = @Ref;


IF @@ERROR <> 0
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN 0
END

 

[NickISD]

Dubeaum,
You can do the exact same thing with VB. Assume an insert to a table, VB form has two text box's and one set up for a date :-

cSql = &quot;INSERT MyTable (tField1,tField2,dDateFld) VALUES (&quot;
csql = csql & &quot;'&quot; & txtInput(0) & &quot;','&quot; & txtInput(1) & &quot;',&quot;
If IsDate(txtDate) then
csql = csql & &quot;'&quot; & format(txtDate,&quot;dd/mmm/yyyy&quot & &quot;'&quot;
else
csql = csql & &quot;NULL&quot;
End If
cSql = cSql & &quot&quot;
MyDb.AdoConnection.Execute(cSql)

Regards, Nick