SQL Server Performance

Using GetRows and Fields parameter Problems (ADO

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by tmarko, Sep 20, 2004.

  1. tmarko New Member

    I want to use GetRows to fill an array from an ADO recordset

    Arr =RS.GetRows

    But I also would like to specify which Fileds from my recordset to include in the array. There is a parameter called Field that is used for this. Can be an array of field names and then only those should be included in the resulting array. But this is not working for me I get an error.I tried this code that I found on the net but without luck

    Dim arrRec As Variant

    'Create array that will hold fields to be returned
    Dim arrNum() As Variant

    'Fill the arrNum with Field Names

    'Grab the entire recordset by passing in the recordcount
    'as the number of rows requested.

    'The second argument tells ADO to begin with a specific bookmark.
    'Since we haven't created one, we leave the argument blank

    'The third argument is the array of field names that will be returned
    'All other fields will not be assigned to the array.
    arrRec = objRec.GetRows(objRec.RecordCount, , arrNum)

    (moved from General DBA section)
  2. tdong New Member

    Can you be more specific on what you want to do ? you want to query some table and put all the data in to an array ? here are part of my sub. RS.movenext should correct your problem. RS.Fields(sColName).Value give you the value of that row under sColName<br /><br />Dim sql As String<br /> Dim rs As ADODB.Recordset<br /> Dim datesArray() As String<br /> Dim ratesArray() As String<br /> <br /> 'Getting dates and rates into array for search and compute<br /> sql = "select coupon_date, rate from bond_cashflows where bond_id ='" & sID & "' order by coupon_date"<br /> Set rs = GetResultSet(sql)<br /> count = rs.RecordCount<br /> ReDim datesArray(count) As String<br /> ReDim ratesArray(count) As String<br /> Counter = 1<br /> While Not (rs.EOF)<br /> If Counter &lt;= count Then<br /> datesArray(Counter) = FormatDateStd(GetColumnValue(rs, "coupon_date", ""))<br /> ratesArray(Counter) = GetColumnValue(rs, "rate", "")<br /> End If<br /> rs.MoveNext<br /> Counter = Counter + 1<br /> <br /> Wend<br /> CloseResultSet rs<br /><br />Public Function GetColumnValue(oRS As ADODB.Recordset, sColName As String, Optional sNullDefault As String)<br /><br /> If IsNull(oRS.Fields(sColName).Value) Then<br /> GetColumnValue = IIf(IsNull(oRS.Fields(sColName).Value), sNullDefault, Null)<br /> Else<br /> GetColumnValue = oRS.Fields(sColName).Value<br /> End If<br /><br />End Function<br /><br />Public Function GetResultSet(sSql As String) As ADODB.Recordset<br /><br /> Dim Connect As String<br /> Dim Rs1 As New ADODB.Recordset<br /> <br /> 'gAdoConnectStr = "DSN=Trade;UID=sa<img src='/community/emoticons/emotion-4.gif' alt=';P' />WD=victoria;"<br /> <br /> Rs1.Open sSql, GetConnection(), adOpenStatic, adLockOptimistic<br /> Set GetResultSet = Rs1<br /><br />End Function<br /><br />Private gConnection As ADODB.Connection<br /><br />'Public Const gAdoConnectStr As String = "driver={SQL Server};OLE DB Services=-1;" & _<br />' "server=ntsvr1;uid=sa<img src='/community/emoticons/emotion-4.gif' alt=';p' />wd=password;database=Database"<br />Public Function GetConnection() As ADODB.Connection<br /> If (gConnection Is Nothing) Then<br /> Set gConnection = New ADODB.Connection<br /> gConnection.ConnectionString = gAdoConnectStr<br /> gConnection.Open<br /> End If<br /> Set GetConnection = gConnection<br /><br />End Function<br /><br />Public Sub CloseDBConnection()<br /> If (Not gConnection Is Nothing) Then<br /> gConnection.Close<br /> Set gConnection = Nothing<br /> End If<br />End Sub<br /><br />May the best cheaters win

Share This Page