Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Vb.net check for multiple values

I have created some code that works (am in vb studio) provided the variable StrCommon is limited to 1 string value.

The code has been designed so that if a user wants to relink fields then they can, it just deletes the previous entries. I soon discovered that in some cases strcommon could have more than one value.
so in the embedded picture my desire is to link the selected codes but first I have to get the first strcommon value  'XC90 SE Ocean Race'  search and delete, then 'xc90 vor premium'  and search and delete.

My program in its current state only allows for 1 strcommon value and I am not sure how to change that I know it relies on this:
DataGridViewStringsBulk.SelectedRows(i).Cells("StrCommon").Value  and getting the grouping somehow. ive started by adding a for loop.

 Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
        Dim StrCommon As String
        Dim StrSearch As String
        Dim StrProcName As String
        Dim i As Integer
        Dim cellcount As Int16
        cellcount = 0

        i = 0
        StrCommon = ""
        StrSearch = ""
        StrProcName = "usp_DeleteShortDescLinkWords"

        Dim selectedRowCount As Integer = DataGridViewStringsBulk.Rows.GetRowCount(DataGridViewElementStates.Selected)
        If selectedRowCount > 0 Then
            'check if datagridview StrCommen is populated if so call usp to delete the rows from link table

            If Convert.IsDBNull(DataGridViewStringsBulk.SelectedRows(i).Cells("StrCommon").Value) = False Then


                'pass value to usp for deletion
                'strcommon populated so find value
                StrSearch = DataGridViewStringsBulk.SelectedRows(i).Cells("StrCommon").Value

                cellcount = DataGridViewStringsBulk.SelectedRows(i).Cells.Count

                For cellcount = 0 To cellcount
                    'not sure what to do here to get effectively grouping of strcommon

                Next cellcount


                Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
                    Using cmdInsert As New SqlCommand(StrProcName, cnSql)
                        cmdInsert.CommandType = CommandType.StoredProcedure
                        cnSql.Open()
                        cmdInsert.CommandTimeout = 0
                        cmdInsert.Parameters.AddWithValue("@Word", StrSearch)
                        cmdInsert.ExecuteScalar()


                    End Using
                End Using
            End If


            'all below works fine
            'strcommon not populated so go ahead and create the links
            Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
                Using cmdInsert As New SqlClient.SqlCommand
                    cmdInsert.Connection = cnSql
                    cnSql.Open()
                    cmdInsert.CommandTimeout = 0
                    For i = 0 To selectedRowCount - 1
                        'get rows
                        If i = 0 Then
                            StrCommon = DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value
                        End If
                        cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value & "','" & StrCommon & "'" & ")"
                        cmdInsert.ExecuteNonQuery()
                    Next i
                End Using
            End Using
        End If
        MessageBox.Show("Linking finished")
    End Sub

Open in new window

ee1.JPG
ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

It didnt quite work,in my initial code when it creates the links it chose the first strshort and used that as the strcommon for this reason:

this is what one client calls an ocean race:
'XC90 SE Ocean Race' so its field strshort is as you see here
another client calls the vehicle:
'XC90 VOR' so when I highlight the ones i want to link it picked the first strshort and made that the strcommon for the whole group.
so when linking it would (in this example)
strshort                                        strcommon
'XC90 SE Ocean Race'                'XC90 SE Ocean Race'
 'XC90 VOR'                                  'XC90 SE Ocean Race'

it didnt matter which one had been used as long as the common definition was there
strshort                                        strcommon
'XC90 SE Ocean Race'                'XC90 VOR'
 'XC90 VOR'                                 'XC90 VOR'

in the current 'new code' it just duplicates (however I hadnt mentioned that)
User generated image
My original linking part worked well as expected
    '        'all below works fine
    '        'strcommon not populated so go ahead and create the links
    '        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
    '            Using cmdInsert As New SqlClient.SqlCommand
    '                cmdInsert.Connection = cnSql
    '                cnSql.Open()
    '                cmdInsert.CommandTimeout = 0
    '                For i = 0 To selectedRowCount - 1
    '                    'get rows
    '                    If i = 0 Then
    '                        StrCommon = DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value
    '                    End If
    '                    cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & DataGridViewStringsBulk.SelectedRows(i).Cells("Strshort").Value & "','" & StrCommon & "'" & ")"
    '                    cmdInsert.ExecuteNonQuery()
    '                Next i
    '            End Using
    '        End Using
    '    End If
    '    MessageBox.Show("Linking finished")
    'End Sub

Open in new window

Ive moved things around a little, it has to do the deletions first  based on strcommon, so ive enacted that. once the deletions are done it can then do the insert. The insert strcommon can be the first strshort it gets from the selected as that will be the common name that links them where the descriptions are different. I dont think its far off now but I think the insert line has a flaw where I tried to add strcommon in it.

For some reason its still making strcommon = strshort in all instances despite my small line of code.
  If StrCommonSet = False Then
                    StrTemp = row.Cells("Strshort").Value
                    StrCommonSet = True
                End If

Open in new window


    Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
        Dim deletions As New List(Of String)
        Dim StrCommonSet As Boolean
        Dim StrTemp As String
        StrTemp = ""

        StrCommonSet = False
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
            cnSql.Open()
            For Each row As DataGridViewRow In DataGridViewStringsBulk.SelectedRows
                If Not Convert.IsDBNull(row.Cells("StrCommon").Value) Then

                    If Not deletions.Contains(row.Cells("StrCommon").Value, StringComparer.OrdinalIgnoreCase) Then
                        deletions.Add(row.Cells("StrCommon").Value)
                    End If


                End If
            Next

            For Each term In deletions
                Using cmdInsert As New SqlCommand("usp_DeleteShortDescLinkWords", cnSql)
                    cmdInsert.CommandType = CommandType.StoredProcedure
                    cmdInsert.CommandTimeout = 0
                    cmdInsert.Parameters.AddWithValue("@Word", term)
                    cmdInsert.ExecuteScalar()
                End Using
            Next


            For Each row As DataGridViewRow In DataGridViewStringsBulk.SelectedRows
                If StrCommonSet = False Then
                    StrTemp = row.Cells("Strshort").Value
                    StrCommonSet = True
                End If


                Using cmdInsert As New SqlClient.SqlCommand
                    cmdInsert.Connection = cnSql
                    cmdInsert.CommandTimeout = 0
                    cmdInsert.CommandText = String.Format("INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('{0}','{0}')", row.Cells("Strshort").Value, StrTemp)
                    cmdInsert.ExecuteNonQuery()
                End Using

            Next

        End Using

        MessageBox.Show("Linking finished")
    End Sub

Open in new window

rearrange it a little but its now working fine. I struggled but had  to get over the learning curve anyway thank you