Change Recordset Cursor Type ASP VBScript

garethtnash
garethtnash used Ask the Experts™
on
Hello,

I've just got the following error, which would indicate to me that I need to change the recordset cursor type.....

How do I do this, in my recordset or connection string,

assuming my recordset is -

<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
RsProductDetail_cmd.CommandText = "SELECT Something from dbo.somewhere" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

Set RsProductDetail = RsProductDetail_cmd.Execute
RsProductDetail_numRows = 0
%>

Open in new window


OR my connection string is -

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO" 
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_Connection_STRING
MM_Connection_STRING = "Provider=SQLOLEDB.1;Password=APassword;User ID=AUserID;Initial Catalog=ACatologue;Data Source=111.111.111.11"
%>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
what error?

Author

Commented:
"Microsoft OLE DB Provider for SQL Server error '80040e18'
Rowset position cannot be restarted.
/detail.asp, line 36"


Thanks
Top Expert 2011

Commented:
There is got to be more to your connection string.

Is that all you have?

Author

Commented:
Connection string or recordset? I have not included the exact query of the recordset in the post, that aside yes that is everything
Top Expert 2011

Commented:
I have not seen the rest of your code but it seems to me that you have FORWARDONLY cursor.

You need to add these lines to your query.

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient ' 3
rs.Open RsProductDetail_cmd, , adOpenStatic ' 3

Open in new window


OR

find adovbs and add to the top of your page as include like:

 #include'd the "adovbs.inc"

And you should be ok


Author

Commented:
Thanks Sammy, where do I add those three lines? I've tried -

<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient  3
rs.Open RsProductDetail_cmd, , adOpenStatic  3
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
RsProductDetail_cmd.CommandText = "SELECT something from somewhere" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

Set RsProductDetail = RsProductDetail_cmd.Execute
RsProductDetail_numRows = 0
%>

Open in new window


But I get the following error -

""Microsoft VBScript compilation error '800a0401'
Expected end of statement
/detail.asp, line 21
rs.CursorLocation = adUseClient  3""
Top Expert 2011

Commented:
comment out the rs.cursorlocation for now just to isolate the error

Author

Commented:
That gives me -

Microsoft VBScript compilation error '800a0401'
Expected end of statement
/detail.asp, line 21
rs.Open RsProductDetail_cmd, , adOpenStatic  3

ALSO shouldn't

rs.Open RsProductDetail_cmd, , adOpenStatic  3

be rs.Open RsProductDetail_cmd, adOpenStatic  3
Top Expert 2011

Commented:
Copy this entire code into a file and name it as adovbs.inc

Then include it at top of your page.

<!--#include filel="adovbs.inc"-->


Remove those 3 lines of code I sent you and use this instead.


<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' Copyright (c) 1996-1998 Microsoft Corporation.
'
'
'
' 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
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- ExecuteOptionEnum Values ----
Const adAsyncExecute = &H00000010
Const adAsyncFetch = &H00000020
Const adAsyncFetchNonBlocking = &H00000040
Const adExecuteNoRecords = &H00000080
Const adExecuteStream = &H00000400

'---- ConnectOptionEnum Values ----
Const adAsyncConnect = &H00000010

'---- ObjectStateEnum Values ----
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001
Const adStateConnecting = &H00000002
Const adStateExecuting = &H00000004
Const adStateFetching = &H00000008

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 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
Const adChapter = 136
Const adFileTime = 64
Const adPropVariant = 138
Const adVarNumeric = 139
Const adArray = &H2000

'---- 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
Const adFldIsChapter = &H00002000
Const adFldNegativeScale = &H00004000
Const adFldKeyColumn = &H00008000
Const adFldIsRowURL = &H00010000
Const adFldIsDefaultStream = &H00020000
Const adFldIsCollection = &H00040000

'---- EditModeEnum Values ----
Const adEditNone = &H0000
Const adEditInProgress = &H0001
Const adEditAdd = &H0002
Const adEditDelete = &H0004

'---- 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

'---- BookmarkEnum 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 adAffectAllChapters = 4

'---- ResyncEnum Values ----
Const adResyncUnderlyingValues = 1
Const adResyncAllValues = 2

'---- CompareEnum Values ----
Const adCompareLessThan = 0
Const adCompareEqual = 1
Const adCompareGreaterThan = 2
Const adCompareNotEqual = 3
Const adCompareNotComparable = 4

'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterConflictingRecords = 5

'---- SearchDirectionEnum Values ----
Const adSearchForward = 1
Const adSearchBackward = -1

'---- PersistFormatEnum Values ----
Const adPersistADTG = 0
Const adPersistXML = 1

'---- StringFormatEnum Values ----
Const adClipString = 2

'---- 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
Const adModeRecursive = &H400000

'---- RecordCreateOptionsEnum Values ----
Const adCreateCollection = &H00002000
Const adCreateStructDoc = &H80000000
Const adCreateNonCollection = &H00000000
Const adOpenIfExists = &H02000000
Const adCreateOverwrite = &H04000000
Const adFailIfNotExists = -1

'---- RecordOpenOptionsEnum Values ----
Const adOpenRecordUnspecified = -1
Const adOpenOutput = &H00800000
Const adOpenAsync = &H00001000
Const adDelayFetchStream = &H00004000
Const adDelayFetchFields = &H00008000
Const adOpenExecuteCommand = &H00010000

'---- 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 adErrProviderFailed = &Hbb8
Const adErrInvalidArgument = &Hbb9
Const adErrOpeningFile = &Hbba
Const adErrReadFile = &Hbbb
Const adErrWriteFile = &Hbbc
Const adErrNoCurrentRecord = &Hbcd
Const adErrIllegalOperation = &Hc93
Const adErrCantChangeProvider = &Hc94
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 adErrNotReentrant = &He7e
Const adErrStillExecuting = &He7f
Const adErrOperationCancelled = &He80
Const adErrStillConnecting = &He81
Const adErrInvalidTransaction = &He82
Const adErrUnsafeOperation = &He84
Const adwrnSecurityDialog = &He85
Const adwrnSecurityDialogHeader = &He86
Const adErrIntegrityViolation = &He87
Const adErrPermissionDenied = &He88
Const adErrDataOverflow = &He89
Const adErrSchemaViolation = &He8a
Const adErrSignMismatch = &He8b
Const adErrCantConvertvalue = &He8c
Const adErrCantCreate = &He8d
Const adErrColumnNotOnThisRow = &He8e
Const adErrURLIntegrViolSetColumns = &He8f
Const adErrURLDoesNotExist = &He8f
Const adErrTreePermissionDenied = &He90
Const adErrInvalidURL = &He91
Const adErrResourceLocked = &He92
Const adErrResourceExists = &He93
Const adErrCannotComplete = &He94
Const adErrVolumeNotFound = &He95
Const adErrOutOfSpace = &He96
Const adErrResourceOutOfScope = &He97
Const adErrUnavailable = &He98
Const adErrURLNamedRowDoesNotExist = &He99
Const adErrDelResOutOfScope = &He9a
Const adErrPropInvalidColumn = &He9b
Const adErrPropInvalidOption = &He9c
Const adErrPropInvalidValue = &He9d
Const adErrPropConflicting = &He9e
Const adErrPropNotAllSettable = &He9f
Const adErrPropNotSet = &Hea0
Const adErrPropNotSettable = &Hea1
Const adErrPropNotSupported = &Hea2
Const adErrCatalogNotSet = &Hea3
Const adErrCantChangeConnection = &Hea4
Const adErrFieldsUpdateFailed = &Hea5
Const adErrDenyNotSupported = &Hea6
Const adErrDenyTypeNotSupported = &Hea7
Const adErrProviderNotSpecified = &Hea9

'---- 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
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

'---- EventStatusEnum Values ----
Const adStatusOK = &H0000001
Const adStatusErrorsOccurred = &H0000002
Const adStatusCantDeny = &H0000003
Const adStatusCancel = &H0000004
Const adStatusUnwantedEvent = &H0000005

'---- EventReasonEnum Values ----
Const adRsnAddNew = 1
Const adRsnDelete = 2
Const adRsnUpdate = 3
Const adRsnUndoUpdate = 4
Const adRsnUndoAddNew = 5
Const adRsnUndoDelete = 6
Const adRsnRequery = 7
Const adRsnResynch = 8
Const adRsnClose = 9
Const adRsnMove = 10
Const adRsnFirstChange = 11
Const adRsnMoveFirst = 12
Const adRsnMoveNext = 13
Const adRsnMovePrevious = 14
Const adRsnMoveLast = 15

'---- 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 adSchemaReferentialConstraints = 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
Const adSchemaDBInfoKeywords = 30
Const adSchemaDBInfoLiterals = 31
Const adSchemaCubes = 32
Const adSchemaDimensions = 33
Const adSchemaHierarchies = 34
Const adSchemaLevels = 35
Const adSchemaMeasures = 36
Const adSchemaProperties = 37
Const adSchemaMembers = 38
Const adSchemaTrustees = 39
Const adSchemaFunctions = 40
Const adSchemaActions = 41
Const adSchemaCommands = 42
Const adSchemaSets = 43

'---- FieldStatusEnum Values ----
Const adFieldOK = 0
Const adFieldCantConvertValue = 2
Const adFieldIsNull = 3
Const adFieldTruncated = 4
Const adFieldSignMismatch = 5
Const adFieldDataOverflow = 6
Const adFieldCantCreate = 7
Const adFieldUnavailable = 8
Const adFieldPermissionDenied = 9
Const adFieldIntegrityViolation = 10
Const adFieldSchemaViolation = 11
Const adFieldBadStatus = 12
Const adFieldDefault = 13
Const adFieldIgnore = 15
Const adFieldDoesNotExist = 16
Const adFieldInvalidURL = 17
Const adFieldResourceLocked = 18
Const adFieldResourceExists = 19
Const adFieldCannotComplete = 20
Const adFieldVolumeNotFound = 21
Const adFieldOutOfSpace = 22
Const adFieldCannotDeleteSource = 23
Const adFieldReadOnly = 24
Const adFieldResourceOutOfScope = 25
Const adFieldAlreadyExists = 26
Const adFieldPendingInsert = &H10000
Const adFieldPendingDelete = &H20000
Const adFieldPendingChange = &H40000
Const adFieldPendingUnknown = &H80000
Const adFieldPendingUnknownDelete = &H100000

'---- SeekEnum Values ----
Const adSeekFirstEQ = &H1
Const adSeekLastEQ = &H2
Const adSeekAfterEQ = &H4
Const adSeekAfter = &H8
Const adSeekBeforeEQ = &H10
Const adSeekBefore = &H20

'---- ADCPROP_UPDATECRITERIA_ENUM Values ----
Const adCriteriaKey = 0
Const adCriteriaAllCols = 1
Const adCriteriaUpdCols = 2
Const adCriteriaTimeStamp = 3

'---- ADCPROP_ASYNCTHREADPRIORITY_ENUM Values ----
Const adPriorityLowest = 1
Const adPriorityBelowNormal = 2
Const adPriorityNormal = 3
Const adPriorityAboveNormal = 4
Const adPriorityHighest = 5

'---- ADCPROP_AUTORECALC_ENUM Values ----
Const adRecalcUpFront = 0
Const adRecalcAlways = 1

'---- ADCPROP_UPDATERESYNC_ENUM Values ----

'---- ADCPROP_UPDATERESYNC_ENUM Values ----

'---- MoveRecordOptionsEnum Values ----
Const adMoveUnspecified = -1
Const adMoveOverWrite = 1
Const adMoveDontUpdateLinks = 2
Const adMoveAllowEmulation = 4

'---- CopyRecordOptionsEnum Values ----
Const adCopyUnspecified = -1
Const adCopyOverWrite = 1
Const adCopyAllowEmulation = 4
Const adCopyNonRecursive = 2

'---- StreamTypeEnum Values ----
Const adTypeBinary = 1
Const adTypeText = 2

'---- LineSeparatorEnum Values ----
Const adLF = 10
Const adCR = 13
Const adCRLF = -1

'---- StreamOpenOptionsEnum Values ----
Const adOpenStreamUnspecified = -1
Const adOpenStreamAsync = 1
Const adOpenStreamFromRecord = 4

'---- StreamWriteEnum Values ----
Const adWriteChar = 0
Const adWriteLine = 1

'---- SaveOptionsEnum Values ----
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2

'---- FieldEnum Values ----
Const adDefaultStream = -1
Const adRecordURL = -2

'---- StreamReadEnum Values ----
Const adReadAll = -1
Const adReadLine = -2

'---- RecordTypeEnum Values ----
Const adSimpleRecord = 0
Const adCollectionRecord = 1
Const adStructDoc = 2
%>

Open in new window

Author

Commented:
Sorry Sammy, no luck -

Microsoft OLE DB Provider for SQL Server error '80040e18'
Rowset position cannot be restarted.
/detail.asp, line 37

:(
Top Expert 2011

Commented:
please show me ALL your query code.

Author

Commented:
OK, but the thing is the recordset loads the first time (Which tells me the select statement is returning a recordset no problem....

It is only when I do a -

if (cStr(Request.Form("theform")) <> "" OR cStr(Request.Form("theform.x")) <> "")     then

call that it throughs the error --

Thank you
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Connection.asp" -->
<!--#include file="includes/adovbs.inc" -->
<!--#include file="includes/Category-Navigation.asp" -->
<!--#include file="includes/Related-Products-Top.asp" -->
<%
Dim RsProductDetail__MMColParam
RsProductDetail__MMColParam = "0"
If (Request("p") <> "") Then 
  RsProductDetail__MMColParam = Request("p")
End If
%>
<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
RsProductDetail_cmd.CommandText = "SELECT P.ID, P.[Product-Name],  P.[Product-Description] AS Description,  Case When P.[Display-XXX-Special-Price]='Y' then DP.[Special-Price] Else DP.[Normal-Price] end AS Price,  Case When P.[Display-XXX-Special-Price]='Y' then DP.[Normal-Price] end AS  OriginalPrice,  CASE WHEN P.[Display-XXX-Special-Description]='Y' THEN P.[Special-Description-XXX] end AS SpecialDescription, CONVERT(VARCHAR(10), P.[Availability-date], 103) AS AvailableDate, DATEDIFF(d,GETDATE(),P.[Availability-date]) AS AvailableWhen, Case When P.[Suggested-Sale-Price] <> NULL Then P.[Suggested-Sale-Price] end AS SSP, I.Image AS MainImage,  I2.Image as Image2, I3.Image as Image3, I4.Image as Image4, I5.Image as Image5, I6.Image as Image6, P.SKUCode, P.StockCount, NC.Code AS NOMINALCode FROM dbo.[Direct-Product] P  LEFT OUTER join dbo.[Direct-Product-Image] I on I.ProductID = P.ID AND I.[Default-Image] = 'Y' LEFT OUTER join dbo.[Direct-Product-Image] I2 on I2.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I2b where i2b.ProductID = i2.productid  AND I.[Default-Image] <> 'Y' and I2b.id <= I2.id) = 1 LEFT OUTER join dbo.[Direct-Product-Image] I3 on I3.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I3b where i3b.ProductID = I3.ProductID  AND I.[Default-Image] <> 'Y' and I3b.id <= I3.id) = 2 LEFT OUTER Join dbo.[Direct-Product-Image] I4 on I4.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I4b where I4b.ProductID = i4.productid  AND I.[Default-Image] <> 'Y' and I4b.id <= I4.id) = 3 LEFT OUTER Join dbo.[Direct-Product-Image] I5 on I5.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I5b where I5b.ProductID = i5.productid  AND I.[Default-Image] <> 'Y' and I5b.id <= I5.id) = 4 LEFT OUTER Join dbo.[Direct-Product-Image] I6 on I6.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) FROM dbo.[Direct-Product-Image] I6b where I6b.ProductID = i6.productid  AND I.[Default-Image] <> 'Y' and I6b.id <= I6.id) = 5 inner join dbo.[Direct-Product-Price] DP  on DP.[Product-ID] = P.ID  inner join dbo.[Direct-Product-Category] DPC on DPC.[Product-ID] = P.ID  inner join dbo.[Direct-Category] C on C.ID = DPC.[Category-ID] inner join dbo.[Nominal-Codes] NC ON P.NominalCodeID = NC.ID WHERE P.[Display-XXX-Direct] = 'Y' AND P.ID = ? ORDER BY P.ID DESC" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

Set RsProductDetail = RsProductDetail_cmd.Execute
RsProductDetail_numRows = 0
%>

Open in new window

Top Expert 2011

Commented:
Try this:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Connection.asp" -->
<!--#include file="includes/adovbs.inc" -->
<!--#include file="includes/Category-Navigation.asp" -->
<!--#include file="includes/Related-Products-Top.asp" -->
<%
Dim RsProductDetail__MMColParam
RsProductDetail__MMColParam = "0"
If (Request("p") <> "") Then 
  RsProductDetail__MMColParam = Request("p")
End If
%>
<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
Set rs=Server.CreateObject("ADODB.Recordset")
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
rs.CursorLocation = 3

RsProductDetail_cmd.CommandText = "SELECT P.ID, P.[Product-Name],  P.[Product-Description] AS Description,  Case When P.[Display-XXX-Special-Price]='Y' then DP.[Special-Price] Else DP.[Normal-Price] end AS Price,  Case When P.[Display-XXX-Special-Price]='Y' then DP.[Normal-Price] end AS  OriginalPrice,  CASE WHEN P.[Display-XXX-Special-Description]='Y' THEN P.[Special-Description-XXX] end AS SpecialDescription, CONVERT(VARCHAR(10), P.[Availability-date], 103) AS AvailableDate, DATEDIFF(d,GETDATE(),P.[Availability-date]) AS AvailableWhen, Case When P.[Suggested-Sale-Price] <> NULL Then P.[Suggested-Sale-Price] end AS SSP, I.Image AS MainImage,  I2.Image as Image2, I3.Image as Image3, I4.Image as Image4, I5.Image as Image5, I6.Image as Image6, P.SKUCode, P.StockCount, NC.Code AS NOMINALCode FROM dbo.[Direct-Product] P  LEFT OUTER join dbo.[Direct-Product-Image] I on I.ProductID = P.ID AND I.[Default-Image] = 'Y' LEFT OUTER join dbo.[Direct-Product-Image] I2 on I2.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I2b where i2b.ProductID = i2.productid  AND I.[Default-Image] <> 'Y' and I2b.id <= I2.id) = 1 LEFT OUTER join dbo.[Direct-Product-Image] I3 on I3.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I3b where i3b.ProductID = I3.ProductID  AND I.[Default-Image] <> 'Y' and I3b.id <= I3.id) = 2 LEFT OUTER Join dbo.[Direct-Product-Image] I4 on I4.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I4b where I4b.ProductID = i4.productid  AND I.[Default-Image] <> 'Y' and I4b.id <= I4.id) = 3 LEFT OUTER Join dbo.[Direct-Product-Image] I5 on I5.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) from dbo.[Direct-Product-Image] I5b where I5b.ProductID = i5.productid  AND I.[Default-Image] <> 'Y' and I5b.id <= I5.id) = 4 LEFT OUTER Join dbo.[Direct-Product-Image] I6 on I6.ProductID = P.ID AND I.[Default-Image] <> 'Y' and (select COUNT(*) FROM dbo.[Direct-Product-Image] I6b where I6b.ProductID = i6.productid  AND I.[Default-Image] <> 'Y' and I6b.id <= I6.id) = 5 inner join dbo.[Direct-Product-Price] DP  on DP.[Product-ID] = P.ID  inner join dbo.[Direct-Product-Category] DPC on DPC.[Product-ID] = P.ID  inner join dbo.[Direct-Category] C on C.ID = DPC.[Category-ID] inner join dbo.[Nominal-Codes] NC ON P.NominalCodeID = NC.ID WHERE P.[Display-XXX-Direct] = 'Y' AND P.ID = ? ORDER BY P.ID DESC" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

Set RsProductDetail = RsProductDetail_cmd.Execute
rs.Open RsProductDetail_cmd,,adOpenStatic,adLockReadOnly
RsProductDetail_numRows = 0
%>

Open in new window


Since we are embedding the cursor types and locations in the code, you can remove the adovbs.inc file.
Give the code where you are trying to use the recordset... it appears that you traverse through the recordset and try to move back in the recordset
Top Expert 2012

Commented:
For performance reasons the Command method you are using returns a firehose (forward-only, read-only) cursor.  

If performance is not a big deal for you and you just have to navigate back in the recordset than consider using the Recordset object's Open method instead.
Top Expert 2012

Commented:
Ah, never mind, I see now that has already been explained to you.
Top Expert 2012

Commented:
Another workaround you can use is to leave your code as is (with teh firehose cursor) and just load an array with the resultset using the Recordset's GetRows method.  You can then do what you like with the array.  This way you get the performance you may need and the flexbility you are after.
Top Expert 2011

Commented:
I have to admit that I have never been a good fan of writing code the way it is being written.

It doesn't seem elegant to me and it is very confusing (to me atleast) and you guys can see, debugging is a nightmare.

Author

Commented:
Thanks Sammy, but that gives me,

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/detail.asp, line 29

ACPerkins, could you explain the other options please?

Sorry about the way the code is written.. Any suggestions on how to improve it?.

Thanks

Author

Commented:
Ultimately, as long as my select statement can run, I'm open to best suggestion as to the construction of the recordset object itself.

Thank you All
Top Expert 2011

Commented:
what is on line 29?

Top Expert 2011

Commented:
I am not criticizinng your code at all. I am just simply saying that method which has existed before you and I started coding is not my cup of tea.

Author

Commented:
Sammy, no offence taken on the code, I'm open to suggestions, I just need to get the RS working...

Line 29 was Set RsProductDetail = RsProductDetail_cmd.Execute


I'll be off line for a few hours, will pick up later this evening, thank you all for your help so far :)
Top Expert 2011
Commented:
You can either change this
Set RsProductDetail = RsProductDetail_cmd.Execute

to

Set rs = RsProductDetail_cmd.Execute

which matches the recordset I had set earlier at

set rs=server.createOject("adodb.recordset")

or anywhere you see rs, change it to RsProductDetail

we will revisit this line:
set rs=server.createOject("adodb.recordset")

later.

Author

Commented:
Hi Sammy,

I think I might have found the solution... well its your solution just slightly rewritten,,

<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
RsProductDetail_cmd.CommandText = "SELECT Something from dbo.somewhere" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

RsProductDetail_cmd.ActiveConnection.CursorLocation = 3
Set RsProductDetail = RsProductDetail_cmd.Execute
RsProductDetail_numRows = 0
%>

Open in new window


I've checked a previous project on my home laptop, this appeared to have worked previously..

I'll let you know on Monday,.. thanks for your help today...

Author

Commented:
Morning,

Sadly the fix hasn't worked, my code now looks like -

<%
Dim RsProductDetail
Dim RsProductDetail_cmd
Dim RsProductDetail_numRows

Set RsProductDetail_cmd = Server.CreateObject ("ADODB.Command")
RsProductDetail_cmd.ActiveConnection = MM_Connection_STRING
RsProductDetail_cmd.CommandText = "SELECT Something from Somewhere Order By ID DESC" 
RsProductDetail_cmd.Prepared = true
RsProductDetail_cmd.Parameters.Append RsProductDetail_cmd.CreateParameter("param1", 5, 1, -1, RsProductDetail__MMColParam) ' adDouble

RsProductDetail_cmd.ActiveConnection.CursorLocation = 3
Set RsProductDetail = RsProductDetail_cmd.Execute
RsProductDetail_numRows = 0
%>

Open in new window


AND

<%
if (cStr(Request.Form("ShoppingCart_1_ATC")) <> "" OR cStr(Request.Form("ShoppingCart_1_ATC.x")) <> "")     then
  ATC_itemID = cStr(Request.Form("ShoppingCart_1_ID_Add"))
  ATC_AddIfIn = 0
  ATC_RedirectAfter = "cart.html"
  ATC_RedirectIfIn  = ""
  ATC_RSPos = RsRelatedProduct.AbsolutePosition
  RsRelatedProduct.MoveFirst()
  DO while (NOT RsRelatedProduct.EOF)
    if (cStr(RsRelatedProduct.Fields("ID").value) = cStr(ATC_itemID))  then
      ATC_itemName = "" & cStr((RsRelatedProduct.Fields.Item("Product-Name").Value)) & ""' column binding
      ATC_itemDescription = "" & cStr((RsRelatedProduct.Fields.Item("Description").Value)) & ""' column binding
      ATC_itemWeight = cDbl("0")' column binding
      ATC_itemQuantity = "" & cStr(Request.Form("ShoppingCart_1_Quantity_Add")) & ""' column binding
      ATC_itemPrice = cDbl("" & cStr((RsRelatedProduct.Fields.Item("Price").Value)) & "")' column binding
      ATC_itemItemDeliveryCharge = cDbl("0")' column binding
      Exit DO
    end if
    RsRelatedProduct.MoveNext()
  Loop
  RsRelatedProduct.AbsolutePosition = ATC_RSPos
  if (IsNumeric(ATC_itemQuantity) AND ATC_itemQuantity <> 0)     then
    set ShoppingCart = ShoppingCart_AddToCart(ATC_AddIfIn, ATC_RedirectIfIn, ATC_itemID, ATC_itemName, ATC_itemDescription, ATC_itemWeight, ATC_itemQuantity, ATC_itemPrice, ATC_itemItemDeliveryCharge)
    if (ATC_RedirectAfter <> "" AND ShoppingCart_redirStr = "")     then
      ShoppingCart_redirStr = ATC_RedirectAfter
    end if
    Session("WAEC_ContinueRedirect") = cStr(Request.ServerVariables("Script_Name"))  & "?" & cStr(Request.QueryString())
  end if
end if
%>

Open in new window


The error I am getting is -

Microsoft OLE DB Provider for SQL Server error '80040e18'
Rowset position cannot be restarted.
/detail.asp, line 37


Line 37 is

RsRelatedProduct.MoveFirst()

in the second statement...

Thank you

Author

Commented:
Thank you
Top Expert 2012

Commented:
>>Sadly the fix hasn't worked, my code now looks like -<<
Right.  The Command object's Execute method returns a Forward Only cursor (that means you cannot use MoveFirst).  See here http:#a36317087

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial