problem with SQL statement after host migrated updated webserver

I have a sight that my webhost migrated to a new server and now one of my classic asp dynamic pages isn't loading correctly for the life of me I can't figure it out.

The site is http://www.performanceboatcenter.com

If you click on 'boats for sale' or the actual URL:

http://www.performanceboatcenter.com/boats_view_all_search.asp?boat_make=ALL&boat_price=0+AND+10000000&boat_LOA=0+AND+100000

I get a 'An error occurred on the server when processing the URL. Please contact the system administrator.
If you are the system administrator please click here to find out more about this error.'

But if I reaplace the boat_make= to an actual category like MTI the page will load, such as this:
http://www.performanceboatcenter.com/boats_view_all_search.asp?boat_make=MTI&boat_price=0+AND+10000000&boat_LOA=0+AND+100000

I'm guessing it has to do something with my recordset, I think this is enough code to look at it?

<%
Dim boats__MMColParam
boats__MMColParam = "boat"
If (Request("MM_EmptyValue") <> "") Then 
  boats__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Boats__MMColParam1
Boats__MMColParam1 = "0 AND 1000000"
If (Request.QueryString("boat_price") <> "") Then 
  Boats__MMColParam1 = Request.QueryString("boat_price")   
End If
%>
<%
Dim Boats__MMColParam2
Boats__MMColParam2 = "Cigarette"
If (Request.QueryString("boat_make") <> "") Then 
  Boats__MMColParam2 = Request.QueryString("boat_make")
End If
%>
<%
Dim Boats__MMColParam3
Boats__MMColParam3 = "0 AND 100000"
If (Request.QueryString("boat_LOA") <> "") Then 
  Boats__MMColParam3 = Request.QueryString("boat_LOA")
End If
%>

<%
Dim boats
Dim boats_cmd
Dim boats_numRows
Set boats_cmd = Server.CreateObject ("ADODB.Command")
boats_cmd.ActiveConnection = MM_powerboat_STRING
	If request.QueryString("boat_make") = "ALL" then 
	boats_cmd.Commandtext = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " ORDER BY boat_LOA ASC"
	Elseif request.QueryString("boat_make") <> "ALL" Then 
	boats_cmd.Commandtext = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " AND boat_make = " & "'" & Boats__MMColParam2 & "'" & " ORDER BY boat_LOA ASC"
	End If
boats_cmd.Prepared = true
'rsboats_cmd.Parameters.Append rsboats_cmd.CreateParameter("param1", 200, 1, 50, rsboats__MMColParam) ' adVarChar

Set boats = boats_cmd.Execute
boats_numRows = 0
%>

Open in new window


Not sure if its cause I'm doing a classic asp thing and the new server doesn't quite support that or what the problem is.  Any help or directions in troubleshooting would be great, host is slow getting back to me, as I figure they are busy trying to troubleshoot all of the folks that got migrated also?

Thanks
Baub
Baub EisAsked:
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.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change your if statement

If request.QueryString("boat_make") = "ALL" then 
	boats_cmd.Commandtext = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " ORDER BY boat_LOA ASC"
	Elseif request.QueryString("boat_make") <> "ALL" Then 
	boats_cmd.Commandtext = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " AND boat_make = " & "'" & Boats__MMColParam2 & "'" & " ORDER BY boat_LOA ASC"
End If

Open in new window

to
  
dim sql
If request.QueryString("boat_make") = "ALL" then 
	sql = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " ORDER BY boat_LOA ASC"
	Elseif request.QueryString("boat_make") <> "ALL" Then 
	sql = "SELECT * FROM mathews_powerboat1.boats WHERE boat_category = " & "'" & boats__MMColParam & "'" & " AND boat_price BETWEEN " & Boats__MMColParam1 & " AND boat_LOA BETWEEN " & Boats__MMColParam3 & " AND boat_make = " & "'" & Boats__MMColParam2 & "'" & " ORDER BY boat_LOA ASC"
End If

response.write sql
response.end

Open in new window


this should print out the sql being used, have a look at it an make sure everything looks right, even run in your database directly. this should give you a good start on troubleshooting
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
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Can you have your host service turn on errors temporarily. This is off by default.


Also this is not safe
 Boats__MMColParam2 = Request.QueryString("boat_make")

Open in new window

You should be in the habit of scrubbing your data before you send it to your db.  Otherwise you are open to sql injection.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Another option, you may want to convert "ALL" to ucase.  This way, if somebody types in "all" or "ALL" or "All" they get the expected result.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
excellent point, couldn't agree more :)
0
Baub EisAuthor Commented:
Waiting to hear back from host.  I'll keep you posted.  As far as the "all" thing, I am the only one making calls to that so I would always use the ALL.  You can't manually type that in anywhere.  I'll keep you guys posted....
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
did you try displaying the sql statement as I suggested?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
0
Baub EisAuthor Commented:
Thanks on the SQL injection stuff.  I'm familiar with it I've had to fight it off before.  Thanks for all of your help.

I believe that when I was entering test data to test what was going on i got the phrase 'test' inserted into a 'int' field.  So that was the problem.   So I'm giving the majority of points to Big Daddy.  But I'm gonna give a few to the others that chimed in.  As being a web developer that is a one man show, I don't always have other folks to ask questions and this forum has saved me I don't know how many times.  My appreciation goes out to all of you!
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
Microsoft SQL Server

From novice to tech pro — start learning today.