Using a database with ajaxed
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:
- Automatically through an AjaxedPage instance or
- 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.
-
<!--#include virtual="/ajaxed/ajaxed.asp"-->
-
<%
-
set p = new AjaxedPage
-
p.DBConnection = true
-
p.draw()
-
-
sub init() : end sub
-
-
sub callback(action)
-
if action = "load" then
-
p.return(db.getRecordset("SELECT column FROM myTable"))
-
end if
-
end sub
-
-
sub main() %>
-
-
<script>
-
function loaded(recs) {
-
for (i = 0; i <recs.length; i++) {
-
$('container').innerHTML += recs[i].column + "<br>";
-
}
-
}
-
</script>
-
-
<form id="frm">
-
<div id="container"></div>
-
<button onclick="ajaxed.callback('load', loaded)" type="button">load</button>
-
</form>
-
-
<% 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:
-
numberProducts = db.getScalar("SELECT COUNT(*) FROM product", 0)
-
numberProducts = db.getScalar("SELECT name, id FROM product", 0)
-
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:
-
<!--#include virtual="/ajaxed/ajaxed.asp"-->
-
<%
-
set myDB = new Database
-
with myDB
-
.open("some connectionstring")
-
'when the alternative is a floating number use the comma.
-
set RS = .getScalar("SELECT totalsales FROM monthlySales", 0.0)
-
.close()
-
end with
-
%>
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:
-
<!--#include virtual="/ajaxed/ajaxed.asp"-->
-
<%
-
set p = new AjaxedPage
-
p.DBConnection = true
-
p.draw()
-
-
sub init() : end sub
-
-
sub main()
-
'selects data from Database A
-
set RS = db.getRecordset("SELECT * FROM tableA")
-
'switches to Database B
-
db.open("connectionstring of Database B")
-
'add the records from Database A to B
-
while not RS.eof
-
db.getRecordset("INSERT INTO tableB (name) VALUES ('" & str.SQLSafe(RS("name")) & "')")
-
RS.movenext()
-
wend
-
end sub
-
%>
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.
(21 votes, average: 4.43 out of 5)
Loading ...
July 18th, 2007 at 3:26 am
what does this do ?
"sub init() : end sub"
July 18th, 2007 at 7:50 am
"...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.
July 18th, 2007 at 9:19 am
exam passed uri ;)
July 19th, 2007 at 5:41 pm
woot... :)
July 19th, 2007 at 7:16 pm
I should clarify, why the "life-cycle" design, instead of having
Subs independently called as desired ?
July 19th, 2007 at 10:09 pm
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
July 21st, 2007 at 5:31 pm
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.
July 25th, 2007 at 11:03 am
@dee: i see ... bu what would be your solution instead of using a predfined flow?
July 31st, 2007 at 7:46 pm
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.
July 31st, 2007 at 7:49 pm
@ron... have you got the latest version and did you copy&paste the example from here? when does the undefined come up?
July 31st, 2007 at 8:03 pm
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
July 31st, 2007 at 8:11 pm
have you changed this line?
$('container').innerHTML += recs[i].column + "";
you need to replace the .column by the column name of your database table..
July 31st, 2007 at 8:19 pm
Hi,
yeah i changed it to my column name but it just returns for example 10 undefineds in my div.
Thanks for your help.
July 31st, 2007 at 8:46 pm
note: the columns are parsed lowercase
can you paste the code here...
July 31st, 2007 at 11:00 pm
The lowercasing worked a treat. Thankyou for all your help. Much appreciated.
August 6th, 2007 at 5:39 pm
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
August 6th, 2007 at 6:10 pm
maybe you have sometime to check why this happens .. and i could fix this. thx
August 6th, 2007 at 9:01 pm
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
August 7th, 2007 at 7:43 am
try this:
body {
overflow-y: scroll;
}
August 7th, 2007 at 10:16 pm
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?
August 7th, 2007 at 11:13 pm
be sure to use lowercase for the field names ...
August 11th, 2007 at 5:48 pm
i used all lowercase but still fails. i tried to post the code but this forum refuses to display postings with code. (?)
August 12th, 2007 at 7:27 pm
just surround the code with
<code></code>August 12th, 2007 at 7:54 pm
I tried wrapping with code tags but it still does not display ...(?)
August 14th, 2007 at 11:51 am
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
August 22nd, 2007 at 9:00 pm
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?
August 22nd, 2007 at 9:33 pm
what do you mean enjama? what is the error message ... are you sure you have a valid form-tag which hast the id "frm"
September 20th, 2007 at 4:36 pm
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!
September 20th, 2007 at 4:50 pm
I can see it,the id of form is frm.Why!
September 22nd, 2007 at 6:25 am
Hi Michal! What is your email? Can you tell me? I have some doubts about your ajaxed library.
September 27th, 2007 at 8:46 am
i got same problem of Ron, Michael didnot came out of solution !!
the returned recordset has the right length, but all [undefined] !!
October 1st, 2007 at 9:40 am
i use getRecordset method, to populate a select box
alert (recs.length);
for (i = 0; i
October 31st, 2007 at 10:22 am
i solved it, the field must be lower case :D
February 4th, 2008 at 7:04 pm
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?
February 5th, 2008 at 5:06 am
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()
February 5th, 2008 at 5:08 pm
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?
February 5th, 2008 at 5:11 pm
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
February 5th, 2008 at 5:20 pm
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
February 5th, 2008 at 5:22 pm
It is indeed, M. Thanks tons!
February 12th, 2008 at 7:58 pm
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.
March 13th, 2008 at 10:46 pm
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.
March 14th, 2008 at 12:57 pm
dimwitty07 try to turn on the ajaxedPage.debug property to get more details.
March 14th, 2008 at 3:03 pm
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.
March 14th, 2008 at 3:16 pm
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
if this works fine, then there must be some problem with your code within the javascript callback. hope this is it
March 14th, 2008 at 3:37 pm
i solved it, i forgot the name of the form and the fields name are lowercase.
Thanks AJAXED is great efforts bodies.
April 14th, 2008 at 4:47 pm
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
April 24th, 2008 at 8:19 am
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
April 24th, 2008 at 9:23 am
@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
April 25th, 2008 at 1:38 pm
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)
}
}
April 25th, 2008 at 2:24 pm
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..
April 29th, 2008 at 3:38 am
Hi Michal
Thanks heaps for that, ended up being
$('container').options[$('container').options.length] = new Option(recs[i].customerid, recs[i].contactname)
Cheers
Allan
June 19th, 2008 at 3:42 am
Is there an ajaxed demo library somewhere? I'd love to see how this works prior to installation...
Peace out,
J
June 19th, 2008 at 4:47 am
Jer sure there is a demo .. check http://www.webdevbros.net/ajaxed/ for more details about ajaxed .. at the top you will find 2 links with demos. enjoy it!
July 15th, 2008 at 8:46 pm
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:
and the vbscript code:
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.
July 16th, 2008 at 1:33 am
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?
If this works then you have a problem with your javascript. However you should try update() to update the elements content:
btw: if you use the DBConnection property of your page then you dont need to open and close the DB manually.
hope that works
July 17th, 2008 at 5:59 pm
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","":"6\u002F17\u002F2007"}]}
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.
July 17th, 2008 at 6:25 pm
Hello All,
This was the problem for me....
SHOULD BE:
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.
July 18th, 2008 at 12:51 am
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","":"6\u002F17\u002F2007"}]}
do you recognize them? is that really what comes back or is it just a typo from your side?
July 18th, 2008 at 12:56 am
justin btw: the escaping with hex is okay .. it escapes the
/char within the data. thats by JSON definitionJuly 18th, 2008 at 8:28 pm
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:
Thanks in advance.
July 19th, 2008 at 4:48 am
justin, to access the param you use page.RF(fieldname). i use the name ID in the example:
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 nameid. Example: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
hope that helps you for your further development.
cheers
July 19th, 2008 at 4:51 am
btw Justin in your case i would also recommend to parse the ID in order to prevent any malicious input:
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 -1hope its clear ;)
July 20th, 2008 at 12:02 am
Thanks!!!!
August 2nd, 2008 at 1:20 pm
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];
August 19th, 2008 at 12:52 pm
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