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

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

paul_nodenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paul_nodenAuthor Commented:
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
Wayne BarronAuthor, Web DeveloperCommented:
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
hieloCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

paul_nodenAuthor Commented:
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
hieloCommented:
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
paul_nodenAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hieloCommented:
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
hieloCommented:
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
paul_nodenAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.