Excel VBA阵列未从存储过程记录集中接收所有值-与在Excel外部运行存储过程的结果不同 [英] Excel VBA Array not receiving all values from stored procedure recordset - different result than running stored procedure outside of Excel

问题描述

我一直在尝试解决问题。我在MySql数据库中有一个存储过程，该数据库通过Excel VBA应用程序调用。 VBA应用程序将记录集传递到数组中，然后使用For循环将数组中的每个项目放置到工作表上。

I have quite a conundrum which I have been trying to troubleshoot. I have a stored procedure in a MySql database, which I call through an Excel VBA application. The VBA application passes the recordset into an Array, and then I use a For Loop to place each of the items in the Array onto a worksheet.

这里是问题：两个记录集中的值在Excel中保持空白。奇怪的是，这两个位于数组的中间，而不是开头或结尾。但是，如果我通过另一个查询程序（例如HeidiSql）调用存储过程，则会收到所有值。我茫然不知为什么我没有通过Excel接收所有值...或者为什么数组没有以任何速率接收所有值。

Here's the problem: two of the values in the recordset keep coming back blank in Excel. Oddly, the two are in the middle of the Array, not the beginning or end. However, if I call the stored procedure through another query program such as HeidiSql, I receive ALL values back. I'm at a loss as to why I'm not receiving all of the values through Excel... or why the Array isn't receiving them all, at any rate.

谢谢您的帮助。

这是我的代码：

Sub StartHereFlexFunderCust()

On Error GoTo ErrorHandler

   Dim Password As String
   Dim SQLStr As String
   'OMIT Dim Cn statement. Cn stands for Database Connection
   Dim Server_Name As String
   Dim User_ID As String
   Dim Database_Name As String
   Dim custID As String
   Dim myArray()
   'OMIT Dim rs statement. rs stands for Database Recordset and is the Recordset of what is returned

   Set RS = CreateObject("ADODB.Recordset")
   Server_Name = Range("O10").Value
   Database_Name = Range("O11").Value ' Name of database
   'id user or username. We need to write code to insert the current user into this variable (Application.Username) if possible. But they may not be consistent across all machines.
   'For example mine is "Ryan Willging" and we would have to shorten it to rwillging but others may be rwillging.
   'This is important because if we do not do this all queries will come from the same person and that is not good for debugging.
   User_ID = Range("O12").Value
   Password = Range("O13").Value
   custID = Range("C4").Value 'Deal Number from Start here that we are passing into the stored procedure

    'This is the storedprocedure call and it passes in the value of the DealId to the Stored Procedure
   SQLStr = "call flexFundByCustomer(" + custID + ")"

   Set cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
   'This statement takes the variables from the checklist and passes them into a connection string
   cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=" & _
           Server_Name & ";Database=" & Database_Name & _
           ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    'This statement queries the database using the SQL string and the connection string.
    'The adOpenStatic variable returns a static copy of a set of records that you can use to find data or generate reports. There are other variables that
    'could be used but I think this one will suffice.
   RS.Open SQLStr, cn, adOpenForwardOnly



Debug.Print msg    'or MsgBox msg

   'Take all of the info from the queries and put them into the spreadsheet
   myArray = RS.getrows()
   Dim Fld_Name As String
   Dim Val_of_Field As String

   Dim starthere As Worksheet

   Fld_Name = UBound(myArray, 1)
   Val_of_Field = UBound(myArray, 2)

   Set starthere = ThisWorkbook.Sheets("Start Here")
   MsgBox "No error yet defined Start Here!"
'This little loop works well to dump the recordset into excel. We can then map the correct fields 'k inputs the headers and R inputs the rows returned in the Recordset
   For K = 0 To Fld_Name ' By using a For loop the data is inputed into excel one row at a time
       starthere.Range("U4").Offset(0, K).Value = RS.fields(K).Name
       For R = 0 To Val_of_Field
          starthere.Range("U4").Offset(R + 1, K).Value = myArray(K, R)
       Next
   Next

   RS.Close
   Set RS = Nothing
   cn.Close
   Set cn = Nothing

ErrorHandler:
MsgBox "There's been an error!"
Exit Sub

End Sub

推荐答案

考虑使用 Range.CopyFromRecordset 方法来避免任何使用数组。或者，如果内存不允许，则在Recordset列之间使用 Do While循环：

Consider using Range.CopyFromRecordset method to avoid any use of arrays. Or if memory does not allow, use a Do While Loop across Recordset columns:

' COLUMN HEADERS
For i = 1 To RS.Fields.Count
    starthere.("Results").Range("U4").Offset(0, i) = RS.Fields(i - 1).Name
Next i

' DATA ROWS        
' COPYFROMRECORDSET APPROACH
starthere.Range("U5").CopyFromRecordset RS  

' DO WHILE LOOP APPROACH   
starthere.Activate 
starthere.Range("U5").Activate

row = 5
Do While Not RS.EOF
   For i = 0 To RS.Fields.Count - 1
       ActiveCell.Offset(0, i) = RS.Fields(i)
   Next i

   row = row + 1            
   ActiveCell.Offset(row, 21)
   RS.MoveNext
Loop 

对于返回空值，可能是MySQL和Excel的数据类型不兼容。例如，您可能将一个表字段设置为MySQL的最大十进制数（65，30），这表示不能在电子表格上反映的最大数字为65和最大30个小数点。单元格值的当前精度限制为15个小数点。

As for the values returning empty that may be a MySQL and Excel incompatibility of data types. For instance, you may have a table field set to MySQL's maximum decimal (65, 30) which denotes max digits of 65 and max 30 decimal points which cannot be reflected on a spreadsheet. Current precision limit of a cell value is 15 decimal points.

或者，您可能具有 VARCHAR（65535），它是65,535个字节的限制或开放式<没有限制的code> TEXT 列也无法在电子表格上显示。当前一个单元格中的字符数限制为32,767。

Alternatively, you may have a VARCHAR(65535) which is the 65,535 byte limit or the open-ended TEXT column of no limit that also cannot be displayed on spreadsheet. Current limit of characters in one cell is 32,767.

尝试将列修改为较小的类型：

Try modifiying column to a smaller type:

ALTER TABLE `tableName` MODIFY COLUMN `largenumberfield` DECIMAL(10,7);

ALTER TABLE `tableName` MODIFY COLUMN `largetextfield` VARCHAR(255);

为什么其他程序（例如HeidiSQL）会检索值？可能是由于它们的内部转换功能将数据值强制转换为特定格式（即删除空格，截断值），然后该格式才能在Excel中充分呈现。

Why the other programs such as HeidiSQL retrieve values? It might be due to their internal conversion features forcing data values into a specific format (i.e., removing whitespaces, truncating values) which then renders adequately in Excel.

这篇关于Excel VBA阵列未从存储过程记录集中接收所有值-与在Excel外部运行存储过程的结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！