JavaScript - Working with Database
After reading this coding, you will know the following facts.
- How to connect the database in JavaScript
- 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>
gpuneet said
am July 22 2006 @ 2:39 pm
Hi Mike,
I was able to run this code locally on windows platform, but when I deployed it on Solaris, I run into problems. I am getting the following message
“the connection cannot be used to perform this operation. It is either closed on invalid in this context”.
Does the connection string need to be specified differently to run on UNIX. I updated the path for the var strDbPath (used /folder/file.mdb), but kept everything else same for var conn_str.
var conn_str = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strDbPath.
Would you know how to resolve this error. I can be reached at gpuneet@hotmail.com
Michael Sync said
am July 22 2006 @ 7:18 pm
Hi gpuneet,
Sorry.. I have no idea about Solaris.
But here are some facts that I would like to suggest.
1. The user need to have “Read Access” or “Write Access” to the folder where MS Access file is located.
2. Microsoft Jet Driver should be availble in Server.
3. The coding above wont’ work on Firefox Browser because Firefox doesn’t support ActiveXObject Object. So, GeckoActiveXObject Object is needed to be used.
Please check it in this discussion.
http://forums.devx.com/showthread.php?t=153769
One thing is that “Connecting to database using JavaScript” is not pretty good idea. The reason why I posted this thread is that I just want other people to know that is possible. If you are using PHP, It’s better if you use PHP WebService and Ajax.
Thanks and Regards,
Michael Sync
TrunkSquadron said
am October 5 2006 @ 6:56 pm
What about if a wanna to modify or edit any record? which is the code?
Michael Sync said
am October 10 2006 @ 5:34 am
>>What about if a wanna to modify or edit any record? which is the code?
You just need to change the SQL Statement.
Michael Sync said
am October 10 2006 @ 8:44 am
Hi TrunkSquadron,
Let me know if you can’t solve your problem..
Actually, you only need to change the SQL Statement for that variable.
eg:
strQuery = “UPDATE Stocks
SET StockName = ‘Your New Value’
FROM Stocks
WHERE StockID = 1″;
Note: ‘Your New Value’ = Name that you wanna change.
‘1′ = Stock ID that you wanna update.
Hope it would help..
john said
am November 10 2006 @ 8:10 am
Hi Mike,
your article on database conectivity using JS was extremely helpful, I was wondering if you could assist me on how to pull a particular field from a database a diplay it under a tag and also create a hyperlink for each row of data to another tag
do let me know if you have any soulution for such a problem.
Thanks,
John
john said
am November 10 2006 @ 8:11 am
Hi Mike,
your article on database conectivity using JS was extremely helpful, I was wondering if you could assist me on how to pull a particular field from a database a diplay it under a div tag and also create a hyperlink for each row of data to another div tag
do let me know if you have any soulution for such a problem.
Thanks,
John
Michael Sync said
am November 10 2006 @ 8:33 am
Yeah. Sure. I have something to show you all.
I’m writing an article about this and hope, I will publish it on my blog and codeproject very soon.. It wil be about Ajax things with step by step examples.
john said
am November 10 2006 @ 8:53 am
Hi again,
in my previous message i had mentioned about a div tag. alow me to explain further. in your sample MS access database “inventory” you hade a table by name stocks and within that you several fields one of them being “stockname”. now i was trying to get all details pertaining to the field “stockname” come inside a layer created thru div tags in a simple html webpage…. could this be done in a simple manner.
Regards
John
john said
am November 10 2006 @ 8:57 am
your help on this issue will be very much appreciated
Thanks again,
john
Michael Sync said
am November 10 2006 @ 2:03 pm
You mean, you wanna show “Stock Name” in DIV ?
eg:
<DIV>
<a href=”www.currenturl.com/#1″ rel=”nofollow”>Stock Name</a>
</DIV>
Yeah. Of course.
You can add any HTML things in strHTML (in my sample) ..
eg:
strHtml += "<td> ";
strHtml += " <div>";
strHtml += " <a href=\"http://something.com/#1\">" + rs.fields(1).value +
"</a>";
strHtml += " </div>";
strHtml += "</td>";
But I’m not that clear how you mean by “to another div”?
Which DIV you wanna link?
It would be great if you can show me the picture of what you want..
john said
am November 13 2006 @ 9:09 am
Hi Mike,
thanks you for taking time to respond to my query. here is the situation i am facing. I have 2 layers on my html web page.I have also made a small modification on your database in addition to the stock table in your access database i have added another table to the same database naming “beer” with the same contents as in “stocks”. now i was able to pull details from the column “StockName” (under stock table)to layer1 now the problem i am facing is if i click on “beer” (which is pulled from the table “stock” and appears on layer1) i want details from beer to come on layer2 (layers are created using div tags). please let me know if there is a way to do this.
Regards
John
john said
am November 13 2006 @ 9:12 am
Hi Mike,
thanks you for taking time to respond to my query. here is the situation i am facing. I have 2 layers on my html web page.I have also made a small modification on your database in addition to the stock table in your access database i have added another 2 tables to the same database naming “beer” and “pepsi” with the same contents as in “stocks”. now i was able to pull details from the column “StockName” (under stock table)to layer1 now the problem i am facing is if i click on “beer” (which is pulled from the table “stock” and appears on layer1) i want details from beer or pepsi (which ever i click from layer1 to come on layer2 (layers are created using div tags). please let me know if there is a way to do this.
Regards
John
john said
am November 15 2006 @ 11:07 am
Hi Mike,
Any solutions on the above mentioned isues
do let me know if you come up with something.
Thanks,
John
Michael Sync said
am November 15 2006 @ 1:05 pm
Hey John,
Sorry for late reply..
I have read ur comment but I dont get exactly what you wanna do.
You have three tables in your database.
1. Stocks
2. Beer
3. Pepsi
There three tables have same contents.
Why did you create three separated tables which has same fields.?
Dont you think it’s not good if you use only one table?
Now, you already show the StockName in DIV1. If you click this DIV1, you wanna show another DIV called “DIV2″ for details.
Let me know if I’m wrong.
Could you please send me your code and your database that you have done so far?
My Email is mchlsync AT gmail.com.
I may see what exactly you wanna do.
and I will send the detail what you should change back to you.
It would be great if you can tell me step by step what you wanna do.
eg:
1. Displaying all stocks informations from Stock Table.
2. These fields are StockID and StockName.
3. Stock Name will have Hyperlink.
4. When a user click “Hyperlink”, another DIV should be shown like “Yahoo.UI.Dialog” or “LightBox”
P:S: there steps are just a sample.
I hope you got what I mean.
Thanks and very sorry for taking time to response.
Mike
john said
am November 16 2006 @ 3:56 am
Hey Mike,
I will send you the files (mdb and html page) to your gmail id along with a document explaining clearly what i am trying to fix and i need you help on. hopefully this will give you a better picture. but you have understood the requirement pretty clearly from your msg …
Thanks again for assisting me in this small project…
Regards
John
john said
am November 16 2006 @ 5:12 am
Hi Mike,
I have email you with a zip file attachment with all files i have been working on do let me know if you have or havent recieved them.
Regards
John
Michael Sync said
am November 16 2006 @ 6:32 am
Hi John,
I just got ur mail. I will take a look and will reply you today itself.
Michael Sync said
am November 16 2006 @ 8:07 am
Here is link for sample that you can download. (I also sent that file to u.)
I have tested in IE7 and it is working fine.
There are three things that I’d like to suggest you.
1. Database Design
You should think about Table Design. Currently, you have Stocks table. According to the StockName of this table, you have the seperated table for each stock name.
What I would like to suggest is that you should change the name of “Stocks” table to “Categories” table.
then, you should create “Stocks” table which has categoryID. (so, you dont need to create the seperated table for each stock name.)
eg:
Categories
1. Pepsi
2. Beer
Stocks
Stock ID, StockName, CategoryID
1. UB 1
2. kingfisher 1
3. haywards 1
4. mountain dew 2
5. Diet 2
2.
Dont add unecessary complexity to your code
3.
Connecting Database from Javascript is not very good Idea as i said in first comment.
Ajax is the best solutions for your case.
john said
am November 16 2006 @ 10:28 am
Hi Mike,
Thanks a ton for you assistance you are a guru in JS are’nt you? well thanks a lot. one last question i get a information dialogue box every time i click on a link any idea why this comes..
Best Regards
John
Michael Sync said
am November 16 2006 @ 10:48 am
Oh. Yah. I forget to remove it.
Please remove that line.. alert(strHtml) ; from showDetails function. (one line above catch(ex);)
>>Thanks a ton for you assistance
If you want to thanks me for that, you can thanks me by giving my blog URL to a lot of your friends and tell them to visit my blog.
john said
am November 17 2006 @ 3:01 am
Its already done ………………
John said
am November 17 2006 @ 2:49 pm
Hi Mike,
is there no way to write a seperate function for each row of data in the Stocks table to fetch data from any of the tables and not being directly liked to a table having the same row name ?
Regards,
John
Peifang said
am November 17 2006 @ 8:25 pm
Hi Michael,
In my application, I will also use js to connect a database. I tested your code in ASP.NET, but when I debugged the source code. A message poped up and said : ActiveXObject is not defined. So would you please tell me how to define ActiveXObject in IE 6?
Peifang said
am November 17 2006 @ 8:25 pm
Thank you in advance!
Michael Sync said
am November 20 2006 @ 3:10 am
Hi Peifang,
Sorry for late reply. I’m rather use internet internet on Weekend.
Try with these sample files.
http://www.geocities.com/mchlsync/JavaScriptDatabaseSrc.zip
OR
http://www.esnips.com/doc/94023b0f-8699-40fb-af40-81cb8113667c/JavaScript-Database.rar
Don’t make changes anything to that file. Just run..
Let me know the result..
Michael Sync said
am November 20 2006 @ 3:14 am
Hi John,
>>is there no way to write a seperate function for each row of data in the Stocks table to fetch data from any of the tables and not being directly liked to a table having the same row name ?
As of now, we have the same name for the row of stock table and the name of other tables.
If we dont have the same name then how come we will know which row is for which detailed table..
That’s why I told you. You better change the database design..
I guess, you seems like you dont wanna change your database design.
Why?
john said
am November 20 2006 @ 6:50 am
Hi Mike,
First of all thanks for taking time to respond to my query, The reason why i want to a seperate function for each row in stock table to be linked to a seperate table is because i can have control over updates to table(s). this way should i update or create a new table for a anyreason and i want a specific row in the main tablw to access info from that table i can do it without havein to change data from the main table. my answer may not sound convincing enough for you but since the main table desingn has been fixed already theres nothing much i can do at the moment to do thing otherwise. I am sure that there is was a writing a function for each and every row to manually pick a table of the users choice …… I hope that you could provide me a solution if possible…
Thanks again Mike,
John
Michael Sync said
am November 20 2006 @ 9:12 am
Hi John,
Okay. As you said, we can write the manual function. But we need to think about one thing.
As of now, we know which row is for which table because they are the same names.
In case they are NOT the same names, how can we know which row is for which table?
Even we do it manually picking each table in loop, we need to have something that can be link between the row of Stock and other tables.
So, What is your plan for that?
It’s not about Javascript. asking your plan…
If you have something about that, I may suggest some Javascript coding for you.
Actually, the users can do thing with your current design. You only need to make ensure that whenever the user add new row in Stock table, the required tables (detailed tbl) should be created.. then, your code will be working fine as long as the stock name of stock table and the table name are the same.
Peifang said
am November 20 2006 @ 12:25 pm
Hi, Michael
It works now! Thank u very much!
Regards!
Peifang
John said
am November 20 2006 @ 3:25 pm
Hi Mike,
i will go by your suggestion here on this modification 
I will try and modify the database design and see if that will simplify things a bit. i hope that a few changes to the DB design itself will make a big difference
Regards
John
Vin said
am November 23 2006 @ 5:51 am
Hi Micael,
Is there a way to diplay some data form a database using Javascript. Assume this example in your database you have a table called stocks and all values are pulled into a web page using Javascript. now if i place my mouse over say pepsi can i get values from stockid field and reordrlevel field to be displayed like a splash screen (in some formated way by mentioning the font type and size) outsid the table on the web page.
Please do let me know if you know a way for this.
Regards
Vin
Vin said
am November 23 2006 @ 5:52 am
Hi Michael,
Sorry that i misspelled your name
in my previous message.
Regards
Vin
Michael Sync said
am November 23 2006 @ 6:40 am
Hi Vin,
I guess, You are asking me the same question asked by John..
Please check this example..
http://www.esnips.com/doc/94023b0f-8699-40fb-af40-81cb8113667c/JavaScript-Database.rar
But I would suggest you to have Categories and Stock Table instead of having one stock table and many detailed tables.
Please check the example below first.
Then, If you are not able to solve ur problem after checking the sample, please send me your code that you hav done so far, screenshot you expect and the requirement mentioning step by step..
Vin said
am November 23 2006 @ 8:17 am
Hi Mike,
i downloaded the mdb file and html file and ran it on my system but i already have something similar working but what i need is something like a tooltip functionality. So based on the mdb file and html file i downloaded all i need is if i place my mouse on top of beer or pepsi or which ever the case may be from that table is it possible to get a tooltip (a description) from the coresponding tables but not in a layer or anything it can be outside the layers.
when i say a tooltip an example would be like when you place a mouse pointer over a image you get a small description of the picture at the tip of your mouse but only i want it out on the page rather than the tip of the mouse also i am trying to format the description using fonts and sizes
Please do let me know if you know how
Best Regards!
Vin
Michael Sync said
am November 23 2006 @ 8:42 am
Okay.. vin..
you just need to add your function on mouseover event of your DIV…
you can do whatever you want from that function..
please send me your code that you hav done so far, screenshot you expect and the requirement mentioning step by step..
I will check what I can do.. but I wont write the whole code..
Vin said
am November 23 2006 @ 8:54 am
That will do. I will send you the files and a txt file with an explaination. do let me know if you need more. I will forward the files to the following email id
……….My Email is mchlsync AT gmail.com.
Vin said
am November 23 2006 @ 9:17 am
Hi Mike,
i have sent you an email to your gmail id hopefully you have recieved it.
I hope that you could help me out on this.
Best Regards
Vin
Michael Sync said
am November 23 2006 @ 10:02 am
try like that..
while(!rs.eof) {
strHtml += "<tr>";
strHtml += "<td width=\"10px\" onmouseover=\"showDetails(’"
+ rs.fields(0).value + "’)\" onmouseout=\"hideDetails();\"
style=\"cursor:pointer\">"+ rs.fields(0).value +"</td>";
strHtml += "</tr>";
rs.MoveNext();
}
function hideDetails(){
var objDIV = document.getElementById("layer2");
objDIV.style.visibility="hidden";
}
Hope, it would help..
John said
am November 24 2006 @ 5:24 am
Hi Mike,
As you had suggested i had changed the db design a bit and i needed you JS expertise help with te front end. I was hopeing that you would assist me on this….Please
Regards,
John
John said
am November 27 2006 @ 5:18 am
Hi Mike,
Last week i has sent you an email with a spec doc kindly disregard that email. right now i just need one help form you. in one of your JS files that you had sent me there was a function written showDetails(dtlTblName) where if there was a data in a table and if it had a correponding table it would pull in the data from that table automatically (upon clicking it). Now i just need to know if if there is a way to do the same thing but instead of pulling in data from a table with the same name can i get details from a table having a different name but having the same row name. I hope i dint confuse you but if you did get confused please do let me know and i will email a sample file to you for your better understanding.
Thanks again Mike
your suggestion on changing the DB design did a lot good to my little project and if you could help me with the query mentioned above im will be very thankful to you.
Regards
John
Vin said
am November 27 2006 @ 9:22 am
Hi Mike,
by the way I got to know about this site from John who i work with. he had tld me that this site and you in particular was like a one stop shop for any js related queries and i believe him now. Thanks again Mike. this site is very very useful to people like myself and John who have little expierence in JS.
Thanks for the tip it was very useful
Best Regards
Vin
Michael Sync said
am November 27 2006 @ 10:43 am
Sorry for late reply.. I’m kinda busy during these days.. I might be able to reply you around yesterday…
john said
am November 27 2006 @ 10:54 am
sorry i dint quite understand you…did you mean wednesdayor later .. ? either ways to recieve you response saying that you will respond to my query itself is big relief
Thanks again Mike….
Regards
John
Michael Sync said
am November 28 2006 @ 2:53 am
>>I might be able to reply you around yesterday…
I mean.. tomorrow… not yesterday.
I wil reply u today..
spike said
am November 28 2006 @ 4:19 am
Hello Mike,
I am contacting you because i understand from your website that you are a guru in Javascript, well i was hope that you could assist me on a small issue that i am trying to fix in javascript. I’ll get straight to the point.
I have a database within which there is a table called “table_1″ there are 3 fields within the table namel (product, type and description) now i have populated the table in a way that the table would look like the dratf below…
table_1
product type description
a drink soft drink
a drink hot drink
a snack cake
a snack veg
what i am trying to do is i want to display all data under description that belongs to the coresponding field “drink” to come in one row of a table and all data under description which has the corresponding field “snack” to come in the row below on the same table. now at a later stage if i add more values to the table for drink or snack this should automayically be displayed on the table on the web page.
I do hope that you know how a way to suggest me.
Thanks !
Spike
spike said
am November 28 2006 @ 4:22 am
I am sorry that the spaces that i added inbetween the data has gone but the “a” is a seperate character under “product”… “drink and snack” is a data under “type” and “softdrink, hot drink, cake and veg” come under description
john said
am November 28 2006 @ 4:24 am
Thanks a lot Mike
i am awaiting your response…thanks for your time
Regards
John
John said
am November 28 2006 @ 12:01 pm
Hi Mike,
Please do let me know if you were able to get the link happening..
Thanks and Regards
John
john said
am November 29 2006 @ 4:00 am
Hi Mike,
I have emailed you the files i was working with, hopefully that will assist you in finding a solution. please do let me know if you need more details and i can provide it.
Regards
John
Michael Sync said
am November 29 2006 @ 11:34 am
sorry for late reply and i was too busy during these day.
Please check it this file. [download]
I also made changes your database design..
hope, it would help u.
john said
am November 30 2006 @ 3:14 am
Hi Mike,
I saw the modifications you had made to the database but the problem is i use sql server and i the mdb is just a working model. I cannot make the kind of changes you made to the mdb file on the sql database for technical reasons. i tried integrating the your code with the file i was working and it dint work it was giving me an error “Data type mistatch” i beieve the reason its working on the mdb is because of the modifications you made at the design level. I understand that you are a very busy person and i am sorry to have kept querying you like this all the time. please tell me if there is a way to get the output the way you already did except without changing anything at the database level.
I hope that you can help me out on this. and i am desperately looking forward to your help on this.
Best Regards
John
spike said
am December 6 2006 @ 4:40 am
just wanted to know how to write a if condidtion to check a value from the database . i.e. if the value is same then display all vaues in the same row if they are different then display them in different rows.
for example in my database table i have 2 fields row and value.
displayed as below.
row Value
a 1
a 2
a 3
a 4
b 5
b 6
b 7
b 8
c 9
c 10
c 11
so now i am trying to check if the value belongs to the same row display in all in one row of a table else display them in a seperate row of the same table.
the output should look like this
a = 1234
b = 5678
c = 91011
is this possible.
Spike
spike said
am December 7 2006 @ 2:59 am
Hi Mike,
I dint see your reply but just wanted to know if you have any suggestion.
Thanks
Spike
Michael Sync said
am December 7 2006 @ 3:46 am
Yeah.
It’s not about Javascript. It’s about T-SQL Query String.
Suppose:
Note: the following code is just a concept.. not correct code syntax.
Table Name : Table1
Fields : RowID, RowValue
rs1.GetData (”SELECT RowId FROM Table1 GROUP BY RowID”);
while(rs1.eof){
rs2.GetData(”SELECT RowValue FROM Table1 WHERE RowID=’” + rs1[0] + “‘”);
string str = rs[0] + ” = ” ;
while(rs2.eof){
str += rs2[0];
}
console.log(str); // Here the result wil be logged. eg: a = 1234
rs2.close();
}
rs1.close();
spike said
am December 7 2006 @ 6:41 am
below is my code i tried the group by function but dint quite work can you point out where i am going wrong from the code below
var strHtml =”";
strHtml += “”;
strHtml += “appid“;
var conn = getAdoDb(”ADODB.Connection”);
conn.open(conn_str, “”, “”);
var rs = new ActiveXObject(”ADODB.Recordset”);
strQuery = “SELECT rowvalue FROM table WHERE app_id=’00210′”;
rs.open(strQuery, conn, adOpenDynamic, adLockOptimistic);
strHtml += “”;
if(!rs.bof){
rs.MoveFirst();
while(!rs.eof) {
strHtml += “”+ rs.fields(0).value +”";
rs.MoveNext();
}
}
else{
//No Records.
strHtml += “”;
strHtml += “no data”;
strHtml += “”
}
strHtml += “”;
conn.close();
strHtml += “”;
i am trying to get values that belong to the same row to come in diff td’s of the same row and if the row id is different they should come ina a diff row .
Thanks in advance
Spike
Michael Sync said
am December 7 2006 @ 2:53 pm
var strHtml =””;
strHtml += “”;
var conn = getAdoDb(”ADODB.Connection”);
conn.open(conn_str, “”, “”);
var rs = new ActiveXObject(”ADODB.Recordset”);
var rs2 = new ActiveXObject(”ADODB.Recordset”); // Declaring another recordset
//If you wanna get all id then you shouldnot use the specific id.
strQuery = “SELECT RowId FROM Table1 GROUP BY RowID”;
rs.open(strQuery, conn, adOpenDynamic, adLockOptimistic);
if(!rs.bof){
rs.MoveFirst();
while(!rs.eof) {
//This is recordset 2
rs2.open(”SELECT RowValue FROM Table1 WHERE RowID=’” + rs.fields(0).value + “‘”, conn, adOpenDynamic, adLockOptimistic);
strHtml += “”+ rs.fields(0).value +””; //result strHtml = “a”
strHtml += “ = “; //result strHtml = “a = ”
if(!rs2.bof){
rs2.MoveFirst();
while(!rs2.eof) {
strHtml += “”+ rs2.fields(0).value +””; //result strHtml = “a = 1234″
rs2.MoveNext();
}
}
rs2.close();
rs.MoveNext();
}
}
else{
//No Records.
strHtml += “”;
strHtml += “no data”;
strHtml += “”
}
rs.close();
conn.close();
Hope, It would help for you…..
spike said
am December 8 2006 @ 5:35 am
The code is working but i ran into a small problem after that. there are functions i have written whrn there i click on the values that come form the database but after implementing the code the functions dont seem to work. when i say function i have written an on click event for the values that come on the page so if i click on a value the a query is passed and some values are returned to me this does not happen now.
if you dont mind can i send you the file i am working with just view it and tell me where i wne t wrong.
…
Spike
Michael Sync said
am December 8 2006 @ 7:08 am
>>if you dont mind can i send you the file i am >>working with just view it and tell me where i >>wne t wrong.
Yeah. sure. it’s better..
I think, you are john. right? cuz i noticed that ur ipaddress and john’s address are the same..
spike said
am December 8 2006 @ 7:46 am
How do you know John
…. John haug? if its the same person he is my friend and we studied to gather the same IP address could be because its a cafe (browsing center.) anyways Mike i identified the error it was a rs issue I was able to fix it myself …..Thanks for the help.
Michael Sync said
am December 8 2006 @ 8:37 am
okay.. i see…
>>anyways Mike i identified the error it was a rs >>issue I was able to fix it myself …..Thanks for >>the help
Your welcome. Spike.
spike said
am December 11 2006 @ 4:49 am
Hi Mike just wanted to know one thing is it possible to call java API’s from a java script. Basically if instead of using straight forward SQL querys to the database use API’s created in JAVA to call them on your webpage using javascript. which is the best option for this purpose. also if you have some examples this would be great.
Thanks
Spike
Michael Sync said
am December 11 2006 @ 12:24 pm
Ajax might be a solution for u..
Here is free training course.. I recommend you that it’s very cool ..
http://www.javapassion.com/ajaxcodecamp/
fpt said
am June 29 2007 @ 6:03 pm
Hi. Thanks for the good read.
Gorazd said
am August 16 2007 @ 9:55 pm
Hi Michael,
very helpful and useful example.
Could the code also be used for adding new records to the database table with SQL ‘INSERT INTO’ statement? Should the locking variables be changed or statement ‘rs.add’ or something be used?
Thanks
Michael Sync said
am August 17 2007 @ 1:20 am
Yes.
It’s better if you use “Command” instead of “Recordset” for inserting new data.
Let me know if you still have prob
Gorazd said
am August 18 2007 @ 10:36 pm
Hi Mike
Thanks for your suggestions. In fact I managed to get code to work even without using “Adodb.Command”.
So for other readers, here’s the code:
function AddNewRecord() {
var conn = getAdoDb(”ADODB.Connection”);
conn.open(conn_str, “”, “”);
strQuery = “INSERT INTO Table1 (Field1, Field2) VALUES (’Value1′, ‘Value2′)”;
conn.execute(strQuery);
conn.close();}
Of course you would probably want to have an input text field or two to write in the strings you want to add to database:
Then the third line of the code will be (in IE):
strQuery = “INSERT INTO Table1 (Field1, Field2) VALUES (’” + document.all.input1.value + “‘, ‘” + document.all.input2.value + “‘)”;
And the strings you entered in the two Input fields will be added as a new record to the database. (You’ll need of course also a submit button with action or onclick property set to ‘AddNewRecord()’.)
Thanks again for your example and suggestions, Mike, and I hope this code too will help someone of the readers.
Gorazd
Michael Sync said
am August 19 2007 @ 2:12 am
Thanks a lot. Gorazd.
Kamran said
am August 31 2007 @ 10:54 am
My issue is that I am using .net dll (with com interface enabled for asp pages) and I need to do runtime calculations based on customer input parameters.
Lets say I want to do this in javascript.
x= “test” //value entered by customer on form
function GetData(x)
{
call DB(x) //toget database data
write response on asp page
}
One solution i thought of is using a record set and filter it with value “x” and write the response on asp page (thats why i cam on you site.
Another solution is to hard code the value of test and get data by calling asp method that calls .net dll and writes the response (its working for me but its no good to me).
Another solution is to use remote java script but i assume it would require to install remote scripting on clients as well which is not any good thing to do.
What do you think? any solution?
thank you for your time.
regards,
Kamran
Kamran said
am August 31 2007 @ 10:56 am
I also dont want to expose sqlserver password in javascript (I am new to javascript not java) so I assume using recordset in javascript would expose the connection string.. ………….weird…
Michael Sync said
am September 4 2007 @ 1:50 pm
yeah… it is just a demostration of how things can be done with javascript.. it is not good practice to use that way in real project….
Chirag Modi said
am September 7 2007 @ 4:51 pm
Hi Michael,
This is Extremely Useful article for Client Side DB connection.
Your work is really appreciable
This could be more better if it works in Mozila/Firefox.
any idea for Firefox?
Thanks & Regards,
Chirag Modi
Michael Sync said
am September 8 2007 @ 5:36 am
Firefox doesn’t support any ActiveX object by default…..
Check-out this plug-in
http://www.iol.ie/~locka/mozilla/plugin.htm
I haven’t tested yet since I don’t have the FF version that match with this plug-in..
Emefa said
am September 29 2007 @ 1:44 pm
Does anyone realize that putting connection strings as in the above javascript opens your username and password to the world?
Just a though.
Michael Sync said
am September 30 2007 @ 4:02 am
Hi Emeta,
You are absolutely right about that. The main reason why I post this is that I wanna show how to connect db from JS code. I already mentioned about that in previous comment.
rajesh said
am October 6 2007 @ 8:08 am
hi Michael,
Thanks for ur article.it is very usefull for me.i have use this with asp.net,it works.i want to connect to sql server,i tried but it is not working .can u please help me?
thnx in adv,
rajesh
rajesh said
am October 6 2007 @ 8:23 am
hi Michael,
dont mind my preivios message.it works now.
with regards,
rajesh
Michael Sync said
am October 6 2007 @ 1:44 pm
okay.. good.
alice said
am March 24 2008 @ 8:17 pm
hi!Michael,can i ask u sth regarding js connecting with an ms access database?
what i want to do is to insert sth like a jump menu. the contents in the menu are from one particular column of the db say Name. then i created a table below the menu, displaying the other column content in the same row (like the name of a student, followed by his phonr number, home address..). my request is that every time i choose one name from the menu, i can get the returned details from the db in the form i created within the same page.
can i achieve this using js?
Michael Sync said
am March 24 2008 @ 9:06 pm
Yes. It’s possible to do. Please read all previous comments. I think someone ask the same question before. You can get the sample file (something like master/detail things) in my previous replies..
alice said
am March 24 2008 @ 10:29 pm
thanks for ur reply!
i didnt expect that u could reply me…^^
i’ll read thru the whole page and see if i can get the answer.
Michael Sync said
am March 24 2008 @ 11:03 pm
Hi Alice, Of course. I used to reply every comments as soon as I can. This is my respect for my blog reader.
I hope you will get the answer.. 
Good Luck!
alice said
am March 24 2008 @ 11:52 pm
thanks a lot!I thought it was so old that u wouldnt reply~~^^ u r so kind!:D
there is sth confused me…
i still cant find out how did u manage to make it.
the difficulty i met is the returned value should be returned are not displayed correctly…
ps: i’m using dreamweaver to develop a website…but it should be ok to use js as programming language.
Michael Sync said
am March 25 2008 @ 7:56 am
Hi,
I think you can take a look this sample.
Anyway, As I said so many time before, it’s not good idea to use Javascript for connecting database. This code is NOT cross-browser. The best way would be “Using Ajax + Server-side code (e.g. PHP, ASP.NET or Java or etc. )”
alice said
am March 25 2008 @ 6:23 pm
Hi, thank u for the advice.
For me now, i’m using dreamweaver to develop a website. It’s easy to setup a jump menu…the prob i met is that i can not get value returned from my database when i select sth in the menu.
so that’s why i’m thinking about using a script to develop what i want…
and my friend saw this page and told me to have a look at it…after looking thru it, i found all were about js, that’s why i’m asking sth about how to use js to achieve my goal.
but if there is a better way, i think i should try it out…
so…do u have any suggestions?
thanks!
alice said
am March 25 2008 @ 8:08 pm
i run and studied the sample u gave me~
but there is sth difficult for me to modify, so that i can use in my application.
i have 1 table (not the multiple-table db as in ur sample) in the database including all datas that may be used. i dunt noe how to convert the application to that when i select one option from the jump menu, the other info in the same row of the same table in the db comes to the table in the webpage, as the one shown in ur sample
alice said
am March 27 2008 @ 2:48 am
hi…are u there?
i really need ur help…
i’m dealing with it for 2 days…nothing was done..
Michael Sync said
am March 27 2008 @ 8:42 am
Hi Alice,
Sorry for late reply. I didn’t get the notification of your message.. maybe.. something wrong with my mail server.
No. This is where Ajax comes in. You can invoke the web service or other types of server-side script from Javascript using XmlHttpRequest Javascript object. The server-side will query the data from database and it will return as JSON or XML to Javascript. So, you get the data from database in client-side.
Using Ajax is the best solution and it will work in any browser. Using ActiveX object that I used in my sample doesn’t work in other browsers.
You can use the similiar code. I can’t provide the exact code but I’m sure that you won’t need. You can get the idea from the code and figure out how you can use in your way.
Here is the sample code.
var varMenuId = 1; //This is the variable where you have to put the menu id
var strHtml =””;
strHtml += “”;
var conn = getAdoDb(”ADODB.Connection”);
conn.open(conn_str, “”, “”);
var rs = new ActiveXObject(”ADODB.Recordset”);
//We are doing querying data based on menuId
strQuery = “SELECT RowId FROM Table1 WHERE MenuId=” + varMenuId;
rs.open(strQuery, conn, adOpenDynamic, adLockOptimistic);
if(!rs.bof){
rs.MoveFirst();
strHtml += “”+ rs.fields(0).value +””; //result strHtml = “a”
rs.MoveNext();
}
}
else{
//No Records.
strHtml += “”;
strHtml += “no data”;
strHtml += “”
}
rs.close();
conn.close();
Let’s there are three menuitems called menu1, menu2 and menu1. If you click menu1, you want to show the sub-menus based on what you selected (menu1 in this case)
alice said
am March 27 2008 @ 6:40 pm
thanks for ur code.
but when i applied it to my script and displayed it in IE, there was an error:
Error Type:
Microsoft JScript compilation (0×800A03F6)
Invalid character
/sdhwo/test.asp, line 101, column 13
var strHtml =””;
————^
alice said
am March 27 2008 @ 6:47 pm
And this is my whole script for the jump menu:
DDF
<option value=”">
0) {
if (!Recordset1.BOF) Recordset1.MoveFirst();
} else {
Recordset1.Requery();
}
%>
0) {
if (!Recordset1.BOF) Recordset1.MoveFirst();
} else {
Recordset1.Requery();
}
%>
0) {
if (!Recordset1.BOF) Recordset1.MoveFirst();
} else {
Recordset1.Requery();
}
%>
alice said
am March 27 2008 @ 6:49 pm
why the script was not displayed correctly?
so strange
alice said
am March 27 2008 @ 7:55 pm
can i send my script to u via email?
because i am too new to js, maybe there is so code i understood or applied wrongly…
Michael Sync said
am March 27 2008 @ 8:29 pm
Hi Alice,
I have mailed you.. thanks.
alice said
am March 27 2008 @ 8:48 pm
i’d say that really really thanks for ur help!
alice said
am March 27 2008 @ 9:21 pm
i’d like to say sth abt the asp file.
because some of the code was automatically written by Dreamweaver, so it might be long and messy.
again, thanks for ur help
alice said
am March 27 2008 @ 11:51 pm
the prob now for me is the error
Microsoft JScript runtime (0×800A138F)
Object expected
for the line of: var conn = getAdoDb(”ADODB.Connection”);
Michael Sync said
am March 28 2008 @ 12:02 am
Hi Alice,
Are you using window? Do you have Microsoft Visual Studio installed? I think some components are missing in your system. maybe, you need to install Microsoft Data Access Components (MDAC) 2.8. You can get it from this link
For me, I didn’t need to install separately since I already have VS6,2005 and 2008 installed in my machine.
alice said
am March 28 2008 @ 12:05 am
i only have iis in my computer. it is said that to run an asp file, the iis is enough.
btw, is the script working on ur computer?
alice said
am March 28 2008 @ 12:13 am
hi, when i tried to install it, there was a popup window saying that all that r part of windows
alice said
am March 31 2008 @ 1:31 am
hi…r u there?
i have struggled for another 3 days…
adn still got a lot of errors…
the most frequent one is Microsoft JScript runtime (0×800A138F)
Object expected
so sad about this…
alice said
am April 2 2008 @ 6:14 pm
i hav solved my prob now..
thanks for ur help!!
basanthi said
am April 30 2008 @ 12:04 am
hi
i am getting an error while opening the internet explorer page as no values givan for required parameters.i have connected html and access using java script.where i am doing wrong i could not get it.
pls help me out
Michael Sync said
am April 30 2008 @ 2:28 am
Hi Alice, Sorry for very very late response.. I was so busy these days and couldn’t have that much time to take a look it.. actually, using ActiveX is not good one..
Hi basanthi, using activex is not very good idea. So, Could you please do it with Ajax?
alice said
am May 4 2008 @ 7:42 pm
Hi Michael, thanks again for your kindness~
K Bhanu Sravani said
am May 5 2008 @ 5:39 am
hai
I wrote the same code as above. Frontend is HTML, Backend is MS-access. we are having a group of linking forms,in first form data is storing in access and from second form data is not storing in access. Where am i going wrong Please help me.
Michael Sync said
am May 5 2008 @ 5:46 am
You should probably tell me what error you got or etc.. With your question, I’m not sure how I can help you.. The most of problems are already discussed in old comments so if you read each and every comments in this post, you may probably solve the problem… one more thing… I suggest not to use ActiveX object as much as possible.. Aax can do better..
K Bhanu Sravani said
am May 11 2008 @ 11:16 pm
i wrote the code in the same format as above.
when i enter values in form it is storing in database.And after completion of that file entry it is closingi.e.., the browser is collapsed.
MY requirement is it should find the existing record and should enter the fields which are kept empty