?
Solved

problem with a oRs.Open command in asp

Posted on 2010-01-10
26
Medium Priority
?
613 Views
Last Modified: 2013-11-25
I just moved over my site to a new server and got the sql moved over and main site moved over ok.

the site uses asp coding, however after moving it over and changing the connection strings i get the following error:

ADODB.Recordset error '800a0bb9'

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

/store/0128-newproducts.asp, line 118

any ideas where i have gone wrong ? It was working on the old server fine.

------------

the code i use is as follows:
      strConnection = "Provider=sqloledb;Data Source=infdb3;Initial Catalog=airpower;User Id=userid;Password=*********;"
      Set oDb = Server.CreateObject("ADODB.Connection")
      Set oRs = Server.CreateObject("ADODB.Recordset")

oDb.Open strConnection
oRs.Open strSql, oDb,adOpenForwardOnly, adLockReadOnly, adCmdText
0
Comment
Question by:aclv
[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
  • 13
  • 13
26 Comments
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26280200
Its in the SQL command and/or its parameters - if you are using parameterized SQL.

What is the actual value of strSql at the time you execute oRs.Open ...

e.g. Do this:

oDb.Open strConnection
'***********************
'* temp - debug
'***********************
Response.Write strSql
Response.End
oRs.Open strSql, oDb,adOpenForwardOnly, adLockReadOnly, adCmdText

Then verifiy strSQL is correct and has the correct values.

If that is not it, we will do some other debug commands until we find the issue.
0
 

Author Comment

by:aclv
ID: 26280215
strSelectedItem = Request.QueryString("opcd")
      if strSelectedItem <> "" then
            strSql = "Select ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder  from Items where optioncode = '" & strSelectedItem & "'"
      else
            strSql = "Select ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder  from Items where optioncode is not NULL"
      end if
            oDb.Open strConnection
            oRs.Open strSql, oDb,adOpenForwardOnly, adLockReadOnly, adCmdText
            if oRs.EOF then
                  'No Products Available for Purchase
            else
                  Do Until oRs.EOF
                        if oRs("ItemID") = 10 then
                              strChecked = " checked"
                        else
                              strChecked = ""
                        end if
0
 

Author Comment

by:aclv
ID: 26280223
hi,

using your debug it returns :

Select ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder from Items where optioncode = '6 '
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26280308
optioncode = '6 '  If optioncode is a number type (integer) then placing single quotes around the number '6 ' could cause this error, e.g. string type instead of numeric type.

It also appears there is a space between the quotes which should'nt be there.

What is the defined fieldtype of optioncode in the table?
0
 

Author Comment

by:aclv
ID: 26280494
hi again,

i ran the string through the query analyzer on the new server and it returned the correct values.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26280578
What is the defined fieldtype of optioncode in the table?
0
 

Author Comment

by:aclv
ID: 26280619
optioncode is type :  varchar
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26280646
Do you know why you picked up the errant space or is that an optical illusion.  Can you Trim() that value before placing it in the SQL?


(Query Analyzer may be correcting an issue that the ADODB library doesn't)
0
 

Author Comment

by:aclv
ID: 26280663
it was a mistake in arguments i passed over it had a space in the calling url. I changed the URL and it passed over the 6 without a space.

i ran it again with the correct parameters and still the same issue.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26280822
Bed time here, but I've red flaged the notif as requiring additional attention.  This should not be anything difficult, but I like to go step by step so as to cover the likely culprits and not having false assumptions left unaddressed.

The error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." is somewhat strange in that you only have one value it could be complaining about.  I usually see this when a char value is passed where an integer is expected, as an example.  In your case you are passing a delimited value which should be interpreted as an acceptable varchar value.

It isn't obvious, but I'm tired.

Unrelated but I can't help myself...

strSelectedItem = Request.QueryString("opcd")  <-- SQL Injection vulnerability

      if strSelectedItem <> "" then  <-- not reliable, you need to test for null and empty, so

If strSelectedItem & "" <> "" Then

And all the following should be parameterized SQL to eliminate 95% of you SQL injection vulnerabilities and be faster and more stable...

See: http://www.rodsdot.com/asp/Parameterized-SQL-Library-Select-With-Parameters.asp

            strSql = "Select ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder  from Items where optioncode = '" & strSelectedItem & "'"
      else
            strSql = "Select ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder  from Items where optioncode is not NULL"
      end if
            oDb.Open strConnection
            oRs.Open strSql, oDb,adOpenForwardOnly, adLockReadOnly, adCmdText
            if oRs.EOF then
#### If oRs.Eof AND oRs.Bof is more reliable as there is no absolute certainty the recordset cursor will be at any given location when you open a recordset: See the ADO documentation. ####
                  'No Products Available for Purchase
            else
                  Do Until oRs.EOF
                        if oRs("ItemID") = 10 then
                              strChecked = " checked"
                        else
                              strChecked = ""
                        end if

If a expert does not solve this before the AM I'll try to get back with you.  I do have a doctor's appointment and have to pick up my car from the shop, so trust me when I say I will work you through this, but my time will be squeezed tomorrow. If another expert comes along and solves the problem quicker, so much the better for you.  And if you solve it yourself, you save the points <grin>.

Sorry to bail...but I'm looking at 5 hours sleep at best.
0
 

Author Comment

by:aclv
ID: 26280906
thanks for your help,, ill work on it ,, the code was written several years ago and i havent really touched much coding since then,... so please forgive me if I ask stupid questions.

0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26283288
>I ask stupid questions.

No such thing.

Humor me and try this (with proper error checking)


Dim cmdTxt, cmdObj, strConnection, rs, connection, param, strSelectedItem, strChecked

strSelectedItem = CStr(CInt(Request.QueryString("opcd"))) '<-- minimal validation, will stop most SQL Injection

On Error Resume Next
'*****************************************************************************************************************
'*  Put your userid and password back in here and don't post those in your comments
'*****************************************************************************************************************
strConnection = "Provider=sqloledb;Data Source=infdb3;Initial Catalog=airpower;User Id=userid;Password=*********;"
 
Set cmdObj = Server.CreateObject("ADODB.Command")
If Err Then
	Response.Write "Error creating command object.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If
Set connection = Server.CreateObject("ADODB.Connection")
If Err Then
	Response.Write "Error creating connection object.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If
Set rs = Server.CreateObject("ADODB.Recordset")
If Err Then
	Response.Write "Error creating recordset object.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

connection.open strConnection
If Err Then
	Response.Write "Error opening connection.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

Set cmdObj.ActiveConnection = connection
If Err Then
	Response.Write "Error setting active connection.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

cmdTxt = "SELECT ItemID, Name, AnchorName, ShortDesc, Description, RetailCost, AddLicense, OptionCode, HexColor, ImgFolder FROM Items WHERE "
If strSelectedItem & "" <> "" Then
	cmdTxt = cmdTxt & "(optioncode = ?)"
	Set param = cmdObj.CreateParameter(?,adVarChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)
	'* OR Set param = cmdObj.CreateParameter("?",adVarChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)
Else
	cmdTxt = cmdTxt & "(optioncode IS NOT NULL)"
End If

cmdObj.Parameters.Append param
If Err Then
	Response.Write "Error appending parameter.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

cmdObj.CommandText = command
If Err Then
	Response.Write "Error setting command string.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

rs.Open cmdObj adOpenForwardOnly, adLockReadOnly, adCmdText
'* OR rs.Open cmdObj,,adOpenStatic
If Err Then
	Response.Write "Error opening recordset.<br>" & Err.Number & " " & Err.Description & " " & Err.Source & "<br>"
	Response.End
End If

If NOT (rs.BOF AND rs.EOF) Then
	rs.movefirst ' make sure you have the cursor are at the begining of the recordset
	While NOT rs.EOF
		If Trim(rs("ItemID").Value) = 10 Then
			strChecked = " checked"
		Else
			strChecked = ""
		End If
                ' or add your other code. NOTE: rs not oRs in this code
	Wend
End If

On Error Goto 0

Open in new window

0
 

Author Comment

by:aclv
ID: 26283567
thanks for this:  i entered the code and the error i get is:
Error appending parameter.
-2147352571 Type mismatch. Provider
0
 

Author Comment

by:aclv
ID: 26283753
as a fyi: im not sure if i coded this part correctly"
cmdTxt = cmdTxt & "(optioncode = ?)"
Set param = cmdObj.CreateParameter(?,adVarChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)

i replaced the ? with a value 4  is that ok ?
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26285573
SQL requires ?.  MS Access can do @string type of place holders.  I believe MySql and others also use ?.

I'll run a test after I get back from my appointments, but I've always used ?, none of my running pages have needed attention so I havn't looked at them in ages.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26285586
I was mainly after the error checking, obviously by the check after each step.
0
 

Author Comment

by:aclv
ID: 26285664
when i leave in the ? i get the following error:
Microsoft VBScript compilation error '800a0408'

Invalid character

Set param = cmdObj.CreateParameter(?,adVarChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26285762
MSDN says you can use the @string syntax with MS SQL from an ASP pages, so:


cmdTxt = cmdTxt & "(optioncode = @opt)"
        Set param = cmdObj.CreateParameter("@opt",adVarChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)

Open in new window

0
 

Author Comment

by:aclv
ID: 26286124
Error appending parameter.
-2147352571 Type mismatch. Provider
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26288907
That is exactly the same as your original error.  (different only because we changes access method, but exact same complaint.) ADODB says the type of your parameter does not match the defined type of the field as reported by MS SQL server (Provider).

But you said it was type varChar and that is what type of parameter we attempted to append, but the Provider (MS SQL) is saying it isn't.

You could try.

1st.  Comment out the   Set param and cmdObj.Parameters.Append param lines and change cmdTxt = cmdTxt & "(optioncode = ""6"")"  <-- hard code the value and see if it runs.

If you do get a successful result,
2nd. Put the commented out lines back the way they were, put cmdTxt = cmdTxt & "(optioncode = @opt)" and change the param type to adChar.

Set param = cmdObj.CreateParameter("@opt",adChar,adParamInput,CLng(Len(strSelectedItem)),optioncode)
0
 

Author Comment

by:aclv
ID: 26296689
Hi Again.

I think i have figured it out, i will let you know but it appears that the new server has a different ado version.  So i will change the ADO version in the global.asa metatag and see what happens. I will keep you posted

0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26298723
>it appears that the new server has a different ado version.

Entirely possible, but the ADODB include shouldn't have changed, but if that works to solve your problem that would be great news.
0
 

Author Comment

by:aclv
ID: 26299626
well this is intresting: i had to upload for now into a seperate folder the  adovbs.inc and then i had to put a include statement in each asp.

<!--#Include file="Includes/adovbs.inc"-->

its does the trick for now but im still looking for a better solution.
0
 
LVL 29

Accepted Solution

by:
rdivilbiss earned 2000 total points
ID: 26299672
I put

<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->

at the very beginning of every page which uses ADO.  Did you try that?
0
 

Author Comment

by:aclv
ID: 26311281
Hi, i discovered the problem.. like an idiot i didnt have the IIS properly configured.  I didnt have the scripts in a virtual directory.  However i did implement your suggested changes..

thanks for the help...

patrick
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26311362
It would be super if you confined your web scripts to parametrized SQL.  It is usually faster to develop and to execute, you'll neven need to worry about changing O'Brian to O''Brian when inserting a record, and it will eliminate the majority of SQL injections.  I'm very happy you went that way.

It is even easier with the library code.

http://www.rodsdot.com/asp/Parameterized-SQL-Library-Select-With-Parameters.asp
http://www.rodsdot.com/asp/Parameterized-SQL-Library-Select-With-Parameters.asp
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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