Solved

Connecting SQL Server using ASP

Posted on 2004-08-16
9
188 Views
Last Modified: 2012-05-05
Hi All Experts,
I am new to ASP, so please suggest me with this question.
I am trying to connect to SQL Server using ASP page. My database is sitting on one machine, IIS server on the other and the clients will be using the database over internet. When i tried to connect using the following code i am getting and error. My code is:

<%
dim dbcon, sSQL, rs1
set dbcon=CreateObject("ADODB.Connection")
dbcon.Open "Provider=SQLOLEDB;Initial Catalog=MARS;DATA SOURCE=OCEANIC;User ID=mars;Password=mars"
sSQL="select * from transaction"
set rs1=CreateObject("ADODB.RecordSet")
rs1.Open sSQL,dbcon,adOpenKeyset,adLockPessimistic
%>

I am getting an error:

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

can any one please suggest me to resolve this problem.

Thanks in advance

0
Comment
Question by:bhaskarchalla
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 6

Expert Comment

by:casstd
ID: 11816767
Hi,

          This may be you did not include the ADOVBS.INC file in this because you use the adopenkeyset and adLockPessimistic.

<!--#include file="adovbs.inc"-->                           '''''''' Must include this file.


dim Dbobj, DbobjMembers
Dbobj =  "Provider=sqloledb;Data Source=(local);Initial Catalog=MARS;DATA SOURCE=OCEANIC;User ID=mars;Password=mars"
Set dbcon= Server.CreateObject("ADODB.CONNECTION")
dbcon.Open Dbobj

set rs1=CreateObject("ADODB.RecordSet")                            '''''''Mistake here

Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open "Transaction",dbcon, adOpenStatic, adLockReadOnly


Copy the below code and store as ADOVBS.INC and include this file where ever you use adopenstatic object cursor position.

Hope this would be helful to you.


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

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

'---- 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 = 1
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
%>

0
 
LVL 4

Expert Comment

by:Tasneem
ID: 11818016
re check  your connection string
dbcon.Open "Provider=SQLOLEDB;Initial Catalog=MARS;DATA SOURCE=OCEANIC;User ID=mars;Password=mars"
OCEANIC is the machine name. Try calling it with the ip address.
MARS is your database name.
Try this also
set rs1= dbcon.execute(sSQL)
0
 
LVL 2

Accepted Solution

by:
phunchak earned 125 total points
ID: 11821754
Try this...you don't need adovbs.inc for this way.


sessionConnection= "DSN=OCEANIC;UID=mars;Password=mars"
set Connection = server.CreateObject("ADODB.Connection")
Connection.Open(sessionConnection)      
sSQL="select * from transaction"
Set rowset = Connection.Execute(sSQL)
' Print out a column
response.write(rowset("somedatabasecolumn"))
0
 

Author Comment

by:bhaskarchalla
ID: 11853055
Hi Casstd/Tasneem/Phunchak,
Thanks to u all..
Casstd.. i have tried your option but i am still getting the same error.  and when i tried the other options i am getting and authentication error as my IIS and database server are on different machines.
if i use
        rs1.Open sSQL,dbcon, adOpenStatic
it works but i can not make any conditions on the quary. and if i specify lock type i am getting the error.

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

can any one please suggest me to resolve this problem.

Please suggest me in this regard.

Thanks..
Bhaskar
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:phunchak
ID: 11853089
Have you tried setting up an odbc connection and testing to make sure that works?
0
 

Author Comment

by:bhaskarchalla
ID: 11855187
phuchak,
odbc connection is fine.
0
 
LVL 2

Expert Comment

by:phunchak
ID: 11855234
Did you try my code snippet?  What's the exact error you are getting?
0
 

Expert Comment

by:challabhaskar
ID: 11870578
phunchak,
I am getting the following Error when i use 'group by' in the query.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'by'.

/CCRecharge/TopUP1OLEDBTest.asp, line 24

My syntax is:

dim sSQL, rs1
sessionConnection= "DSN=OCEANIC;UID=mars;Password=mars"
set Connection = server.CreateObject("ADODB.Connection")
Connection.Open(sessionConnection)
'sSQL="select * from [transaction]"
sSQL= "select * from (select convert(varchar,t1.trans_date,106) as Call_Date, "
sSQL=sSQL &      "sum(t1.total_value) as Total_Value, "
sSQL=sSQL & "count(t1.total_value) as Total_Count "
sSQL=sSQL & "from [transaction] t1"
sSQL=sSQL & "group by convert(varchar,t1.trans_date,106)) as a "
sSQL=sSQL & "order by convert(datetime,call_date)"
Set rs1 = Connection.Execute(sSQL)
'response.write(rowset("trans_date"))
0
 

Expert Comment

by:challabhaskar
ID: 11870619
phunchat,
i got it.. thanks..
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now