Results 1 to 3 of 3
  1. #1

    ReadExcel_ADO Sting Vs Numeric - Array forced null


    I ran into an interesting situation; which I was not expecting. I was hoping you could offer some assistance here.

    Using the ReadExcel_ADO example (and previous code customized for my Excel format); I am running into an odd problem. Could this be a bug, or am I doing something wrong?

    I have a column in Excel that can be either numeric or a string; as an example, a user can insert the value "Bob" or a value of "3".

    My data extractor script is setup identical to the ReadExcel_ADO example; with the exception of my own column names. The column in question, under the function CreateOrderInfo is setting the object value to = "".

    The problem: When I run the script, everything runs through without throwing an error. Yet, one of the values stored in the array is being set to NULL - when there is actually a string for it be set to. The cause for this is when there is a numeric value in that column I was talking about before. Without changing the code, two different behaviors occur (very odd).

    Excel Example:
    Column Names: Process, StepNumber, Keyword, FieldWindow, IV, Comment
    (note: IV is the column which could have a numeric or a string)
    Column values:
    (First record) Yes, 1, Input, TestW, 3, Test123
    (Second record) Yes, 2, Select, MainW, test, Yes

    What I am see when I run with that data above:
    1) My IV variable is NULL when setting its value from the array (IV = OrdArr(I).IV)
    2) The logging acts very weird; as I have two records and would be displaying two log entries when completed. The first log entry is completely blank. The second log entry is the FIRST record of the array (when it should be the second one!)
    LogMessage #1: Blank Entry
    LogMessage #2: 1,1,Input,TestW,3

    When I change the data to remove the numeric from the column with a text field; everything works how I would expect it to.
    LogMessage #1: 1,1,Input,TestW,test
    LogMessage #2: 2,2,Select,MainW,test

    When I change the data and swap the record position of the numeric in the IV column (place the numeric in record #2 rather than record #1)
    LogMessage #1: Blank
    LogMessage #2: 2,2,Select,MainW,3

    The Driver code:

    Sub Main
    Const ExcelFileName = "c:\framework.xls" '// This is the Excel File Name
    Const ExcelTableName = "Sheet1" '// This is the tab name
    call DefineOrderInfoClass
    OrdArr = ReadExcel_ADO
    For I = 0 To VarArrayHighBound(OrdArr, 1)
    If UCase(OrdArr(I).Process) = "NO" then
    X = X + 1
    ElseIf UCase(OrdArr(I).Process) = "YES" Then
    CycleCount = CycleCount + 1
    CycleCount2 = IntToStr(CycleCount)
    StepNumber = IntToStr(OrdArr(I).StepNumber)
    Keyword = OrdArr(I).Keyword
    FieldWindow = OrdArr(I).FieldWindow
    tmpIV = IsNumeric(OrdArr(I).IV)
    If tmpIV = False then
    IV = OrdArr(I).IV
    IV = IntToStr(OrdArr(I).IV)
    End IF
    call log.message(CycleCount2 + "," + StepNumber + "," + Keyword + "," + FieldWindow + "," + IV)
    End IF
    End Sub

    The Data Extractor Code:

    'USEUNIT Unit1
    ' Creates and initializes the OrderInfo object
    Sub DefineOrderInfoClass

    if (ODT.Classes.Count=0) Then

    Set OrderInfoClass = ODT.Classes.Declare("OrderInfo")

    ' Defines fields of the OrderInfo class
    OrderInfoClass.AddProperty "Process"
    OrderInfoClass.AddProperty "StepNumber"
    OrderInfoClass.AddProperty "Keyword"
    OrderInfoClass.AddProperty "FieldWindow"
    OrderInfoClass.AddProperty "IV"
    OrderInfoClass.AddProperty "Comment"
    End if
    End Sub

    Function CreateOrderInfo
    Set OrderInfoObj = ODT.Classes.New("orderinfo")
    OrderInfoObj.Process = ""
    OrderInfoObj.StepNumber = ""
    OrderInfoObj.Keyword = ""
    OrderInfoObj.FieldWindow = ""
    OrderInfoObj.IV = ""
    OrderInfoObj.Comment = ""
    Set CreateOrderInfo = OrderInfoObj
    End Function

    Function ReadExcel_ADO
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataDir + ExcelFileName + _
    ";Extended Properties=Excel 8.0"

    Set Connection = Sys.OleObject("ADODB.Connection")

    Connection.Open (ConnStr)
    Set Recordset = Connection.Execute("SELECT * FROM [" + ExcelTableName + "$]")

    OrdArr = CreateVariantArray(0, 0)
    LineCount = 0

    While Not Recordset.EOF
    Set OrdInfo = CreateOrderInfo

    OrdInfo.Process = Recordset("Process").Value
    OrdInfo.StepNumber = Recordset("StepNumber").Value
    OrdInfo.Keyword = Recordset("Keyword").Value
    OrdInfo.FieldWindow = Recordset("FieldWindow").Value
    OrdInfo.IV = Recordset("IV").Value
    OrdInfo.Comment = Recordset("Comment").Value

    LineCount = LineCount + 1
    VarArrayRedim OrdArr, LineCount-1
    Set OrdArr(LineCount-1) = OrdInfo



    ReadExcel_ADO = OrdArr
    End Function


  2. #2

    Re: ReadExcel_ADO Sting Vs Numeric - Array forced null


  3. #3

    Re: ReadExcel_ADO Sting Vs Numeric - Array forced null

    Hello Mike,

    I believe that you are experiencing the same problem as the one discussed here:

    Is this the case?
    Alex - SmartBear Support



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
BetaSoft Inc.
All times are GMT -8. The time now is 07:32 PM.

Copyright BetaSoft Inc.