Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Web Programming > JavaScript > Javascript How-To
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Javascript How-To Ask your "How do I do this with Javascript?" questions here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Javascript How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 7th, 2005, 04:43 PM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Javascript Access Database Query

I'm having trouble getting all the syntax and parameters complete for a database lookup function in javascript. I'm used to programming in ASP and VBScript, but the javascript is throwing me off a little bit. Can anyone tell what is wrong with this code? It is giving me an error saying the connection string is incorrect. I've tried several variations, but it won't work. Also, it is having a problem with Server not being an object; do I need to use 'new'? Here is the code:

Code:
function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db\\sdi.mdb"; 

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var recordCount = RS.Fields.Count;
    alert ("recordCount" + recordCount);
    //RS.Fields(x).Name
}
Thanks in advance!

Panuvin

Reply With Quote
  #2 (permalink)  
Old September 7th, 2005, 05:40 PM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

Hi Panuvin,

Server is only used in ASP, and isnt available in javascript. Also, if this is normal client-side javascript, the database would have to be on the local computer. I dont know if you can read access databases across the web.

To convert to javascript, you dont have to change much, mainly just defining constants and creating ActiveXObjects instead of using Server:

adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db\\sdi.mdb";

var ConnectObj = new ActiveXObject("ADODB.Connection");
var RS = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT * FROM Categories;";

ConnectObj.Open(myConnect);
RS.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

var recordCount = RS.Fields.Count;
alert ("recordCount: " + recordCount);

Hope this helps
Philip Cole
Reply With Quote
  #3 (permalink)  
Old September 8th, 2005, 01:49 AM
joefawcett's Avatar
Wrox Author
Points: 9,712, Level: 42
Points: 9,712, Level: 42 Points: 9,712, Level: 42 Points: 9,712, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,066
Thanks: 1
Thanked 36 Times in 35 Posts
Default

Is this running server-side or client-side?
Aside from the fact that the path is not well-formed, unless "db" was a typo for "d:" then either, if server-side:
Code:
function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb"; 

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    Response.Write ("Field Count" + fieldCount);
    RS.Close();
    ConnectObj.Close();
}
Client-side (security settings will need to be set to allow unsafe ActiveX initialisation, database must be on client in D: drive root).
Code:
function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb"; 

    var ConnectObj = new ActiveXObject("ADODB.Connection");
    var RS = new ActiveXObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    alert("Field Count" + fieldCount);    
    RS.Close();
    ConnectObj.Close();
}
Are you sure empId is a text field not an integer? If an integer you don't need quotes around the value?
You should also test for the recordset having any rows at all before reading data:
Code:
if (RS.BOF && RS.EOF)
{
  //No data
}
Finally you need to make sure the constants such as adCmdText are defined somehow in the page.



--

Joe (Microsoft MVP - XML)
Reply With Quote
  #4 (permalink)  
Old September 8th, 2005, 09:11 AM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

Thank you two for your replies. This is going to be running server side. The database exists on the server and ActiveX is disabled on both the server and every client machine (5000+) at my company. This page is an ASP page, but does this mean that I can't use javascript to make this work? The reason [I thought] I needed to use javascript is that the code utilizes the onchange() method to call a javascript function to do a database look-up and then puts the value in the input text field. I wasn't sure if I could do this in ASP/VBScript. I'm so lost :/

Thanks again in advance for any assistance. I'll attempt a few more approaches in ASP/VBScript, but would prefer to use javascript for simplicity.

Panuvin

Reply With Quote
  #5 (permalink)  
Old September 9th, 2005, 12:40 PM
joefawcett's Avatar
Wrox Author
Points: 9,712, Level: 42
Points: 9,712, Level: 42 Points: 9,712, Level: 42 Points: 9,712, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,066
Thanks: 1
Thanked 36 Times in 35 Posts
Default

You seem to be confusing the idea of client-side versus server-side code and JavaScript versus VBScript. Your code is intended to run on the server, this means that it runs before the client loads the page and has no knowledge of such things as "onchange".

If your company has really disabled ActiveX on the server then you won't be able to access any database information as ADO is an ActiveX, or COM, component.

--

Joe (Microsoft MVP - XML)
Reply With Quote
  #6 (permalink)  
Old September 9th, 2005, 02:35 PM
Authorized User
 
Join Date: Sep 2005
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for all your help. I did later realize after playing with some Javascript that I would not be able to interact with the server-side database. I was able to modify some of the ASP data and use ASP/VBScript to solve my problem. I'm still not a big fan of Javascript :P ...but then again I need to learn ASP.Net. Thanks again!

Panuvin

Reply With Quote
  #7 (permalink)  
Old July 24th, 2007, 06:23 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi everyone!
This topic explained in a few lines, what I've been meaning to program for a week!

Although I would like to revive the topic and ask for advice.

On the server-side function here:

[code]
function dblookup()
{
    var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\\sdi.mdb";

    var ConnectObj = Server.CreateObject("ADODB.Connection");
    var RS = Server.CreateObject("ADODB.Recordset");
    var sql="SELECT * FROM employeespulled WHERE empid='1';";

    ConnectObj.Open (myConnect);
    RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockRea dOnly,adCmdText);

    var fieldCount = RS.Fields.Count;
    Response.Write ("Field Count" + fieldCount);
    RS.Close();
    ConnectObj.Close();
}[code]

when I use
Code:
     var ConnectObj = Server.CreateObject("ADODB.Connection");
I get a
Quote:
quote: 'Server' is undefined
error.
Am i doing something wrong or do I have to include any libraries?

Reply With Quote
  #8 (permalink)  
Old July 24th, 2007, 07:04 AM
joefawcett's Avatar
Wrox Author
Points: 9,712, Level: 42
Points: 9,712, Level: 42 Points: 9,712, Level: 42 Points: 9,712, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,066
Thanks: 1
Thanked 36 Times in 35 Posts
Default

If the code is really in a server-side script block then it cannot be an ASP page or, alternatively, ASP is disabled. Show the surrounding context if you're not sure.

--

Joe (Microsoft MVP - XML)
Reply With Quote
  #9 (permalink)  
Old July 24th, 2007, 07:49 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is what I have .... but it is not printing at all ....

Can you help ?


Code:
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<script type="text/javascript" src="Extras/CSJSRequestObject.js"></script>

<%
var sql = "bla bla bla......." ;
var odbc = Server.CreateObject("ADODB.Connection");
var dsn = "DSN=myDSN;UID=Myusername;PWD=Mypass";
var rst = Server.CreateObject("ADODB.Recordset");
odbc.open(dsn);
rst.open(sql,odbc);


if (rst.EOF) { rst.close;
                odbc.close;
               alert("There was no data matching your query")
              }
%>


<html>
<head><title>Daily Script Results</title></head>
<body>
<body bgcolor=lightblue>
Printing the results in the web page.

<table>
   <tr>
<% while (!rst.EOF)
                        {
                            Response.write("<tr>")        
                                    for (var d in Request.QueryString()) {


                                                document.write("<td>" + rst(d) + "</td>")


                                        }
                            Response.write("</tr>")            
                            rst.moveNext();
                            }
                            rst.close(); odbc.close(); %>  



                        <% for (var i in Request.QueryString()) {
                            if (typeof Request.QueryString(i) != "function") 
                                {                                  
                                    if ( Request.QueryString(i)==1) 
                                    {  
                                    document.write("<td>" + i + " </td>")
                                    }
                                }    

                            } %>
the Request.QueryString(i) is included in the other file.
Reply With Quote
  #10 (permalink)  
Old July 24th, 2007, 10:57 AM
joefawcett's Avatar
Wrox Author
Points: 9,712, Level: 42
Points: 9,712, Level: 42 Points: 9,712, Level: 42 Points: 9,712, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,066
Thanks: 1
Thanked 36 Times in 35 Posts
Default

I'm not sure why you get the error message you said but I can see at least two errors where you mix client-side and server-side code. The first is where you try to alert inside a server-side script block when there are no records, the second where you try to use document.write inside a server-side block. Try to simplify the code. Just open a connection to the database and then use Response.Write to write back the connectionString property and the connection.state. Forget all the other stuff and see how far you get.

--

Joe (Microsoft MVP - XML)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
access sql database using javascript vivek_inos Javascript How-To 2 October 12th, 2006 01:29 PM
database connection(query) within javascript funct muthumari_p2003@yahoo.com Pro JSP 0 April 5th, 2006 06:48 AM
Parameterised Access Query in javascript howbadboy Javascript 2 February 26th, 2005 11:39 AM
Access Database from Javascript with rds nosreg Access 2 February 3rd, 2005 06:19 PM



All times are GMT -4. The time now is 12:58 PM.


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