Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with creating  concatenating like statement fron DatagridView

Posted on 2014-02-01
21
Medium Priority
?
238 Views
Last Modified: 2014-02-07
Hi,

I used the code below to build several concatenated Like statements depending on rows selected from an C1TrueDBGrid control; However, when attempting to do the same with the DataGridView, the celltext property does not exist. How do I modify the code below to work with the DataGridView?

  Dim xyzw As String
        xyzw = ""
        Dim Srow As Integer
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid2.SelectedRows
            If vc1 > 0 Then
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid2.Columns(0).(Srow) & "*'"
            Else
                xyzw = "'*" & Me.C1TrueDBGrid2.Columns(0).CellText(Srow) & "*'"
            End If
            vc1 = vc1 + 1
        Next

Thanks,

Victor
0
Comment
Question by:vcharles
  • 11
  • 6
  • 4
21 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39827655
Hi vcharles;

SelectedRows returns a collection of rows and so you should be able to do the following.

Dim xyzw As String
xyzw = ""
Dim Srow As DataGridViewRow
Dim vc1 As Integer

For Each Srow In Me.C1TrueDBGrid2.SelectedRows
    If vc1 > 0 Then
        xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Srow(0) & "*'"
    Else
        xyzw = "'*" & Srow(0) & "*'"
    End If
    vc1 = vc1 + 1
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39827671
Hi,

Thanks for the code. I'm getting the following error on "Srow(0)"

Error:  Class 'System.Windows.Forms.DataGridViewRow' cannot be indexed because it has no default property.

Victor
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39827679
Can you please post the actual code that you are using.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:vcharles
ID: 39827770
Hi,

I'm using the code bellow from a button click event. The error occurs on both Srow(0), before I run the project. I need to select the rows from a checkbox in the Grid but I can't see what is selected because of the error.

Dim xyzw As String
xyzw = ""
Dim Srow As DataGridViewRow
Dim vc1 As Integer

For Each Srow In Me.C1TrueDBGrid2.SelectedRows
    If vc1 > 0 Then
        xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Srow(0) & "*'"
    Else
        xyzw = "'*" & Srow(0) & "*'"
    End If
    vc1 = vc1 + 1
Next
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39827797
You state, "I need to select the rows from a checkbox in the Grid", so you are NOT selecting the row by clicking on the very first column which will highlight the row and to select more rows by holding down the control key and clicking on other rows you want? This is what will populate the SelectedRows collection and most likely the cause of your error.
0
 

Author Comment

by:vcharles
ID: 39827862
Hi,

The code in part A is in my Form Load event, the checkbox is included from the code in Part B and I am using the code in Part C to retrieve rows selected (i.e. yxa = 'BEL','CAN','USA; using yxa = String.Join(",", CheckCountryListb.ToArray)). Bu now I need to set another variable (XYZ) with and AND statement for each row selected, similar to the code used with C1TrueDBGrid.

xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid2.Columns(0).(Srow) & "*'"

How do I modify the code to work when selecting rows from the checkbox?


Part A:

        Dim objDataAdapter1 As New OleDb.OleDbDataAdapter("select country from country ORDER BY country", objConnection)        
        Dim objDataSet As New DataSet
        objConnection.Open()
        objDataAdapter1.Fill(objDataSet, "Country")
        C1TrueDBGrid2.DataSource = objDataSet
        C1TrueDBGrid2.DataMember = "Country"
        CheckControlb()


Part B:

 Private Sub CheckControlb()
        Dim CheckBoxCol As New DataGridViewCheckBoxColumn
        With CheckBoxCol
            .Name = "CheckBoxColumn"
            .HeaderText = "Select"
            .Width = 50
        End With
        Me.C1TrueDBGrid2.Columns.Insert(0, CheckBoxCol)
    End Sub

Part C:

   Private Sub C1TrueDBGrid2_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles C1TrueDBGrid2.CellValueChanged
        If e.ColumnIndex = 0 Then

            Try

                If C1TrueDBGrid2.Columns(e.ColumnIndex).Name = "CheckBoxColumn" Then
                    Dim checkCell As DataGridViewCheckBoxCell = _
                        CType(C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn"),  _
                        DataGridViewCheckBoxCell)
                    If CType(checkCell.Value, [Boolean]) = True Then

                        C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn").Style.BackColor = Drawing.Color.Blue
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn").Style.SelectionBackColor = Drawing.Color.GreenYellow
                        CheckCountryListb.Add("'" & C1TrueDBGrid2.Rows(e.RowIndex).Cells(1).Value & "'")

                        'CheckCountryLista.Add(C1TrueDBgrid2.Rows(e.RowIndex).Cells(2).Value)
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.BackColor = Drawing.Color.Blue
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.SelectionBackColor = Drawing.Color.Blue
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.ForeColor = Drawing.Color.White
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.SelectionForeColor = Drawing.Color.Blue

                    Else

                        ' MsgBox("b")
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn").Style.BackColor = Drawing.Color.White
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn").Style.SelectionBackColor = Drawing.Color.White
                        CheckCountryListb.Remove("'" & C1TrueDBGrid2.Rows(e.RowIndex).Cells(1).Value & "'")
                        'CheckCountryLista.Remove(C1TrueDBgrid2.Rows(e.RowIndex).Cells(2).Value)
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.BackColor = Drawing.Color.White
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.SelectionBackColor = Drawing.Color.White
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.ForeColor = Drawing.Color.Black
                        C1TrueDBGrid2.Rows(e.RowIndex).DefaultCellStyle.SelectionForeColor = Drawing.Color.Black

                    End If
                    C1TrueDBGrid2.Invalidate()
                End If


                yxa = String.Join(",", CheckCountryListb.ToArray)
               
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try

        Else
            '  MsgBox("JJJJJJJ")
        End If


        Try


            If C1TrueDBGrid2.Columns(e.ColumnIndex).Name = "CheckBoxColumn" Then

                Dim checkCell As DataGridViewCheckBoxCell = _
                    CType(C1TrueDBGrid2.Rows(e.RowIndex).Cells("CheckBoxColumn"),  _
                    DataGridViewCheckBoxCell)
                If CType(checkCell.Value, [Boolean]) = True Then
                    For i As Integer = 0 To Me.C1TrueDBGrid2.Columns.Count - 1
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells(i).Style.BackColor = Drawing.Color.Blue
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells(i).Style.SelectionBackColor = Drawing.Color.Blue
                    Next
                Else
                    For j As Integer = 0 To Me.C1TrueDBGrid2.Columns.Count - 1
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells(j).Style.BackColor = Drawing.Color.White
                        C1TrueDBGrid2.Rows(e.RowIndex).Cells(j).Style.SelectionBackColor = Drawing.Color.White
                    Next
                End If

                C1TrueDBGrid2.Invalidate()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Thanks,

V.
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39828096
Hi Victor;

I can not locate the orginal code we were looking at in the question. Please mark the location in Part C that you are having issues with.

Please when posting code place cursor in Post text box where you want the code to go. Then in the Tool box click on the word Code. Then without moving the cursor hit the return key two time and past your code between the two markers.
0
 

Author Comment

by:vcharles
ID: 39837855
Hi,

I'm sorry for the late reply. The code below was in my initial post, it not part of the code in my last post, it only works with C1TrueDBGrid. I'm trying to modify it instead of using the code in my last post, but because of the checkbox in the datagrid view, it's not that simple.

Dim xyzw As String
        xyzw = ""
        Dim Srow As Integer
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid2.SelectedRows
            If vc1 > 0 Then
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid2.Columns(0).(Srow) & "*'"
            Else
                xyzw = "'*" & Me.C1TrueDBGrid2.Columns(0).CellText(Srow) & "*'"
            End If
            vc1 = vc1 + 1
        Next

Open in new window


Thanks,

Victor
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39838447
So in the else part, you want to just use the text of first column of first row?

Try

xyzw = "'*" & Me.DataGridView1.Rows(0).Cells(0).Value & "*'"
0
 

Author Comment

by:vcharles
ID: 39838464
I need the text from the first column.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39838739
Change it to

xyzw = "'*" & Srow.Cells(0).Value & "*'"
0
 

Author Comment

by:vcharles
ID: 39840656
Hi,

I'm afraid it did not work, I  received error message "cells is not a member of integer".

on line: xyzw = "'*" & Srow.Cells(0).Value & "*'"

        Dim xyzw As String
        xyzw = ""
        Dim Srow As Integer
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid1.SelectedRows
            If vc1 > 0 Then
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid1.Columns(0).(Srow) & "*'"
            Else
                '  xyzw = "'*" & Me.C1TrueDBGrid1.Columns(0).Cell(Srow) & "*'"
                xyzw = "'*" & Srow.Cells(0).Value & "*'"
            End If
            vc1 = vc1 + 1
        Next
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39840678
I thought you are using datagridview now. If you are then you need to change this line

For Each Srow In Me.C1TrueDBGrid1.SelectedRows

To

For Each Srow In Me.datagridview1.SelectedRows
0
 

Author Comment

by:vcharles
ID: 39840688
I am, just kept the same name.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39840698
Then srow should be a datagridviewrow not integer
0
 

Author Comment

by:vcharles
ID: 39840709
It removed the error , but I get a new error on "Srow" on  line:

xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid1.Columns(0).(Srow) & "*



 Dim xyzw As String
        xyzw = ""
        Dim Srow As DataGridViewRow
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid1.SelectedRows
            If vc1 > 0 Then
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid1.Columns(0).(Srow) & "*'"
            Else
                '  xyzw = "'*" & Me.C1TrueDBGrid1.Columns(0).Cell(Srow) & "*'"
                xyzw = "'*" & Srow.Cells(0).Value & "*'"
            End If
            vc1 = vc1 + 1
        Next

Thanks.
0
 

Author Comment

by:vcharles
ID: 39840747
I fixed the other error using your previous code, but because I'm using a checkbox in the DataView control, it does not recognize rows selected, message box below returns no values when I click on a row.

   Dim xyzw As String
        xyzw = ""
        Dim Srow As DataGridViewRow
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid1.SelectedRows
            If vc1 > 0 Then
                '      xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid1.Columns(0).(Srow) & "*'"
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Srow.Cells(1).Value & "*'"
            Else
                '  xyzw = "'*" & Me.C1TrueDBGrid1.Columns(0).Cell(Srow) & "*'"
                xyzw = "'*" & Srow.Cells(1).Value & "*'"
            End If
            vc1 = vc1 + 1
        Next
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39842696
If you are using checkbox to select rows, you need to loop through Rows not SelectedRows. Then you need to check if the checkbox is selected

   Dim xyzw As String
        xyzw = ""
        Dim Srow As DataGridViewRow
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid1.Rows
          If Srow("CheckboxColumnName") Then
            If vc1 > 0 Then
                '      xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Me.C1TrueDBGrid1.Columns(0).(Srow) & "*'"
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Srow.Cells(1).Value & "*'"
            Else
                '  xyzw = "'*" & Me.C1TrueDBGrid1.Columns(0).Cell(Srow) & "*'"
                xyzw = "'*" & Srow.Cells(1).Value & "*'"
            End If
           End If
            vc1 = vc1 + 1
        Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39842781
Hi,

I am receiving the following :

Error: class system.Windows.Forms.DataGridViewRow cannot be indexed because it is not a default property.

on

Line:
If Srow("Select") Then

Select is the name of the checkbox's column.

Thanks,

Victor
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 39843464
Ah. Confused it with datatable rows

Try

   Dim xyzw As String
        xyzw = ""
        Dim Srow As DataGridViewRow
        Dim vc1 As Integer
        For Each Srow In Me.C1TrueDBGrid1.Rows
          If Srow.Cells("Select").Value Then
            If vc1 > 0 Then
                xyzw = xyzw & "AND {CTRY.CA} LIKE " & "'*" & Srow.Cells(1).Value & "*'"
            Else
                xyzw = "'*" & Srow.Cells(1).Value & "*'"
            End If
           End If
            vc1 = vc1 + 1
        Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39843559
It worked!

Thank You.

Victor
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question