How to pass a null to a SQL 2K database

[rash]

just wanted to know....
How to pass a null to a SQL 2K database

thanks in advance
rash

[Joe Sage]

Here is a function I use, I think this is what you are looking for

re.entityInfo.SC_AuthorityURL = DeNull(rs("SC.Authority_URL"))

Private Function DeNull(value As Variant) As String
If IsNull(value) Then
DeNull = ""
Else
DeNull = Trim(CStr(value))
End If
End Function

[Jeff Johnson]

"rash" <rash...@rediffmail.com> wrote in message
news:88fe01c197b5$184f00b0$9be62ecf@tkmsftngxa03...

> just wanted to know....
> How to pass a null to a SQL 2K database

In what? An SQL string? An ADO Command object? SQL Enterprise Manager?
That's a pretty vague question.

[ras...@rediffmail.com]

in an SQL string...(using ado)

>.
>

[Jeff Johnson]

<ras...@rediffmail.com> wrote in message
news:6d7a01c197bc$683fc4e0$b1e62ecf@tkmsftngxa04...

> >> just wanted to know....
> >> How to pass a null to a SQL 2K database

> in an SQL string...(using ado)

NULL

[Joe "Nuke Me Xemu" Foster]

"rash" <rash...@rediffmail.com> wrote in message <news:88fe01c197b5$184f00b0$9be62ecf@tkmsftngxa03>...

> just wanted to know....

> How to pass a null to a SQL 2K database

Here's something I sometimes use when building SQL strings:

Public Function FormatSQL(ByRef Value As Variant) As String
Select Case VarType(Value)
Case vbEmpty
FormatSQL = "Nz(Null)" ' ADODB might not be able to call Nz()
Case vbNull
FormatSQL = "Null"
Case vbString
FormatSQL = "'" & Replace(Replace(Value, "'", "''"), "|", "' & Chr(124) & '") & "'"
Case vbCurrency
FormatSQL = "CCur(" & FormatSQL(CStr(Value)) & ")"
Case vbDecimal
FormatSQL = "CDec(" & FormatSQL(CStr(Value)) & ")"
Case vbBoolean
If Value Then FormatSQL = "True" Else FormatSQL = "False"
Case vbError
FormatSQL = "CVErr(" & CLng(Value) & ")"
Case vbDate
If Value = DateValue(Value) Then
FormatSQL = Format$(Value, "\#yyyy\-mm\-dd\#")
ElseIf Value = TimeValue(Value) Then
FormatSQL = Format$(Value, "\#hh\:nn\:ss\#")
Else
FormatSQL = Format$(Value, "\#yyyy\-mm\-dd hh\:nn\:ss\#")
End If
Case Is >= vbArray
On Error Resume Next
Dim Base As Long: Base = LBound(Value)
Dim Count As Long: Count = UBound(Value) - LBound(Value) + 1
On Error GoTo 0
Dim i As Long: For i = Base To Base + Count - 1
FormatSQL = FormatSQL & ", " & FormatSQL(Value(i))
Next
If Len(FormatSQL) = 0 Then FormatSQL = "(Null)" _
Else FormatSQL = "(" & Mid$(FormatSQL, 3) & ")"
Case Else
FormatSQL = Trim$(Str$(Value))
End Select
End Function

Here's a back-port of Replace, if you're using VB 5.0 or earlier:

Public Function Replace(ByVal Expression As String, _
ByVal Find As String, ByVal Repl As String, _
Optional ByVal Start As Long = 1, Optional ByVal Count As Long = -1, _
Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As String

If Start < 1 Or Count < -1 Then
Err.Raise 5
ElseIf Start > Len(Expression) Then
' nothing to do!
Exit Function
ElseIf Len(Find) < 1 Or Count = 0 Then
If Start <> 1 Then Replace = Mid$(Expression, Start) _
Else Replace = Expression
Exit Function
End If

'Replace = Expression

Dim p As Long, q As Long, rlen As Long
p = Start
Do
q = InStr(p, Expression, Find, Compare)
If q = 0 Then Exit Do

If rlen + q - p + Len(Repl) > Len(Replace) Then
Replace = Replace & Replace & Space$(q - p + Len(Repl))
End If

If p < q Then Mid$(Replace, rlen + 1) = Mid$(Expression, p, q - p)
If Len(Repl) Then Mid$(Replace, rlen + q - p + 1) = Repl

Count = Count - 1
rlen = rlen + q - p + Len(Repl)
p = q + Len(Find)
Loop Until Count = 0
Replace = Left$(Replace, rlen) & Mid$(Expression, p)
End Function

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!