DateTime parameter keeps seeing string even though not passed

[Guest]

I have code that works fine if there is a date to feed the parameter...
if there isn't...

It seems as though I've trapped the parameter from even presenting itself
with:
If Len(sGROUP) <> 0 And Len(sSGR) <> 0 And FROMdt <> "" Then...

'AND just to be redundant...

If FROMdt <> "" Then
.Add("@FROM", SqlDbType.DateTime).Value = FROMdt
Else
.Add("@FROM", SqlDbType.DateTime).Value = DBNull.Value
End If

But I keep getting the following error
The string was not recognized as a valid DateTime. There is a unknown word
starting at index 1.
(what am I missing?)

'See the majority of this function below:
Public Shared Function GetMember(ByVal lMRN As Integer, _
ByVal sGROUP As String, _
ByVal sSGR As String, _
ByVal FROMdt As String) As Member

Dim Member As New Member
Dim drMember As SqlDataReader
Dim conMembers As SqlConnection = GetMembershipConnection()
'has all possible parameters
If Len(sGROUP) <> 0 And Len(sSGR) <> 0 And FROMdt <> "" Then
Dim sSqlCommand = "SELECT MRN, MemNAME, SSN, DOB, SEX, HML, PHN,
[GROUP], SGR, REL, PROVIDER, PCL, [EXP], [FROM-DT], [THRU-DT] FROM MEMBERSHIP
WHERE (MRN = @MRN) AND ([GROUP]=@GROUP) AND (SGR = @SGR) AND ([FROM-DT] =
@FROM)"
Dim cmdMember As New SqlCommand(sSqlCommand, conMembers)
With cmdMember.Parameters
.Add("@MRN", SqlDbType.Int).Value = lMRN
.Add("@GROUP", SqlDbType.NVarChar).Value = sGROUP
.Add("@SGR", SqlDbType.NVarChar).Value = sSGR

If FROMdt <> "" Then
.Add("@FROM", SqlDbType.DateTime).Value = FROMdt
Else
.Add("@FROM", SqlDbType.DateTime).Value = DBNull.Value
End If


End With
conMembers.Open()
drMember = cmdMember.ExecuteReader(CommandBehavior.SingleRow)

 

[Cor Ligthert [MVP]]

Jonefer,

A datatime (in Net) cannot be spaces, it holds always a kind of datetime.

You can try to test it against = Nothing
or if it is a DataBase DateTime = DBNull.Value

I hope this helps,

Cor

 

[Guest]

Shouldn't this 'IF' statement prevent it from even seeing that value?:
If Len(sGROUP) <> 0 And Len(sSGR) <> 0 And FROMdt <> "" Then...


....but I also added the DBNull.Value

should I set it to 'Nothing'?

 

[Cor Ligthert [MVP]]

Jonefer,

The answer is in my opinion yes, however this is so simple to try, why did
you not do that?

Cor

 

[Guest]

I did both. compared it against = nothing, AND set the value to dbnull.value.
The error goes away, but then it doesn't find the record.

What I don't understand is that I am checking for all parameters to be
filled to be non-empty values
MRN, [Group], SGR, and [From-DT]

If len(MRN)<> 0 AND len([GROUP]) <> 0 AND len(SGR)<> 0 and
([From-Dt])<>nothing...

It goes down this branch obviously because it thinks it sees a value in
[From-Dt].
So I tried what you set and checked for -(FROM-DT) <> nothing

'These are session variables that are all 'STRING'
As long as the From-Dt is filled in it pulls a value, because I define it in
the parameter at that point. - But if the string is empty, I dont' even write
a select with the parameter - so why is it still going down that branch?

If Len(sGROUP) <> 0 And Len(sSGR) <> 0 And FROMdt <> Nothing Then
Dim sSqlCommand = "SELECT MRN, MemNAME, SSN, DOB, SEX, HML, PHN,
[GROUP], SGR, REL, PROVIDER, PCL, [EXP], [FROM-DT], [THRU-DT] FROM MEMBERSHIP
WHERE (MRN = @MRN) AND ([GROUP]=@GROUP) AND (SGR = @SGR) AND ([FROM-DT] =
@FROM)"
Dim cmdMember As New SqlCommand(sSqlCommand, conMembers)
With cmdMember.Parameters
.Clear()
.Add("@MRN", SqlDbType.Int).Value = lMRN
.Add("@GROUP", SqlDbType.NVarChar).Value = sGROUP
.Add("@SGR", SqlDbType.NVarChar).Value = sSGR

If FROMdt <> Nothing Then
.Add("@FROM", SqlDbType.DateTime).Value = FROMdt
Else
.Add("@FROM", SqlDbType.DateTime).Value = Nothing
End If
'==========================
maybe I should supply a bogus date string in the past for null value
parameters?

 

[Cor Ligthert [MVP]]

Jonefer,

I did not test it however in my opinion has it at least to be

If FROMdt <> Nothing Then
.Add("@FROM", SqlDbType.DateTime).Value = FROMdt
Else

.Add("@FROM", SqlDbType.DateTime).Value = DBNull.value

End If

Cor