• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• The code teaches you how to convert the data in a recordset to string using ADO

The code teaches you how to convert the data in a recordset to string using ADO

The code teaches you how to convert the data in a recordset to string using ADO

API Declarations


Dim strConnectionString As String 'stores the connection string
Dim rs As New ADODB.Recordset 'used to store the data fetched
Dim strData As String 'used to store the string returned by the GetString method




Rate The code teaches you how to convert the data in a recordset to string using ADO

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'Use the GetString method of the Recordset Object.
'
'Syntax:
'------
'Set Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
'
'Arguments:
'---------
'StringFormat    - This argument has to be 'adClipString'.
'NumRows     - Optional. The number of rows in the recordset to convert. If NumRows is not specified, or if it is greater than the total number of rows in the recordset, then all the rows in the recordset are converted.
'ColumnDelimiter - Optional. Delimiter used between columns if specified, otherwise the TAB character.
'RowDelimiter    - Optional. Delimiter used between rows if specified, otherwise the CARRIAGE RETURN character.
'NullExpr    - Optional. Expression used in place of a NULL value if specified, otherwise the empty string.
'
'Output:
'------
'Returns the Recordset as a string.
'Note : Pl. do not go by the size of the code as most part of the code is comments
'       which will help you understand the GetString method in a better way.


Private Sub Form_Load()
    'initialize the connection string
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sameer\Visual Basic\TestDB.mdb;"

    'open the recordset
    rs.Open "SELECT * FROM [emp]", strConnectionString, adOpenKeyset 'note that connection object is not used
    'structure of emp file
    'Empno  - numeric
    'Name   - string
    'Sal    - numeric
    'Deptno - numeric

    'say Table 'emp' contains following data
    'Empno   Name    Sal Deptno
    '1       Sameer  2000    10
    '2       Kalpesh 3000    10
    '3       Sumit   3000    20
    '4       Niket   1500    30
    '5       Homi    4000    20

    'now let me explain what the output will be with respect to this data

    'Fetch all data
    '--------------
    strData = rs.GetString(adClipString)
    Debug.Print strData
    'Returns -
    '1   Sameer  2000    10
    '2   Kalpesh 3000    10
    '3   Sumit   3000    20
    '4   Niket   1500    30
    '5   Homi    4000    20

    'Fetch the first 2 rows
    '----------------------
    strData = rs.GetString(adClipString, 2)
    Debug.Print strData
    'Returns -
    '1   Sameer  2000    10
    '2   Kalpesh 3000    10

    'Specify the Column Delimiter
    '----------------------------
    strData = rs.GetString(adClipString, , ";")
    Debug.Print strData
    'Returns -
    '1;Sameer;2000;10
    '2;Kalpesh;3000;10
    '3;Sumit;3000;20
    '4;Niket;1500;30
    '5;Homi;4000;20

    'Specify the Row Delimiter
    '-------------------------
    strData = rs.GetString(adClipString, , ";", ":")
    Debug.Print strData
    'Returns -
    '1;Sameer;2000;10:2;Kalpesh;3000;10:3;Sumit;3000;20:4;Niket;1500;30:5;Homi;4000;20:

    'Specify what to display in case of Null
    '---------------------------------------
    'Suppose we add a new record to the emp table -
    '6       <NULL>  1000    20
    'Note : In the 6th record the name is null

    strData = rs.GetString(adClipString, , ",")
    'Returns -
    '1,Sameer,2000,10
    '2,Kalpesh,3000,10
    '3,Sumit,3000,20
    '4,Niket,1500,30
    '5,Homi,4000,20
    '6,,1000,20

    strData = rs.GetString(adClipString, , ",", , "NULL")
    'Returns -
    '1,Sameer,2000,10
    '2,Kalpesh,3000,10
    '3,Sumit,3000,20
    '4,Niket,1500,30
    '5,Homi,4000,20
    '6,NULL,1000,20
End Sub