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.Se lectedRows (i).Cells( "StrCommon ").Value and getting the grouping somehow. ive started by adding a for loop.
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.Se
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
ee1.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
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
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
ASKER
rearrange it a little but its now working fine. I struggled but had to get over the learning curve anyway thank you
ASKER
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)