Jamie Tilma Jamie Tilma - 1 month ago 9
Vb.net Question

Extracting the First (Oldest) Value from Dataset Based on Column Value

I don't have a great deal of experience working with DataSets and haven't been able to find the best way of achieving what I want to achieve.

I basically create a DataSet using a SQL Query and then I am trying to find a Specific Value in the 'Field' column and then if there is a 'Y' in the 'Flag' (as apposed to a 'N') Column on the same Row then I want it to change a check box's state to Checked as well as updating a labels text.

What I have seems to work however if no data is returned I get the below error:


Object reference not set to an instance of an object


If I change the code slightly from .FirstOrDefault() to .First() I get this error:


Sequence contains no elements


The part of the code that appears to be causing the problem is listed below. If you need to know anything else I will add it in.



Dim sSQL As String
sSQL =
<SQL>
SELECT MAX(UpdateTime) AS UpdateTime FROM AdminCS_Data_Current
WHERE UpdateUser = |@@UpdateUser|
</SQL>
sSQL = Replace(sSQL, "@@UpdateUser", AdminCB.Text)
Me.LastUserUpdate.Text = "Last Action: " & Format(ReturnDatabaseValue(sSQL, "UpdateTime", "Data"), "dd/MM/yyyy HH:mm:ss")

Dim EmployeeDataset As New DataSet
Try
sSQL =
<SQL>
SELECT * FROM AdminCS_Data_Current
WHERE UpdateUser = |@@UpdateUser| AND CONVERT(DATE, UpdateTime) = CAST(GETDATE() AS DATE)
ORDER BY UpdateTime ASC
</SQL>
sSQL = Replace(sSQL, "@@UpdateUser", AdminCB.Text)
EmployeeDataset = ReturnDataSet(sSQL, "Data")

If EmployeeDataset IsNot Nothing Then
Dim eData = EmployeeDataset.Tables(0)
If (eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()("Flag")) IsNot Nothing Then
If eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()("Flag").ToString.Trim = "Y" Then
TShtY.CheckState = CheckState.Checked
TShtTime.Text = Format(eData.Select("Field = 'Timesheets Checked'").First()("UpdateTime"), "HH:mm:ss")
Else
TShtN.CheckState = CheckState.Checked
End If
End If
' The above two IF statements would be repeated several times on each change of "Field"
End If

Answer

It would appear that this code has introduced not just iunefficiency but also a bug:

    If (eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()("Flag")) IsNot Nothing Then
        If eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()("Flag").ToString.Trim = "Y" Then
            TShtY.CheckState = CheckState.Checked
            TShtTime.Text = Format(eData.Select("Field = 'Timesheets Checked'").First()("UpdateTime"), "HH:mm:ss")
        Else
            TShtN.CheckState = CheckState.Checked
        End If
    End If

It should have been written like this in the first place:

    Dim row = eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()

    If row IsNot Nothing Then
        If row("Flag").ToString.Trim = "Y" Then
            TShtY.CheckState = CheckState.Checked
            TShtTime.Text = Format(row("UpdateTime"), "HH:mm:ss")
        Else
            TShtN.CheckState = CheckState.Checked
        End If
    End If

Easier to read, more efficient and avoids that nasty bug.

Also, I'd much rather see this:

    Dim row = eData.Select("Field = 'Timesheets Checked'").FirstOrDefault()

    If row IsNot Nothing Then
        If row("Flag").ToString.Trim = "Y" Then
            TShtY.Checked = True
            TShtTime.Text = CDate(row("UpdateTime").ToString("HH:mm:ss")
        Else
            TShtN.Checked = True
        End If
    End If

You should never use the CheckState of a Checkbox unless it's tri-state, which maybe yours are but I doubt it. As for Format, we're not in VB6 anymore Toto.