How to query with firebird parameters and conditions

Question

I'm trying to build a function on a single button, to search my database based on the selections that are made in multiple comboboxes. I have four comboboxes that can be null or valued. My final result should be to be able to filter the datagridview by enhancing the comboboxes and thus obtain dynamic and concatenated queries.

Private Sub Button10_Click_1(sender As Object, e As EventArgs) Handles Button10.Click
Dim conn As New 

FbConnection("User=SYSDBA;Password=masterkey;Database=..\DB.gdb;DataSource=localhost;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;")
    Dim startdate As String = DateTimePicker1.Value.ToString("dd.MM.yyyy")
    Dim enddate As String = Microsoft.VisualBasic.DateAdd(DateInterval.Day, 1, DateTimePicker2.Value).ToString("dd.MM.yyyy")
    Dim adapter As New FbDataAdapter("SELECT * from ORDER WHERE DATEORDER BETWEEN @StartDate AND @EndDate AND ZIPCODE = CASE WHEN  @Param1 IS NULL THEN ZIPCODE ELSE @Param1 END ", conn)
    
    adapter.SelectCommand.Parameters.AddWithValue("@Param1", ComboBox5.Text)
    adapter.SelectCommand.Parameters.AddWithValue("@Param2", ComboBox3.Text)

    Dim table As New DataTable

    adapter.Fill(table)
    DataGridView1.DataSource = table
End Sub

jmcilhinney · Answer 1 · 2021-05-10T02:42:07.623

In general, you can write a query with optional parameters like this:

Dim query = "SELECT *
             FROM MyTable
             WHERE (@Column1 IS NULL OR Column1 = @Column1)
             AND (@Column2 IS NULL OR Column2 = @Column2)"
Dim command As New SqlCommand(query, connection)

With command.Parameters
    .Add("@Column1", SqlDbType.VarChar, 50).Value = If(ComboBox1.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox1.Text)
    .Add("@Column2", SqlDbType.VarChar, 50).Value = If(ComboBox2.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox2.Text)
End With

If no item is selected in a ComboBox then the corresponding parameter is set to NULL and the corresponding criterion matches all records, effectively ignoring that parameter.

The code above is written for SQL Server and I don't use Firebird so I'm not sure exactly what details would change, but the principle holds for any database. If the Firebird provider doesn't support named parameters, which is the case for Access databases, then one change required would be that you need to add two parameters per column rather than one, e.g.

Dim query = "SELECT *
             FROM MyTable
             WHERE (@Column1A IS NULL OR Column1 = @Column1B)
             AND (@Column2A IS NULL OR Column2 = @Column2B)"
Dim command As New SqlCommand(query, connection)

With command.Parameters
    .Add("@Column1A", SqlDbType.VarChar, 50).Value = If(ComboBox1.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox1.Text)
    .Add("@Column1B", SqlDbType.VarChar, 50).Value = If(ComboBox1.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox1.Text)
    .Add("@Column2A", SqlDbType.VarChar, 50).Value = If(ComboBox2.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox2.Text)
    .Add("@Column2B", SqlDbType.VarChar, 50).Value = If(ComboBox2.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox2.Text)
End With

How to query with firebird parameters and conditions

1 Answers1