Excel VBA MySQL“SELECT * FROM table”不完整信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26369937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA MySQL "SELECT * FROM table" not full informaton
提问by Exic
I have a really weird issue with my Excel VBA code selecting values from a MySQL database. I structured a simple database with following columns:
我的 Excel VBA 代码从 MySQL 数据库中选择值时遇到了一个非常奇怪的问题。我用以下列构建了一个简单的数据库：
- ID
- Name
- Surname
- City
- ID
- 姓名
- 姓
- 城市
And lets assume having following entrys:
让我们假设有以下条目：
01; Pan; Peter; NYC
02; P; Peter; NYC
01; 平底锅; 彼得；纽约市
02；P; 彼得；纽约市
But now the issue, if I select * from my table it shows me only following output:
但现在的问题是，如果我从表中选择 * 它只显示以下输出：
01; P; Pan; NYC
02; P; Pan; NYC
01; P; 平底锅; 纽约市
02；P; 平底锅; 纽约市
That means the I only see the minimal lenght of an entry .... what is going on there? I have really casual VBA code in different modules for that task:
这意味着我只能看到条目的最小长度......那里发生了什么？对于该任务，我在不同的模块中有非常随意的 VBA 代码：
Public variables:
公共变量：
Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public strSql As String
Connection module:
连接模块：
Public Function connectDB()
Dim strServer_Name As String
Dim strDB_Name As String
Dim strUser_ID As String
Dim strPassword As String
With Sheet2
strServer_Name = .Range("B2").Value
strDB_Name = .Range("B3").Value
strUser_ID = .Range("B4").Value
strPassword = .Range("B5").Value
End With
Set cn = New ADODB.Connection
cn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver}" & _
";SERVER=" & strServer_Name & _
";DATABASE=" & strDB_Name & _
";UID=" & strUser_ID & _
";PWD=" & strPassword & ""
End Function
Sub module:
子模块：
Sub request()
strSql = "SELECT * FROM test"
Call connectDB
Set rs = New ADODB.Recordset
rs.Open strSql, cn, adOpenDynamic
With Sheet1.Range("A1")
.ClearContents
.CopyFromRecordset rs
End With
Call disconnectDB
End Sub
Is this a VBA issue or are there any bugs in my MySQL?
这是 VBA 问题还是我的 MySQL 中存在任何错误？
回答by Exic
Okay guys I've found a solution in this post: sql-query-doesnt-return-full-results-for-only-one-field
好的，我在这篇文章中找到了解决方案：sql-query-doesnt-return-full-results-for-only-one-field
Solved it the same way the other guy did. In my code it looks like this:
和其他人一样解决了。在我的代码中，它看起来像这样：
Sub request()
Dim iCols As Integer
Dim iRows As Integer
strSql = "SELECT * FROM test"
Call connectDB
Set rs = New ADODB.Recordset
rs.Open strSql, cn, adOpenDynamic
iRows = 10
While Not rs.EOF
For iCols = 0 To rs.Fields.Count - 1
Tabelle1.Cells(iRows, iCols + 1).Value = rs.Fields(iCols).Value
Next
rs.MoveNext
iRows = iRows + 1
Wend
Call disconnectDB
End Sub
But thanks to everyone who tried to help me!
但感谢所有试图帮助我的人！
