Solved

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

Posted on 2009-07-03
9
802 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
  • 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 30

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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

20 Experts available now in Live!

Get 1:1 Help Now