How to create editable recordset without using a database table?

[John Dalberg]

Is there a way to create a dataset in VBScript without doing a query to a
real table? Can I define the recordset's fields and types through vbscript
only?

I want to use the recordset for memory use only. I can't use database
tables or xml files.

The record set needs to be editable.

John Dalberg

[Bob Barrows [MVP]]

Sure. I call it an ad hoc recordset, although many refer to it as a
disconnected recordset (which IMO is a different thing).

Quick example:

const adInteger=3
const advarchar=200
set rs=createobject("adodb.recordset")
with rs.Fields
.Append "col1",adInteger
.Append "col2",advarchar,200
End With
rs.open
rs.AddNew
rs(0)=258
rs(1) = "test"
rs.Update

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[ekkehard.horner]

John Dalberg schrieb:

If "dataset" doesn't imply .NET, you can use a "disconnected recordset"
(Classic ADO). This sample code (using such a recordset to sort an array)

Sub sortLinesAdo( aLines )
Const adVarWChar = 202 ' 000000CA
Dim oRS : Set oRS = CreateObject( "ADODB.Recordset" )
Dim nIdx

oRS.Fields.Append "Fld0", adVarWChar, 2000
oRS.Open

For nIdx = 0 To UBound( aLines )
oRS.AddNew
oRS.Fields( 0 ).value = aLines( nIdx )
oRS.UpDate
Next
If Not (oRS.BOF Or oRS.EOF) Then
oRS.Sort = "Fld0"
nIdx = 0
oRS.MoveFirst
Do Until oRS.EOF
aLines( nIdx ) = oRS( "Fld0" ).value
nIdx = nIdx + 1
oRS.MoveNext
Loop
End If
End Sub

shows some important actions that can be done to such a beast. Whether
it will be good for you, depends on what you want to achieve.

[Haritha]

I have created a disconnected recordset but I want to insert the recordset
data finally into my sql database table.

I have a table with two columns OrderNumber and EnrolledCourseId. On the ASP
form the user could select mutliple courses. So I am identifying all the
courses selected and inserted them into disconnected recordset created. But
finally I want to insert this recordset data into my sql table.

Is it possible using Classic ado?

Any help is highly appreciate.

Thanks

[Bob Barrows [MVP]]

Haritha wrote:
> I have created a disconnected recordset but I want to insert the
> recordset data finally into my sql database table.
>
> I have a table with two columns OrderNumber and EnrolledCourseId. On
> the ASP form the user could select mutliple courses. So I am
> identifying all the courses selected and inserted them into
> disconnected recordset created. But finally I want to insert this
> recordset data into my sql table.
>
> Is it possible using Classic ado?
>
> Any help is highly appreciate.
>

There are two types of recordsets that people tend to call "disconnected":
1. A true disconnected recordset which is opened on a database table or
query and then disconnected by setting its ActiveConnection property to
Nothing. This type of disconnected recordset can subsequently be reconnected
to the database by setting its ActiveConnection property to a variable
referencing an open Connection object. If the recordset was opened using the
adLockUpdateBatch setting, any changes made to the data in the recordset
while disconnected can be sent to the database with a single call to
UpdateBatch.

2. A recordset that is created entirely in code without ever being connected
to a database. I like to refer to this type of recordset as an "ad hoc"
recordset in order to distinguish it from the true disconnected recordset.
Since an ad hoc recordset was never connected to a database, it lacks all
the metadata information that would allow it to be connected to a database.
So, if you want to update a database table with the data in an ad hoc
recordset, you need to loop through the recordset, build the DML statements
needed, and execute them with a connection object.

You would do well to read about these topics in the documentation which can
be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"