We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
862 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

Comment
Watch Question

Author

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

Wayne BarronAuthor, Web Developer
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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?
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

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

CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

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

Author

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.