sql - Search database and project results to datagridview

Question

I have an sql database with about 100 different variables. What I basically want is to be able to search the database based on some of the variables either as one by one or as a multiple search. For this purpose I have created a search form in VS 2010 with the desired number of comboboxes (each combobox represents one variable and takes distinct values from the database). Based on which variables the user chose i want the results to be shown on a datagridview table.

The code for some reason is wrong and I can't figure it out. The problem is located da.Fill(datatable1) with an unexpected error. Is there any alternatives and provide some guidelines to complete the idea?

Private Sub display_datagrid()
    con2.ConnectionString = "Server= DESKTOP-KQG48T5SQL2021; Database = Fotilas2021; Integrated Security = True;"
    con2.Open()

    
    Dim cmd2 As New SqlCommand("select Name,IdentityNumber,FatherSurname,LocalCelebDay,BirthNomos,NumofChilds,MilitaryService,CurrentCountry,CurrentCity,CellNumber,DateAdd,Occupation,OccupPosition,ElectWeight,ElectContact,ElectCategory,ElectCollege,DeleteGroup,ElectPeriphery,Sex,FatherName,MotherName,Birthday,FamilyStatus,BirthDimos,Adreess,CurrentNomos,Landline,OccupSpeciality,ElectImportance,ElectMechanism,ElectPeriphery,ElectDimotikiEnotita,ElectNumber,EduLanguages,SuggestSurname,SuggestPhone,ElectDimos,EduUniversity,Surname,FatherSurname,WifeName,NameDay,BirthCountry,BirthCity,HealthStatus,Postcode,CurrentDimos,HomeLine,Fax,OccupStatus,OccupFirm,ElectType,ElectCategory,ElectDiamerisma,EduLevel,EduPC,ElectEterodimotis,EduQualifications from Fotilas2021 where Name like '%" + ComboBox2.Text + "%', IdentityNumber like '%" + ComboBox3.Text + "%', FatherSurname like '%" + ComboBox4.Text + "%', LocalCelebDay like '%" + ComboBox5.Text + "%', BirthNomos like '%" + ComboBox6.Text + "%', NumofChilds like '%" + ComboBox7.Text + "%', MilitaryService like '%" + ComboBox8.Text + "%' CurrentCountry like '%" + ComboBox9.Text + "%', CurrentCity like '%" + ComboBox10.Text + "%', CellNumber like '%" + ComboBox11.Text + "%', DateAdd like '%" + ComboBox13.Text + "%', Occupation like '%" + ComboBox14.Text + "%', OccupPosition like '%" + ComboBox15.Text + "%', ElectWeight like '%" + ComboBox16.Text + "%', ElectContact like '%" + ComboBox17.Text + "%', ElectCategory like '%" + ComboBox19.Text + "%', ElectCollege like '%" + ComboBox20.Text + "%', DeleteGroup like '%" + ComboBox21.Text + "%', ElectPeriphery like '%" + ComboBox22.Text + "%', Sex like '%" + ComboBox30.Text + "%', FatherName like '%" + ComboBox31.Text + "%', MotherName like '%" + ComboBox32.Text + "%', Birthday like '%" + ComboBox33.Text + "%', FamilyStatus like '%" + ComboBox34.Text + "%', BirthDimos like '%" + ComboBox35.Text + "%', Adreess like '%" + ComboBox37.Text + "%', CurrentNomos like '%" + ComboBox38.Text + "%', Landline like '%" + ComboBox39.Text + "%', OccupSpeciality like '%" + ComboBox43.Text + "%', ElectImportance like '%" + ComboBox45.Text + "%', ElectMechanism like '%" + ComboBox50.Text + "%', ElectPeriphery like '%" + ComboBox51.Text + "%', ElectDimotikiEnotita like '%" + ComboBox52.Text + "%', ElectNumber like '%" + ComboBox53.Text + "%', EduLanguages like '%" + ComboBox56.Text + "%', SuggestSurname like '%" + ComboBox57.Text + "%', SuggestPhone like '%" + ComboBox29.Text + "%', ElectDimos like '%" + ComboBox23.Text + "%', EduUniversity like '%" + ComboBox27.Text + "%', Surname like '%" + ComboBox59.Text + "%', FatherSurname like '%" + ComboBox60.Text + "%', WifeName like '%" + ComboBox61.Text + "%', NameDay like '%" + ComboBox62.Text + "%', BirthCountry like '%" + ComboBox63.Text + "%', BirthCity like '%" + ComboBox64.Text + "%', HealthStatus like '%" + ComboBox65.Text + "%', Postcode like '%" + ComboBox66.Text + "%', CurrentDimos like '%" + ComboBox67.Text + "%', HomeLine like '%" + ComboBox68.Text + "%', Fax like '%" + ComboBox69.Text + "%', OccupStatus like '%" + ComboBox71.Text + "%', OccupFirm like '%" + ComboBox72.Text + "%', ElectType like '%" + ComboBox74.Text + "%', ElectCategory like '%" + ComboBox76.Text + "%', ElectDiamerisma like '%" + ComboBox81.Text + "%', EduLevel like '%" + ComboBox84.Text + "%', EduPC like '%" + ComboBox85.Text + "%', ElectEterodimotis like '%" + ComboBox25.Text + "%', EduQualifications like '%" + ComboBox28.Text + "%'", con1)

    Dim da As New SqlDataAdapter
    Dim datatable1 As New DataTable

    da.SelectCommand = command
    datatable1.Clear()
    da.Fill(datatable1)
    DtGrid_Search.DataSource = datatable1
    con2.Close()

End Sub

question from:https://stackoverflow.com/questions/65648594/search-database-and-project-results-to-datagridview

Answer 1

You should use parameters for a start, and you should construct your SQL so that each parameter is effectively optional, e.g.

Dim sql = <sql>
              SELECT *
              FROM SomeTable
              WHERE (@Column1 IS NULL OR Column1 = @Column1)
              AND (@Column2 IS NULL OR Column2 = @Column2)
              AND (@Column3 IS NULL OR Column3 = @Column3)
          </sql>

There are a number of things to note here.

Firstly, this code uses an XML literal. Before multiline String literals were introduced in (I think) 2015, that is the best way to write long inline SQL, because it allows you to break it over multiple lines without clutter making it hard to read.

Secondly, the SQL code uses parameters. If you think of the SQL code as being like a VB method, each of the things prefixed with @ is basically a method parameter. When you execute the SQL code, you provide a value for each of those parameters, just like when you call a VB method. Parameters make your code easier to read, not subject to formatting issues and, most importantly, not subject to SQL injection attacks. You can do some research to find out more if you want.

Finally, the way each parameter is used makes them effectively optional by allowing you to set a parameter to NULL to make it effectively ignored. For example, if you set the @Column1 to NULL then the first criterion in the WHERE clause will be true for every record, so it's like not having that criterion at all. If, on the other hand, you set @Columnn1 to a value then the first criterion will only be true for records that contain that value in Column1. The same goes for each of the other parameters, so you can have as many "optional" parameters as you like.

The way to make use of this in your VB code would be as follows:

Dim table As New DataTable

Using adapter As New SqlDataAdapter(sql, "connection string here")
    With adapter.SelectCommand.Parameters
        .Add("@Column1", SqlDbType.VarChar, 50).Value = ComboBox1.Text
        .Add("@Column2", SqlDbType.VarChar, 50).Value = ComboBox2.Text
        .Add("@Column3", SqlDbType.VarChar, 50).Value = ComboBox3.Text
    End With

    adapter.Fill(table)
End Using

BindingSource1.DataSource = table
DataGridView1.DataSource = BindingSource1

Obviously the details may vary but the principle is fixed: create a data adapter with the SQL code, add the appropriate parameter to the SelectCommand and then call Fill to populate your DataTable. You can then do whatever you want with that, e.g. bind it to a DataGridView via a BindingSource. When you add a parameter, just specify the same name as you used in the SQL code and the appropriate database data type, then set the Value to the appropriate value from your app, casting or converting the data as appropriate.

For example, if you are using a parameter to compare to a column that is varchar(50) in the database then you specify SqlDbType.VarChar and 50 when adding the parameter and you set the Value using a String. If your database column is type int then you specify SqlDbType.Int and no size, then set the Value using an Integer.

If you want ignore a paramater then, as I said, you needs to set it to NULL in the SQL. That might look like this in the VB code:

.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)
.Add("@Column3", SqlDbType.VarChar, 50).Value = If(ComboBox3.SelectedItem Is Nothing, CObj(DBNull.Value), ComboBox3.Text)

In each case, the code first checks whether the user has selected an item in the appropriate ComboBox and, if not, the parameter's Value is set to DBNull.Value, which is the ADO.NET representation of a database NULL. If an item is selected, the selected value is used. You can modify the details of the code as required but, again, the principle is constant.

Note that the CObj is required because the If operator used here requires both possible return values to be the same type or one assignable to the other. DBNull and String are not the same type and neither inherits the other so you cannot use those two types. By casting one to type Object using CObj, you enable the other value to be any type, because every other type inherits Object.