Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006

    Unanswered: Add one more field in the grid

    This is some part of the code. What i need is to add a field name tbl_Distlist_Dep.DuplicateCount, My question is where i do
    the changes in the code so that it will also shows in the grid.

    'sqlquery=Select * from tbl_Distlist_Dep Where (clt_ID LIKE 'CERT%') and (cur_Bal between 0 and 749.99) ORDER BY Cur_Bal

    ' open DEP list for this Client name and date
    oDBUtil.OpenADORecordSet sqlquery, cnADOSQL, rsADO, lROF, adCmdText
    Debug.Print sqlquery
    ' load needed information for arrays from recordset created
    Do Until rsADO.EOF
    ReDim Preserve sClientName(i)
    ReDim Preserve fBalance(i)
    ReDim Preserve sDebtorID(i)
    ReDim Preserve sWorklist(i)
    ReDim Preserve bDistributed(i)
    ReDim bDistributedTemp(i)
    sClientName(i) = Trim(rsADO!Clt_id)
    fBalance(i) = rsADO!Cur_bal
    sDebtorID(i) = rsADO!Debtor_id
    sWorklist(i) = rsADO!Worklist
    i = i + 1
    nAccountsPerWorklist = i / nIndex
    If nIndex <= (UBound(fBalance) + 1) Or nAccountsPerWorklist = 0 Then
    nQuantity = UBound(sWorklist) / nIndex
    ' distribute quantity evenly amount users
    For i = 0 To UBound(sWorklist) 'Story Start Line
    If y > nIndex - 1 Then
    y = 0
    End If
    If sWorklist(i) <> sDistribution(y) And Not InStr(Exceptions, sWorklist(i)) > 0 Then 'This line also check
    k = k + 1
    grdDistribute.Rows = k + 1
    grdDistribute.TextMatrix(k, 1) = sClientName(i)
    grdDistribute.TextMatrix(k, 2) = sDebtorID(i)
    grdDistribute.TextMatrix(k, 3) = fBalance(i)
    grdDistribute.TextMatrix(k, 4) = rsADOSQL!Balance_Range
    grdDistribute.TextMatrix(k, 5) = sDistribution(y)
    fDistributionAmount(y) = fDistributionAmount(y) + fBalance(i)
    nDistributionCount(y) = nDistributionCount(y) + 1
    ReDim Preserve sDistributeID(nDistribute)
    sDistributeID(nDistribute) = sDistribution(y)
    sqlquery = "Insert Into tbl_DistList_AssignID(ClientID, AssignID, BalRangeID, BalLower, BalUpper) " & _
    "SELECT '" & sClientName(i) & "' , '" & sDistribution(y) & "' , '" & rsADOSQL!Balance_Range & "' , " &

    Val(Replace(rsADOSQL!bal_lower, ">", "")) & ", " & Val(rsADOSQL!bal_upper)
    ' Debug.Print sDistribution(y), rsADOSQL!Balance_Range
    Debug.Print sqlquery
    cnADOSQL.Execute sqlquery
    End If

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    I'm curious as to why you need to create an entirely new set of arrays? You're re-dimming them, populating them, and then immediately inserting the values from the arrays in to the grid. This is very inefficient. There doean't seem to be the requirement for the extra overhead, since you can just as easily copy the data from the recordset to the textgrid directly, without using the extra memory.

    Now. Since you're using an unbound grid for display, you can create the extra column in several different places.

    I would suggest that you create a placeholder column in your SQL Query. Instead of select *, use a select fieldlist aproach, and add an alias column with a fixed value. The field contents can be updated for any of the records in the recordset..

    Select Field1, Field2, ... FieldN, 1 as DupCount from Table...
    This will create an additional "field" with an alias of DupCount. Use this field as any other when creating a column in the flexgrid & populating it.

    Now, instead of looping through the arrays, loop through the recordset. You can use recordset fields just as easily as you can array elements. (you can refer to the fields by field number, if you wish)
    ' Open the recordset.
    ' Populate the grid.
    Do While not rs.EOF
      k = k + 1  ' Row Counter
      grdDistribute.Rows = k + 1
      For i = 0 to rs.Fields.Count-1  
        ' Copy all field values for record k to the grid
        grdDistribute.TextMatrix(k, i) = rs.Fields(i).Value
      Next i
    Likewise, you can use the recordset values directly when building a SQL insert string.

    Now, iterating through a recordset IS less efficient than iterating through an array. However, issuing a redim preserve for every record in the recordset, copying data from recordset to array, and immediately copying data from array to grid is hugely inefficient as well. Not only in processing time, but in wasted memory also. You've already created the overhead of a recordset - why not use IT efficiently instead. (Note that ADO recordset searches and sorts are much more efficient than searches and sorts in arrays, for instance)
    Last edited by loquin; 01-31-07 at 01:32.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts