:: Forum >>

Simple ASP/ADODB.Recordset example

Here is simple example for ASP, ADODB and VBScript. Again, I am not expert in ASP so any suggestions to improve this code would be very much appreciated.

<%@ LANGUAGE = VBScript %>
<%
    Dim oConnection
    Dim oRecordset

    Set oConnection = Server.CreateObject("ADODB.Connection")
    oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb")

    Set oRecordset = oConnection.Execute("SELECT * FROM table")

function aw_string(s)

    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""") 'replace javascript control characters - ", \
rem s = Replace(s, vbCr, "\r")
rem s = Replace(s, vbLf, "\n")

    aw_string = """" & s & """"

end function

function aw_headers(oRecordset)

    Dim i, count, headers()

    count = oRecordset.fields.count
    ReDim headers(count-1)

    For i=0 to count-1
        headers(i) = aw_string(oRecordset(i).name)
    Next

    Response.write("[" & Join(headers, ", ") & "];")

end function

function aw_cells(oRecordset)

    Dim i, col_count, row_count, columns(), rows()

    row_count = 0
    col_count = oRecordset.fields.count
    ReDim columns(col_count-1)

    Do while (Not oRecordset.eof)

        For i=0 to col_count-1
            columns(i) = aw_string(oRecordset(i))
        Next

        ReDim preserve rows(row_count)

        rows(row_count) = vbTab & "[" & Join(columns, ", ") & "]"
        row_count = row_count + 1

        oRecordset.MoveNext
    Loop

    Response.write("[" & vbNewLine & Join(rows, "," & vbNewLine) & vbNewLine & "];" & vbNewLine)

end function

%>
<html>
<head>
    <script src="../../runtime/lib/aw.js"></script>
    <link href="../../runtime/styles/xp/aw.css" rel="stylesheet"></link>
</head>
<body>
<style>

</style>
<script>

// insert javascript arrays produced by ASP functions
    var myHeaders = <%= aw_headers(oRecordset) %>
    var myCells = <%= aw_cells(oRecordset) %>

// create grid control
var obj = new AW.UI.Grid;

// set grid text
obj.setHeaderText(myHeaders);
obj.setCellText(myCells);

// set number of columns/rows
obj.setColumnCount(myHeaders.length);
obj.setRowCount(myCells.length);

// write grid to the page
document.write(obj);

</script>
</body>
</html>
<%
    oRecordset.close
    oConnection.close
%>
Alex (ActiveWidgets)
Monday, March 6, 2006
What is wrong with the following code inside javascript i cant able to call asp function?

<%@ LANGUAGE = VBscript %>
<%

Response.Write "So can write somthing here" & "<br>"
Dim oConnection, oRecordset,oFld,sql
Set oConnection = Server.CreateObject("ADODB.Connection")
set oRecordset=server.CreateObject("ADODB.Recordset")

oConnection.ConnectionString="DSN=DSNklse;UID=sa;"
oConnection.Open

sql = "SELECT top 20 stockCode as Code,Stockname as Name,Openp as Ref, " _
& "highp as High,low,B1_Cum as 'B/Qty',B1_Price as Buy, " _
& "S1_Price as Sell,S1_Cum as 'S/Qty',d_price as Last, " _
& "l_cum as LVol,d_price-Openp as Chg, " _
& "round((d_price-Openp)*100/d_price,2) as 'Chg%', " _
& "d_cum as Volume,st as time " _
& "FROM tbstock t1 WHERE " _
& "d_cum=(SELECT MAX(t2.d_cum) FROM tbstock t2 WHERE " _
& "t2.serial=t1.serial) order by t1.d_cum desc"

oRecordset.Open sql, oConnection

If Not oRecordset.EOF Then
Response.write sql
end if

function aw_string(s)

s = Replace(s, "\", "\\")
s = Replace(s, """", "\""")
'replace javascript control characters - ", \
rem s = Replace(s, vbCr, "\r")
rem s = Replace(s, vbLf, "\n")
aw_string = """" & s & """"

end function

function aw_headers(oRecordset)
Response.Write "Yes i can be in the headers"
Dim i, count, headers()

count = oRecordset.fields.count
ReDim headers(count-1)

For i=0 to count-1
headers(i) = aw_string(oRecordset(i).name)
Next

Response.write("[" & Join(headers, ", ") & "];")

end function

function aw_cells(oRecordset)

Dim i, col_count, row_count, columns(), rows()

row_count = 0
col_count = oRecordset.fields.count
ReDim columns(col_count-1)

Do while (Not oRecordset.eof)

For i=0 to col_count-1
columns(i) = aw_string(oRecordset(i))
Next

ReDim preserve rows(row_count)

rows(row_count) = vbTab & "[" & Join(columns, ", ") & "]"
row_count = row_count + 1

oRecordset.MoveNext
Loop

Response.write("[" & vbNewLine & Join(rows, "," & vbNewLine) & vbNewLine & "];" & vbNewLine)

end function

%>
<html>
<head>
<META HTTP-EQUIV="REFRESH" Content="10;URL=/klse/asp2grids.asp">
<title>ActiveWidgets Grid :: Examples</title>
<style> body, html {font: menu; background: threedface;} </style>

<script src="/klse/ActiveWidgets/runtime/lib/aw.js"></script>
<link href="/klse/ActiveWidgets/runtime/styles/xp/aw.css" rel="stylesheet"></link>
</head>
<style>
</style>
<body>
<script>
//insert javascript arrays produced by ASP functions
var myHeaders = "<%= aw_headers(oRecordset) %>"
var myCells = "<%= aw_cells(oRecordset) %>"

//create grid control
var obj = new AW.UI.Grid;

//set grid text
obj.setHeaderText(myHeaders);
obj.setCellText(myCells);

//set number of columns/rows
obj.setColumnCount(myHeaders.length);
obj.setRowCount(myCells.length);

//write grid to the page
document.write(obj);
</script>
</body>
</html>
<%
oRecordset.Close
oConnection.close
%>
stephen
Wednesday, May 31, 2006
I have test it and found one bug and place if syntax should solve the enpty row problem...

function aw_string(s)

if s<>"" then
s = Replace(s, "\", "\\")
s = Replace(s, """", "\""") 'replace javascript control characters - ", \
rem s = Replace(s, vbCr, "\r")
rem s = Replace(s, vbLf, "\n")

aw_string = """" & s & """"
else
aw_string=""
end if

end function
John
Tuesday, October 17, 2006
Well well well...
Downloaded evaluation and it worked straight away using asp code above getting data from access database.

Now all I have to do is find out how I can save it back to the database :-)

Mike
Wednesday, February 14, 2007
Mike...or anyone else...

Have you been able to figure out how to post the updates made on the clientside to the database? I see examples of how to populate the grid but no where is an example of how to update the database after a user makes changes in the grid.

An example of updating the database would be AWESOME!

Thanks.
Todd
Wednesday, April 25, 2007
Todd.

No, I can not find any way of posting the changes back into the database.

I really can not see the point of doing this in JavaScript in any case. I can create a datagrid to display records just using asp.


Mike
Tuesday, May 22, 2007
<%@ LANGUAGE = VBScript %>
<%
Dim oConnection
Dim oRecordset

Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("beeda_db.mdb")

Set oRecordset = oConnection.Execute("SELECT pd_id as pdfno, pd_title as ptitle FROM tbl_pdf_files")

function aw_string(s)

if s<>"" then
s = Replace(s, "\", "\\")
s = Replace(s, """", "\""") 'replace javascript control characters - ", \
rem s = Replace(s, vbCr, "\r")
rem s = Replace(s, vbLf, "\n")

aw_string = """" & s & """"
else
aw_string=""
end if

end function


function aw_headers(oRecordset)

Dim i, count, headers()

count = oRecordset.fields.count
ReDim headers(count-1)

For i=0 to count-1
headers(i) = aw_string(oRecordset(i).name)
Next

Response.write("[" & Join(headers, ", ") & "];")

end function

function aw_cells(oRecordset)

Dim i, col_count, row_count, columns(), rows()

row_count = 0
col_count = oRecordset.fields.count
ReDim columns(col_count-1)

Do while (Not oRecordset.eof)

For i=0 to col_count-1
columns(i) = aw_string(oRecordset(i))
Next

ReDim preserve rows(row_count)

rows(row_count) = vbTab & "[" & Join(columns, ", ") & "]"
row_count = row_count + 1

oRecordset.MoveNext
Loop

Response.write("[" & vbNewLine & Join(rows, "," & vbNewLine) & vbNewLine & "];" & vbNewLine)

end function

%>
<html>
<head>
<script src="../../runtime/lib/aw.js"></script>
<link href="../../runtime/styles/xp/aw.css" rel="stylesheet"></link>
</head>
<body>
<style>

</style>
<script>

// insert javascript arrays produced by ASP functions
var myHeaders = <%= aw_headers(oRecordset) %>
var myCells = <%= aw_cells(oRecordset) %>

// create grid control
var obj = new AW.UI.Grid;

// set grid text
obj.setHeaderText(myHeaders);
obj.setCellText(myCells);

// set number of columns/rows
obj.setColumnCount(myHeaders.length);
obj.setRowCount(myCells.length);

// write grid to the page
document.write(obj);

</script>
</body>
</html>
<%
oRecordset.close
oConnection.close
%>


i m using the above code it doesn't showing any record or any error. Also table has two records.Please help me to solve this problem
NM Mughal
Wednesday, June 20, 2007
Check that you have aw.js and aw.css properly.Check path
R.P.Mahajan
Wednesday, June 20, 2007
Thanks for your kind reply. I have checked both files and they exit on the same path with the same name.

Regarding Grid I take much of my time to eliminate this problem and on one example is shows the result as follows:

File Code ::
__________________________________________________________

<%@ LANGUAGE = VBScript %>
<%
Dim oConnection
Dim oRecordset

' connect to the database
Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("beeda_db.mdb")

' retrieve the grid data
Set oRecordset = oConnection.Execute("SELECT pd_id as PDFCODE, pd_title as PDFTITLE FROM tbl_pdf_files")

' encodes control characters for javascript
function aw_string(s)


if s<>"" then
s = Replace(s, "\", "\\")
s = Replace(s, """", "\""") 'replace javascript control characters - ", \
rem s = Replace(s, vbCr, "\r")
rem s = Replace(s, vbLf, "\n")

aw_string = """" & s & """"
else
aw_string=""
end if


end function

' returns the field names from the recordset formatted as javascript array
function aw_headers(oRecordset)

Dim i, count, headers()

count = oRecordset.fields.count
ReDim headers(count-1)

For i=0 to count-1
headers(i) = aw_string(oRecordset(i).name)
Next

Response.write("[" & Join(headers, ", ") & "];")

end function

' returns the recordset data formatted as javascript array
function aw_cells(oRecordset)

Dim i, col_count, row_count, columns(), rows()

row_count = 0
col_count = oRecordset.fields.count
ReDim columns(col_count-1)

Do while (Not oRecordset.eof)

For i=0 to col_count-1
columns(i) = aw_string(oRecordset(i))
Next

ReDim preserve rows(row_count)

rows(row_count) = vbTab & "[" & Join(columns, ", ") & "]"
row_count = row_count + 1

oRecordset.MoveNext
Loop

Response.write("[" & vbNewLine & Join(rows, "," & vbNewLine) & vbNewLine & "];" & vbNewLine)

end function

%>
<html>
<head>
<title>ActiveWidgets Examples</title>
<style>body {font: 12px Tahoma}</style>

<!-- include links to the script and stylesheet files -->
<script src="../../runtime/lib/aw.js"></script>
<link href="../../runtime/styles/xp/aw.css" rel="stylesheet"></link>

<!-- change default styles, set control size and position -->
<style>
#myGrid {height: 150px}
</style>
</head>
<body>

<h3>ASP classic - VBScript, ADO</h3>
<p>Make VBScript functions returning the data formatted as javascript arrays,<br />
Insert the results inside Javascript data block</p>
<pre>
var myHeaders =<%= aw_headers(oRecordset) %>
var myCells = <%= aw_cells(oRecordset) %>
</pre>

<!-- insert control tag -->
<span id="myGrid"></span>

<!-- add data block -->
<script language="javascript">


// insert javascript arrays produced by ASP functions
// var myHeaders = <%= aw_headers(oRecordset) %>
// var myCells = <%= aw_cells(oRecordset) %>





// create grid control
var obj = new AW.UI.Grid;

// set grid text
obj.setHeaderText(myHeaders);
obj.setCellText(myCells);

// set number of columns/rows
obj.setColumnCount(myHeaders.length);
obj.setRowCount(myCells.length);

// write grid to the page
//obj.refresh();
document.write(obj);



</script>
</body>
</html>
<%
oRecordset.close
oConnection.close
%>

___________________________________________________________

The codes shows me the result with all database table records as follows:

ASP classic - VBScript, ADO
Make VBScript functions returning the data formatted as javascript arrays,
Insert the results inside Javascript data block


Page Output in Browser :::

________________________________________________________

ASP classic - VBScript, ADO
Make VBScript functions returning the data formatted as javascript arrays,
Insert the results inside Javascript data block


var myHeaders =["PDFCODE", "PDFTITLE"];
var myCells = [
["1", "Fast Food Menus"],
["2", "Ice Cream Menus"],
["3", "Latest Recipies Menu"]
];


_________________________________________________________

but I haven't yet got any output in Grid View. Kindly help me to solve out this issue.



NM Mughal
Thursday, June 21, 2007

This topic is archived.


Back to support forum

Forum search