Solved

Struggling to resolve ADO ASP error for SQL Server 2008 and IIS7

Posted on 2009-07-03
9
821 Views
Last Modified: 2013-12-25
Trying to convert a number of queries from dynamic sql to parameterised sql. The query used works as a dynamic sql statement, but with parameters I am getting:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict
 with one another.

conn.errors.count shows 0


SQL = "UPDATE tblListings SET userSynopsis = '" & userSynopsis & "' WHERE ListingID = "  & ListingID & ";"
 
		Set DataConnection = Server.CreateObject("ADODB.Connection")
		strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
		strconn=strconn & DatabasePathString
		DataConnection.Open SQLstrDataConnectionString
		'dataconnection.Execute SQL
 
		Set Cmd1 = CreateObject("ADODB.Command")
		Set Param1 = CreateObject("ADODB.Parameter")
		Set Param2 = CreateObject("ADODB.Parameter")
		Set Rs1 = CreateObject("ADODB.Recordset")
 
		Cmd1.ActiveConnection = DataConnection
		Cmd1.CommandText = "UPDATE tblListings SET userSynopsis = '?' WHERE ListingID = ?;"
 
		Set Param1 = Cmd1.CreateParameter("@userSynopsis", adVarWChar, adParamInput, 250,CStr(userSynopsis))
		Cmd1.Parameters.Append Param1
		Set Param2 = Cmd1.CreateParameter("@ListingID", adSmallInt, adParamInput, 4,CInt(ListingID))
		Cmd1.Parameters.Append Param2
		Set Param1 = Nothing
		Set Param2 = Nothing
 
		' Open Recordset Object.
		'Set Rs1 =
		Cmd1.Execute()
 
 
 
		DataConnection.Close
		Set DataConnection = Nothing

Open in new window

0
Comment
Question by:paul_noden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 

Author Comment

by:paul_noden
ID: 24774226
SQL Code Creation... most of this you won't need, but included for completeness!
CREATE TABLE [dbo].[tblListings](
	[listingID] [int] IDENTITY(1,1) NOT NULL,
	[listingProductID] [int] NULL,
	[dateListAdded] [datetime] NULL,
	[intListStatus] [int] NULL,
	[intPersonID] [int] NULL,
	[intCreditCost] [float] NULL,
	[memoDesc] [nvarchar](max) NULL,
	[intConditionType] [int] NULL,
	[bSold] [bit] NULL,
	[intUpdated] [int] NULL,
	[secureHash] [int] NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
	[userSynopsis] [nvarchar](250) NULL,
 CONSTRAINT [tblListings$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[listingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__listi__3D5E1FD2]  DEFAULT ((0)) FOR [listingProductID]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__intLi__3E52440B]  DEFAULT ((0)) FOR [intListStatus]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__intPe__3F466844]  DEFAULT ((0)) FOR [intPersonID]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__intCr__403A8C7D]  DEFAULT ((0)) FOR [intCreditCost]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__intCo__412EB0B6]  DEFAULT ((0)) FOR [intConditionType]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__bSold__4222D4EF]  DEFAULT ((0)) FOR [bSold]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__intUp__4316F928]  DEFAULT ((0)) FOR [intUpdated]
GO
 
ALTER TABLE [dbo].[tblListings] ADD  CONSTRAINT [DF__tblListin__secur__440B1D61]  DEFAULT ((0)) FOR [secureHash]
GO

Open in new window

0
 
LVL 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 240 total points
ID: 24776492
make sure that all names are correct.

Connection strings
RecordSet names
And Object names.

This error sometimes happens when something is mis-spelled.
So, double check everything.

And this looks like a Code issue, not a database issue.
(All of the above information is taken from my Personal Knowledge base that will be public
By Christmas. Everything that I personally experiance on my own, I write information on it. These are what I have come up against with this same error issue)

Good Luck
Carrzkiss
0
 
LVL 82

Assisted Solution

by:hielo
hielo earned 260 total points
ID: 24778797
instead of:
Set Param2 = Cmd1.CreateParameter("@ListingID", adSmallInt, ...
t
try using adIntgeger:
Set Param2 = Cmd1.CreateParameter("@ListingID", adInteger,...

http://www.w3schools.com/ADO/ado_datatypes.asp

 You have:
[listingID] [int] IDENTITY <==
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:paul_noden
ID: 24781395
Thanks for that, I had started with adInteger but must have forgot to put back the correct ado type after testing other types.

Set Param1 = Cmd1.CreateParameter(, adVarWChar, adParamInput, 250, CStr(userSynopsis))

is the first parameter to be reported as problematic.

Which is of course supposed to be the correct type.. Any thoughts?
0
 
LVL 82

Assisted Solution

by:hielo
hielo earned 260 total points
ID: 24781698
Hmm, try getting rid of the "@":

http://msdn.microsoft.com/en-us/library/aa905910(SQL.80).aspx


Set DataConnection = Server.CreateObject("ADODB.Connection")
                strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
                strconn=strconn & DatabasePathString
                DataConnection.Open SQLstrDataConnectionString
                'dataconnection.Execute SQL
 
                Set Cmd1 = CreateObject("ADODB.Command")
                Set Param1 = CreateObject("ADODB.Parameter")
                Set Param2 = CreateObject("ADODB.Parameter")
                Set Rs1 = CreateObject("ADODB.Recordset")
 
                Cmd1.ActiveConnection = DataConnection
                Cmd1.CommandText = "UPDATE tblListings SET userSynopsis = '?' WHERE ListingID = ?;"
'=>CStr(userSynopsis)
'Are you sure you have 'userSynopsis' initialized somewhere
                Set Param1 = Cmd1.CreateParameter("userSynopsis", adVarWChar, adParamInput, 250,CStr(userSynopsis))
                Cmd1.Parameters.Append Param1
                Set Param2 = Cmd1.CreateParameter("ListingID", adInteger, adParamInput, 4, CInt(ListingID))
                Cmd1.Parameters.Append Param2
 
                ' Open Recordset Object.
                'Set Rs1 =
                Cmd1.Execute()
                DataConnection.Close
                Set Param1 = Nothing
                Set Param2 = Nothing
                Set DataConnection = Nothing

Open in new window

0
 

Accepted Solution

by:
paul_noden earned 0 total points
ID: 24781781
Yeah I have, I was really looking for someone who'd know what was wrong, I can try (and have been trying variatons)

Best thing i've done was to break the createParameter into seperate statements which gave me more specific feedback as to which bits were wrong.

Fixed it see below.

also I needed <!-- METADATA TYPE="TypeLib" FILE="C:\Program Files\Common Files\system\ado\msado15.dll" -->  in the global.asa to include the constants files.
Set Cmd1 = CreateObject("ADODB.Command")
		Set Param1 = CreateObject("ADODB.Parameter")
		Set Param2 = CreateObject("ADODB.Parameter")
		Set Rs1 = CreateObject("ADODB.Recordset")
		Cmd1.ActiveConnection = DataConnection
		Cmd1.CommandText = "UPDATE tblListings SET userSynopsis = ? WHERE ListingID = ?;"
		Param1.Type=adVarWChar
		Param1.Size=250
		Param1.Direction = adParamInput
		Param1.Value=CStr(userSynopsis)
		Cmd1.Parameters.Append Param1
		Param2.Type=adInteger
		Param2.Size=4
		Param2.Direction = adParamInput
		Param2.Value=CInt(ListingID)
		Cmd1.Parameters.Append Param2
		Set Param1 = Nothing
		Set Param2 = Nothing

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
ID: 24782358
On what you posted originally you have:
DataConnection.Open SQLstrDataConnectionString

but your connection string is stored in  the variable:
strconn

NOT  SQLstrDataConnectionString.

>>also I needed ...
I didn't realize you didn't have that already. Since you were using many constants all over, it was logical to assume you had them defined somewhere (either via the METADATA OR via the tradional adovb.inc file)

Lastly, on the sql statements, the question marks should not have apostrophes around them, even if the value is a string. Below is a sample code that worked for me
Set DataConnection = Server.CreateObject("ADODB.Connection")
                strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
                strconn=strconn & DatabasePathString
                DataConnection.Open strconn 'SQLstrDataConnectionString
                'dataconnection.Execute SQL
 Const adVarWChar=202
 Const adParamInput=1
 Const adInteger=3
                Set Cmd1 = CreateObject("ADODB.Command")
                Set Param1 = CreateObject("ADODB.Parameter")
                Set Param2 = CreateObject("ADODB.Parameter")
                Set Rs1 = CreateObject("ADODB.Recordset")
 
                Cmd1.ActiveConnection = DataConnection
'                Cmd1.CommandText = "UPDATE tblListings SET userSynopsis = '?' WHERE ListingID = ?;"
'do not put apostrophes around the question marks
                Cmd1.CommandText = "UPDATE [Inserts] SET Email = ? WHERE ID = ?;"
 
'                Set Param1 = Cmd1.CreateParameter("@userSynopsis", adVarWChar, adParamInput, 250,CStr(userSynopsis))
                Set Param1 = Cmd1.CreateParameter("@Email", adVarWChar, adParamInput, 250,"test_12999@fake.domain.com")
                Cmd1.Parameters.Append Param1
'                Set Param2 = Cmd1.CreateParameter("@ListingID", adSmallInt, adParamInput, 4,CInt(ListingID))
                Set Param2 = Cmd1.CreateParameter("@ID", adInteger, adParamInput, 4,425)
                Cmd1.Parameters.Append Param2
Response.Write Cmd1.CommandText
 
                ' Open Recordset Object.
                'Set Rs1 =
                Cmd1.Execute()
                Set Param1 = Nothing
                Set Param2 = Nothing 
 
Notice that I commented out some of your original statements

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
ID: 24786212
On my last post I forgot to mention that since you are already using the METADATA, you won't need these definitions:
 Const adVarWChar=202
 Const adParamInput=1
 Const adInteger=3
0
 

Author Comment

by:paul_noden
ID: 24786301
Yes, this code was someone else's that I'm trying to overhaul (there's some serious issues with the approaches taken) and apparently defining their own constants was part of the fun. Changing from an access database to SQL Server and leaving the remnants of the access file lying all over the code is another as you've now spotted. It's using the correct connection, the strconn lines appear to be defunct.

"on the sql statements, the question marks should not have apostrophes around them, even if the value is a string" good to know, I've seen MSDN articles use both mechanisms when researching for my solution.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

690 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