My first ADO (ActiveX Data Objects) example. Running only in IE. Client-side Javascript connects MYOB or QuickBooks Accounting system in your local machine. ---------------------------- example.html -------------------------------- <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd";> <html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en"> <head> <title>MYOB and QuickBooks Example</title> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <script src="ADODB.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="ADODB.css" /> </head> <body> <form id="onlineOrder" action="" method="post"> <fieldset> <legend><b>Please choose your accounting system</b></legend> <input type="radio" name="product" id="myob" value="myob" onclick="javascript:ReadNames ();" /> <label for="myob">MYOB</label> <input type="radio" name="product" id="quicken" value="quicken" onclick="javascript:ReadNames ();" /> <label for="quicken">QuickBooks</label> </fieldset> <table> <tr> <td align="center" colspan="2" valign="middle"> <table> <tr> <td align="center" colspan="3"> <br /> <strong style="text-decoration: underline">Client details from MYOB or QuickBooks</strong><br /> Then select a client from the list to populate the fields<br /> </td> </tr> <tr> <td><b> </b></td> </tr> <tr> <td style="margin-left: 40px"></td> <td align="center" style="border: 3 ridge; background-color: #FF00FF"> <table> <tr> <td align="center"><b>MYOB Client List</b></td> <td align="center"> <select name="allClients" onchange ="javascript:GetPurchaserAddressValues ()"> <option value=""></option> </select> </td> </tr> </table> </td> <td style="margin-left: 40px"></td> </tr> </table> </td> </tr> <tr> <td><br /></td> </tr> <tr> <td align="right">Contact Name</td> <td align="left"><input type="text" name="ContactName" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">Street Address</td> <td align="left"><input type="text" name="Street" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">Suburb</td> <td align="left"><input type="text" name="Suburb" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">State</td> <td> <select name="State"> <option value=""></option> <option value="VIC">VIC</option> <option value="NSW">NSW</option> <option value="SA">SA</option> <option value="WA">WA</option> <option value="QLD">QLD</option> <option value="TAS">TAS</option> <option value="NT">NT</option> <option value="ACT">ACT</option> </select> </td> </tr> <tr> <td align="right">Postcode</td> <td align="left"><input type="text" name="Postcode" size="4" maxlength="4" /></td> </tr> <tr> <td align="right">Phone No.</td> <td align="left"><input type="text" name="PhoneNo" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">Fax No.</td> <td align="left"><input type="text" name="FaxNo" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">Email Address</td> <td align="left"><input type="text" name="emailAdd" size="40" maxlength="50" /></td> </tr> <tr> <td align="right">Website</td> <td align="left"><input type="text" name="WWW" size="40" maxlength="80" /></td> </tr> <tr> <td><b> </b></td> </tr> <tr> <td align="left" colspan="3"> <i> Note for MYOB Forum Members. For this to work you need:<br /> - A DSN called 'DSNforClearwater'<br /> - Your clearwater DB in the location "C:\Premier9\Clearwtr.MYO"<br /> - Your application in the location "C:\Premier9\Myobp.exe"<br /> Alternatively you can hack the source code and alter the ADO connection strings to suite your setup.<br /> </i> </td> </tr> </table> </form> <p> <br /> </p> <p> <a href="http://validator.w3.org/check?uri=referer";><img src="http://www.w3.org/Icons/valid-xhtml10"; alt="Valid XHTML 1.0 Strict" height="31" width="88" border="0" /></a> </p> </body> </html> ---------------------------- example.html -------------------------------- ----------------------------- ADODB.js ----------------------------------- var adOpenForwardOnly = 0; var adLockReadOnly = 1; var adOpenDynamic = 2 var adLockOptimistic = 3 // MYOB DSN string // var myobstring = "DSN=DSNforClearwater"; // var myobstring = "DSN=DSN4Clearwater"; // var myobstring = "DSN=DSN4Clearwater2"; // MYOB DSNless string // var myobstring = "Driver={MYOB_ODBC};DATABASE=C:\\Premier9\\Clearwtr.MYO;TYPE=MYOB;HOST_EXE_PATH=C:\\Premier9\\Myobp.exe;UID=Administrator;ACCESS_TYPE=READ_WRITE;DRIVER_COMPLETION=DRIVER_NOPROMPT;NETWORK_PROTOCOL=TCPIP"; // var myobstring = "Driver={MYOB_ODBC};DATABASE=C:\\Premier9\\Clearwtr.MYO;TYPE=MYOB;HOST_EXE_PATH=C:\\Premier9\\Myobp.exe;UID=Administrator;ACCESS_TYPE=READ_WRITE;KEY=C:\\MYOBODBC\\MYOB.key;DRIVER_COMPLETION=DRIVER_NOPROMPT;NETWORK_PROTOCOL=TCPIP"; // var myobstring = "Driver={MYOB_ODBC};DATABASE=C:\\Premier9\\The_Company_People_Pty_Ltd.MYO;TYPE=MYOB;HOST_EXE_PATH=C:\\Premier9\\Myobp.exe;UID=Administrator;DRIVER_COMPLETION=DRIVER_NOPROMPT;NETWORK_PROTOCOL=TCPIP"; var myobstring = "Driver={MYOB_ODBC};DATABASE=C:\\Premier9\\The_Company_People_Pty_Ltd.MYO;TYPE=MYOB;HOST_EXE_PATH=C:\\Premier9\\Myobp.exe;UID=Administrator;PWD=;ACCESS_TYPE=READ_WRITE;KEY=C:\\Premier9\\617083218280.key;DRIVER_COMPLETION=DRIVER_NOPROMPT;NETWORK_PROTOCOL=TCPIP"; // Quicken DSN string // var quickenstring = "QuickBooks Data" // Quicken DSN string // connection string from Alpha Five // DSN=QuickBooks Data;DFQ=C:\Program Files\Intuit\QuickBooks Premier\Stadium Construction and Hardware Pty Ltd QB Premier.QBW;OpenMode=M;SERVER=QODBC;OptimizerDBFolder=C:\Program Files\QODBC Driver for QuickBooks\Optimizer // var quickenstring = "Driver={QODBC Driver for QuickBooks};DFQ=C:\\Program Files\\Intuit\\QuickBooks Premier\\Stadium Construction and Hardware Pty Ltd QB Premier.QBW;OpenMode=M;OLE DB Services=-2" // var quickenstring = "Driver={QODBC Driver for QuickBooks};OpenMode=M;OLE DB Services=-2" var quickenstring = "Driver={QODBC Driver for QuickBooks};DFQ=C:\\Temp\\test.qbw;OpenMode=M;OLE DB Services=-2" var conn; var connstring; var rs; var selectedID; // return the value of the radio button that is checked // return an empty string if none are checked, or // there are no radio buttons function getRadioCheckedValue (radioObj) { if (!radioObj) return ""; var radioLength = radioObj.length; if (radioLength == undefined) { if (radioObj.checked) return radioObj.value; else return ""; } for (var i = 0; i < radioLength; i++) { if (radioObj[i].checked) { return radioObj[i].value; } } return ""; } function GetPurchaserAddressValues () { var selectedRecord; var selectedClient = document.forms['onlineOrder'].allClients.value; var query1; var query2; conn = new ActiveXObject ("ADODB.Connection"); selectedID = getRadioCheckedValue (document.forms['onlineOrder'].elements['product']); if (selectedID == "myob") { connstring = myobstring; conn.open (connstring); } else if (selectedID == "quicken") { connstring = quickenstring; conn.open (connstring, "", ""); } else { alert ("Bugger! selectedID is null"); } if (conn) { document.forms['onlineOrder'].ContactName.value = ""; document.forms['onlineOrder'].Street.value = ""; document.forms['onlineOrder'].Suburb.value = ""; document.forms['onlineOrder'].State.value = ""; document.forms['onlineOrder'].Postcode.value = ""; document.forms['onlineOrder'].PhoneNo.value = ""; document.forms['onlineOrder'].FaxNo.value = ""; document.forms['onlineOrder'].emailAdd.value = ""; document.forms['onlineOrder'].WWW.value = ""; rs = new ActiveXObject ("ADODB.Recordset"); if (selectedID == "myob") { query1 = "SELECT CardRecordId FROM CARDS WHERE NAME = '" + selectedClient + "'"; rs.Open (query1, conn, adOpenForwardOnly, adLockReadOnly); selectedRecord = rs.fields("CardRecordId").value; rs.close (); rs = null; query2 = "SELECT * FROM ADDRESS WHERE Location = 1 AND CardRecordId = '" + selectedRecord + "'"; rs.Open (query2, conn, adOpenForwardOnly, adLockReadOnly); document.forms['onlineOrder'].ContactName.value = rs.fields("ContactName").value; document.forms['onlineOrder'].Street.value = rs.fields("Street").value; document.forms['onlineOrder'].Suburb.value = rs.fields("City").value; document.forms['onlineOrder'].State.value = rs.fields("State").value; document.forms['onlineOrder'].Postcode.value = rs.fields("Postcode").value; document.forms['onlineOrder'].PhoneNo.value = rs.fields("Phone1").value; document.forms['onlineOrder'].FaxNo.value = rs.fields("Fax").value; document.forms['onlineOrder'].emailAdd.value = rs.fields("Email").value; document.forms['onlineOrder'].WWW.value = rs.fields("WWW").value; rs.close (); rs = null; } else if (selectedID == "quicken") { query2 = "select * from Vendor where Name = '" + selectedClient + "'"; rs.open (query2, conn, adOpenDynamic, adLockOptimistic); document.forms['onlineOrder'].ContactName.value = rs.fields("Salutation").value + " " + rs.fields("FirstName").value + " " + rs.fields("LastName").value; document.forms['onlineOrder'].Street.value = rs.fields("VendorAddressAddr2").value; document.forms['onlineOrder'].Suburb.value = rs.fields("VendorAddressAddr3").value; document.forms['onlineOrder'].State.value = rs.fields("VendorAddressCounty").value; document.forms['onlineOrder'].Postcode.value = rs.fields("VendorAddressPostalCode").value; document.forms['onlineOrder'].PhoneNo.value = rs.fields("Phone").value; document.forms['onlineOrder'].FaxNo.value = rs.fields("Fax").value; document.forms['onlineOrder'].emailAdd.value = rs.fields("Email").value; document.forms['onlineOrder'].WWW.value = rs.fields("NameOnCheck").value; rs.close (); rs = null; } conn.close (); conn = null; return true; } else { conn.close (); conn = null; return false; } } function ReadNames () { selectedID = getRadioCheckedValue (document.forms['onlineOrder'].elements['product']); conn = new ActiveXObject ("ADODB.Connection"); if (selectedID == "myob") connstring = myobstring; else if (selectedID == "quicken") connstring = quickenstring; // alert (connstring); conn.open (connstring); if (conn) { document.forms['onlineOrder'].ContactName.value = ""; document.forms['onlineOrder'].Street.value = ""; document.forms['onlineOrder'].Suburb.value = ""; document.forms['onlineOrder'].State.value = ""; document.forms['onlineOrder'].Postcode.value = ""; document.forms['onlineOrder'].PhoneNo.value = ""; document.forms['onlineOrder'].FaxNo.value = ""; document.forms['onlineOrder'].emailAdd.value = ""; document.forms['onlineOrder'].WWW.value = ""; // completely clear a select box document.forms['onlineOrder'].allClients.options.length = 0; rs = new ActiveXObject ("ADODB.Recordset"); if (selectedID == "myob") rs.Open ("SELECT NAME from CARDS", conn, adOpenForwardOnly, adLockReadOnly); else if (selectedID == "quicken") rs.open ("select Name from Vendor", conn, adOpenDynamic, adLockOptimistic); var counter = 0; var clientoption = ""; while (! rs.EOF) { if (counter == 0) { document.forms['onlineOrder'].allClients[counter++] = new Option ("Please select your client", "", false); } if (selectedID == "myob") clientoption = rs.fields("NAME").value; else if (selectedID == "quicken") clientoption = rs.fields("Name").value; document.forms['onlineOrder'].allClients[counter++] = new Option (clientoption, clientoption, false); rs.MoveNext (); } rs.close (); rs = null; conn.close (); // release conn memory!!! conn = null; return true; } else { conn.close (); conn = null; return false; } } ----------------------------- ADODB.js ----------------------------------- ----------------------------- ADODB.css ---------------------------------- U { text-decoration: underline } ----------------------------- ADODB.css ---------------------------------- -- Terrence Miao Independent IT Consultant ... __o Intelligent Business Solutions & Services Pty. Ltd. ... -\<, URL: http://www.terrencemiao.com/ ... (_)/(_) .. |