CODEWITHSUNDEEP
sqlvb.netlinqdataset
Jamie Tilma
Jamie Tilma

Reputation: 69

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

Upvotes: 0

Views: 57

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54457

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.

Upvotes: 2

Related Questions