Using a database with ajaxed

By | July 16
67 Comments

Since version 0.2 it is possible to use a database with ajaxed out of the box. The library offers the ability to configure a connection string for the use of ODBC within your pages. After that convenient methods enable access to the database. The following paragraphs give a short insight into the usage of a database within ajaxed.
First of all open the config.asp in the root of ajaxed and configure the connection string which is necessary for your database. The constant is called AJAXED_CONNSTRING and needs to hold a valid connection string. Some are already suggested and can be used directly just by uncommenting. Don’t forget to change the password, user and database within the predefined ones (for more connection strings check www.connectionstrings.com/). If your database is running on a different machine than the webserver then you need to change the server adress as well.

After that the database is ready to use and there are two ways of using it:

  1. Automatically through an AjaxedPage instance or
  2. manually by accessing the Database class directly.

Using the database automatically with an instance of the AjaxedPage means that you tell the page that you need a connection to the database (for the time the page is running) and it will take care of it. This is achieved by setting the DBConnection property to true. After this you can access the database from within the init(), callback() and main() procedures. All methods from the Database class are available. The following example demonstrates how to use the configured database. It queries records from the database and displays the data in a div container.

asp
< view plain text >
  1. <!--#include virtual="/ajaxed/ajaxed.asp"-->
  2. <%
  3. set p = new AjaxedPage
  4. p.DBConnection = true
  5. p.draw()
  6.  
  7. sub init() : end sub
  8.  
  9. sub callback(action)
  10.     if action = "load" then
  11.         p.return(db.getRecordset("SELECT column FROM myTable"))
  12.     end if
  13. end sub
  14.  
  15. sub main() %>
  16.  
  17.     <script>
  18.         function loaded(recs) {
  19.             for (i = 0; i < recs.length; i++) {
  20.                 $('container').innerHTML += recs[i].column + "<br />";
  21.             }
  22.         }
  23.     </script>
  24.  
  25.     <form id="frm">
  26.         <div id="container"></div>
  27.         <button onclick="ajaxed.callback('load', loaded)" type="button">load</button>
  28.     </form>
  29.  
  30. <% end sub %>

Clicking the load button will grab all records from myTable and select the column called column. This is achieved with the getRecordset() method which accepts an SQL query and returns an ADODB.Recordset. After the records are fetched we let the page return them within the callback() state of the page. After that the whole recordset is accessible on client side within the JavaScript callback function loaded. The column names have been automatically “transfered” to JavaScript and are therefore accessible.

getRecordset() returns a locked recordset from the database. This means you are not allowed to do any modications to it. If you are in need of an “unlocked” recordset you should use getUnlockedRecordset(). This will open the recordset with a static cursor type and its loacation will be on the client. Now modifications can be done and properties like e.g. RecordCount are accessible also.

Sometimes you need to grab just one value from the database. Normally a count of records or even some calculation result. For this the ajaxed Library offers a method called getScalar() which always returns the value of the first column in the first row. It expects an alternative value which will be returned when the value cannot be identified (e.g. the query returned no records, etc). The following example shows the usage:

asp
< view plain text >
  1. numberProducts = db.getScalar("SELECT COUNT(*) FROM product", 0)
  2. numberProducts = db.getScalar("SELECT name, id FROM product", 0)
  3. numberProducts = db.getScalar("SELECT id, name FROM product", 0)

The variable numberProducts will always be assigned with an integer value. If there are no records available or the value cannot be parsed into the datatype of the alternative then the alternative value (in this case 0) is being passed through. The first line would count the records and result in a recordset with one row and one column which holds a number. Thus this case would pass the value from the recordset. The second line select all products and the first column is obviously a string column. In this case getScalar() will pass through the alternative value 0 because the name cannot be parsed into an integer. Last but not least the third line selects the ID of the products as the first column and therefore - if IDs are numeric values - the ID of the first product will be passed through.

Do you remember that there was another approach to access the database? Yeah the manual one. The manual approach might be useful e.g. if you are not using the AjaxedPage. Why? There might be several reasons. Maybe you are generating a file, etc. For this it is necessary to load the Database class, instantiate it and open the database connection manually. This would look like the following:

asp
< view plain text >
  1. <!--#include virtual="/ajaxed/ajaxed.asp"-->
  2. <%
  3. set myDB = new Database
  4. with myDB
  5.    .open("some connectionstring")
  6.    'when the alternative is a floating number use the comma.
  7.    set RS = .getScalar("SELECT totalsales FROM monthlySales", 0.0)
  8.    .close()
  9. end with
  10. %>

Last but not least I want to show you how to switch databases within one page. This scenario can be useful if you import/export data between two databases. Lets say we want to grab the data from a table of Database A and bung it into a table of Database B. The following example demonstrates this:

asp
< view plain text >
  1. <!--#include virtual="/ajaxed/ajaxed.asp"-->
  2. <%
  3. set p = new AjaxedPage
  4. p.DBConnection = true
  5. p.draw()
  6.  
  7. sub init() : end sub
  8.  
  9. sub main()
  10.    'selects data from Database A
  11.    set RS = db.getRecordset("SELECT * FROM tableA")
  12.    'switches to Database B
  13.    db.open("connectionstring of Database B")
  14.    'add the records from Database A to B
  15.    while not RS.eof
  16.       db.getRecordset("INSERT INTO tableB (name) VALUES ('" & str.SQLSafe(RS("name")) & "')")
  17.       RS.movenext()
  18.    wend
  19. end sub
  20. %>

In order to switch to another database you need just to call the db.open() method with a connection string of your desired database. After that all methods of db are executed against the “new” database. The database connection is closed automatically at the end of page processing. Alternatively you could have created a new instance of Database and have both Databases opened.

67 comments on “Using a database with ajaxed”

  1. what does this do ?

    “sub init() : end sub”

  2. “…init() is executed on every request the main() and callback() are never executed togehter. All the presentation should be put in the main() - you see the form in the example is in the main(). All security checks, preperations, initializations, etc should be put into the init() which is always executed before the main() and callback(action). ”

    you should read the getting started tutorial.

  3. exam passed uri ;)

  4. woot… :)

  5. I should clarify, why the “life-cycle” design, instead of having
    Subs independently called as desired ?

  6. Hi Dee

    “:” is only a special char that allows you to write more than one statement on one line.

    Example:
    Dim s_test : s_test = “Hello World”

    Sub Init() : End Sub is equivalent to:

    Sub Init()
    ‘// Nothing
    End Sub

  7. Peter, thanks, I understand what a colon does.

    My question is apparently still unclear .

    I am wondering why an Ajax library requires a prescribed sequence of ASP functions . Libraries should be independent of ASP code.

  8. @dee: i see … bu what would be your solution instead of using a predfined flow?

  9. I’ve got the adding numbers one working fine but the database one just returns undefined after pressing load. Can you help?

    Thanks in advance.

  10. @ron… have you got the latest version and did you copy&paste the example from here? when does the undefined come up?

  11. Hi,

    Yeah downloaded the latest version, copy the first code example but changed the table & column name, changed my connection string in the config file and it finds the database ok but just returns undefined when you hit load. Its even get the right number of records back.

    Thanks for your quick reply

  12. have you changed this line?


    $('container').innerHTML += recs[i].column + "";

    you need to replace the .column by the column name of your database table..

  13. Hi,

    yeah i changed it to my column name but it just returns for example 10 undefineds in my div.

    Thanks for your help.

  14. note: the columns are parsed lowercase
    can you paste the code here…

  15. The lowercasing worked a treat. Thankyou for all your help. Much appreciated.

  16. getting back to take another look at this .net like framework

    I noticed the GabLibrary Documentor does not display a side scroll bar in Firefox2. Can only be used in MSIE

  17. maybe you have sometime to check why this happens .. and i could fix this. thx

  18. I tried this change and it returned the firefox vertical scrollbar.

    body {
    padding:0; margin:0;
    /* overflow:hidden; */
    }

    There are other problems with the left menu not staying fixed, but, the mix of tables and div tags is ….Ugh

  19. try this:


    body {
    overflow-y: scroll;
    }

  20. running the configured database example with SQL of “select title from employees”, against the northwind database, I only get a repeated listing back of “undefined’

    how can I debug/resolve this type of error?

  21. be sure to use lowercase for the field names …

  22. i used all lowercase but still fails. i tried to post the code but this forum refuses to display postings with code. (?)

  23. just surround the code with < code>< /code>

  24. I tried wrapping with code tags but it still does not display …(?)

  25. Hi,

    I’m having problems with more than one column.

    If I use one column, everything works fine, but if I use 2 only the first column is returned. The other is undefined.

    Using Firefox DOM inspector I can see that only one is returned :

    { “root”: [{"longitude": -117.659517},{"longitude": -86.142218},{"longitude": -81.614023},{"longitude"
    : -80.098117},{"longitude": -122.707064}]
    }

    It should return longitude and latitude.

    “SELECT TOP 5 longitude,latitude FROM vwGoogleMap”

    Any help is appreciated.

    Best Regards,

    Hugo Dias

  26. Hello - I’ve been banging my head against this one. page.RF is showing that the form object doesn’t exist
    sub callback(action)

    if action = “CheckStateZip” then
    if page.RFHas(“searchZip”) Then
    page.return(CheckStateZip(page.RF(“searchZip”),page.RF(“propertyStateCode”)))
    Else
    page.return(“no form posted??”)
    End if
    end if
    Any Ideas?

  27. what do you mean enjama? what is the error message … are you sure you have a valid form-tag which hast the id “frm”

  28. Hi michal! I am interested in your library. I use your library to build the form,and test some data.but can’t insert data into my database.You can see as follow:
    sub callback(action)
    if action = “do” then
    p.return(db.getRecordset(“Insert Into [tt](Title,Content)values(‘”& p.RF(“tle”) & “‘,’” & p.RF(“cent”) & “‘)”))
    end if
    end sub

    Can’t get the value of the form,the infomation of error showed p.RF(“tle”) and p.RF(“cent”) is null
    Help please!Thanks!

  29. I can see it,the id of form is frm.Why!

  30. Hi Michal! What is your email? Can you tell me? I have some doubts about your ajaxed library.

  31. i got same problem of Ron, Michael didnot came out of solution !!
    the returned recordset has the right length, but all [undefined] !!

  32. i use getRecordset method, to populate a select box

    alert (recs.length);
    for (i = 0; i

  33. i solved it, the field must be lower case :D

  34. I need to access a recordset in JavaScript, not ASP. return, however, only works through a callback, and I need to hit the recordset at init time to build a javascript array with the results BEFORE the page loads.

    In other words, I need to be able to use recs[i].columnname during Init.

    Any ideas?

  35. hi gordon … i am not quite sure what you want to achieve. but the only way you can use the recordset within JS is to return it within a callback. therefore if you need it early in your page then just call the ajaxed.callback in the beginning of the main()

  36. Fair enough, Michal, and thanks for the reply.

    This, however, raises another question. :)

    Since callback() is the only place I can use return to stuff a recordset into a javascript array, I appear to be “stuck” using it for any event that’s going to require such an array.

    Problem is, several of said events need to pass more than one argument, yet callback only accepts the one.

    Any ideas for working around this?

  37. FWIW,

    I should have mentioned below…

    I CAN do this by combinding the action and other arguments into a single string…say, “getStuff_SomeID” and then having the first part of the callback function split on the _

    Certainly doable, but not very pretty. :)

    So, I guess my question is, got any “slick” ways of managing this? :)

    Thanks again

  38. gordon you can use the third parameter of the ajaxed.callback … it lets provide you POST parameters for your action as a hash… just call it e.g. like:

    ajaxed.callback('action', doThis, {id: 10})

    or more

    ajaxed.callback('action', doThis, {id: 10, firstname: 'gordon'})

    btw: if you have a form-tag with the id “frm” then all its values are posted automatically if you dont provide the params parameter yourself. hope this is what you needed

  39. It is indeed, M. Thanks tons!

  40. I’ve been trying to execute stored procedures using ajaxed and it doesn’t seem to work. I don’t get any errors from the page it just shows the “loading” then the callback function doesn’t do anything or more preciselly it’s never “reached” (tried and alert(“hi!”) on the first line and it never gets executed).
    I tried executing sql statements and that works without a problem but i need the stored procedure functionality. Any ideas? i checked the database.asp file and i see it’s only doing a connection.execute so i don’t really see what can be the problem.

  41. I copy and pasted your code for database displaying. When I click the button the “loading…” flashes briefly and then nothing happens. I was curious why this might be, I suspected maybe a db connection problem, but unsure of how to verify. The connection string is correct, so if you have any ideas. apparently I am the first with this issue given the comments section.

  42. dimwitty07 try to turn on the ajaxedPage.debug property to get more details.

  43. The debug message that pops up when I press the button is:
    Action (to be handled in callback):
    Load

    Params passed to XHR:
    {“PageAjaxed”: “load”}

    In the big message it shows the appropriate data trying to come through but it will not display on the page. Thanks for quick response earlier.

  44. the first thing which comes to my mind is that you might have some syntax error in your javascript callback function. delete everything there and try to to

    js
    < view plain text >
    1. function yourJSCallback(result) {
    2.   alert(result);
    3. }

    if this works fine, then there must be some problem with your code within the javascript callback. hope this is it

  45. i solved it, i forgot the name of the form and the fields name are lowercase.
    Thanks AJAXED is great efforts bodies.

  46. I get the same problem, nothing is returned. I have run the debug and it says the same thing but then I get an error Invalid Object name tbl_company /library.asp line 97

    Not sure why as the table name is correct and the column name is correct.


    function loaded(recs) {
    for (i = 0; i <recs.length; i++) {
    $('container').innerHTML += recs[i].compname + "";
    }
    }

    load

  47. Hi there,

    Thanks for this great resource.
    Just wondering if you could show us how to populate a select list with this?

    ie modify the code:

    $(‘container’).innerHTML += recs[i].column + “”;

    Many thanks

    Allan

  48. @allan juse create new options for the select .. innerHTML wont work .. i cannot remember the exact syntax at the top of my head but it something like

    $(‘yourselect’).options[$('yourselect').options.length + 1] = new Option(‘value’, ‘text’)

    just browse the net for the exact javascript syntax

  49. I have got my address data copming from a database but if the field is NULL it writes NULL on the page. Can I make the Function not return a value if it is NULL.

    function gotDetails(recs) {
    $(‘details’).update();
    for (i = 0; i < recs.length; i++) {
    $(‘details’).insert(“” + recs[i].compname + “” + recs[i].buildname + “” + recs[i].buildno + “ ” + recs[i].office + “” + recs[i].street + “” + recs[i].town + “” + recs[i].county + “” + recs[i].postcode + “(t): ” + recs[i].tel + “ (f): ” + recs[i].fax)
    }
    }

  50. andyc convert the NULLs directly in your sql-query with the ISNULL function (if you use MSSql):

    SELECT ISNULL(yourColumn, ‘N/A’) as yourColumn FROM yourTable

    this writes N/A instead of nulls..

  51. Hi Michal

    Thanks heaps for that, ended up being

    $(‘container’).options[$('container').options.length] = new Option(recs[i].customerid, recs[i].contactname)

    Cheers
    Allan

  52. Is there an ajaxed demo library somewhere? I’d love to see how this works prior to installation…

    Peace out,

    J

  53. Jer sure there is a demo .. check https://www.webdevbros.net/ajaxed/ for more details about ajaxed .. at the top you will find 2 links with demos. enjoy it!

  54. Hi,

    I am passing a stored procedure name and parameter to the getRecordset function, rather than raw sql code. The debug indicates the record is coming through fine. However the data is not displayed and there is no error message. The stored procedure only returns one row of data.

    here is the javascript code:

    javascript
    < view plain text >
    1. ajaxed.callback('getheaderinfo', 'displayheader', null, null, 'timecard.asp');
    2.  
    3. function displayheader(headerinfo) {
    4. $('comp').appendChild(document.createTextNode(headerinfo[0].comp));
    5.  
    6. $('job').appendChild(document.createTextNode(headerinfo[0].jobname));
    7.  
    8. $('ass').appendChild(document.createTextNode(headerinfo[0].asssup));
    9.  
    10. $('cp').appendChild(document.createTextNode(headerinfo[0].cpsup));
    11. }

    and the vbscript code:

    asp
    < view plain text >
    1. sub callback(action)
    2. db.openDefault()
    3.  
    4. select case action
    5. case "getheaderinfo":
    6. page.return(db.getRecordset("GetTimecardHeaderInfo_2 3169"))
    7. end select
    8.  
    9. db.close()
    10.  
    11. end sub

    I’ve tried doing this many different ways thinking that creating text nodes would not work, thinking that appendChild would not work I tried to simply say $(‘themaindiv’).innerHTML = headerinfo[0].cpsup…

    This really seems quite simple to learn and use. Am I missing something terribly obvious?

    Thanks in advance.

  55. hello justin .. i havent tried the getRecordset for SPs yet.. i dont know if its working or not. does the data really come through? so e.g. this works?

    javascript
    < view plain text >
    1. function displayheader(headerinfo) {
    2.   alert(headerinfo[0].comp);
    3. }

    If this works then you have a problem with your javascript. However you should try update() to update the elements content:

    javascript
    < view plain text >
    1. function displayheader(headerinfo) {
    2.   if (headerinfo.length == 0) return;
    3.   $('comp').update(headerinfo[0].comp);
    4. }

    btw: if you use the DBConnection property of your page then you dont need to open and close the DB manually.
    hope that works

  56. Hey thanks for the response and thoughts! to me it seems as if the displayheader function is not even called so the alert statement you typed above is not even firing. However when I set the page debug to true I see that the last message box, “Response on Callback” contains this:

    {"root":["compname":"Brandrund Furniture Inc", "jobname":"Marketing Coordinator", "jobid":4669, "asssup":"John Smith", "asseml":"john@brandrund.com", "csup": "Samantha Smith", "cpeml":"samantha@testcorp.com","":"6u002F17u002F2007"}]}

    You see the last name:value pair the column name is blank. This may be my issue (for all of you who have tried this same thing) I will look further into it and post my findings. Also in the last name:value pair the date contains some hex(?) code. That may also affect the output.

    So I feel that the stored procedure is indeed returning data to your Code Michael. I hope that my findings will help you to scare up any bugs!

    Thanks for the tip on the DBConnection property I will do that.

    Good job on the code base. it appears to function quite well.

  57. Hello All,

    This was the problem for me….

    javascript
    < view plain text >
    1. ajaxed.callback('getheaderinfo', 'displayheader', null, null, 'timecard.asp');

    SHOULD BE:

    javascript
    < view plain text >
    1. ajaxed.callback('getheaderinfo', displayheader, null, null, 'timecard.asp');

    Notice that the second param of the callback function in the 2nd statement has no single quotes around the func (2nd) parameter?

    Now I can move forward very quickly.

    Thanks again. PS: your code handles stored procedures just fine.

  58. justin you were fast finding the mistake . .i couldnt even get a chance to help :) btw: the returned JSON seems to be incorrect as there are two } in the end…


    {"root":["compname":"Brandrund Furniture Inc", "jobname":"Marketing Coordinator", "jobid":4669, "asssup":"John Smith", "asseml":"john@brandrund.com", "csup": "Samantha Smith", "cpeml":"samantha@testcorp.com","":"6u002F17u002F2007"}]}

    do you recognize them? is that really what comes back or is it just a typo from your side?

  59. justin btw: the escaping with hex is okay .. it escapes the / char within the data. thats by JSON definition

  60. Here’s another question for you!

    the following code is in my callback sub:

    [vbscript]
    page.return(db.getRecordset(“spTT_Timecard_GetTimecardHeaderInfo_2 3198″ ))
    [/vbscript]

    currently the 3198 sp param is hardcoded. I need this parameter to be passed to my callback sub.

    How would I do that?

    once again here is the javascript call to the callback sub:

    code
    < view plain text >
    1. ajaxed.callback('getheaderinfo', displayheader, null, null, 'timecard.asp');

    Thanks in advance.

  61. justin, to access the param you use page.RF(fieldname). i use the name ID in the example:

    asp
    < view plain text >
    1. page.return(db.getRecordset("spTT_Timecard_GetTimecardHeaderInfo_2 " & page.RF("id") ))

    you have 2 choices to pass the id now to the callback.

    1) give your form tag the id frm. Then all form fields will be passed to the callback. Thus you would need a form field with the name id. Example:

    HTML4
    < view plain text >
    1. <form id="frm">
    2.   <input type="text" name="id">
    3.   <button onclick="ajaxed.callback('getheaderinfo', displayheader)">load</button>
    4. </form>

    note: you see i am only using two parameters for the function .. as the others are optional and can be omitted.

    2) pass the values you need manually using the params parameter

    javascript
    < view plain text >
    1. ajaxed.callback('getheaderinfo', displayheader, {id: 10});
    2. //ID taken from a form field named 'id'
    3. //prototypes helper method $F is used
    4. ajaxed.callback('getheaderinfo', displayheader, {id: $F('id')});

    hope that helps you for your further development.
    cheers

  62. btw Justin in your case i would also recommend to parse the ID in order to prevent any malicious input:

    asp
    < view plain text >
    1. page.return(db.getRecordset("spTT_Timecard_GetTimecardHeaderInfo_2 " & str.parse(page.RF("id"), 0) ))

    The str.parse() method ensures that the value will be an integer. If it cannot be parsed then 0 is returned. if you need the value 0 then just use it with e.g -1

    asp
    < view plain text >
    1. page.return(db.getRecordset("spTT_Timecard_GetTimecardHeaderInfo_2 " & str.parse(page.RF("id"), -1) ))

    hope its clear ;)

  63. Thanks!!!!

  64. I need to pass a variable to function loaded(recs), but am having what I think are syntax problems.

    I have a variable number of rows that are all identical except for the row number is at the end of each form.field (eg. document.form.Price2). How can I get the variable passed into the loaded function for use?

    function BarCodeLookup(x,recs) {
    var Condition = document.frm.Inventory_Condition_GUID[x];
    var Type = document.frm.Inventory_Type_GUID[x];
    var Size = document.frm.Inventory_Size_GUID[x];

  65. Hi

    Great stuff, works brilliantly.

    I’m trying to use the scriptaculous InPlaceEditor with the Ajaxed library,
    and I’m not sure how a can call the ajaxed.callback using the InPlaceEditor.
    Does anyone know how to link the 2 together? Any help would be greatly appreciated

    Thanks

  66. Where can I get the working example for displaying data in DataTable ?
    Please help…

  67. hi i’m new to using ajaxe library. i have copied the necessary folders to my webroot. but when i tried the above example i only got to display load but when i click the load button nothing happens… pls help. tnx