Problem using RecordSet methods

[xyuri]

I am using the following code to create abd use a recordset:

SET RS = Server.CreateObject("ADODB.RecordSet")
SET RS.CursorLocation = aduseclient
SET RS.CursorType = dymanic
SET RS = cn.execute("SELECT * FROM thistable")

But all I get is the following error:

Microsoft VBScript runtime error '800a01a8'
Object required: 'CursorLocation'
/RUNGE ARCHIVING/default.asp, line 67

which is pointing to the folling line:
SET RS.CursorLocation = aduseclient

If I tafe out that line it will give problems in the line below it, it i delete that it works fine. But I want use to a dynamic recordset. Is there something I typed wrong or a setting I overlooked, or anything on how to fix this ??

any help or information greatly appreciated

[DaveMaxwell]

Do you adovbs.inc included so that adUseClient is understood? It's not a system constant persay that you can use automatically. You need to include the vbscript file which has that value defined.

[MarcusJT]

1) You only need the Set statement when you are assigning a value (e.g. Nothing) to an object, or assigning an object to a variable. The .CursorLocation property expects a numeric value (adUseClient is a defined constant with the value 3 for instance), not an object, and so the Set statement is not required. Ditto for .CursorType - consult your documentation to see what properties of the various ADO objects *are* actually objects, and which are simply string or numeric values.

2) Although not actually the cause of your current problem, once you have removed the unnecessary Set statements you may find that it will give you an error (something like "adUseClient is not defined") or perhaps simply not work as expected (but fail to raise an error). In both cases, and as DaveMaxwell has already pointed out, you must ensure that the ADO constants have been loaded by the ASP script engine - either by including the ADOVBS.INC file (the old way) or by including the appropriate METADATA statement in your script (which is much better)... see here for more...

3) "dymanic" is not only spelt incorrectly, but is not a valid CursorTypeEnum constant anyway - perhaps you mean "adOpenDynamic"?

4) Finally, your second "Set RS..." statement completely destroys the Recordset object that you have manually created and replaces it with completely new Recordset object, and hence your CursorLocation and CursorType settings (as well as anything else about the Recordset that you have altered) will be lost.

There are a great many ways to perform a query and return a recordset, but since you already seem to have an open Connection object and you clearly want to use specific Recordset settings, the most efficient method is probably to do the following (which also includes the changes I have suggested earlier):

<!-- METADATA TYPE="TypeLib" NAME="Microsoft ADO Type Library" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->
<%
Dim RS
Set RS = Server.CreateObject("ADODB.RecordSet" )
RS.CursorLocation = adUseClient
RS.CursorType = adOpenDynamic
Set RS.ActiveConnection = cn
RS.Source = "SELECT * FROM thistable"
RS.Open
%>

This could also be written like this:

Dim RS
Set RS = Server.CreateObject("ADODB.RecordSet" )
RS.CursorLocation = adUseClient
RS.Open "SELECT * FROM thistable", cn, adOpenDynamic

(note that there is no optional parameter for the CursorLocation property in the Open() method)

Does any of that help?


PS - I strongly suggest you download & refer to the ADO documentation which comes as part of the MDAC SDK, is located online here and is available for download here.

[xyuri]

I'll read on this more when I get home (@ work at the moment). where can I get this "adovbs.inc" from ?? I think it was included with something I downloaded, but I cant remember right now.

[xyuri]

well, I found the thing where I had seen it, and for anyone who finds any interest in it, here is the contents:

<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996 Microsoft Corporation.  All Rights Reserved.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- ExecuteOptionEnum Values ----
Const adRunAsync = &H00000010
'---- ObjectStateEnum Values ----
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001
Const adStateConnecting = &H00000002
Const adStateExecuting = &H00000004
'---- CursorLocationEnum Values ----
Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3
Const adUseClientBatch = 3
'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
'---- FieldAttributeEnum Values ----
Const adFldMayDefer = &H00000002
Const adFldUpdatable = &H00000004
Const adFldUnknownUpdatable = &H00000008
Const adFldFixed = &H00000010
Const adFldIsNullable = &H00000020
Const adFldMayBeNull = &H00000040
Const adFldLong = &H00000080
Const adFldRowID = &H00000100
Const adFldRowVersion = &H00000200
Const adFldCacheDeferred = &H00001000
'---- EditModeEnum Values ----
Const adEditNone = &H0000
Const adEditInProgress = &H0001
Const adEditAdd = &H0002
'---- RecordStatusEnum Values ----
Const adRecOK = &H0000000
Const adRecNew = &H0000001
Const adRecModified = &H0000002
Const adRecDeleted = &H0000004
Const adRecUnmodified = &H0000008
Const adRecInvalid = &H0000010
Const adRecMultipleChanges = &H0000040
Const adRecPendingChanges = &H0000080
Const adRecCanceled = &H0000100
Const adRecCantRelease = &H0000400
Const adRecConcurrencyViolation = &H0000800
Const adRecIntegrityViolation = &H0001000
Const adRecMaxChangesExceeded = &H0002000
Const adRecObjectOpen = &H0004000
Const adRecOutOfMemory = &H0008000
Const adRecPermissionDenied = &H0010000
Const adRecSchemaViolation = &H0020000
Const adRecDBDeleted = &H0040000
'---- GetRowsOptionEnum Values ----
Const adGetRowsRest = -1
'---- PositionEnum Values ----
Const adPosUnknown = -1
Const adPosBOF = -2
Const adPosEOF = -3
'---- enum Values ----
Const adBookmarkCurrent = 0
Const adBookmarkFirst = 1
Const adBookmarkLast = 2
'---- MarshalOptionsEnum Values ----
Const adMarshalAll = 0
Const adMarshalModifiedOnly = 1
'---- AffectEnum Values ----
Const adAffectCurrent = 1
Const adAffectGroup = 2
Const adAffectAll = 3
'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterPredicate = 4
'---- SearchDirection Values ----
Const adSearchForward = 0
Const adSearchBackward = 1
'---- ConnectPromptEnum Values ----
Const adPromptAlways = 1
Const adPromptComplete = 2
Const adPromptCompleteRequired = 3
Const adPromptNever = 4
'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &Hc
Const adModeShareDenyNone = &H10
'---- IsolationLevelEnum Values ----
Const adXactUnspecified = &Hffffffff
Const adXactChaos = &H00000010
Const adXactReadUncommitted = &H00000100
Const adXactBrowse = &H00000100
Const adXactCursorStability = &H00001000
Const adXactReadCommitted = &H00001000
Const adXactRepeatableRead = &H00010000
Const adXactSerializable = &H00100000
Const adXactIsolated = &H00100000
'---- XactAttributeEnum Values ----
Const adXactCommitRetaining = &H00020000
Const adXactAbortRetaining = &H00040000
'---- PropertyAttributesEnum Values ----
Const adPropNotSupported = &H0000
Const adPropRequired = &H0001
Const adPropOptional = &H0002
Const adPropRead = &H0200
Const adPropWrite = &H0400
'---- ErrorValueEnum Values ----
Const adErrInvalidArgument = &Hbb9
Const adErrNoCurrentRecord = &Hbcd
Const adErrIllegalOperation = &Hc93
Const adErrInTransaction = &Hcae
Const adErrFeatureNotAvailable = &Hcb3
Const adErrItemNotFound = &Hcc1
Const adErrObjectInCollection = &Hd27
Const adErrObjectNotSet = &Hd5c
Const adErrDataConversion = &Hd5d
Const adErrObjectClosed = &He78
Const adErrObjectOpen = &He79
Const adErrProviderNotFound = &He7a
Const adErrBoundToCommand = &He7b
Const adErrInvalidParamInfo = &He7c
Const adErrInvalidConnection = &He7d
Const adErrStillExecuting = &He7f
Const adErrStillConnecting = &He81
'---- ParameterAttributesEnum Values ----
Const adParamSigned = &H0010
Const adParamNullable = &H0040
Const adParamLong = &H0080
'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialContraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29
%>

ok, after looking at that table I managed to resolve my needs to the following code:

SET RS = Server.CreateObject("ADODB.RecordSet")
'RS.CursorLocation = 3
'RS.CursorType = 2
SET RS = cn.execute("SELECT * FROM " & the_table_name & " WHERE " & job_id_field & "=" & request.querystring("job") &"")

whch works on its own, although the reason I needed this was because I wanted to use the "RS.MovePrevious" Method, which now gives a "Operation is not allowed in this context" error.

[siteguru]

cn.Execute method creates a ReadOnly, ForwardOnly recordset. To use .MovePrevious you need to use a Dynamic or Keyset cursor ...

sSQL = SELECT * FROM " & the_table_name & " WHERE " & job_id_field & "=" & request.querystring( "job" )
SET RS = Server.CreateObject( "ADODB.RecordSet" )
RS.Open sSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText

[xyuri]

aaaaaah Thank you very much siteguru.

[MarcusJT]

It's all in the docs.....!!!

[xyuri]

well, M@cro, you know from previous postings of mine that I am nowhere near as blind as some The original code I posted was straight from a book I own and didnt think anything extra was needed, it did also have a reference in it of all the RecordSet methods, but they werent too helpfull. I think I did actually look on msdn for a small bit, but my comp is almost unable to run more than a single program at a time

anyway M@cro, just for you I'll do some extra research in future

[MarcusJT]

>>pulls hair out<<

Doing research isn't for my benefit, it's for yours!!

However, it *is* frustrating when I spend 20 minutes writing a comprehensive post for you and then find that you don't even appear to have read it...

- you search for the (outdated) ADOVBS.INC file when I've said you don't need it and given you a better method (with code - see the link I posted)

- the code you come up with after my post STILL does a SET twice (despite my having explained why this is counter-productive and inherently pointless)

- you create a new problem for yourself and then don't look up the .Execute method in the docs, where it clearly says

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

Of course, you don't *have* to read people's posts, but it's kind of the point of a forum, and it naturally makes me less inclined to try to help you in the future.


PS - you *also* keep getting my name wrong (it's M@rco not M@cro)

[xyuri]

I am sorry if I have pissed you off M@rco, but I have(and I have remembered seeing your name for thr first time ages ago) always, for some reason, thought it was M@cro. sorry about that.

Reguarding your posing, I'm extremely sorry that I forgot to read the second half of it thoroughly, but 99.9% of the time I'm on this computer it crashes or I'm told to get off the net "right now!!!!!" or I'm at work and can only read things very briefly, that is why any response was very short. now the rush is over and I only have study for the rest of the week and have time to read. I do see what you are saying and it does makse sense. Thank you very much for the time you put in in giving good responses, and you should know that this is not the first time I have aknowleged and thanked you for it

I will, therefore, have to use the OPEN method hey well, EXECUTE seems rather crap to me now that I know a bit more about it.

thanks again.

[siteguru]

.Execute is actually the best way to go - it is faster and uses far less server resources. Just about anything you can do with the .Open method you can do with the .Execute method, as long as you construct your SQL command correctly.

[MarcusJT]

xyuri - Don't worry, sorry I was a bit peeved, glad you've seen the light, all is forgiven!

<action type="pick fight">

.Execute is actually the best way to go - it is faster and uses far less server resources.

siteguru - I don't agree with what you say... the Connection.Execute method uses an open Connection object and creates a RecordSet (with default properties only), whereas the Recordset.Open method requires both an open Connection and a created (but not necessarily configured) Recordset. Either way, the same two objects must be created and hence the same resources are used.

The Recordset.Open approach allows you to configure the various Recordset properties, and the other does not (as we have both pointed out/quoted earlier in this thread), so there is no advantage to using the Connection.Execute method, apart from slightly less code, but I would hope that everyone encapsulates their queries in functions rather than typing the same boring code every time, so this (minor) advantage is nullified, and the versatility of the Recordset.Open approach wins hands down.

Just about anything you can do with the .Open method you can do with the .Execute method, as long as you construct your SQL command correctly.

Furthermore, there is *no* difference at all between the ways that the two methods interpret or execute T-SQL statements, so I don't follow you at all on that point.
</action>

[xyuri]

I may be slightly mistaken, but am I correct in saying that the .EXECUDE method cannot at all go backwards ??? cuase it will automaticall create an only-forward recordset ?

[DaveMaxwell]

The only time I can see a real advantage to using the execute method is when doing database updates (inserts/updates/deletes). In that case the execute method doesn't require the opening of a recordset.

[siteguru]

The only time I can see a real advantage to using the execute method is when doing database updates (inserts/updates/deletes). In that case the execute method doesn't require the opening of a recordset.

This is basically what I was alluding to in my previous post - why create a recordset when you don't need one?

[DaveMaxwell]

This is basically what I was alluding to in my previous post - why create a recordset when you don't need one?

Yeah, but in this case, the SQL being executed is a select, so you'd need the recordset anyways which is why I think M@rco (almost called you M@cro just because..) offered his later post.

[MarcusJT]

I may be slightly mistaken, but am I correct in saying that the .EXECUDE method cannot at all go backwards ??? cuase it will automaticall create an only-forward recordset ?

Yes, that's precisely what it says in the docs, as I've already quoted above (in my third post in this thread)....

>>pulls newly regrown hair out again<<

[vgarcia]

>>pulls newly regrown hair out again<<

Stop doing that M@rco, you're supposed to go bald AFTER you make management!

[MarcusJT]

I'm clearly getting old before my time.....

[vgarcia]

I'm clearly getting old before my time.....

Take a vacation </hijacked thread>

[MarcusJT]

lol