User Tag List

Results 1 to 2 of 2
  1. #1
    Junior Member
    Join Date
    Jun 2004
    Salt Lake City, Utah
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Generic DB query

    I want to create a function that will return the data from a recordset in an array. Problems is that I want the code to be generic in that it can determine how many rows and fields are in the array so I can redim the array. for example if the query passed to the function has 4 fields or 20 fields how do you dynamicly redim the array to look like: redim arrRecords(rowcount, 1,1,n...)Is there a way to do this with out using 20 if statements or large case statement?

    Below is a function I used to create a generic function that returns a record count.

    Option Explicit

    Const adOpenStatic = 3
    Const adOpenDynamic = 2
    Const adOpenKeyset = 1
    Const adOpenforwardOnly = 0
    Const adLockOptimistic = 3
    Const adUseClient = 3

    Public Function DBGetRecordCount(strSQL, sServer, sUser, sPassword)

    if strSQL = "" then
    msgbox ("Usage: DBGetRecordCount(strSQL, sServer, sUser, sPassword)" & vbcrlf & _
    "example: DBGetRecordCount(""Select count(*) from mytable"", ""myDBInstance"", ""UserName"", ""Password"")")
    end if

    Dim objConnection
    Dim objRecordset

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    objConnection.Open "Driver={Microsoft ODBC for Oracle};Server=" & sServer & _
    ";Uid=" & sUser & _
    ";Pwd=" & sPassword & ";"

    objRecordset.CursorLocation = adUseClient
    objRecordset.CursorType = adOpenStatic
    objRecordset.LockType = adlockoptimistic
    ObjRecordset.Source= strSQL

    ObjRecordset.Open 'This will execute your Query
    DBGetRecordCount = 0
    While Not ObjRecordset.EOF
    DBGetRecordCount = ObjRecordset.Fields(0).Value
    ' WSCript.Echo ObjRecordset.Fields(0).Value
    Set ObjRecordset = Nothing
    set objConnection = Nothing
    End Function

  2. #2
    Join Date
    Jul 2005
    Post Thanks / Like
    0 Post(s)
    7 Thread(s)

    Re: Generic DB query

    GetRows Method
    Retrieves multiple records of a Recordset object into an array.

    array = recordset.GetRows( Rows, Start, Fields )
    Return Value
    Returns a Variant whose value is a two-dimensional array.

    Optional. A GetRowsOptionEnum value that indicates the number of records to retrieve. The default is adGetRowsRest.
    Optional. A String value or Variant that evaluates to the bookmark for the record from which the GetRows operation should begin. You can also use a BookmarkEnum value.
    Optional. A Variant that represents a single field name or ordinal position, or an array of field names or ordinal position numbers. ADO returns only the data in these fields.
    Use the GetRows method to copy records from a Recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.

    If you do not specify a value for the Rows argument, the GetRows method automatically retrieves all the records in the Recordset object. If you request more records than are available, GetRows returns only the number of available records.

    If the Recordset object supports bookmarks, you can specify at which record the GetRows method should begin retrieving data by passing the value of that record's Bookmark property in the Start argument.

    If you want to restrict the fields that the GetRows call returns, you can pass either a single field name/number or an array of field names/numbers in the Fields argument.

    After you call GetRows, the next unread record becomes the current record, or the EOF property is set to True if there are no more records.



Posting Permissions

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

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 01:43 AM.

Copyright BetaSoft Inc.