by Sameer Kamat (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 10th December 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
'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
No comments have been posted about The code teaches you how to convert the data in a recordset to string using ADO. Why not be the first to post a comment about The code teaches you how to convert the data in a recordset to string using ADO.
