Question Why my MS access SQL output is not the same as VB.NET SQL output?

[h4tred]

Hi guys,
I have the SQL command:

SELECT [First Name], [Last Name], [Student Number], [Program], Count(*) AS NumberOfTimesAttended
FROM 5
GROUP BY [First Name], [Last Name], [Student Number], [Program];

In MS access the number of record is 126, while in VB it is only 125
The codes in VB that I'm using are:

Public Sub g_PopulateGridForStudentAllProgramsAndSubjects(ByVal oThisFormA As frmHome, sSearchWeekA As String, ByVal sDBNameA As String)

        Dim iCount As Integer
        Dim aoRowColumn() As Object
        Dim sSQL As String

        Dim oDataReader As OleDbDataReader = Nothing    'To instantiate a DataReader obj.


            sSQL = "SELECT [First Name], [Last Name], [Student Number], [Program], Count(*) AS NumberOfTimesAttended " & _
                " FROM " & sSearchWeekA & " GROUP BY [First Name], [Last Name], [Student Number], [Program];"

            oDataReader = g_getDataReader(sSQL, sDBNameA)

            oThisFormA.dgvStudent.Rows.Clear()

            ReDim aoRowColumn(oDataReader.FieldCount)

            If oDataReader.Read Then
                Do While oDataReader.Read()

                    For iCount = 0 To oDataReader.FieldCount - 1
                        aoRowColumn(iCount) = oDataReader.Item(iCount)
                    Next

                    oThisFormA.dgvStudent.Rows.Add(aoRowColumn)

                Loop
            Else
                'oThisFormA.btnClear.PerformClick()
            End If
           
            If oThisFormA.dgvStudent.Rows.Count > 0 Then
                oThisFormA.txtTotalVisitation.Text = dCalculateTotal().ToString()
            End If
    End Sub

    Private Function dCalculateTotal() As Double
        Dim dTotal As Double = 0
        Dim iCount As Integer = 0
        For iCount = 0 To frmHome.dgvStudent.Rows.Count - 1
            dTotal = dTotal + Convert.ToDouble(frmHome.dgvStudent.Rows(iCount).Cells("NumberOfVisitation").Value)
        Next iCount
        Return dTotal
    End Function

Public Function g_oConnection(ByVal sDBNameA As String) As OleDbConnection
        Dim sConnection As String
        Dim oConn_Local As OleDbConnection

        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"
        sConnection &= "User ID=Admin;"
        sConnection &= "Data Source="
        sConnection &= Application.StartupPath & "\" & sDBNameA & ".accdb"

        oConn_Local = New OleDbConnection(sConnection)
        oConn_Local.Open()

        Return oConn_Local
    End Function
    Public Function g_getDataReader(sSelectSqlA As String, sDBFileNameA As String) As OleDbDataReader
        Dim oConn As New OleDbConnection
        Dim oCmd_Select As New OleDbCommand
        Dim oDataReader As OleDbDataReader

        oDataReader = Nothing
        oConn = g_oConnection(sDBFileNameA)

        oCmd_Select = oConn.CreateCommand()
        oCmd_Select.CommandText = sSelectSqlA
        oDataReader = oCmd_Select.ExecuteReader()

        Return oDataReader
    End Function
    Public Sub g_ExecuteQuery(ByVal sActionSqlA As String, _
                                   ByVal sDBFileNameA As String)

        Dim oConn As New OleDbConnection
        Dim oCmd_Action As OleDbCommand

        oConn = g_oConnection(sDBFileNameA)
        oCmd_Action = oConn.CreateCommand()
        oCmd_Action.CommandText = sActionSqlA
        oCmd_Action.ExecuteNonQuery()

        oConn.Close()

    End Sub

I'm pretty sure the SQL command is the same as what I've typed in the MS access, but then again it miss 1 particular record. So what is going on?

 

[jmcilhinney]

The issue is your code:

            If [B][U]oDataReader.Read[/U][/B] Then
                Do While [B][U]oDataReader.Read()[/U][/B]
 
                    For iCount = 0 To oDataReader.FieldCount - 1
                        aoRowColumn(iCount) = oDataReader.Item(iCount)
                    Next
 
                    oThisFormA.dgvStudent.Rows.Add(aoRowColumn)
 
                Loop
            Else
                'oThisFormA.btnClear.PerformClick()
            End If

Every time you call Read, the data reader reads a row. You are calling Read twice before ever getting any data from the data reader, so you're simply discarding the first row. If all you want to do is check whether the result set is empty or not then you use the HasRows property, not the Read method.

By the way, please tell me that you don't have multiple tables in your database that all have the same schema but represent different weeks. That would be absolutely criminal database design. Such a situation should be handled with a single table and a column to represent the week. If you then want data for just one week you simply add a WHERE clause to filter on the column that contains the week number.

 

[IanRyder]

Well spotted jmcilhinney, I missed that one.

Cheers,

Ian

 

[h4tred]

@jmcilhinney: thanks its working now, by the way that generic code was from my tutor, so surprise he got it wrong. Also yes there will be a multiple tables (week 4, week 5, etc) with the same schema, but this is what my client want even though I have told them that's a bad idea.