index > Windows Forms Data Controls and Databinding > Capture data from datagridview with a click of a button

Capture data from datagridview with a click of a button

Hi,

I am able to display data in a datagridview in vb.net(windows form) using Microsoft access. My question is how do I capture the data(lets say Email column) using checkboxes to select rows with a click of a button(btnAdd) and display them in a textbox(txtTo) of another form(form1). I have 3 columns which is CustName, Email, Selection(Boolean). After i checked a few rows of the checkboxes, there is an error:-

Message Error: IndexOutOfRangeException was unhandled
There is no row at position 3.

Below are my codes for my windows form.

Please advise.

Thank you.

Imports System.Data
Imports System.Data.OleDb

Public Class Form2

    Dim conn As OleDbConnection

    'create data adapter
    Dim da As OleDbDataAdapter

    'create dataset
    Dim ds As DataSet = New DataSet

    'Set up connection string
    Dim cnString As String

    Dim sqlQRY As String

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\User.mdb"

        sqlQRY = "SELECT CustName, Email, Selection FROM CustDetail"

        conn = New OleDbConnection(cnString)

        Try
            ' Open connection
            conn.Open()

            da = New OleDbDataAdapter(sqlQRY, conn)

            'create command builder
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)

            'fill dataset
            da.Fill(ds, "CustDetail")

            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "CustDetail"

        Catch ex As OleDbException
            MsgBox(ex.ToString)
        Finally
            ' Close connection
            conn.Close()
        End Try

    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
       

        Dim i As Integer

        For i = 0 To (DataGridView1.Rows.Count - 1)
            If ds.Tables(0).Rows(i).Item("Selection") = True Then
                Form1.TextBox1.Text = ds.Tables(0).Rows(i).Item("Email").ToString & vbCrLf
            End If
        Next



    End Sub
End Class

Edited byMd Azmil Thursday, July 16, 2009 3:36 AM
Edited byMd Azmil Thursday, July 16, 2009 3:37 AM
Edited byMd Azmil Thursday, July 16, 2009 3:40 AM
Edited byMd Azmil Thursday, July 16, 2009 4:14 AM

Md Azmil Thursday, July 16, 2009 3:01 AM

Hi Md Azmil,

Please try to change "For i = 0 To (DataGridView1.Rows.Count - 1)" TO "For i = 0 To (ds.Tables(0).Rows.Count - 1)". I think the problem is because the DataGridView has one more row than DataTable, that row is the NewRow with star at the row header.

Sincerely,
Kira Qian

Please mark the replies as answers if they help and unmark if they don't.

Marked As Answer byMd Azmil Tuesday, July 21, 2009 2:02 AM

Kira Qian Friday, July 17, 2009 8:51 AM

Hi Md Azmil,

For this code:
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

Dim i As Integer

For i = 0 To (ds.Tables(0).Rows.Count - 1)
If DataGridView1.Rows(i).Cells("Selection").Value IsNot Nothing Then
If DataGridView1.Rows(i).Cells("Selection").Value = True Then
Form2.RichTextBox1.Text = DataGridView1.Rows(i).Cells("Email").Value.ToString & vbCrLf
End If
End If
Next
End Sub

I just guess you will set the RichTextBox1.Text with Email for the rows checked, if the cell is System.DBNull.Value, nothing need to do. So please replace
"If DataGridView1.Rows(i).Cells("Selection").Value IsNot Nothing Then"
with "If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then"

Please understand I just guess from your code, I cannot know exactly function of your application. If I misunderstand you, please tell me the detail of the problem, If possible, send me the project. v-kiqian@microsoft.com

Sincerely,
Kira Qian

Please mark the replies as answers if they help and unmark if they don't.

Edited byKira QianMSFT, ModeratorMonday, July 20, 2009 7:32 AM
Marked As Answer byMd Azmil Tuesday, July 21, 2009 2:03 AM

Kira Qian Monday, July 20, 2009 7:00 AM

Hi all,

To contribute to the forum, I will post my final working codes here. I hope these codes can help anyone who is having the same problem as me one way or another. Good Luck!

Best Regards,
Md Azmil

Imports System.Data

Imports System.Data.SqlClient



Public Class Form1



    Private da As SqlDataAdapter

    Private conn As SqlConnection

    Private bsource As BindingSource = New BindingSource()

    Private ds As DataSet

    Private sql As String

    Private cnString As String

    'Private cusTable As DataTable = New DataTable()

    Private form2 As Form2 = New Form2()



    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim connectionString As String = "Data Source=ITCU-21\SQLEXPRESS;Initial Catalog=User;" & "Integrated Security=SSPI;"

        conn = New SqlConnection(connectionString)

        sql = "SELECT * FROM CustDetail"



        da = New SqlDataAdapter(sql, conn)

        conn.Open()

        ds = New DataSet()

        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

        da.Fill(ds, "CustDetail")


        DataGridView1.DataSource = ds.Tables("CustDetail")

        'cusTable.Columns.Add("Selection", GetType(Boolean))

        'cusTable.Columns.Add("Email", GetType(String))



        'cusTable.Rows.Add(True, "test1@microsoft.com")

        'cusTable.Rows.Add(False, "test2@microsoft.com")

        'cusTable.Rows.Add(System.DBNull.Value, "test3@microsoft.com")

        'DataGridView1.DataSource = cusTable

        form2.Show()

    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim i As Integer

        Dim ds As DataTable = CType(DataGridView1.DataSource, DataTable)

        form2.RichTextBox1.Text = ""

        For i = 0 To (ds.Rows.Count - 1)

            If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then

                If DataGridView1.Rows(i).Cells("Selection").Value = True Then

                    form2.RichTextBox1.Text += DataGridView1.Rows(i).Cells("Email").Value.ToString() & vbCrLf

                End If

            End If

        Next

    End Sub

End Class

Marked As Answer byKira QianMSFT, ModeratorTuesday, July 21, 2009 2:04 AM

Md Azmil Tuesday, July 21, 2009 2:00 AM

Please mark the replies as answers if they help and unmark if they don't.

Marked As Answer byMd Azmil Tuesday, July 21, 2009 2:02 AM

Kira Qian Friday, July 17, 2009 8:51 AM

I've edited the codes you've corrected but there is an error when i click the 'Add ' button after checking a few checkboxes:-

InvalidCastException was unhandled
Operator '=' is not defined for type 'DBNull' and type 'Boolean'.

Please advise.

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private da As SqlDataAdapter
    Private conn As SqlConnection
    Private bsource As BindingSource = New BindingSource()
    Private ds As DataSet
    Private sql As String
    Private cnString As String

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String = "Data Source=ITCU-21\SQLEXPRESS;Initial Catalog=User;" & "Integrated Security=SSPI;"
        conn = New SqlConnection(connectionString)
        sql = "SELECT * FROM CustDetail"

        da = New SqlDataAdapter(sql, conn)
        conn.Open()
        ds = New DataSet()
        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Fill(ds, "CustDetail")
        bsource.DataSource = ds.Tables("CustDetail")
        DataGridView1.DataSource = bsource

    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click


        Dim i As Integer
        

        For i = 0 To (ds.Tables(0).Rows.Count - 1)
            If DataGridView1.Rows(i).Cells("Selection").Value = True Then
                Form2.TextBox1.Text = DataGridView1.Rows(i).Cells("Email").Value.ToString & vbCrLf
            End If
        Next
    End Sub
End Class

Md Azmil Monday, July 20, 2009 2:13 AM

Hi Md Azmil,

That is because the last row of your DataGridView is the "NewRow" which hold the cell value as null. Put an If statement around it can fix the problem.
For i = 0 To (ds.Tables(0).Rows.Count - 1)
If DataGridView1.Row(i).Cells("Selection").Value IsNot Nothing Then
If DataGridView1.Rows(i).Cells("Selection").Value = True Then
Form2.TextBox1.Text = DataGridView1.Rows(i).Cells("Email").Value.ToString & vbCrLf
End If
End If
Next

Sincerely,
Kira Qian

Please mark the replies as answers if they help and unmark if they don't.

Kira Qian Monday, July 20, 2009 2:45 AM

I edited the codes and it still gives me the same error.

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Private da As SqlDataAdapter
    Private conn As SqlConnection
    Private bsource As BindingSource = New BindingSource()
    Private ds As DataSet
    Private sql As String
    Private cnString As String

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String = "Data Source=ITCU-21\SQLEXPRESS;Initial Catalog=User;" & "Integrated Security=SSPI;"
        conn = New SqlConnection(connectionString)
        sql = "SELECT * FROM CustDetail"

        da = New SqlDataAdapter(sql, conn)
        conn.Open()
        ds = New DataSet()
        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Fill(ds, "CustDetail")
        bsource.DataSource = ds.Tables("CustDetail")
        DataGridView1.DataSource = bsource

    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

        Dim i As Integer
        
        For i = 0 To (ds.Tables(0).Rows.Count - 1)
            If DataGridView1.Rows(i).Cells("Selection").Value IsNot Nothing Then
                If DataGridView1.Rows(i).Cells("Selection").Value = True Then
                    Form2.RichTextBox1.Text = DataGridView1.Rows(i).Cells("Email").Value.ToString & vbCrLf
                End If
            End If
        Next
    End Sub
End Class

Md Azmil Monday, July 20, 2009 6:40 AM

Which line did you get the error?

Please mark the replies as answers if they help and unmark if they don't.

Kira Qian Monday, July 20, 2009 6:42 AM

this line:-

If DataGridView1.Rows(i).Cells("Selection").Value = True Then

Md Azmil Monday, July 20, 2009 6:44 AM

Is DataGridView1.Rows(i).Cells("Selection").Value = System.DBNull.Value? If so, set

If DataGridView1.Rows(i).Cells("Selection").Value <> System.DBNull.Value Then

Please mark the replies as answers if they help and unmark if they don't.

Edited byKira QianMSFT, ModeratorMonday, July 20, 2009 6:49 AM
Edited byKira QianMSFT, ModeratorMonday, July 20, 2009 6:49 AM
Edited byKira QianMSFT, ModeratorMonday, July 20, 2009 6:51 AM

Kira Qian Monday, July 20, 2009 6:48 AM

how do i know if DataGridView1.Rows(i).Cells("Selection").Value = System.DBNull.Value?

And where should i insert this line in:-

If DataGridView1.Rows(i).Cells("Selection"
).Value <>
 System.DBNull.Value Then

Md Azmil Monday, July 20, 2009 6:52 AM

Please mark the replies as answers if they help and unmark if they don't.

Edited byKira QianMSFT, ModeratorMonday, July 20, 2009 7:32 AM
Marked As Answer byMd Azmil Tuesday, July 21, 2009 2:03 AM

Kira Qian Monday, July 20, 2009 7:00 AM

Sure. I've just send a copy of my project to your email.

Md Azmil Monday, July 20, 2009 7:10 AM

Imports System.Data

Imports System.Data.SqlClient



Public Class Form1



    Private da As SqlDataAdapter

    Private conn As SqlConnection

    Private bsource As BindingSource = New BindingSource()

    Private ds As DataSet

    Private sql As String

    Private cnString As String

    'Private cusTable As DataTable = New DataTable()

    Private form2 As Form2 = New Form2()



    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim connectionString As String = "Data Source=ITCU-21\SQLEXPRESS;Initial Catalog=User;" & "Integrated Security=SSPI;"

        conn = New SqlConnection(connectionString)

        sql = "SELECT * FROM CustDetail"



        da = New SqlDataAdapter(sql, conn)

        conn.Open()

        ds = New DataSet()

        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)

        da.Fill(ds, "CustDetail")


        DataGridView1.DataSource = ds.Tables("CustDetail")

        'cusTable.Columns.Add("Selection", GetType(Boolean))

        'cusTable.Columns.Add("Email", GetType(String))



        'cusTable.Rows.Add(True, "test1@microsoft.com")

        'cusTable.Rows.Add(False, "test2@microsoft.com")

        'cusTable.Rows.Add(System.DBNull.Value, "test3@microsoft.com")

        'DataGridView1.DataSource = cusTable

        form2.Show()

    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim i As Integer

        Dim ds As DataTable = CType(DataGridView1.DataSource, DataTable)

        form2.RichTextBox1.Text = ""

        For i = 0 To (ds.Rows.Count - 1)

            If DataGridView1.Rows(i).Cells("Selection").Value IsNot System.DBNull.Value Then

                If DataGridView1.Rows(i).Cells("Selection").Value = True Then

                    form2.RichTextBox1.Text += DataGridView1.Rows(i).Cells("Email").Value.ToString() & vbCrLf

                End If

            End If

        Next

    End Sub

End Class

Marked As Answer byKira QianMSFT, ModeratorTuesday, July 21, 2009 2:04 AM

Md Azmil Tuesday, July 21, 2009 2:00 AM

You can use google to search for other answers

Custom Search

More Threads

• how can i convert the interger value to NULL?

• Rotating rows and columns in DataGridView 2.0

• Combobox - pick from list or add new item

• SelectionForeColor in DataGridViewLinkColumn

• tableadapters, odbc, and sybase dates

• Dataset Update filed to NULL??

• Prevent binding to business object properties?

• ComboBox Items Not Showing On Dropdown

• DataGrid - Unbound Mode?

• Sorting problem in DataGridView