Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Web Programming > JavaScript > Javascript
|
Javascript General Javascript discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Javascript section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 24th, 2012, 10:17 AM
Registered User
 
Join Date: Oct 2012
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Question connecting to MS Access mdb via javascript

I have 2 tables in a mdb file. One have project_name (pk) and its details. The other have project_name (fk) with the environments associated with it. Now I am trying to have a page created using the access only, which have a dropdown list with all the project_name. It is created with element taken from the table having project_name as primary key (pk). Now, my idea was to display the relevant environments associated with a project when ever any one of them is selected from the dropdown list. Below is the function written for onchange even of the dropdown list. It is not working. The record count gives -1 even though the table has records in it. I am exhausted looking for the problem. Please someone help me... :-(

By the way this is my first time with javascript and access.
I am basically a PL/SQL guy. So, please forgive me for my silliness...

The code --
function selectDropdown2(){var dropdownValue=document.getElementById("DropdownLis t1").value;
document.getElementById("Label2").innerText = "project --> " + dropdownValue;
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;
var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\temp\\envstat.mdb";
var ConnectObj = new ActiveXObject("ADODB.Connection");
//var ConnectObj = Server.CreateObject("ADODB.Connection"); --for server side
var RS = new ActiveXObject("ADODB.Recordset");
//var RS = Server.CreateObject("ADODB.Recordset"); --for server side
var sql = "SELECT * FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";
ConnectObj.Open(myConnect);
RS.Open(sql, ConnectObj, adOpenForwardOnly, adLockReadOnly, adCmdText);
//RS.GetRows;
var recordCount = RS.RecordCount;
var fieldcount = RS.Fields.Count;
document.getElementById("Label3").innerText = "sql --> " + sql
//alert ("recordCount: " + recordCount);
//var strRet = "the result -- ";
//RS.BOF
//while(!RS.EOF)
//{strRet = strRet + ';' + RS.Fields("env_alias");
//RS.MoveNext()
//}
//var recordCount = 0;

document.getElementById("Label4").innerText = "EOF --> " + RS.EOF
document.getElementById("Label5").innerText = "record count --> " + recordCount
document.getElementById("Label6").innerText = "field count --> " + fieldcount
//RS.MoveFirst;
document.getElementById("Label7").innerText = "BOF --> " + RS.BOF
//for (var i=1; i<6; i++)
//{
//alter ("The number is " + i + "<br>");
//alter ("column -- " + RS.Fields(i).Name);
//}

//for each x in RS.Fields
// document.getElementById("Label8").innerText = "name --> " + x.name;
// document.getElementById("Label9").innerText = "value --> " + x.value;
//next

//while (!RS.EOF)
//recordCount = RS.RecordCount;
//document.getElementById("Label8").innerText = "record --> " + RS.Index(1);
//alter ("inside loop");
// for each x in RS.Fields
// Response.Write(x.project_name)
// Response.Write(" = ")
// Response.Write(x.value)
// Response.Write(" have ")
// Response.Write(x.env_alias)
// Response.Write(" = ")
// Response.Write(x.value & "<br />")
// next
// Response.Write("<br />")
//RS.MoveNext()
//loop

//if RS.Supports(adApproxPosition)=true then
// i=RS.RecordCount
// response.write("The number of records is: " & i)
//end if


document.getElementById("Label10").innerText = "<--- i am here --> "

//if (RS.BOF && RS.EOF)
//{ alert ("no records");//No data
//}

RS.Close();
ConnectObj.Close();
//return strRet;
}
 
Old October 24th, 2012, 12:29 PM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Your code is unlikely to work unless the IE browser security settings are set to 'none-existent'. A web page cannot usually access a local file, especially via ActiveX. You should make sure that you have errors notifications turned on (in settings advanced) so that you can tell if the ActiveX objects are being created.

If you have overcome this hurdle then your problem maybe the way you are retrieving the dropdown value. Try hardcoding a value that you know will work and seeing what happens. The normal way to get a dropdown value is:
Code:
var dropdown = document.getElementById("DropDownList1");
var selectedValue = dropdown.options[dropdown.selectedIndex].value;
RecordCount isn't always populated, especially with a forward only recordset cursor until all records have been read.
__________________
Joe
http://joe.fawcett.name/
The Following User Says Thank You to joefawcett For This Useful Post:
bijunator (October 25th, 2012)
 
Old October 25th, 2012, 10:19 AM
Registered User
 
Join Date: Oct 2012
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Question new problem

well i got around the previous issue...now i am able to get the data. the record count is still -1 but as joefawcett said, i guess its not always popualted or something. now the new issue is i am trying to get data into the spreadsheet activex object inside the access form. but my date fields in the db are displayed as some number in the form. i tried to find something like spreadsheet1.column(2).datatype = Date; or something. but i am still looking why is that there is not much help from MS on these things like you have the oracle documentation..which got anything and everything at one place.

my code is as below --

function selectDropdown2(){
var dropdownValue=document.getElementById("DropdownLis t1").value;
Spreadsheet1.Cells.ClearContents();

??? Spreadsheet1.Columns. ?????

document.getElementById("Label2").innerText = "";
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;
var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\temp\\envstat.mdb";
var ConnectObj = new ActiveXObject("ADODB.Connection");
ConnectObj.Open(myConnect);
var rs = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT * FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";
rs.Open(sql, ConnectObj, adOpenForwardOnly, adLockReadOnly, adCmdText);
var recordCount = rs.RecordCount;
var fieldcount = rs.Fields.Count;
if(!rs.bof) {
rs.MoveFirst();
var x = 0;
while(!rs.eof) {
x = x+1;
//------------------the below works but commented for now--------
//for (var y=0;y < fieldcount;y++){
//Spreadsheet1.Cells(x, y+1).Value = rs.Fields.Item(y);
//}
//------------------------------------------------------------------

//------------------the below date stuff is not working-----
var d1 = new date();
d1 = rs.Fields.Item(2);
document.getElementById("Label2").innerText = "start date --" + d1;
//--------------------------

Spreadsheet1.Cells(x, 1).Value = rs.Fields.Item(0);
Spreadsheet1.Cells(x, 2).Value = rs.Fields.Item(1);
Spreadsheet1.Cells(x, 3).Value = rs.Fields.Item(2);
Spreadsheet1.Cells(x, 4).Value = rs.Fields.Item(3);
Spreadsheet1.Cells(x, 5).Value = rs.Fields.Item(4);

rs.MoveNext();
}
}
else {
document.getElementById("Label2").innerText = "No data found";
};
rs.Close();
ConnectObj.Close();
}
 
Old October 25th, 2012, 11:55 AM
Registered User
 
Join Date: Oct 2012
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Unhappy the crap

the access db returns the date column value as -- Sun Jan 1 00:00:00 UTC 2012

i tried everything on the face of the earth --
//------- start date --Sun Jan 1 00:00:00 UTC 2012
var s1 = rs.Fields.Item(2);
//var part = s1.split(" ");
//var str = "my date --> "+parseInt(part[5],10)+string(part[1])+parseInt(part[2],10);
//var mydate = new Date(parseInt(part[5],10),string(part[1]),parseInt(part[2],10));
//var month = mydate.getMonth();
//var year = mydate.getYear();
//var s2=month;
//var s3=year;
//var s4=getWeek(parseInt(part[2],10),parseInt(part[1],10)-1,parseInt(part[0],10));
//var s5 = s2 + s3 + s4;
//var d1 = Date.parse(s1.replace(/[a-z]/gi,''));
//var s2 = s1.replace(/"00:00:00 UTC "/gi,'');
//var d1 = new date(s1);
document.getElementById("Label2").innerText = "start date -- " + s1; //part[1] + part[2] + part[3];

but nothing seems to work...i am in coder's block now (synonymous to writer's block ;-p) help..........
 
Old October 25th, 2012, 12:16 PM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

This might help: Changing Cell-formating in Excel via VBA

In your case I think you need to set the NumberFormat of the cell to "dd/mm/yyyy" or similar.

You can search online for Excel VBA object API or use the recording trick from the first link above.
__________________
Joe
http://joe.fawcett.name/
The Following User Says Thank You to joefawcett For This Useful Post:
bijunator (October 25th, 2012)
 
Old October 25th, 2012, 01:34 PM
Registered User
 
Join Date: Oct 2012
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Thumbs up thanks

this worked Spreadsheet1.Columns(3).NumberFormat = "dd/mm/yyyy";

also for the other stuff, i infact changed the sql query altogether --
var sql = "SELECT project_name,env_alias,format(alloc_start_dt,'dd/mm/yyyy'),alloc_end_tentative_dt,alloc_end_actual_dt FROM env_proj_alloc_stat WHERE project_name = '" + dropdownValue + "';";

thank you for your comments Joe





Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access .mdb & .ldb database corrupted faisal573 Access 17 December 6th, 2017 04:38 AM
conversion of ms access .mdb to .exi GeoIrshad Access 2 March 11th, 2008 06:21 AM
MS Access - Connecting to dartcoach VB.NET 2002/2003 Basics 2 February 27th, 2007 12:05 PM
MS Access .mdb calling SQL server 2000 stored proc fazzou Access 6 September 8th, 2006 11:32 AM
text like Ms Access mdb sal21 Excel VBA 0 May 6th, 2006 03:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.