Problem with closing connection and showing data

This is my first attempt at SQLCE and I am having an issue that I can't figure out. If I close the connection in the finally statement as shown below I only get the first record from the database. When I uncomment it I get both records. What am I doing wrong?

This is my code:

VB Code:

'
        Dim con As SqlCeConnection = Nothing
        Try
            Dim dir As String = Path.GetDirectoryName( _
                Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)
 
            con = New SqlCeConnection("Data Source = '" & dir & "\RouteDB.sdf'")
            con.Open()
 
            Dim cmd As SqlCeCommand = New SqlCeCommand( _
                "SELECT RouteID " & _
                ", Name FROM Routes " & _
                "Where IsDeleted = 0", con)
            cmd.CommandType = CommandType.Text
 
            Dim resultSet As SqlCeResultSet = _
                cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
 
            dgRoutes.DataSource = resultSet
 
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        Finally
            'con.Close()
            ' If I uncomment the close I only get the first record
            ' in the database. If I comment it out as is here
            ' both the records in the database show up.
        End Try

Hey,

Really? That seems very strange?!?!

Are you sure that is the only change that you are making?

Gary

Yes I tried it over and over. In fact the only reason I found that it made a difference is that I had used the same block of code on another datagrid and had accidently commented the close out when I commented out the TRY block. When I ran the program the second datagrid worked so when I was comparing the two blocks the only difference was the close. I removed it and it worked.

Ok, one thing to try...

Rather then calling .Close in the finally of the try, wrap the SqlCeConnection in a Using statement:

http://www.pluralsight.com/community...4/28/7834.aspx

Does that work?

Gary

Hi,
debug it and count the rows in the resultset - I would guess it is the grid binding that is failing, not the get.