Is VB.NET MYSQL Datagridview empty?

Question

When I click the search button, this code will work. There are no errors in the code, but the datagridview shows only the column name .. Is there an error for the query?

mySqlConn = New MySqlConnection myCommand = New MySqlCommand dt = New DataTable() Try If dt IsNot Nothing Then dt.Dispose() End If If da IsNot Nothing Then da.Dispose() End If If DataGridView1.DataSource IsNot Nothing Then DataGridView1.DataSource = Nothing End If mySqlConn.ConnectionString = connStr myCommand.CommandText = "Select * from createproject Where (FloatNumber = @floatNo OR @floatNo is Null) AND (DeveloperName = @devName OR @devName is Null) AND (DevelopmentType = @devType OR @devType is Null) AND (LotPt = @lotPt OR @lotPt is Null) AND (Mukim = @mukim OR @mukim is Null) AND (Daerah = @daerah OR @daerah is Null) AND (Negeri = @negeri OR @negeri is Null) AND (TempReference = @tempRef OR @tempRef is Null)" myCommand.Connection = mySqlConn mySqlConn.Open() myCommand.Parameters.AddWithValue("@floatNo", TextBox3.Text.Trim()) myCommand.Parameters.AddWithValue("@devName", ComboBox6.Text.Trim()) myCommand.Parameters.AddWithValue("@devType", ComboBox7.Text.Trim()) myCommand.Parameters.AddWithValue("@lotPt", TextBox4.Text.Trim()) myCommand.Parameters.AddWithValue("@mukim", ComboBox8.Text.Trim()) myCommand.Parameters.AddWithValue("@daerah", ComboBox9.Text.Trim()) myCommand.Parameters.AddWithValue("@negeri", ComboBox10.Text.Trim()) myCommand.Parameters.AddWithValue("@tempRef", TextBox6.Text.Trim()) da = New MySqlDataAdapter(myCommand.ToString(), mySqlConn) da.SelectCommand = myCommand da.Fill(dt) DataGridView1.DataSource = dt da.Update(dt) Catch ex As MySqlException MsgBox(ex.ToString()) Finally mySqlConn.Close() mySqlConn.Dispose() End Try 

And this is after I clicked Search

Answer 1

If your intention is to be able to ignore the criteria, if the user leaves the field blank, you actually need to pass a NULL value to the request in this case. Just like String.Empty and Nothing not the same in VB, so an empty string and NULL are not the same in SQL. You would need to do something like this:

 Dim sql = <sql> SELECT * FROM MyTable WHERE (@Column1 IS NULL OR Column1 = @Column1) AND (@Column2 IS NULL OR Column2 = @Column2) </sql> myCommand.CommandText = sql.Value Dim column1 = TextBox1.Text.Trim() Dim column2 = TextBox2.Text.Trim() With myCommand.Parameters .Add("@Column1", SqlDbType.VarChar).Value = If(column1 = String.Empty, CObj(DBNull.Value), column1) .Add("@Column2", SqlDbType.VarChar).Value = If(column2 = String.Empty, CObj(DBNull.Value), column2) End With 

Note that parameters are added using Add , not AddWithValue , because the data type cannot be inferred from DBNull.Value