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)

ncarney August 23rd, 2007 10:25 PM

I'm experiencing the same "'Server' is undefined" error when calling the Server object using javascript. However, it only gives me the error when I declare javascript in the <script> tag, not when javascript is declared as the default for the page. I need to be able to declare it in the <script> tag since I am using both javascript and vbscript in the application.
---------------------------------
The following runs without error:

<%@ LANGUAGE = "JavaScript"%>
<HTML>
<BODY>

<%
var conn = Server.CreateObject("ADODB.Connection");
var rs;
var dbPath = new String("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:/inetpub/testsite/admin/db/db.mdb");
conn.Open(dbPath);
rs = conn.Execute("select * from products");
%>

</BODY>
</HTML>
-----------------------
The following results in "Error: 'Server' is undefined" at the Server.CreateObject line:

<HTML>
<BODY>

<script type="text/javascript">
var conn = Server.CreateObject("ADODB.Connection");
var rs;
var dbPath = new String("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:/inetpub/testsite/admin/db/db.mdb");
conn.Open(dbPath);
rs = conn.Execute("select * from products");
</script>

</BODY>
</HTML>




joefawcett August 24th, 2007 03:46 AM

You need to recognise the difference between server-side and client-side code. You have declared a
Code:

<script type="text/javascript">
b
element without a runat="server" attribute therefore the code runs on the client (in the browser). The client cannot run code on the server or have access to the Server object which is an object created by the ASP runtime when the server-side code is executed. If you want to create a client-side connection object then use
Code:

var conn = new ActiveXObject("ADODB.Connection");
However the remaining code won't run unless you have both lowered the browser's security settings to allow local file access and have a database at the c:\inetpub... location.

--

Joe (Microsoft MVP - XML)

ncarney August 24th, 2007 06:48 AM

Thanks. It was late last night and I totally overlooked the runat="server" issue. My fundamental problem is that I'm trying to span client-side and server-side scripts, which I know is not easy. I've got some client-side javascript code for a screenshow. The image file references are essentially hard-coded into the code (for reasons I understand now). I was happily thinking I could just access a product database on the server to dynamically determine which images to display, but this involves server-side code. I should probably get Dreamweaver or some other tool which makes this really easy, but I was trying to kluge something together with existing tools and infrastructure. Any thoughts?


joefawcett August 24th, 2007 10:27 AM

There are a number of options, you could just get your server-side code to write the image paths into the JavaScript code. There is a long thread on this somewhere in the archives. You need to embed the server-side code in the client-side code such as:
Code:

var myClientVariable = "<% = myServerVariable %>";
Alternatively the modern solution would be to use a web service that returns the list.

--

Joe (Microsoft MVP - XML)

ncarney August 24th, 2007 11:00 AM

Got it. For now I will probably go with running server script to embed the image paths into the client script. Given my level of knowledge, that seems to be the quickest/easiest way to get something going. Anyway, I want to re-write the entire web site soon using more current technologies (once I understand them!). Many thanks for your help and advice.


gok May 6th, 2008 09:40 PM

Thank you, Joe, for you posting. Discussion in this thread was very useful.
I'm also strugling with sqlServer<->htmlClient issue and close to loose hope to solve it. You might give some light what is wrong.

My MSSQL seats on one server and IIS on another.
Straight ASP page works fine across "real" web.

My goal is to bring records to html drop-down list so user can drill the same database later. Its part of frames set and it is not easy to change whole existing design.
Got an Object expected trying to bring records from ASP to client side. As I understand your suggestion it should be easy to do.

This is dbTest.htm:
-----------------------------------
<SCRIPT LANGUAGE="JavaScript" src="readDb.asp" runat="server"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" runat="server">
 var names = new Array();
 names = readDb(); // Object expected
 //names = parent.MyFrame.readDb(); // Object doesn't support this property or method
</SCRIPT>
<HTML>
<HEAD>
<TITLE>DB Test</TITLE>
</HEAD>
<BODY BGCOLOR="#000033">
<SCRIPT LANGUAGE="JavaScript">
document.writeln('Communities');
document.writeln('<select name="quickZoom" size="10">');
document.writeln('<option selected value="-">zoom to...');
for ( j = 0; j < names.length; j++ ) {
 document.writeln('<option value="'+names[j]+ '">' + names[j]);
}
document.writeln('</select>');
</SCRIPT>
</BODY>
</HTML>

And this is DB reader readDb.asp:
-----------------------------------
<SCRIPT LANGUAGE="JavaScript" runat="server">

function readDb() {
    var conn = Server.CreateObject("ADODB.Connection");
    var strConn = "Provider=SQLOLEDB.1;Data source=amsql;Database=asdi;Trusted_Connection=yes; User ID=guest;Password=password;"
    conn.Open(strConn);
    var rs = conn.Execute("SELECT * FROM TBLCOMMUNITIES");
    var j = 0;
    var names = new Array();
    while (!rs.EOF)
    {
        names[j++] = rs("name");
        rs.MoveNext();
        }
    rs.Close();
    conn.Close();
    return names;
}
</SCRIPT>
[/code]

Could you tell if it is possible at all?

Happy programming!

Quote:

quote:Originally posted by joefawcett
  If you want to create a client-side connection object then use
Code:

var conn = new ActiveXObject("ADODB.Connection");

However the remaining code won't run unless you have both lowered the browser's security settings to allow local file access and have a database at the c:\inetpub... location.

--

Joe (Microsoft MVP - XML)

joefawcett May 7th, 2008 02:22 AM

Firstly, as I said before, you must be clear in your mind which code executes on the server and which on the client. When you return names from the server-side code it doesn't go anywhere. You need to create the HTML that you need:
  • Code:

    var names = readDB();
    Response.Write("<script type=\"text/javascript\">\n");
    Response.Write(" var names = " + names.toString() + ";\n");
    Response.Write("</s" + "cript>\n");


  • In your client-side code don't use document.writeln, create a page as you wish with an empty select element and use the DOM to create new Option elements and add them based on the data in the names variable
  • Check the HTMl of the page created to see whether the array has been populated correctly, it should look like:
    Code:

    var names= ["name1", "name2", "name3"];

--

Joe (Microsoft MVP - XML)

gok May 8th, 2008 12:18 PM

Joe,
Still can not put html <-> asp pair to work.
Did create inside ASP (server-side) a hidden list in
var names=["a","b"]; form. But in HTM (client-side)
got runtime error: 'names' is undefined.
Switched example to pub dbase in MSSQL so everybody
can try it.

Is it possible to bring 'names' in .htm?

readDb.asp:
-----------------------------------------
Code:

<%@ LANGUAGE=JavaScript %>
<HTML><HEAD><TITLE>readDB</TITLE></HEAD><BODY>
<%
    Response.Write("<script type='text/javascript' language='javascript'>\n");

    var conn = Server.CreateObject("ADODB.Connection");
    var strConn = "Provider=SQLOLEDB.1;Data source=amsql;Database=pubs;Trusted_Connection=no;User ID=guest;Password=password;"
    conn.Open(strConn);
    var rs = conn.Execute("SELECT * FROM STORES");

    var i=0;
    Response.Write("var names=[");
    while (!rs.EOF)
    {
        if ( i++>0 ) Response.Write(",");
        var name = rs("stor_name");
        // Use a regular expression to replace leading and trailing
           // spaces with the empty string
//          name.replace(/(^\s*)|(\s*$)/g, ""); // Object doesn't support this property or method
        Response.Write("\"" + name + "\"");
        rs.MoveNext();
    }
    Response.Write("];\n");
    rs.Close();
    conn.Close();
    Response.Write("</s" + "cript>");
%>
</BODY></HTML>

dbTest.htm:
-----------------------------------------
Code:

<%@ LANGUAGE=JavaScript %>
<SCRIPT LANGUAGE="JavaScript" src="readDb.asp"></SCRIPT>
<HTML><HEAD><TITLE>dbTest</TITLE></HEAD><BODY>
Store Names:<br>
<%=names%>
    <select name="quickZoom" size="5">
        <option selected value="-">zoom to...
<%            for ( i = 0; i < names.length; ++i )  %>
                <option value="<%=names[i]%>"><%=names[i]%>
        </select>
</BODY></HTML>

Quote:

quote:Originally posted by joefawcett
 Firstly, as I said before, you must be clear in your mind which code executes on the server and which on the client. When you return names from the server-side code it doesn't go anywhere. You need to create the HTML that you need:
  • Code:

    var names = readDB();
    Response.Write("<script type=\"text/javascript\">\n");
    Response.Write(" var names = " + names.toString() + ";\n");
    Response.Write("</s" + "cript>\n");


  • In your client-side code don't use document.writeln, create a page as you wish with an empty select element and use the DOM to create new Option elements and add them based on the data in the names variable
  • Check the HTMl of the page created to see whether the array has been populated correctly, it should look like:
    Code:

    var names= ["name1", "name2", "name3"];

--

Joe (Microsoft MVP - XML)

joefawcett May 8th, 2008 01:05 PM

Okay, so what does the html look like after it's created?

--

Joe (Microsoft MVP - XML)

gok May 8th, 2008 01:46 PM

Html page is empty, just title 'Store Names' and empty selection list. Looks like variable 'names' is invisible for HTML.
On Asp source code view there are results from DB reading:
Code:

<HTML><HEAD><TITLE>readDB</TITLE></HEAD><BODY>
<script type='text/javascript' language='javascript'>
var names=["Eric the Read Books","Barnum's","News & Brews","Doc-U-Mat: Quality Laundry and Books","Fricative Bookshop","Bookbeat"];
</script>
</BODY></HTML>

Quote:

quote:Originally posted by joefawcett
 Okay, so what does the html look like after it's created?

--

Joe (Microsoft MVP - XML)

joefawcett May 8th, 2008 01:55 PM

That looks right. Now if you've got rid of the document.write stuff, which opens a new document, you should be able to use the names array.

--

Joe (Microsoft MVP - XML)

gok May 8th, 2008 02:35 PM

Joe,
I'm confused. There are no document.writeln() in .html page.
Check dbTest.htm above.
Did try to break Asp (changed login name for db connection string) but no any errors opening Htm! Looks like readDb.asp never being called!
Quote:

quote:Originally posted by joefawcett
 That looks right. Now if you've got rid of the document.write stuff, which opens a new document, you should be able to use the names array.

--

Joe (Microsoft MVP - XML)

joefawcett May 8th, 2008 02:50 PM

I'm confused too.
Originally you wanted to use JavaScript server-side to connect to a database and use the data to populate a select list.
Now you could just create the options server-side but I thought you wanted to populate the array server-side and populate the options client-side. Is this not right?

--

Joe (Microsoft MVP - XML)

gok May 8th, 2008 03:28 PM

Yes, thats right, original task was: read db records in .htm using .js.
But in this scenario I'm getting runtime error: 'Server' is undefined (for
Server.CreateObject("ADODB.Connection") call inside .js function).

As an option I was hopping to use .asp (reads db nicely):
<SCRIPT LANGUAGE="JavaScript" src="readDb.asp" runat="server"></SCRIPT>
and use .asp variables on .htm page. Evidently .asp is not
a script and there is no way to read .asp into .htm
Could you suggest any workaround to solve this puzzle?
Thanks a lot for your patient!
Gennady
Quote:

quote:Originally posted by joefawcett
 I'm confused too.
Originally you wanted to use JavaScript server-side to connect to a database and use the data to populate a select list.
Now you could just create the options server-side but I thought you wanted to populate the array server-side and populate the options client-side. Is this not right?

--

Joe (Microsoft MVP - XML)

joefawcett May 8th, 2008 04:04 PM

Code:

<HTML><HEAD><TITLE>dbTest</TITLE>
<SCRIPT type="text/javascript" src="readDb.asp"></SCRIPT>
<script type="text/javascript">

function addNames()
{
var list = document.getElementById("quickZoom");
for (var i = 0, l = names.length; i < l; i++)
{
    var oOption = new Option(names[i], names[i]);
    list.options.add(oOption);
}
}
</script></HEAD>
<BODY onload="addNames()">
<select id="lstNames" name="quickZoom"></select>
<BODY></HTML>

--

Joe (Microsoft MVP - XML)

gok May 8th, 2008 04:12 PM

Thanks for the hints on rendering. I see array in the Head.
How to get 'names' into dbTest.htm scope from readDb.asp though?


gok May 8th, 2008 09:40 PM

Hello, Joe.
After couple of hours playing with code this version works as expected:
Code:

<%@ LANGUAGE=JavaScript %>
<HTML><HEAD><TITLE>dbTest</TITLE>
<%
        // this part is running on Server side
Response.Write("<script type='text/javascript'>\n");
    var conn = Server.CreateObject("ADODB.Connection");
    var strConn = "Provider=SQLOLEDB.1;Data source=amsql;Database=pubs;Trusted_Connection=no;User ID=guest;Password=password;"
    conn.Open(strConn);
    var rs = conn.Execute("SELECT * FROM STORES");
    var i = 0;
    // store recs to the client
    Response.Write("var names=[");
    while (!rs.EOF)
    {
        if ( i++>0 ) Response.Write(",");
        var name = rs.fields("stor_name").value.toString();
        name = name.replace(/(^\s*)|(\s*$)/g, ""); // trim spaces
        Response.Write("\"" + name + "\"");
        rs.MoveNext();
    }
    Response.Write("];\n");
        rs.Close();
    conn.Close();
Response.Write("</script>");
%>
<script type='text/javascript'>
    // this part is running on Client side
    function addNames()
    {
          var list = document.getElementById("quickZoom");
          for (var i = 0, l = names.length; i < l; i++)
          {
            var oOption = new Option(names[i], names[i]);
            list.options.add(oOption);
          }
    }
</script>
</HEAD>

<BODY onload="addNames()">
Store Names:<br>
<select id="lstNames" name="quickZoom"></select>
</BODY>
</HTML>

Thanks a lot for you patience and for sharing your knowledge!
Happy programming!
Gennady


joefawcett May 9th, 2008 02:05 AM

The page you showed should do it although if you are using it as an include you don't need to generate the surrounding script tags, just the JavaScript.

--

Joe (Microsoft MVP - XML)

gok May 9th, 2008 01:53 PM

Did not get this part, Joe.
Did you mean to include DB reader like that:
Code:

<HTML><HEAD><TITLE>dbTest</TITLE>
<script type="text/javascript" language="javascript" src="readDb.asp" runat="server">
    // this part is running on Client side
    function addNames()    { ...


If so, readDb.asp is not readable: Syntax error readDb.asp, line 1
And readDb.asp:
Code:

<%@ LANGUAGE=JavaScript %>
<HTML><HEAD><TITLE>readDB</TITLE></HEAD><BODY>
<% ...

Quote:

quote:Originally posted by joefawcett
 The page you showed should do it although if you are using it as an include you don't need to generate the surrounding script tags, just the JavaScript.

--

Joe (Microsoft MVP - XML)


All times are GMT -4. The time now is 04:54 AM.

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