Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts

    VB.Net problem with data reader

    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.

  2. #2
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    65
    Thanks
    12
    Thanked 4 Times in 4 Posts
    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
    Do your part!!! Report SPAM to http://www.spamcop.net/

  3. The Following User Says Thank You to Bender For This Useful Post:

    njl100 (2015-02-19)

  4. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    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.

  5. #4
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    65
    Thanks
    12
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by njl100 View Post
    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.
    Do your part!!! Report SPAM to http://www.spamcop.net/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •