Solved

Vb.net check for multiple values

Posted on 2016-10-03
5
38 Views
Last Modified: 2016-10-04
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
Comment
Question by:PeterBaileyUk
  • 4
5 Comments
 
LVL 33

Accepted Solution

by:
it_saige earned 500 total points
ID: 41826563
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
 

Author Comment

by:PeterBaileyUk
ID: 41826628
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
 

Author Comment

by:PeterBaileyUk
ID: 41826634
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
 

Author Comment

by:PeterBaileyUk
ID: 41827979
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41828030
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

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

947 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

22 Experts available now in Live!

Get 1:1 Help Now