Solved

Vb.net check for multiple values

Posted on 2016-10-03
5
36 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 32

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 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