Parameterised Access Query in javascript

howbadboy · February 25th, 2005, 08:28 PM

HI,

I'm new to asp/javascript and am strugglling to get to grips with using a form input as an input to a stored procedure in Access.

I've seen a lot of code for VbScript and Ole Db (i'm using odbc) and am still confused!!

Basically I have the following code:

<%@LANGUAGE="JAVASCRIPT"%>
</head>
<body>
<%

    var dbConn = Server.CreateObject("ADODB.Connection");
    dbConn.Open("custconn");

    var dbComm = Server.CreateObject("ADODB.Command");

    dbComm.commandtext = "cityQuery";
    dbComm.commandtype = adCmdStoredProc;

    var objParam = dbComm.CreateParameter("City", adVarChar, adParamInput);
    dbComm.Parameters.Append(dbComm);
    objCommand.Parameters("City") = "London";

    dbComm.activeconnection = dbConn;
    var rSet = Server.CreateObject("ADODB.Recordset");
    dbComm.execute();
%>
</body>
</html>

I'm looking to return all records whose city value is 'London'
I'm trying to kinda copy VBScript examples but really am coming up against the wall...as i said...this is all pretty new territory to me.

Does anyone have some url's that will go through this on a pretty basic or simple level..

thanks

alan

joefawcett · February 26th, 2005, 07:49 AM

Please don't post the same question in two forums.

Assuming your connection succeeds and you have defined the adVarChar etc, normally via an include file, then the only thing that jumps out at me is:

Code:

objCommand.Parameters("City") = "London";

JavaScript doesn't understand default object properties so you need:

Code:

objCommand.Parameters("City").value = "London";

Remember this when you try to output using the recordset:

Code:

//var rSet = Server.CreateObject("ADODB.Recordset"); NOT NEEDED
var rSet =  dbComm.execute();
if (rSet.EOF && rSet.BOF)
{
  //No records
}
else
{
  while (!rSet.EOF)
  {
    Response.Write(rSet.fields(0).value + "<br>");
    rSet.moveNext();
  }
}
rSet.close();

--

Joe (Microsoft MVP - XML)

howbadboy · February 26th, 2005, 12:39 PM

cheers for the quick reply...and apologies for the double post...
hopefully this will do the trick
thanks again