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
– Student, Commented Apr 27, 2016 at 8: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
– jmcilhinney, Commented Apr 27, 2016 at 8: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
– Student, Commented Apr 27, 2016 at 8: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
– jmcilhinney, Commented Apr 27, 2016 at 8:30

jmcilhinney · Accepted Answer · 2016-04-27 08:42:38Z

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, 2016 at 8:42

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Student Over a year ago

But the compiler keep saying add is deprecated. Is it fine to just use it?

jmcilhinney Over a year ago

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.

Student Over a year ago

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 = "" ?

jmcilhinney Over a year ago

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 Over a year ago

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.

|

Collectives™ on Stack Overflow

VB.NET MYSQL Datagridview is empty?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related