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)

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

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'?


Show quoteHide quote

Jonefer,

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

Cor

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?


Show quoteHide quote

Jonefer,

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

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

DataTable and DataGridView
Using Objects as Data Sources
Trying to find the currently logged on user
Architecture Advice
Upgrading VB6 VS2005