VB.Net problem with data reader

Topic

New Reply

I have a form where I am trying to display a summary of records in a ListView.

The SQL is:

SELECT MatchClassID, MatchClassName, Count(tblMatchID) FROM (SELECT tblMatch.ID As tblMatchID, tblSeries.MatchClassID As MatchClassID, tblMatchClass.ClassName As MatchClassName FROM (tblMatch LEFT JOIN tblSeries ON tblMatch.SeriesID = tblSeries.ID) LEFT JOIN tblMatchClass ON tblSeries.MatchClassID = tblMatchClass.ID WHERE (tblMatch.Complete = True) ORDER BY tblMatch.ID ) GROUP BY MatchClassID, MatchClassName

The code to get the data is a follows:

Dim objCommand As OleDb.OleDbCommand

If ConnectToDatabase() = True Then
objCommand = New OleDb.OleDbCommand(strQuery, conDb)
oDR = objCommand.ExecuteReader

Return True
Else
Return False
End If

Where strQuery contains the SQL statement above.

This should return 3 rows and 3 columns however it is returning over 3000 rows. What it is doing is returning the results of the sub-query ie before the aggregation. If I just take the SQL and run it against the database directly I get the correct results so I know the SQL is right. What I cannot understand is why different results are being loaded into the Reader. I have used far more complex aggregate queries with Readers and not had this problem.

Can anyone help?

Thanks.

Reply | Quote

Replies

The sample in the help files shows the command object being prepared before opening and executing it against the connection. For grins and giggles try this to see if it changes anything for you.

Public Sub CreateReader(ByVal connectionString As String, ByVal queryString As String)

Using connection As New OleDb.OleDbConnection(connectionString)

Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()

While reader.Read()
Console.WriteLine(reader(0).ToString())
End While

reader.Close()

End Using

End Sub

Reply | Quote

Hi Bender,

Thanks for the response. I am away from the machine on which I am developing the application at the moment, however I am sure what you suggest would give me a different, and the desired result.

After lots of head scratching yesterday afternoon I pulled code from the different class modules that were involved and merged it all into a single procedure to see if that would change the result. In doing that I noticed a logic error where I was recreating the SQL incorrectly.

It is strange that after looking at something for a long time you stop seeing the obvious.

Thanks anyway.

Reply | Quote

It is strange that after looking at something for a long time you stop seeing the obvious.

Been there / done that. I feel your pain brother.

Reply | Quote