VB.NET MYSQL Datagridview is empty?

Question

When I click on search button, this code below will run. There are no errors on the code but datagridview shows only the column name.. Is there a mistake 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 on Search

@William Is it about the sql injection prevention? I did that already right...? — Student, Apr 27 '16 at 08:27
Presumably there are no records that match your criteria. We shouldn't really need to explain this but you should change your query to use just one condition, then two, etc. As soon as you don't get what you expect, you've found the issue. — jmcilhinney, Apr 27 '16 at 08:28
@jmcilhinney Yeah I tried using only 1 parameter and the record shows up. I am trying to make a convenient search where I do not need to check for every textbox if there is user input and write multiple query for it. — Student, Apr 27 '16 at 08:30
By the way, what's the point of all your NULL checks in your SQL when none of your parameters can possibly be NULL? You'd have to set the parameter to `DBNull.Value` explicitly for a NULL value to be included. An empty string is not the same thing as NULL. — jmcilhinney, Apr 27 '16 at 08:30
If you want to be able to ignore criteria if there's no input then you do need those NULL checks but then you actually need to use NULL for those parameters. I'll demonstrate in an answer. — jmcilhinney, Apr 27 '16 at 08:31
@jmcilhinney Alright thanks! This was my earlier question : [link](http://stackoverflow.com/questions/36881925/vb-net-mysql-how-to-implement-a-more-efficient-search-program/36882108#36882108) — Student, Apr 27 '16 at 08:32

score 1 · Answer 1 · answered Apr 27 '16 at 08:42

1

If your intention is to be able to ignore criteria if the user leaves a field empty then you actually have to pass a NULL value to the query in that case. Just as String.Empty and Nothing are not the same thing in VB, so an empty string and NULL are not the same thing in SQL. You would have 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 the parameters are added using Add rather than AddWithValue, because a data type cannot be inferred from DBNull.Value

answered Apr 27 '16 at 08:42

jmcilhinney

50,448
5
26
46

But the compiler keep saying add is deprecated. Is it fine to just use it? – Student Apr 27 '16 at 08:45
There are a number of overloads of `Add`. The overload that has parameters of types `String` and `Object` has been deprecated because it's been replaced by `AddWithValue`. The overload that I've used has parameters of types `String` and `SqlDbType` though. That overload has not been deprecated. – jmcilhinney Apr 27 '16 at 08:52
I see. Can you please explain more on the SqlDbType and CObj? I don't quite understand what is those and use for. Cant I just put an if statement to check if the textbox is empty then Textbox.text = "" ? – Student Apr 27 '16 at 08:54
1

Just as each parameter in a VB method needs its data type declared, so SQL parameters need their data type declared. That's what the `SqlDbType` value is for. When you call `AddWithValue`, the SQL data type is inferred from the data type of the value. One problem with that is that it may infer wrongly. For instance, a `String` is mapped to `SqlDbType.NVarChar` by default, which can cause issues if your data is type `varchar`. I'm not sure why the compiler is saying that that overload is deprecated but you could use the overload that has three parameters instead and specify a max size. – jmcilhinney Apr 27 '16 at 09:20
You could use an `If...Else` statement if you wanted to but I find it more concise to use the `If` operator as I have done. Because the `If` operator is generic, i.e. its return type is inferred from the two possible return values, those values must be the same type or one of them must be of a type that is assignable from the other. `DBNull` and `String` do not satisfy those requirements so one of them must be cast as type `Object`. Because every type is derived from `Object`, the return type of the `If` operator is inferred to be `Object`. – jmcilhinney Apr 27 '16 at 09:24
I tried using your way but the compiler still give me an error at `da.Fill(dt)`. The exception wrote this **An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll** and **Additional information: Input string was not in a correct format.** – Student Apr 27 '16 at 09:26
Ah, I just realised why the compiler is flagging that issue with `Add`. You're using a `MySqlCommand`, not a `SqlCommand`. As a result, a `SqlDbType` value is being interpreted as the parameter value rather than the data type. You need to use the corresponding MySQL type, which is probably `MyDqlDbType`. – jmcilhinney Apr 27 '16 at 09:36
That fixed the deprecation and the program run smoothly. But datagridview still showing all the column names only. No records is shown... – Student Apr 27 '16 at 09:40
Then no records match the criteria you're using. – jmcilhinney Apr 27 '16 at 10:12
There is. The combobox record is populated from the database as well. Then I just select back from the combobox and search in database. – Student Apr 27 '16 at 10:13
There isn't, or you'd be seeing them. I suggest that do as you should always do and start simple build up from there. Start by simplifying the SQL and testing one parameter at a time and see if they work. If not then go back further, otherwise start trying combinations of two. Work your way up in stages until you find the point at which it breaks. By the way, just because the filter values you're using come from the database doesn't mean that there's any record that matches all of them, assuming that you're using more than one. – jmcilhinney Apr 27 '16 at 11:30

VB.NET MYSQL Datagridview is empty?

1 Answers1

Linked