VB.NET MYSQL Datagridview为空? [英] VB.NET MYSQL Datagridview is empty?
问题描述
当我单击搜索按钮时,下面的代码将运行。代码上没有错误,但是datagridview仅显示列名。.查询是否有错误?
mySqlConn = New MySqlConnection
myCommand = New MySqlCommand
dt = New DataTable()
如果没有dt尝试
然后
dt.Dispose()
如果
如果da什么都不是,则
da.Dispose()
如果
结束,则
如果DataGridView1.DataSource什么都不是,则
DataGridView1.DataSource =什么都没有
如果
mySqlConn.ConnectionString = connStr
myCommand.CommandText =从createproject中选择*其中(FloatNumber = @floatNo或@floatNo为空)AND(DeveloperName = @devName或@devName为Null)AND(DevelopmentType = @ devType或@devType为空)AND(LotPt = @lotPt或@lotPt为空)AND(Mukim = @mukim OR @mukim为空)AND(Daerah = @daerah OR @daerah为Null)AND(Negeri = @negeri OR @negeri为空)并且(TempReference = @tempRef或@tempRef为空)
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。填充(dt)
DataGridView1.DataSource = dt
da.Update(dt)
猫ch ex作为MySqlException
MsgBox(ex.ToString())
最后
mySqlConn.Close()
mySqlConn.Dispose()
结束尝试
这是我点击搜索之后的操作。
如果您的目的是在用户将字段保留为空的情况下忽略条件,那么在这种情况下,您实际上必须将NULL值传递给查询。就像 String.Empty
和 Nothing
在VB中不一样,所以空字符串和NULL也不是在SQL中也是如此。您将必须执行以下操作:
Dim sql =< sql>
结尾pre>
SELECT *
从MyTable
哪里(@ Column1为空或Column1 = @ Column1)
AND(@ Column2为空或Column2 = @ Column2)
< / sql>
myCommand.CommandText = sql.Value
Dim column1 = TextBox1.Text.Trim()
Dim column2 = TextBox2.Text.Trim()
使用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)
以
请注意,参数是使用
Add
而不是AddWithValue $ c添加的$ c>,因为无法从
DBNull.Value
推断数据类型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
解决方案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
andNothing
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 thanAddWithValue
, because a data type cannot be inferred fromDBNull.Value
这篇关于VB.NET MYSQL Datagridview为空?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!