p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Javascript How-To (http://p2p.wrox.com/forumdisplay.php?f=87)
-   -   Javascript Access Database Query (http://p2p.wrox.com/showthread.php?t=33196)

panuvin September 7th, 2005 04:43 PM

Javascript Access Database Query
 
I'm having trouble getting all the syntax and parameters complete for a database lookup function in javascript. I'm used to programming in ASP and VBScript, but the javascript is throwing me off a little bit. Can anyone tell what is wrong with this code? It is giving me an error saying the connection string is incorrect. I've tried several variations, but it won't work. Also, it is having a problem with Server not being an object; do I need to use 'new'? Here is the code:

Code:

function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db\\sdi.mdb";

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var recordCount = RS.Fields.Count;
    alert ("recordCount" + recordCount);
    //RS.Fields(x).Name
}

Thanks in advance!

Panuvin


philip_cole September 7th, 2005 05:40 PM

Hi Panuvin,

Server is only used in ASP, and isnt available in javascript. Also, if this is normal client-side javascript, the database would have to be on the local computer. I dont know if you can read access databases across the web.

To convert to javascript, you dont have to change much, mainly just defining constants and creating ActiveXObjects instead of using Server:

adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db\\sdi.mdb";

var ConnectObj = new ActiveXObject("ADODB.Connection");
var RS = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT * FROM Categories;";

ConnectObj.Open(myConnect);
RS.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

var recordCount = RS.Fields.Count;
alert ("recordCount: " + recordCount);

Hope this helps
Philip Cole

joefawcett September 8th, 2005 01:49 AM

Is this running server-side or client-side?
Aside from the fact that the path is not well-formed, unless "db" was a typo for "d:" then either, if server-side:
Code:

function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb";

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    Response.Write ("Field Count" + fieldCount);
    RS.Close();
    ConnectObj.Close();
}

Client-side (security settings will need to be set to allow unsafe ActiveX initialisation, database must be on client in D: drive root).
Code:

function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb";

    var ConnectObj = new ActiveXObject("ADODB.Connection");
    var RS = new ActiveXObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    alert("Field Count" + fieldCount);   
    RS.Close();
    ConnectObj.Close();
}

Are you sure empId is a text field not an integer? If an integer you don't need quotes around the value?
You should also test for the recordset having any rows at all before reading data:
Code:

if (RS.BOF && RS.EOF)
{
  //No data
}

Finally you need to make sure the constants such as adCmdText are defined somehow in the page.



--

Joe (Microsoft MVP - XML)

panuvin September 8th, 2005 09:11 AM

Hello,

Thank you two for your replies. This is going to be running server side. The database exists on the server and ActiveX is disabled on both the server and every client machine (5000+) at my company. This page is an ASP page, but does this mean that I can't use javascript to make this work? The reason [I thought] I needed to use javascript is that the code utilizes the onchange() method to call a javascript function to do a database look-up and then puts the value in the input text field. I wasn't sure if I could do this in ASP/VBScript. I'm so lost :/

Thanks again in advance for any assistance. I'll attempt a few more approaches in ASP/VBScript, but would prefer to use javascript for simplicity.

Panuvin


joefawcett September 9th, 2005 12:40 PM

You seem to be confusing the idea of client-side versus server-side code and JavaScript versus VBScript. Your code is intended to run on the server, this means that it runs before the client loads the page and has no knowledge of such things as "onchange".

If your company has really disabled ActiveX on the server then you won't be able to access any database information as ADO is an ActiveX, or COM, component.

--

Joe (Microsoft MVP - XML)

panuvin September 9th, 2005 02:35 PM

Thanks for all your help. I did later realize after playing with some Javascript that I would not be able to interact with the server-side database. I was able to modify some of the ASP data and use ASP/VBScript to solve my problem. I'm still not a big fan of Javascript :P ...but then again I need to learn ASP.Net. Thanks again!

Panuvin


djgenesis July 24th, 2007 06:23 AM

Hi everyone!
This topic explained in a few lines, what I've been meaning to program for a week!

Although I would like to revive the topic and ask for advice.

On the server-side function here:

[code]
function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb";

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockRea dOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    Response.Write ("Field Count" + fieldCount);
    RS.Close();
    ConnectObj.Close();
}[code]

when I use
Code:

    var ConnectObj = Server.CreateObject("ADODB.Connection");
I get a
Quote:

quote: 'Server' is undefined
error.
Am i doing something wrong or do I have to include any libraries?


joefawcett July 24th, 2007 07:04 AM

If the code is really in a server-side script block then it cannot be an ASP page or, alternatively, ASP is disabled. Show the surrounding context if you're not sure.

--

Joe (Microsoft MVP - XML)

djgenesis July 24th, 2007 07:49 AM

This is what I have .... but it is not printing at all ....

Can you help ?


Code:

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<script type="text/javascript" src="Extras/CSJSRequestObject.js"></script>

<%
var sql = "bla bla bla......." ;
var odbc = Server.CreateObject("ADODB.Connection");
var dsn = "DSN=myDSN;UID=Myusername;PWD=Mypass";
var rst = Server.CreateObject("ADODB.Recordset");
odbc.open(dsn);
rst.open(sql,odbc);


if (rst.EOF) { rst.close;
                odbc.close;
               alert("There was no data matching your query")
              }
%>


<html>
<head><title>Daily Script Results</title></head>
<body>
<body bgcolor=lightblue>
Printing the results in the web page.

<table>
   <tr>
<% while (!rst.EOF)
                        {
                            Response.write("<tr>")       
                                    for (var d in Request.QueryString()) {


                                                document.write("<td>" + rst(d) + "</td>")


                                        }
                            Response.write("</tr>")           
                            rst.moveNext();
                            }
                            rst.close(); odbc.close(); %> 



                        <% for (var i in Request.QueryString()) {
                            if (typeof Request.QueryString(i) != "function")
                                {                                 
                                    if ( Request.QueryString(i)==1)
                                    { 
                                    document.write("<td>" + i + " </td>")
                                    }
                                }   

                            } %>

the Request.QueryString(i) is included in the other file.

joefawcett July 24th, 2007 10:57 AM

I'm not sure why you get the error message you said but I can see at least two errors where you mix client-side and server-side code. The first is where you try to alert inside a server-side script block when there are no records, the second where you try to use document.write inside a server-side block. Try to simplify the code. Just open a connection to the database and then use Response.Write to write back the connectionString property and the connection.state. Forget all the other stuff and see how far you get.

--

Joe (Microsoft MVP - XML)


All times are GMT -4. The time now is 11:53 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.