Trying to get this to play nice

cutnedge · July 5th, 2009, 11:31 AM

Hello everyone,
I have a problem that is about to drive me nuts. I am trying to get asp and ajax to work. I am using mySql database(4.0), IIS 6.0, and javascript code. I also have a working version of this on a PhP site. I can get things to work in that setup, but I can not get the results to display in the ASP version.
Here is the code I am using currently

Code:

<%@ LANGUAGE = JavaScript%>
<%
 var myProdID = Request.QueryString("prod");
 var strConn = "DRIVER={MySQL ODBC 3.51 
Driver};SERVER=........;DATABASE=.......;UID=......;PWD=......";
 var oConn = Server.CreateObject("ADODB.Connection");
 oConn.Open(strConn);
 var oRs;
 oRs = oConn.Execute("SELECT prodID, prodName FROM tblProds WHERE prodID = 'myProdID'");
 var details;
 details = oRs("prodName").Value;
 
     While Not oRs.EOF
      Response.Write details;
      oRs.MoveNext();
     Wend
 
%>

I have received several different errors from running this, the most recent being this

showStatus() called with ready state of 4 and a response text of "JScript compilation error 800a03ec expected ;" and it has the error arrow (^) pointing to right before the While Not .. line of code. I have checked everything I know to do and I don't see anywhere where I left out a semicolon. This is just a simple experiment to see if I can get things to work with this setup.
Does anyone have any idea why this is not working? I would really appreciate some insight.
Thanks in advance

joefawcett · July 6th, 2009, 03:00 AM

I can't see any Ajax there. Does the page you showed work and display the records from the query? I'd be surprised if it worked as you're not using the myProdID variable, and you're leaving yourself exposed to a SQL injection attack. So this:

Code:

oRs = oConn.Execute("SELECT prodID, prodName FROM tblProds WHERE prodID = 'myProdID'");

should be:

Code:

oRs = oConn.Execute("SELECT prodID, prodName FROM tblProds WHERE prodID = '" + myProdID + "'");

but you should be using parameterised query.

cutnedge · July 6th, 2009, 10:47 AM

Hello Joe,
Thank you for your reply. I agree with the sql injection vulnerability statement. I am just trying to see if I can get this to work before I do any advanced work or even make this available to the public.
That code you saw was what I had placed on the server to respond to the form page I have included below.
That is where the ajax part is.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict-dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
  <title>ASP AJAX Page</title>
 </head>
 <body>
  <form id="nextTry">
   <label for="prodID">Product ID: </label><input type="text" name="prodID" id="prodID" onChange="findProdName();" />
   <label for="prodName">Product Name: </label><input type="text" name="prodName" id="prodName" />
  </form>
  <script language="javascript" type="text/javascript" >
function getHTTPObject() {
  var xmlhttp;
  /*@cc_on
  @if (@_jscript_version >= 5)
    try {
      xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
      try {
        xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
      } catch (E) {
        xmlhttp = false;
      }
    }
  @else
  xmlhttp = false;
  @end @*/
  if (!xmlhttp && typeof XMLHttpRequest != 'undefined') {
    try {
      xmlhttp = new XMLHttpRequest();
    } catch (e) {
      xmlhttp = false;
    }
  }
  return xmlhttp;
}
var request = getHTTPObject(); // We create the HTTP Object
 

   function findProdName()
   {
    var prodctID = document.getElementById("prodID").value;
    if((prodctID == null) || (prodctID == "")) return;
    var chkProd = "ajaxProdsJS.asp?prod=" + escape(prodctID);
    request.open("GET", chkProd, true);
    request.onreadystatechange = showStatus;
    request.send(null);
   }
   function showProdName()
   {
    if (request.readydstate==4)
    {
     var prodNm = request.responseText;
     document.getElementById("prodName").value = prodNm;
    }
   }
   function showStatus()
   {
    alert("showStatus() called with ready state of" + request.readystate + " and a response text of " + request.responseText );
   }
  </script>
 </body>
</html

I used the showStatus function to display and got the ready state of 4, but it said there was a semicolon expected on line 17 of the code I posted earlier.
But thanks for what you posted. I think I see what I did wrong and I will try that next. I am used to writing server side in vbscript and the migration to javascript messed me up. I am about ready to give up on this in asp and just stick with the PhP version, but I will try this and see what it does.
Again, thanks

cutnedge · July 6th, 2009, 02:28 PM

Hello again Joe,
I took your suggestion and changed my code to reflect that improvement and got the same alert message (ie ready state 4 and expectected semicolon on line 17). But thanks anyway

joefawcett · July 7th, 2009, 03:18 AM

Well debugging is done one step at atime. Is the asp page working in stand alone mode? Does it show the product details given a particular ID?

cutnedge · July 8th, 2009, 11:17 AM

Joe,
Thanks again for your interest in my problem. Yes, when using a straight ASP call to mySQL the page has no problem displaying the requested information
Here is an example

Code:

<div id="featureProducts">
    <div id="featureProdHeader">
     <p>Featured Items</p>
    </div>
    <div id="featureProd1">
     <%
     Dim strConn, oConn, qry, oRs, strImg
     strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=......;"
     strConn = strConn & "DATABASE=........;UID=..........;PWD=.........."
     Set oConn = Server.CreateObject("ADODB.Connection")
     oConn.Open strConn
     qry = "SELECT prodID, prodName, prodSmImg FROM tblProds ORDER BY RAND() LIMIT 1"
     Set oRs = oConn.Execute(qry)
     Response.write "<p>" & oRs("prodID") & "<br />"
     Response.write oRs("prodName") & "<br />"
     strImg = "<img src='images/sm/"
     strImg = strImg & oRs("prodSmImg") & "' />"
     strImg = strImg & "</p>"
     Response.write strImg
     oRs.close
     oConn.close
     Set oRs = nothing
     Set oConn = nothing
     %>

I actually have that same code running to pull the images for feature product 2 and feature product 3 so that it will display random products each time the page is reloaded. It is designed to be the front page of an e-comm store. I had no problem getting that to work. I also have a products page and a prod_desc page that work fine. In fact, on the prods.asp page I have 2 different div sections calling the mySQL database based on which link you click to view product types Here is that code

Code:

<div id="level2LeftNav">
     <%
     Dim strConn2, oConn2, qry2, oRs2
     strConn2 = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=.........;"
     strConn2 = strConn2 & "DATABASE=..........;PWD=............."
     Set oConn2 = Server.CreateObject("ADODB.Connection")
     oConn2.Open strConn2
     qry2 = "SELECT DISTINCT prodSubCat FROM tblProds WHERE prodCat = '" & catName & "'"
     Set oRs2 = oConn2.Execute(qry2)
     if not oRs2.EOF then
     while not oRs2.EOF
     Response.write "<ul><li>" & oRs2("prodSubCat") & "</li>"
     oRs2.movenext
     wend
     oRs2.close
     end if
     Response.write "</ul>"
     oConn2.close
     Set oRs2 = nothing
     Set oConn2 = nothing
     %>
    </div>
    <div id="level2ProdsSection">
     <%
     Dim strConn, oConn, qry, oRs, strImg
     strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=..........;"
     strConn = strConn & "DATABASE=.......;UID=..........;PWD=.........."
     Set oConn = Server.CreateObject("ADODB.Connection")
     oConn.Open strConn
     qry = "SELECT prodID, prodName, prodPrice, prodSmImg FROM tblProds WHERE prodCat = '" & catName & "'"
     Set oRs = oConn.Execute(qry)
     if not oRs.EOF then
     while not oRs.EOF
     Response.write "<p>" & oRs("prodID") & "<br />"
     Response.write oRs("prodName") & "<br />"
     strImg = "<img src='images/sm/"
     strImg = strImg & oRs("prodSmImg") & "' />"
     strImg = strImg & "<br />"
     Response.write strImg
     Response.write oRs("prodPrice") & "</p>"
     oRs.movenext
     wend
     oRs.close
     end if
     oConn.close
     Set oRs = nothing
     Set oConn = nothing
     %>

Again, this works fine and as expected. It is just seems to be something that doesn't work when I try to make the call to mySQL from XMLHttpRequest.
Once again, thank you for your interest in my problem. I really appreciate your help

cutnedge · July 8th, 2009, 11:49 AM

Hello again Joe,
I wanted to include the complete revised version of the Server side script that the AJAX page was calling to see if you could see something I was missing there. Thanks again

Code:

<%@ LANGUAGE = JavaScript%>
<%
 var myProdID = Request.QueryString("prod");
 var strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=................;DATABASE=...............;UID=...............;PWD=..............";
 var oConn = Server.CreateObject("ADODB.Connection");
 oConn.Open(strConn);
 var oRs;
 oRs = oConn.Execute("SELECT prodID, prodName FROM tblProds WHERE prodID = '" + myProdID + "'");
 var details;
 details = oRs("prodName").Value;
 Response.Write details
 
%>