VB.Net: Can't find control type from a collection of controls

Question

I’m still new to VB.NET, and trying to find the correct way to simplify some code (with the understanding that it will add to the runtime complexity).

I’ve defined a Windows Form with a number of fields. The user enters data into those textboxes, checkboxes, etc., then hits the form’s “Search” button. My existing (working) code looks at the first input field and passes 4 (or optionally 5) parameters to my BuildSearch function, which returns an SQL Where clause segment based on that first input field. It then repeats the procedure for the remaining input fields, concatenating all of the SQL segments with “ AND ”, before submitting the request to the server.

My existing code looks like this:

If TxtSort_Key.Text <> "" Or LblSort_KeyOp.Text <> "" Then g$ = g$ & BuildSearch("Sort_Key", "", LblSort_KeyOp.Text, TxtSort_Key)   ' Sort_Key

If TxtFullName.Text <> "" Or LblFullNameOp.Text <> "" Then g$ = g$ & BuildSearch("LastName", "", LblFullNameOp.Text, TxtFullName)   ' LastName

There are 19 of these IF statements, one for each of the input fields on the form. The first two fields passed to BuildSearch provide text for the SQL statement. The remaining 2 parameters get text from the various controls to be used in the SQL statement.

Similar code will be used in other forms, using different field names. So rather than coding several more bunches of these IF statements for each form (and as a learning exercise), I’d like to build a Collection in each form containing the 4 (or 5) pieces of data for each input field, then generate the IF statements dynamically. Here’s what I’ve done in the first module:

Public Class FormFld
    Public Property ScrField As Control
    Public Property ScrLabel As Control
    Public Property DbField As String
    Public Property DbNumeric As Boolean
End Class

Public Class FrmSearch
    Private ReadOnly FormFlds As New List(Of FormFld) From
        {
    New FormFld With {.ScrField = TxtSort_Key, .ScrLabel = LblSort_KeyOp, .DbField = "Sort_Key", .DbNumeric = False}} ',
    New FormFld With {.ScrField = TxtFullName, .ScrLabel = LblFullNameOp.Text, .DbField = "LastName", .DbNumeric = False},
    ...

I was expecting to be able to do:

For Each formfld In FormFlds
    With formfld

        ' Examine .ScrField to determine the type of control, then extract the proper field and determine the
        ' proper condition to generate, e.g., TxtSort_Key.Text <> "", or ChkVolunteer.Checked…, etc.
    End With
Next

But when setting a breakpoint there and looking at ScrLabel in the Locals watch, I don’t see anything that tells me the name of the control or what type of control it is: Locals window

Is what I’m trying to do possible? If so, could someone give me a hint as to find the type of control? I appreciate any help you can provide.

Answer 1

I would suggest that you just create a single query that incorporates all possible filters and pass nulls to effectively ignore certain parameters. This may slow the query down a bit but, unless there's a lot of data, it shouldn't be significant. Here's a simple example with two filter fields:

SELECT *
FROM SomeTable
WHERE (@Column1 IS NULL OR Column1 = @Column1)
AND (@Column2 IS NULL OR Column2 = @Column2)

You can then pass DBNull.Value for both parameters to get all records, pass DBNull.Value for one of the parameters to filter only by the other or pass values for both parameters to filter by both, e.g.

With myCommand.Parameters
    .Add("@Column1", SqlDbType.VarChar, 50).Value = If(TextBox1.TextLength = 0, CObj(DBNull.Value), TextBox1.Text)
    .Add("@Column2", SqlDbType.VarChar, 50).Value = If(TextBox2.TextLength = 0, CObj(DBNull.Value), TextBox2.Text)
End With

Note that the If operator used here requires both potential return values to be the same type or assignable to the same type. That is not the case for DBNull and String so one or the other must be cast as type Object.