VBA SQL Server(ADODB)提取null数据返回null

Question

我有一个令人不安的问题。字段MSP_WEB_WORK.WWORK_VALUE是DECIMAL。

当我直接查询数据库时，一切都可以：

select w.WWORK_START AS work_date, 
     w.WWORK_FINISH AS finish_date, 
     p.PROJ_NAME as Project, 
     a.TASK_NAME as Task, 
     a.WASSN_COMMENTS as Comment, 
     w.WWORK_VALUE / 60000 as work, 
     r.RES_NAME as Resource, 
     r.WRES_ID as resource_id, 
     datediff(d,w.wwork_start,w.wwork_finish)+1 AS work_days 
 from SP_WEB_RESOURCES r, 
     MSP_WEB_ASSIGNMENTS a, 
     MSP_WEB_PROJECTS p, 
     MSP_WEB_WORK w 
 where w.WWORK_TYPE = 1 
   and w.WASSN_ID = a.WASSN_ID 
   and a.WPROJ_ID = p.WPROJ_ID 
   and a.WRES_ID = r.WRES_ID 
   and w.WWORK_FINISH between '2014-01-01' and '2014-02-01' 
   and w.WWORK_VALUE > 0

问题是，当我尝试在VBA中获取相同的数据以填充Excel表：

queryPointed = "select w.WWORK_START AS work_date," & _
    " w.WWORK_FINISH AS finish_date," & _
    " p.PROJ_NAME as Project," & _
    " a.TASK_NAME as Task," & _
    " a.WASSN_COMMENTS as Comment," & _
    " w.WWORK_VALUE / 60000 as work," & _
    " r.RES_NAME as Resource," & _
    " r.WRES_ID as resource_id," & _
    " datediff(d,w.wwork_start,w.wwork_finish)+1 AS work_days" & _
" from MSP_WEB_RESOURCES r," & _
     "MSP_WEB_ASSIGNMENTS a," & _
     "MSP_WEB_PROJECTS p," & _
     "MSP_WEB_WORK w" & _
" where w.WWORK_TYPE = 1" & _
  " and w.WASSN_ID = a.WASSN_ID" & _
  " and a.WPROJ_ID = p.WPROJ_ID" & _
  " and a.WRES_ID = r.WRES_ID" & _
  " and w.WWORK_FINISH between '" & _
    Format(startDate, "yyyy-mm-dd") & "' and '" & Format(endDate, "yyyy-mm-dd") & "'" & _
  " and w.WWORK_VALUE > 0"
  queryPointed = queryPointed & " order by finish_date"
    ' Seleciona a planilha com os relatórios
    Worksheets("report").Select
    i = 6
    ' Retorna os dados do banco
    Set dbrs = getRecordset(queryPointed)
    If (dbrs.EOF = True) Then
        MsgBox "A busca ao banco de dados não retornou resultados para Rework e Trip!" & vbNewLine & "Verifique o campo Task Type no cronograma publicado.", vbExclamation, "Busca de Rework e Trip"
    Else
        Do While Not dbrs.EOF
            Worksheets("report").Cells(i, 1) = dbrs.Fields("Resource").Value
            Worksheets("report").Cells(i, 2) = dbrs.Fields("finish_date").Value
            Worksheets("report").Cells(i, 3) = dbrs.Fields("Project").Value
            Worksheets("report").Cells(i, 4) = dbrs.Fields("Task").Value
            Worksheets("report").Cells(i, 5) = dbrs.Fields("work").Value
            Worksheets("report").Cells(i, 6) = dbrs.Fields("Comment").Value
            i = i + 1
            dbrs.MoveNext
        Loop
    End If

所有列都正确插入了我的工作表，但是字段work。

当我尝试使用MsgBox显示work字段值时，它显示为空。

发生了什么事？

编辑：

由于某种原因，我仍然不明白，如果我更改作业的顺序，将word列放在首位，则可以正常工作：

Worksheets("report").Cells(i, 5) = dbrs.Fields("work").Value
Worksheets("report").Cells(i, 1) = dbrs.Fields("Resource").Value
Worksheets("report").Cells(i, 2) = dbrs.Fields("finish_date").Value
Worksheets("report").Cells(i, 3) = dbrs.Fields("Project").Value
Worksheets("report").Cells(i, 4) = dbrs.Fields("Task").Value
Worksheets("report").Cells(i, 6) = dbrs.Fields("Comment").Value

它有效，但我拒绝接受这一点，没有解释为什么会发生这种情况。

某人？

编辑2：

进行一些改组后，我发现问题是Resource字段。它必须在work和Comment字段之后出现：

Worksheets("report").Cells(i, 2) = dbrs.Fields("finish_date").Value
Worksheets("report").Cells(i, 3) = dbrs.Fields("Project").Value
Worksheets("report").Cells(i, 4) = dbrs.Fields("Task").Value
Worksheets("report").Cells(i, 6) = dbrs.Fields("Comment").Value
Worksheets("report").Cells(i, 5) = dbrs.Fields("work").Value
Worksheets("report").Cells(i, 1) = dbrs.Fields("Resource").Value

有关Resource字段的信息：

• 类型：nvarchar（255）
• is_nullable：no
• 无效：0

Answer 1

因此，我通常以略有不同的方式从PostgreSQL，SQL Server或Oracle数据库中获取数据。我实际上有一个我定义的课程，但是我会尝试将其简化为一个子。也许这可以帮助您找到问题。

Sub grabDataAndCreateTable()
     'Make sure that you have the reference Microsoft ActiveX Data Objects x.x Library enabled. For me it x.x was 2.8
     Dim cn As New ADODB.Connection, dataTable As New ADODB.Recordset, connectionString as String, userName as String, password as String
     'Sounds like you know how to find this, but just in case
     'http://www.connectionstrings.com/
     connectionString = "yourConnectionString"
     userName = "username"
     password = "1234"
     Call cn.Open connectionString, userName, password
     'Define your SELECT statement
     Dim queryPointed as String
     queryPointed = "SELECT..." 'I'm snipping your SELECT statement
     cn.CommandTimeout = 1200
     'Open up a result set with the query and connection
     dataTable.Open(queryPointed,cn)
     Dim i As Long, dataSheet as Worksheet
     Set dataSheet = thisWorkbook.Sheets("YourSheet")
     'This loop dumps your headers
     For i = 0 To (dataTable.Fields.Count - 1)
          dataSheet.Cells(1, i + 1).value = dataTable.Fields(i).Name
     Next i
     'This dumps everything else
     dataSheet.Range("A2").CopyFromRecordset dataTable
     Dim tableRange As Range, newTable As ListObject
     Set tableRange = dataSheet.UsedRange
     'I like to set up a table to work with because Tables are great for vba and the end user
     Set newTable = dataSheet.ListObjects.Add(xlSrcRange, tableRange, , xlYes)
     newTable.Name = "TableData"
Exit Sub

当我一直使用此代码时，它以这种格式未经测试。