SQL查询不会只返回一个字段的完整结果 [英] SQL query doesn't return full results for only one field

问题描述

使用VBA执行SQL查询并将结果复制到Excel工作表中时，我遇到问题。

I'm having a problem using VBA to execute a SQL query and copy the results into an Excel worksheet.

当这些子Excel执行时，它只会复制行是256的倍数（所以行256,512,768等是唯一填充到Excel的）。从数据库中复制任何其他字段没有问题。此外，当我在MySQL中运行相同的查询时，工作正常。对于SQL和VBA来说，这是相当新鲜的，我看不出为什么这个特定领域应该引起麻烦的原因。我唯一可以想到的是它的内容是一个始终以下划线开头的字符串（我只提到这个字符串，因为它和它的一些其他字段是唯一的区别）。

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).

有人有什么想法可以发生这种情况吗？

Does anybody have any ideas as to why this may be happening?

干杯，

利亚姆

编辑：
这是一个代码片段。说实话，我不知道看到代码会有什么变化，看到它的工作原理适用于其他情况，但再次，这就是为什么我是新手：）

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

推荐答案

我想我已经找到解决方案，感谢Lamak的帮助：

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.

这篇关于SQL查询不会只返回一个字段的完整结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！