DataGridView Not Scrolling

[GrexD]

I had a data grid on the form and the DataSource was set in the properties window. I was able to query the table by creating a connection and SqlDataAdapter with a SQL string created on the fly. Everything worked fine.

I then refined the code so I could query other tables (SQL Server 2005). I cleared the DataSource property for the DataGrid from the proerties widnow. I can now query other tables, but I don't get scroll bars and I can't view any data that is outside of the visible part of the DataGrid. The RowCount property tells me it returned 3000 rows, but I can't scroll the datagrid to get to anything past the top 10 or so.

Here's is my code. Any help is appreciate.

Private Sub RunSQLCode(ByVal sSQL As String)
        If Len(sSQL) = 0 Then
            Exit Sub
        End If

        Dim connection As New SqlConnection("Data Source=00.100.0.0\XXXXXX;Initial Catalog=XXXXXX;Persist Security Info=True;User ID=sa;Password=XXXXXXX")


        Me.Cursor = Cursors.WaitCursor

        connection.Open()
        Dim adapter As New SqlDataAdapter(sSQL, connection)
        Dim dataset As New DataSet '
        Try
            adapter.Fill(dataset)
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            adapter.Dispose()
            connection.Close()
            MsgBox(ex.ToString)
            Exit Sub
        End Try

        adapter.Dispose()

        connection.Close()

        Dim table As DataTable = dataset.Tables(0)

        DataGridView1.DataSource = table

        lblSQLinfo.Text = DataGridView1.RowCount & " rows returned."

        Me.Cursor = Cursors.Default
End Sub

 

[GrexD]

Never mind! An errant mouse click set the Enabled Property to False. I must have done it just before I modified the code, so I figured my changes were to blame.

It only took 2 hours to figure it out. Grrrr!

 

[GrexD]

While am at it, I'm wondering if there are any comments on the structure of the code. I wrote code in Vb 3 through 6 for a dozen years or so, and the DB part was all DAO. This is my first Vb.Net app. Its a bit of a struggle, but I'm getting it.

The code above works, but wondering if there is anything some of you might do differently. For instance, if you were writing a SQL Server front end, would you have a connection always open, or open and close it each time you run a query? In the code above I open the connection, use it, dispose of it, and close the connection.

Any comments are greatly appreciated.

 

[jshultz]

While am at it, I'm wondering if there are any comments on the structure of the code. I wrote code in Vb 3 through 6 for a dozen years or so, and the DB part was all DAO. This is my first Vb.Net app. Its a bit of a struggle, but I'm getting it.

The code above works, but wondering if there is anything some of you might do differently. For instance, if you were writing a SQL Server front end, would you have a connection always open, or open and close it each time you run a query? In the code above I open the connection, use it, dispose of it, and close the connection.

Any comments are greatly appreciated.

If you are using a tableadapter/dataadapter you don't have to worry about opening and closing the connection, it does both itself when you execute the .Update command. If you are running the SQLCommand, you must have an open connection, in which case it is best to open the connection just before you run the command and then dispose of it after you have run the command. You don't want to keep connections open.

 

[GrexD]

Thanks, so that code looks right?