大佬教程收集整理的这篇文章主要介绍了VBA - Listview 中的 NULL 值,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我创建了一个简单的 VBA 接口来将 Excel 连接到 MysqL 数据库。 VBA 部分充当数据预览,供用户选择要导入 Excel 工作表的项目。
到目前为止,我已经处理了一组非常完整的数据,但是我得到了一个表,其中(由于项目的性质)某些字段为 NulL。
现在,每次我尝试检查 VBA 中的值时,都会收到运行时错误 13 列表视图组件中的类型不匹配。起初我虽然它是一个带有 decimaL 类型的字段,但是在将其更改为 DOUBLE(用于测试)后问题仍然存在,直到我注意到如果只检查没有 NulL 值的列,问题就会消失。 当然我不能省略这个值。
我尝试了一些 .ToString 函数,但没有成功。而且我没有实现 IF 来检查 obj 中的 NulL。
这是我的代码:
Private Sub Exec_search_Click()
'define variables related to Form
Dim Prod_input As MSForms.TextBox
Dim queryResults As ListVIEw
' Connection variables
Dim rs As ADODB.Recordset
Dim conn As New ADODB.Connection
Dim ConnCmd As ADODB.Command
Dim Colnames As ADODB.FIElds
Dim sqlstr As String ' sql to perform varIoUs actions
Dim server_name As String
Dim database_name As String
Dim user_iD As String
Dim password As String
Dim Productname As String
Dim Count As Integer
'Grab the product
Set Prod_inputText = Me.Controls.Item("Prod_input")
Productname = Prod_inputText.Value
Set queryResults = Me.Controls.Item("queryResults")
server_name = "localhost"
database_name = "#####"
user_iD = "########"
password = "#########"
Set conn = New ADODB.Connection
conn.open "DRIVER={MysqL ODBC 8.0 ANSI Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_iD _
& ";PWD=" & password & ""
' Extract MysqL table data to first worksheet in the workbook
Set rs = New ADODB.Recordset
Set ConnCmd = New ADODB.Command
sqlstr = "SELECT referencia,descricao,pvp FROM produtos WHERE referencia liKE '" & Productname & "%'" ' extracts all data
rs.Open sqlstr,conn
'This will allow the command object to use the active connection
ConnCmd.ActiveConnection = conn
'define the query String (Comes from our textBoX) & the command type
ConnCmd.CommandText = sqlstr
ConnCmd.CommandType = adCmdText
'Exectue the query & get the column names (FIElds)
Set rs = ConnCmd.Execute
Set Colnames = rs.FIElds
'Write the results to query section
With queryResults
'Lets clear the old results
.columnheaders.Clear
.ListItems.Clear
'For each fIEld name,add it to the List vIEw as a column header
For Each Colname In Colnames
'Go to the column headers collection,and use the add method to add a new header
.columnheaders.Add Text:=Colname.name
Next
' If the recordset is empty,display a message
If rs.EOF Then
'display the message
MsgBox Prompt:="The query returned no results. No Data to display.",buttons:=vbinformation,title:="query Status"
'Close the connection
conn.Close
'Exit the sub
Exit Sub
Else 'Otherwise begin populaTing the List vIEw
'Grab the recordset
With rs
'Keep going until you've reach the end of the recordset
Do Until .EOF
'Initialize a count,this will Help to determine whether to add a new row vice a new column
Count = 1
'Loop through all the fIElds in the recordset
For Each fld In .FIElds
'If it's the first fIEld of the recordset,that means we have the first column of a new row
If Count = 1 Then
'If it's a new row,then we will add a new ListItems (ROW) object
Set ListItm = queryResults.ListItems.Add(Text:=fld.value)
Else
'If it's not a new row,then add a ListSubItem (ELEMENT) instead
ListItm.ListSubItems.Add Text:=fld.Value
End If
'Make sure to increment the count,or else EVERYONE will be a "New Row"
Count = Count + 1
Next
'Move to the next recordset
.MoveNext
Loop
End With
'When you're done with all the recordsets close the connection
conn.Close
End If
End With
End Sub
我在行中收到错误:ListItm.ListSubItems.Add Text:=fld.Value
我最近才开始研究 VBA,所以我可能会遗漏一些简单的东西,而且我在这里没有找到与此问题相关的任何内容,而且,我对 SO 还很陌生。
如果您不想在 SQL 中添加 IsNull
函数(如 Nathan_Sav 建议的注释):VBA 中有一个 IsNull
函数。有了它,您可以创建一个简单的函数,例如返回一个空字符串(或 0 或任何您喜欢的):
Function getFieldValue(fVal As Variant,Optional DefaultValue = "") As String
If IsNull(fVal) Then
getFieldValue = DefaultValue
Else
getFieldValue = CStr(fVal)
End If
End Function
顺便说一句,如果您使用计数器而不是 for each
循环遍历记录集的字段,您的代码可能会更简单。使用 getFieldValue
函数:
Do Until rs.EOF
Set ListItm = QueryResults.ListItems.Add(getFieldValue(rs(1).value))
For Count = 2 To rs.Fields.Count
ListItm.ListSubItems.Add Text:=getFieldValue(rs(Count).value)
Nex
rs.MoveNext
Loop
以上是大佬教程为你收集整理的VBA - Listview 中的 NULL 值全部内容,希望文章能够帮你解决VBA - Listview 中的 NULL 值所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。