I use this gode in excel to generate all possiable combination in Range A1:C3. I have attempted to modify it to work in my application with a 3x3 datagridwview and to add the combinations to another datagridview.
This is the code I have in my app but does not get all possiable combinations. And seems to be there should be a shorter code to get this to work, like the code I have for excel. If anyone could help that would be great.
VB.NET:
Private Sub GenerateNumbers2()
Dim i As Long
Dim j As Long
Dim k As Long
[E:E].ClearContents
With CreateObject("Scripting.Dictionary")
For i = 0 To 8
For j = 0 To 8
For k = 0 To 8
If i <> j And j <> k And i <> k Then
.Item("'" & Cells(i \ 3 + 1, i Mod 3 + 1) _
& Cells(j \ 3 + 1, j Mod 3 + 1) _
& Cells(k \ 3 + 1, k Mod 3 + 1)) = 1
End If
Next
Next
Next
[E1].Resize(.Count) = WorksheetFunction.Transpose(.Keys)
End With
End SubThis is the code I have in my app but does not get all possiable combinations. And seems to be there should be a shorter code to get this to work, like the code I have for excel. If anyone could help that would be great.
VB.NET:
Private Sub GenerateNumbers()
Dim A As Long
Dim B As Long
Dim C As Long
Dim flag As Boolean = False
Dim iNumberCount = 0
Dim sDate As String = "Never"
Try
cn = New ADODB.Connection
With cn
.Open(sCnn)
For A = 0 To 2
For B = 0 To 2
For C = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(C).Cells("PICKS3").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(C).Cells("PICKS3").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
For A = 0 To 2
For C = 0 To 2
For B = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(B).Cells("PICKS2").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(B).Cells("PICKS2").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
For B = 0 To 2
For A = 0 To 2
For C = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(C).Cells("PICKS3").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(C).Cells("PICKS3").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
For B = 0 To 2
For C = 0 To 2
For A = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(A).Cells("PICKS1").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(A).Cells("PICKS1").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
For C = 0 To 2
For A = 0 To 2
For B = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(B).Cells("PICKS2").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(A).Cells("PICKS1").Value & dgvPicks.Rows(B).Cells("PICKS2").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
For C = 0 To 2
For B = 0 To 2
For A = 0 To 2
For Each row As DataGridViewRow In dgvGeneratedPicks.Rows
If row.Cells("PICK").Value = dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(A).Cells("PICKS1").Value Then
flag = True
Exit For
End If
Next
If flag = False Then
iNumberCount = 0
sDate = "Never"
sSQL = "SELECT PICK_THREE.Time_Stamp, PICK_THREE.Day_Evening, Format([Time_Stamp],'dddd') AS DayOfWeek FROM PICK_THREE WHERE (((PICK_THREE.Number1)=" & dgvPicks.Rows(C).Cells("PICKS3").Value & ") AND ((PICK_THREE.Number2)=" & dgvPicks.Rows(B).Cells("PICKS2").Value & ") AND ((PICK_THREE.Number3)=" & dgvPicks.Rows(A).Cells("PICKS1").Value & ")) ORDER BY PICK_THREE.Time_Stamp DESC;"
rs = New ADODB.Recordset
With rs
.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockBatchOptimistic)
If (Not .EOF) Then
iNumberCount = rs.RecordCount
sDate = Format(rs.Fields("Time_Stamp").Value, "MM/dd/yyyy")
End If
With dgvGeneratedPicks.Rows
.Add(dgvPicks.Rows(C).Cells("PICKS3").Value & dgvPicks.Rows(B).Cells("PICKS2").Value & dgvPicks.Rows(A).Cells("PICKS1").Value, iNumberCount, sDate)
lblGeneratedCount.Text = "Generated Number Count: " & .Count
Application.DoEvents()
End With
.Close()
End With
rs = Nothing
End If
flag = False
Next
Next
Next
.Close()
End With
cn = Nothing
With dgvGeneratedPicks
.Sort(.Columns("TIMESOUT"), System.ComponentModel.ListSortDirection.Descending)
.CurrentCell = .Rows(0).Cells(0)
End With
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
