Solved

Help with creating  concatenating like statement fron DatagridView

Posted on 2014-02-01
21
229 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 62

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 62

Expert Comment

by:Fernando Soto
ID: 39827679
Can you please post the actual code that you are using.
0
 

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 62

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 62

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now