Another helpful way to use a DataGridView

[frederick]

I have seen many entries here about issues involving updating and displaying data from sources such as Access and SQL. I use SQL for all my developments so I will be using SQL in my example.


SQL connection:
1. In your project properties, go to "Settings".
2. Add a connection to your project.
Name: DBPlay
Type: Connection String
Scope: Application
Value: Data Source=PRIMARY;Initial Catalog=TGPInventory;Integrated Security=True

Data Source is the server name or IP
Initial Catelog is the database name​

Populating a DataGridView:
1. Add a datagridview to your form.
2. Double click the form to view the source.
3. Add the following to the very top of the page.

At the top of you source code, add "Imports System.Data.SqlClient"​

4. Create a "Private Sub" for the grid.

Private Sub LoadGrid()
        Try
            '*** Connection to the server
            Dim connString As String = My.Settings.DBPlay
            Dim conn = New SqlConnection(connString)
            conn.Open()

            Dim sqlText As String
            Dim rownum As Integer = 0

            '*** SQL statement
            sqlText = "SELECT * FROM category ORDER BY catname"
            '*** Execute the statement
            Dim sqlStatement As New SqlCommand(sqlText, conn)
            Dim connInfo As SqlDataReader = sqlStatement.ExecuteReader()
            '*** Call the grid
            With Me.DataGrid.Rows
                '*** Clear all rows
                .Clear()
                While connInfo.Read()
                    '*** Add a row
                    .Add()
                    '*** Populate the rows with data
                    With Me.DataGrid.Rows(rownum)
                        .Cells(0).Value = connInfo!catid
                        If IsDBNull(connInfo!catname) = False Then
                            .Cells(1).Value = connInfo!catname
                        Else
                            .Cells(1).Value = ""
                        End If
                    End With
                    rownum = rownum + 1
                End While
                connInfo.Close()
            End With

            conn.Close()
        Catch ex As Exception
            MessageBox.Show("Load Grid: " & vbCrLf & ex.Message)
        End Try
    End Sub

In your form_load section:

Try
            Me.Cursor = Cursors.WaitCursor
            'Execute the Private Sub for the grid
            LoadGrid()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message)
        End Try

Updating the grid:
In your button event.

Try
            Me.Cursor = Cursors.WaitCursor
            Dim connString As String = My.Settings.DBPlay
            Dim conn = New SqlConnection(connString)
            conn.Open()

            Dim sqlText As String
            Dim rownum As Integer = 0
            Dim i As Integer = 0

            sqlText = "SELECT count(*) as count FROM category"
            Dim sqlStatement As New SqlCommand(sqlText, conn)
            Dim connInfo As SqlDataReader = sqlStatement.ExecuteReader()
            connInfo.Read()
            Dim recCount As Integer = connInfo!count
            connInfo.Close()

            For i = 1 To recCount
                With Me.DataGrid.Rows(rownum)
                    sqlText = "UPDATE category SET " & _
                    "catname = '" & .Cells(1).Value.ToString.Replace("'", "''").ToUpper() & "' " & _
                    "WHERE catid = " & .Cells(0).Value
                    Dim sqlStatement1 As New SqlCommand(sqlText, conn)
                    sqlStatement1.ExecuteNonQuery()
                End With
                rownum = rownum + 1
            Next

            conn.Close()

            LoadGrid()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message)
        End Try

Selecting a row in a DataGridView:

Private Sub DataGrid_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGrid.CellClick
        If e.RowIndex > -1 And e.ColumnIndex > -1 Then
            Me.lblRowNumber.Text = e.RowIndex
            With Me.DataGrid.Rows(e.RowIndex)
                Me.txtInvoiceNumber.Text = .Cells(1).Value
                Me.txtQuantityOrdered.Text = .Cells(2).Value
            End With
        End If
    End Sub

I hope this helps those starting out and gets you on your way. I found that working with data grids were the hard ones to use at the beginning.

 

[cjard]

Not to belittle frederick's efforts (which would have been reasonable advice 20 years ago), but please DO NOT DO THIS

Form code is NOT the place to put your Data Access Layer. As a simple rule, if you ever think about writing code like New SqlCommand("SELECT * FROM...") into form code, you're not writing good OO code


Never, ever, ever write code like this:

"UPDATE category SET " & _
                    "catname = '" & .Cells(1).Value.ToString.Replace("'", "''").ToUpper() & "' " & _
                    "WHERE catid = " & .Cells(0).Value

Ever.Period. No arguments. Never, ever write an SQL like that in any program, for the rest of your life. For the reason why, read the PQ link in my signature.

The best way to do data access in .NET 2 and above is by foillowing the tutorials in the DW2 link in my signatuer (Not my creation; Microsoft did them).
Start with the section "Creating a Simple Data App" and once you have the app working, and are curious to know more you can read the other sections. If you want to see what is happening behind the scenes, Go to Tools >> Options >> Debugging >> UNTICK "Just My Code" and step into the designer generated code in a debug session. You can also click the Show All Files button (use the tooltips if you dont know where it is) on the Solution Explorer.. And you can read the various files grouped under the datasets you generate.

Read the DNU link in my signature for help avoiding common trap newbies fall into