Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with creating  concatenating like statement fron DatagridView

Posted on 2014-02-01
21
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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