I have problem in my code where it only triggers the else condition, if I remove the else condition I get the proper output. I need the else condition because when I switch tabs it should reset if the other tab doesn’t contain any data.
Sub filterdb()
Dim dgvposition As New DataGridView
Dim query As String = "SELECT POSITION FROM log_inventory WHERE BUILDING=@building AND STORAGE=@storage AND RACK=@rack"
con.open
Dim cmd As New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@building", lbl_building.Text)
cmd.Parameters.AddWithValue("@storage", lbl_storage.Text)
cmd.Parameters.AddWithValue("@rack", lbl_rack.Text)
Dim adapter As New SqlDataAdapter(cmd)
Dim table As New DataTable
adapter.Fill(table)
DataGridView1.DataSource = table
con.close
End Sub
Sub check_available()
Try
Dim btnname As String
For Each row As DataGridViewRow In DataGridView1.Rows
For Each btn In TabPage4.Controls
If TypeOf btn Is Button Then
btnname = btn.text
If row.Cells(0).Value = btn.Text Then
btn.BackColor = Color.Red
btn.Enabled = False
Else
btn.BackColor = Color.Green
btn.Enabled = True
End If
End If
Next
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Jhoseph Eugene Caleon Reyes is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
The below code includes a number of improvements, even if it doesn’t end up helping with the question. That is, you should use this even if it doesn’t fix the question.
But I think it will help with the question by using a different strategy for matching the buttons, and moving that part to a separate pass through the data.
Sub filterdb()
Dim query As String = "SELECT POSITION FROM log_inventory WHERE BUILDING=@building AND STORAGE=@storage AND RACK=@rack"
Dim table As New DataTable
' connections should be **short-lived**!
Using con As New SqlConnection("connection string here"), _
cmd As New SqlCommand(query, con), _
adapter As New SqlAdapter(cmd)
' Be wary of AddWithValue()!
' Better to set explicit DB types than let ADO.NET try to infer them.
' I had to guess here. You should use the actual types/lengths from the DB
cmd.Parameters.Add("@building", SqlDbType.NVarChar, 40).Value = lbl_building.Text
cmd.Parameters.Add("@storage", SqlDbType.NVarChar, 60).Value = lbl_storage.Text
cmd.Parameters.Add("@rack", SqlDbType.NVarChar, 10).Value = lbl_rack.Text
adapter.Fill(table) ' No open/close needed for .Fill()
End Using
DataGridView1.DataSource = table
End Sub
' Nothing in here was gonna throw, so I removed try/catch
Sub check_available()
' suspend/resume to stop flickering as the form redraws
SuspendLayout()
' Set ALL buttons to the default
' Layout is suspended, so user can't see this yet
For Each btn In TabPage4.Controls.OfType(Of Button)()
btn.BackColor = Color.Green
btn.Enabled = True
Next
' Find just the matching buttons
Dim ButtonKeys = DataGridView1.Rows.Select(Function(r) r.Cells(0).Value.ToString())
Dim buttons = TabPage4.Controls.OfType(Of Button)().
InsertsectBy(ButtonKeys, Function(b) b.Text )
' Put back only the matching buttons.
' Yes, this is an extra pass through the set... but it
' lets the CPU get better branch prediction so it's usually still faster.
For Each btn In buttons
btn.BackColor = Color.Red
btn.Enabled = False
Next
ResumeLayout()
End Sub
If it still doesn’t work, you should make sure the strings in the buttons really do match the strings from the grid, and adjusting as needed.
For example, the selector from the grid could look like this if needed:
Dim ButtonKeys = DataGridView1.Rows.Select(Function(r) r.Cells(0).Value.ToString().Trim().ToLower())
And instead of just b.Text the selector for the button could be b.Text.Trim().ToLower()
But I also wonder if the problem is one of timing and forms memory optimization. That is, at the point where this runs, the DataGridView might not have any rows. Knowing they are not on the screen, the form may have thrown them out to save memory. In that case, you may need to compare with the DB directly, or you may want to change the scope for the DataTable you set as the source so it remains as a member of the class, and compare to that.
If it were me, I’d also convert the filterdb() method to a function that returns the datatable as a value and accepts the textbox contents as arguments. Then the calling code would be responsible for assigning the result to the grid’s DataSource. (Also: give it a more-specific name):
Function GetInventoryPositions(building As String, storage As String, rack As String) As DataTable
Dim query As String = "SELECT POSITION FROM log_inventory WHERE BUILDING=@building AND STORAGE=@storage AND RACK=@rack"
Dim result As New DataTable
' connections should be **short-lived**!
Using con As New SqlConnection("connection string here"), _
cmd As New SqlCommand(query, con), _
adapter As New SqlAdapter(cmd)
' Be wary of AddWithValue()!
' Better to set explicit DB types than let ADO.NET try to infer them.
' I had to guess here. You should use the actual types/lengths from the DB
cmd.Parameters.Add("@building", SqlDbType.NVarChar, 40).Value = building
cmd.Parameters.Add("@storage", SqlDbType.NVarChar, 60).Value = storage
cmd.Parameters.Add("@rack", SqlDbType.NVarChar, 10).Value = rack
adapter.Fill(table) ' No open/close needed for .Fill()
End Using
Return table
End Function
And then the calling code would now look like this:
DataGridView1.DataSource = GetInventoryPositions(lbl_building.Text, lbl_storage.Text, lbl_rack.Text)
1