Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
1 Solution
 
it_saigeDeveloperCommented:
I believe that this might be what you are after:
Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
	Dim deletions As New List(Of String)

	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
			Else
				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)
					cmdInsert.ExecuteNonQuery()
				End Using
			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
	End Using

	MessageBox.Show("Linking finished")
End Sub

Open in new window


-saige-
0
 
PeterBaileyUkAuthor Commented:
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)
ex now
0
 
PeterBaileyUkAuthor Commented:
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

0
 
PeterBaileyUkAuthor Commented:
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

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now