Question Help with SQL Query to return NULL fields

[peterc]

HI all,

I have a datagridview, that I have a search capability on. The searching allows for the filtering of the datagridview.

One of the columns in the table has NULL values in the field, which is totally allowable. However when I search, with this field in play, the search results exclude the NULL values, and I want them to be included.

I could change the NULL fields to be 'blank' but do not think this is an ideal scenario. I believe there is someway to have the search results include the NULL values, but am having trouble with sorting that out.

Any help would be appreciated.

The field in question is the vehicle_model.

The text fields can be blank.

 sql.ExecQuery("SELECT * FROM JOBVEHICLE WHERE " &
                            " client like '%" & txtClient.Text & "%' and " &
                            " owner like '%" & txtOwner.Text & "%' and " &
                            " vehicle_make like '%" & txtVehMake.Text & "%' and " &
                            " vehicle_model like '%" & txtVehModel.Text & "%' and " &
                            " vehicle_rego like '%" & txtVehRego.Text & "%' and " &
                            " vehicle_vin like '%" & txtVehVin.Text & "%' and " &
                            " vehicle_chassis like '%" & txtVehChassis.Text & "%' and " &
                            " job_desc like '%" & txtVehJobDesc.Text & "%' " &
                            " order by job_number")

 

[Herman]

Try this instead:

        Dim query = <sql>SELECT *
                         FROM JobVehicle
                         WHERE (client LIKE '%<%= txtClient.Text %>%' OR client IS NULL)
                           AND (owner LIKE '%<%= txtOwner.Text %>%' OR owner IS NULL)                          
                           AND (vehicle_make LIKE '%<%= txtVehMake.Text %>%' OR vehicle_make IS NULL)
                           AND (vehicle_model LIKE '%<%= txtVehModel.Text %>%' OR vehicle_model IS NULL)
                           AND (vehicle_rego LIKE '%<%= txtVehRego.Text %>%' OR vehicle_rego IS NULL)
                           AND (vehicle_vin LIKE '%<%= txtVehVin.Text %>%' OR vehicle_vin IS NULL)
                           AND (vehicle_chassis LIKE '%<%= txtVehChassis.Text %>%' OR vehicle_chassis IS NULL)
                           AND (job_desc LIKE '%<%= txtVehJobDesc.Text %>%' OR job_desc IS NULL)
                         ORDER BY job_number ASC;</sql>