vba SQL 查询不会仅返回一个字段的完整结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10228328/
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
SQL query doesn't return full results for only one field
提问by Liam Barrett
I'm having a problem using VBA to execute a SQL query and copy the results into an Excel worksheet.
我在使用 VBA 执行 SQL 查询并将结果复制到 Excel 工作表时遇到问题。
When the sub excecutes, it only copies rows that are multiples of 256 (so rows 256, 512, 768 etc are the only ones that are filled into Excel). I'm having no problem copying any of the other fields from the database. Also, when I run the same query in MySQL it works fine. Being fairly new to both SQL and VBA I can't see any reason why this particular field should be causing trouble. The only thing I can think of is that its contents are a string that always begins with an underscore (and I only mention that because it's the only difference between it and some of the other fields).
当子执行时，它只复制 256 的倍数的行（因此行 256、512、768 等是唯一填充到 Excel 中的行）。我从数据库中复制任何其他字段都没有问题。此外，当我在 MySQL 中运行相同的查询时，它工作正常。作为 SQL 和 VBA 的新手，我看不出为什么这个特定字段会引起麻烦。我唯一能想到的是它的内容是一个始终以下划线开头的字符串（我之所以提到这一点，是因为它是它与其他一些字段之间的唯一区别）。
Does anybody have any ideas as to why this may be happening?
有没有人对为什么会发生这种情况有任何想法？
Cheers,
干杯，
Liam
利亚姆
EDIT: Here's a snippet of the code in question. To be honest, I'm not sure if seeing the code will make a difference, seeing as it works just fine for other situations, but then again, that's why I'm the newbie :)
编辑：这是有问题的代码片段。老实说，我不确定看到代码是否会有所作为，因为它在其他情况下工作得很好，但话说回来，这就是我是新手的原因:)
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
con.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=ipaddress;UID=userID;PWD=password;DATABASE=jiradb;OPTION=16427;"
con.Open
sql = "SELECT TEMPO_DATA FROM gssd_worklog WHERE WORK_DATE BETWEEN '2012-01-01' AND '2012-03-31'"
'Open Recordset'
rst.Open sql, con
'Copy Data to Excel'
Set ws = ActiveSheet
ws.Range("A2").CopyFromRecordset rst
采纳答案by Liam Barrett
I think I've found the solution, thanks to Lamak's help:
我想我已经找到了解决方案，感谢拉马克的帮助：
rst.Open sql, con
Dim iRows As Integer
For iCols = 0 To rst.Fields.Count - 1
ws.Cells(1, iCols + 1).Select
With Selection
.Value = rst.Fields(iCols).Name
.Font.Bold = True
.EntireColumn.AutoFit
End With
Next iCols
iRows = 2
While Not rst.EOF
For iCols = 0 To rst.Fields.Count - 1
ws.Cells(iRows, iCols + 1).Value = rst.Fields(iCols).Value
Next iCols
rst.MoveNext
iRows = iRows + 1
Wend
The problem seems to have been trying to copy all the fields out of the record set at once, copying the record field by field and row by row seems to solve the problem.
问题似乎一直试图将所有字段从记录集中一次复制出来，逐个字段和逐行复制记录似乎可以解决问题。
回答by mark e cooke
I ran into a very similar problem yesterday and found this thread whilst researching so wanted to add my "solution" in case it helps anyone else.
我昨天遇到了一个非常相似的问题，并在研究时发现了这个线程，所以想添加我的“解决方案”，以防它对其他人有所帮助。
To refine the problem description I found that it applied to one particular field in my dataset and, interestingly, every subsequent field if I re-ordered the query. Adding trailing or removing earlier fields made no difference to my problem column.
为了改进问题描述，我发现它适用于我数据集中的一个特定字段，有趣的是，如果我重新排序查询，它适用于每个后续字段。添加尾随或删除较早的字段对我的问题列没有影响。
Checking the types revealed it was the same type as some of the other fields that did work so no clue there either.
检查类型显示它与其他一些确实有效的字段的类型相同，因此也没有任何线索。
However, as it was a specific field (in this case a text field), I decided to try changing my SQL query to CAST the problem field, changing:
但是，由于它是一个特定字段（在本例中为文本字段），我决定尝试将我的 SQL 查询更改为 CAST 问题字段，更改：
SELECT Col1, Col2, Col3 FROM TableName
...to...
...到...
SELECT Col1, Col2, CAST(Col3 AS VARCHAR(8)) AS Col3 FROM TableName
...and suddenly all the data appears (including any trailing columns).
...突然所有数据都出现了（包括任何尾随列）。
回答by Lamak
I think that your problem is with your last line, when you are trying to copy the record on the worksheet. Try something like this (code modified from http://msdn.microsoft.com/en-us/library/aa223845(v=office.11).aspx):
当您尝试复制工作表上的记录时，我认为您的问题出在最后一行。尝试这样的事情（从http://msdn.microsoft.com/en-us/library/aa223845(v=office.11).aspx修改的代码）：
For iCols = 0 to rs.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
ws.Range("A2").CopyFromRecordset rst
回答by user3744274
I've had a similar problem using a recordset. It would return a set of customer results in the SQL query just fine, but would not paste the results to the worksheet on certain customers.
我在使用记录集时遇到了类似的问题。它会在 SQL 查询中返回一组客户结果就好了，但不会将结果粘贴到某些客户的工作表中。
Following the tip to cast my data, I dug deeper in my query and noticed that there were null values hidden in the result set. From the appearance of the null in the recordset, no results beyond that null value (ordered by that specific column) would be pasted on my worksheet.
按照提示转换我的数据，我在查询中进行了更深入的挖掘，并注意到结果集中隐藏了空值。从记录集中出现的空值来看，超出该空值（按该特定列排序）的结果将不会粘贴到我的工作表上。
Or in pseudocode:
或者用伪代码：
SELECT DISTINCT a,b,e
FROM DATASET
where column c would contain a null value.
其中 c 列将包含空值。
So my solution : Remove duplicates and null values in your result set in SQL before using it in Excel.
所以我的解决方案：在 Excel 中使用它之前，在 SQL 中删除结果集中的重复项和空值。
回答by Mark Burns
I'm wagering that your issue was that the recordset (in memory) was not fully populated with data from the data source. This is a known behavior of ADO recordsets.
我打赌您的问题是记录集（在内存中）没有完全填充来自数据源的数据。这是 ADO 记录集的已知行为。
The simplest workaround for this is that before the ".CopyFromRceordset rst" method call that you do a "rst.movelast" - and that this method will assure the full loading of the recordset with data from the datasource.
对此最简单的解决方法是在“.CopyFromRceordset rst”方法调用之前执行“rst.movelast”——并且该方法将确保使用来自数据源的数据完全加载记录集。
Your RBAR (Row By Agnonizing Row) alternative process achieves this same result by its use of the .movenext method to sequentially move through the records in the recordset (which causes the ADO code to continually load new data as the record pointer moves through the loaded data pages in memory).
您的 RBAR（Row By Agnonizing Row）替代过程通过使用 .movenext 方法顺序移动记录集中的记录（这导致 ADO 代码在记录指针移动通过加载的记录时不断加载新数据）来实现相同的结果内存中的数据页）。
