JavaScript – Working with Database

June 14, 2006 · by Michael Sync · in

After reading this coding, you will know the following facts.

  1. How to connect the database in JavaScript
  2. How to use the ADODB.Recordset in JavaScript

As I already put the details comments in coding, I hope you will find it useful.

You can download the sourcecode and MSAccess database from this link.

http://michaelsync.net/demo/JavaScriptDatabaseSrc.zip

****

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<script type="text/javascript">
 <!–
var adOpenDynamic = 2;
var adLockOptimistic = 3;

/* Path of database.
If you put the database "Inventory.mdb" in different location,
you need to specify the correct path to this variable.
But When you are running the webpage from Browsers only,
you should use this keyword "window.location.pathname" for getting
the current location. But If you are using FrontPage,
you hav to specity the static path.
*/
var strDbPath = "C:\\JavaScript Database\\Inventory.mdb";

/*
Here is the ConnectionString for Microsoft Access.
If you wanna use SQL or other databases, you hav
to change the connection string..
eg: SQL => var conn_str = "Provider=sqloledb; Data Source=itdev;" +
"Initial Catalog=pubs; User ID=sa;Password=yourpassword";
*/
var conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDbPath;

function getAdoDb(strAdoType){
if (window.ActiveXObject){
return new ActiveXObject(strAdoType);
}
else{
return new ActiveXObject(strAdoType);
}
}

function showReports(){
try{
var strHtml ="";
strHtml += "<table cellpadding=0 cellspacing=0 border=1 width=500px align=center>";
strHtml += "<tr ><td align=center colspan=4><b>Stock List</b></td></tr>";

//Database Connection
var conn = getAdoDb("ADODB.Connection");
conn.open(conn_str, "", "");

//Recordset
var rs = getAdoDb("ADODB.Recordset");
strQuery = "SELECT StockID,StockName,ReOrderLevel,IsActive FROM Stocks";
rs.open(strQuery, conn, adOpenDynamic, adLockOptimistic);

if(!rs.bof){
rs.MoveFirst();
while(!rs.eof) {
strHtml += "<tr>";
strHtml += " <td width=\"10px\">" + rs.fields(0).value + "</td>";
strHtml += " <td width=\"50px\">" + rs.fields(1).value + "</td>";
strHtml += " <td width=\"10px\">" + rs.fields(2).value + "</td>";
strHtml += " <td width=\"5px\">" + rs.fields(3).value + "</td>";
strHtml += "</tr>";

rs.MoveNext();
}
}
else{
//No Records.
strHtml += "<tr colspan=4><td align=center><font color=red>No Records.</font></td></tr>";
}
conn.close();
strHtml += "</table>";
document.write(strHtml);
}catch(ex){
alert(ex.message);
}
}

//–>
</script>
<title>Stock List</title>
</head>
<!–<body onload="show_menu()">
<div id="main" />–>
<body>
<script language="JavaScript">
 showReports();
</script>
</body>
</html>