Javascript Access Database Query

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)