Solved

Help with creating  concatenating like statement fron DatagridView

Posted on 2014-02-01
21
228 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

746 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

9 Experts available now in Live!

Get 1:1 Help Now