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>
July 22nd, 2006 at 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
July 22nd, 2006 at 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
October 5th, 2006 at 6:56 pm
What about if a wanna to modify or edit any record? which is the code?
October 10th, 2006 at 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.
October 10th, 2006 at 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..
November 10th, 2006 at 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
November 10th, 2006 at 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
November 10th, 2006 at 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.
November 10th, 2006 at 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
November 10th, 2006 at 8:57 am
your help on this issue will be very much appreciated
Thanks again,
john
November 10th, 2006 at 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..
November 13th, 2006 at 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
November 13th, 2006 at 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
November 15th, 2006 at 11:07 am
Hi Mike,
Any solutions on the above mentioned isues
do let me know if you come up with something.
Thanks,
John
November 15th, 2006 at 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
November 16th, 2006 at 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
November 16th, 2006 at 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
November 16th, 2006 at 6:32 am
Hi John,
I just got ur mail. I will take a look and will reply you today itself.
November 16th, 2006 at 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.
November 16th, 2006 at 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
November 16th, 2006 at 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. :)
November 17th, 2006 at 3:01 am
Its already done ……………… :)
November 17th, 2006 at 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
November 17th, 2006 at 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?
November 17th, 2006 at 8:25 pm
Thank you in advance!
November 20th, 2006 at 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..
November 20th, 2006 at 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?
November 20th, 2006 at 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
November 20th, 2006 at 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.
November 20th, 2006 at 12:25 pm
Hi, Michael
It works now! Thank u very much!
Regards!
Peifang
November 20th, 2006 at 3:25 pm
Hi Mike,
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 :) i will go by your suggestion here on this modification :)
Regards
John
November 23rd, 2006 at 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
November 23rd, 2006 at 5:52 am
Hi Michael,
Sorry that i misspelled your name :) in my previous message.
Regards
Vin
November 23rd, 2006 at 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..
November 23rd, 2006 at 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
November 23rd, 2006 at 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..
November 23rd, 2006 at 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.
November 23rd, 2006 at 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
November 23rd, 2006 at 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..
November 24th, 2006 at 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
November 27th, 2006 at 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
November 27th, 2006 at 9:22 am
Hi Mike,
Thanks for the tip it was very useful :) 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.
Best Regards
Vin
November 27th, 2006 at 10:43 am
Sorry for late reply.. I’m kinda busy during these days.. I might be able to reply you around yesterday…
November 27th, 2006 at 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
November 28th, 2006 at 2:53 am
>>I might be able to reply you around yesterday…
I mean.. tomorrow… not yesterday. :)
I wil reply u today..
November 28th, 2006 at 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
November 28th, 2006 at 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
November 28th, 2006 at 4:24 am
Thanks a lot Mike :) i am awaiting your response…thanks for your time
Regards
John
November 28th, 2006 at 12:01 pm
Hi Mike,
Please do let me know if you were able to get the link happening..
Thanks and Regards
John
November 29th, 2006 at 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
November 29th, 2006 at 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.
November 30th, 2006 at 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
December 6th, 2006 at 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
December 7th, 2006 at 2:59 am
Hi Mike,
I dint see your reply but just wanted to know if you have any suggestion.
Thanks
Spike
December 7th, 2006 at 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();
December 7th, 2006 at 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
December 7th, 2006 at 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…..
December 8th, 2006 at 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
December 8th, 2006 at 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..
December 8th, 2006 at 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.
December 8th, 2006 at 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.
December 11th, 2006 at 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
December 11th, 2006 at 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/
June 29th, 2007 at 6:03 pm
Hi. Thanks for the good read.
August 16th, 2007 at 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
August 17th, 2007 at 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
August 18th, 2007 at 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
August 19th, 2007 at 2:12 am
Thanks a lot. Gorazd.
August 31st, 2007 at 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
August 31st, 2007 at 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…
September 4th, 2007 at 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….
September 7th, 2007 at 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
September 8th, 2007 at 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..
September 29th, 2007 at 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.
September 30th, 2007 at 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.
October 6th, 2007 at 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
October 6th, 2007 at 8:23 am
hi Michael,
dont mind my preivios message.it works now.
with regards,
rajesh
October 6th, 2007 at 1:44 pm
okay.. good. :)
March 24th, 2008 at 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?
March 24th, 2008 at 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..
March 24th, 2008 at 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.
March 24th, 2008 at 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!
March 24th, 2008 at 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.
March 25th, 2008 at 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. )”
March 25th, 2008 at 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!
March 25th, 2008 at 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
March 27th, 2008 at 2:48 am
hi…are u there?
i really need ur help…
i’m dealing with it for 2 days…nothing was done..
March 27th, 2008 at 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)
March 27th, 2008 at 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 =””;
————^
March 27th, 2008 at 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();
}
%>
March 27th, 2008 at 6:49 pm
why the script was not displayed correctly?
so strange
March 27th, 2008 at 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…
March 27th, 2008 at 8:29 pm
Hi Alice,
I have mailed you.. thanks.
March 27th, 2008 at 8:48 pm
i’d say that really really thanks for ur help!
March 27th, 2008 at 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
March 27th, 2008 at 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”);
March 28th, 2008 at 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.
March 28th, 2008 at 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?
March 28th, 2008 at 12:13 am
hi, when i tried to install it, there was a popup window saying that all that r part of windows
March 31st, 2008 at 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…
April 2nd, 2008 at 6:14 pm
i hav solved my prob now..
thanks for ur help!!
April 30th, 2008 at 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
April 30th, 2008 at 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?
May 4th, 2008 at 7:42 pm
Hi Michael, thanks again for your kindness~
May 5th, 2008 at 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.
May 5th, 2008 at 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..
May 11th, 2008 at 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
June 6th, 2008 at 3:53 am
hi i got this error “ActiveX object not defined”…….
How to rectify this error????????
July 2nd, 2008 at 4:01 pm
Hello,
I want to create a very simple web application that will display the rows of a particular database table. However I also want the application to display new entries into the table, as they are inserted. Is there an easy way to determine when a new row has been inserted?
Thanks.
April 4th, 2009 at 4:33 am
thx
June 8th, 2009 at 10:50 am
To Michael Sync,
I have a program that I’ve created completely in javascript (html wrapped in an ebook exe program) that people love except for the fact that they need to complete all the tasks within my program to create their project; many of them have said they would like the ability to input some data and then return to the program and have the program automatically fill-in what data they inputted previously.
I know cookies will not work, because they will not hold enough data; is it possible to have some kind of javascript database within the program that the software could auto write to and auto retrieve from.
I’ve never worked with databases, but if this is something that can be done, I’m willing to pay for it.
Thanks
June 9th, 2009 at 3:16 am
Hi,
My Problem is that I would like to display all the data which are corresponding to the radio button values, but its not getting, I’m able to access it through the id’s but not through the values.
Is there a way to populate this?
June 24th, 2009 at 5:43 am
Hi Michael,
I know there is a long time to this artical , but realy I need your example
In my application, I’m using js to connect a database. I tested your first code , but when I debugged the source code. A message poped up and said : ActiveXObject is not defined, also for my bad luck the files you have attached to reply Peifang don’t work.
So please if you could send me this files.
Thank you
Rose
July 22nd, 2009 at 12:37 am
hi Michael,
You are really great.I was actually struggling for the last two weeks..in other forums ..i was told that we need to use server side scripting even when db is located locally..great effort..and effort appreciated.
August 26th, 2009 at 6:36 am
i want to check onblur of a text box.when i click outside the text box.that time it will check in database filled value is existing in database or not…for that what can i do?
September 13th, 2009 at 6:36 pm
Hi Mike,
I was able to run this code locally on windows platform, but when I put it into the server, It didn’t work. 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 ? 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 yiu51car@yahoo.com
October 6th, 2009 at 1:28 am
Hi,
I found this code and it is working great, when the path to file is specified on an Harddisk. But how can I get this code working with a database placed on the server, e.g. http://myserver/mydatabase/database.mdb?
Anyone who got this working?
Any help would be appreciated.
Best Regards,
Klaus.
October 6th, 2009 at 1:44 pm
Hi,
I tried the code and it is working fine as long as the .mdb is specified with an absolute path. But when I try to use only the Database name, because it is located in the same folder as the .htm or when I try to use the Intranetlink it does not work. Does this not work at all or am I doing something wrong?
Best Regards,
Klaus.
October 6th, 2009 at 1:47 pm
Sorry, for posting again. I checked this site again and did not find my comment. So I thought maybe something went wrong.
I am really sorry.