Creating Datagrid Control In Asp

Printed from http://www.developerfusion.co.uk/show/2328/

Rated Viewed 56663 times

Creating a Datagrid Class in classic ASP - Introduction Author

musician

Actions Save as Favourite Share with a Friend PDF Version Report Problem Contents 1. Introduction 2. 2-Dimensional Arrays 3. Creating the class 4. Analysis of the Class 5. More Analysis of the Class 6. Using the Class in an asp page

Introduction Well we've all heard of the wonderful Datagrid in ASP.Net and it's pretty handy to say the least. Then again some of you like me may still be stuck with poor old ASP for the moment. But it's not all bad. With vbscript classes in ASP you can acomplish many things. So I got the urge to try and create my own version of the datagrid. To create a DataGrid class my first problem was efficiency. When learning how to get records from a database and display them in a table on your asp page you'll quite often do it like this: <% Do Until objRec.EOF

%> <% objRec.movenext loop %>

<%=objRec("name") %>

<%=objRec("address") %>

<%=objRec("email") %>

This is quite an innefficient way of doing things. A more efficient way of doing things is to use the RecordCount property of the Recordset, for x = 0 to objRec.RecordCount.

2-Dimensional Arrays Even more efficient is to use the GetRows method of the Recordset to populate a 2 dimensional array. Then you no longer have to use up resources accessing the Recordset. dim arTable arTable = objRec.GetRows objRec.Close set objRec = nothing

You now have a 2 dimensional array but how is the recordset information stored in this array? arTable's 2 dimensions are like this: arTable(TotalCols, TotalRows). So if my Recordset returned 10 records of the above fields it would be arTable(3, 10) and you would need to go from arTable(0, 0) to arTable(2, 9) to retreive each value and you must go through the TotalCols elements first, followed by the TotalRows element. You don't know how many records you will return so you need to use the UBound function to evaluate them:dim tCols, tRows tCols = UBound(arTable, 1) tRows = UBound(arTable, 2)

With an array of more than one dimension you need to supply the UBound function with a value to indicate which element you wish to total. As above we set tCols to the total of the first dimension and tRows to the total of the second

dimension. With this information you can now loop through the records using nested For Next loops: <% Dim x, y For x = 0 to tRows Response.Write "" For y = 0 to tCols Response.Write "" Next Response.Write "" Next %>

Not as simple as directly referring to field names in the recordset object but faster and the code is hardly more complicated. So now wehave a good model which we can move/encapsulate in a handy vbscript class.

Creating the class I'm assuming you have heard of classes in vbscript and know a little about OOP. Lets have a look at the class, discuss it and then outline some possible improvements:<% Class caDataGrid 'private variables private pAutoColumns, pConnStr, pSqlStr, intColCnt Private pOutPut, pConn, pRec, x, y, pArray 'this runs when you create a reference to the caDataGrid class Private Sub Class_Initialize() Set pConn = server.createobject("adodb.connection") Set pRec = server.createobject("adodb.recordset") intColCnt = 0 pAutoColumns = True End Sub 'Properties - all writable Public Property Let ConnectionString(strConn) pConnStr = strConn End Property Public Property Let AutoColumns(bAutoCols) If bAutoCols = True or bAutoCols = False then pAutoColumns = bAutoCols End IF End Property Public Property Let SqlString(strSql) pSqlStr = strSql End Property 'Methods for our class Public Sub AddColumn(strColName) If intColCnt = 0 then pOutPut = "

" & rTable(y, x) & "

" & vbcrlf pOutPut = pOutPut & "" & vbcrlf End If pOutPut = pOutPut & "" & vbcrlf intColCnt = intColCnt + 1 End Sub Public Sub Bind pConn.Open pConnStr Set pRec = pConn.Execute(pSqlStr) If pAutoColumns = True then

'assign column names from returned recordset pOutPut = "

<strong>" & strColName & "

" & vbcrlf pOutPut = pOutPut & "" & vbcrlf Redim pColNames(pRec.Fields.Count) For x = 0 to pRec.Fields.Count - 1 pOutPut = pOutPut & "" & vbcrlf Next End If pOutPut = pOutPut & "" & vbcrlf pArray = pRec.GetRows For x = 0 to UBound(pArray, 2) pOutPut = pOutPut & "" & vbcrlf For y = 0 to UBound(pArray, 1) pOutPut = pOutPut & "" & vbcrlf Next pOutPut = pOutPut & "" & vbcrlf Next pOutPut = pOutPut & "

" & pRec.Fields(x).Name & "

" & pArray(y, x) & "

" & vbcrlf Response.Write pOutPut End Sub 'this runs when we destroy our reference to caDataGrid Private Sub Class_Terminate() pOutPut = "" pRec.Close Set pRec = nothing pconn.close Set pConn = nothing End Sub End Class %>

Analysis of the Class O.k. first of you declare the class, private variables and code for when the class is first instantiated. As I said you must understand what is involved in making a class or you might be a bit confused:Class caDataGrid 'private variables private pAutoColumns, pConnStr, pSqlStr, intColCnt Private pOutPut, pConn, pRec, x, y, pArray 'this runs when you create a reference to the caDataGrid class Private Sub Class_Initialize() Set pConn = server.createobject("adodb.connection") Set pRec = server.createobject("adodb.recordset") intColCnt = 0 pAutoColumns = True End Sub

When the Class initializes we set our connection object and recordset object as adodb objects. We also set the Column Count variable to 0 for use when we want to define our own columns and finally we default to AutoColumns=true. It's in this procedure you would put any defaults you want for your class. Next we create the procedures that allow us to set the various proerties:'Properties - all writable Public Property Let ConnectionString(strConn) pConnStr = strConn End Property Public Property Let AutoColumns(bAutoCols) If bAutoCols = True or bAutoCols = False then

pAutoColumns = bAutoCols End IF End Property Public Property Let SqlString(strSql) pSqlStr = strSql End Property

I haven't included code for reading these values, 'Public Property Get', as we don't really need to do this. I'm skimping a bit on error checking but the procedure to set the AutoColumns boolean checks to make sure only true or false is passed to it, for example. Otherwise in this case it remains true from the initializing code. It's a good idea to include more complete error code for these procedures to ensure proper values are set and especially if you wanted to make your class widely available.

More Analysis of the Class Now onto the meat and veg, i.e. the Methods, procedures, for your class. These add the actual functionality to the class:Public Sub AddColumn(strColName) If intColCnt = 0 then pOutPut = "" & vbcrlf pOutPut = pOutPut & "" & vbcrlf End If pOutPut = pOutPut & "" & vbcrlf intColCnt = intColCnt + 1 End Sub

If we choose to specify our own column names then we call this method to add a column name. It just adds a new cell to our grid(table) for each column and this is where the column count comes in. We only want to start the table and the row if it's at 0. Every other time we want to just add the cell code. Once thats done, or not if you use AutoColumns, it's on to the Binding of the grid:Public Sub Bind pConn.Open pConnStr Set pRec = pConn.Execute(pSqlStr) If pAutoColumns = True then 'assign column names from returned recordset pOutPut = "

<strong>" & strColName & "

" & vbcrlf pOutPut = pOutPut & "" & vbcrlf Redim pColNames(pRec.Fields.Count) For x = 0 to pRec.Fields.Count - 1 pOutPut = pOutPut & "" & vbcrlf Next End If pOutPut = pOutPut & "" & vbcrlf pArray = pRec.GetRows For x = 0 to UBound(pArray, 2) pOutPut = pOutPut & "" & vbcrlf For y = 0 to UBound(pArray, 1) pOutPut = pOutPut & "" & vbcrlf Next pOutPut = pOutPut & "" & vbcrlf Next pOutPut = pOutPut & "

" & pRec.Fields(x).Name & "

" & pArray(y, x) & "

" & vbcrlf Response.Write pOutPut End Sub

Basically we open our recordset, if AutoColums=true then we get the fields names and create cells for them or we go with the custom column names, then we use the code already discussed to cycle through our array of values. All of this is being concatenated to an output string which is finally written to the browser at the end. Now lets use the class in an asp page.

Using the Class in an asp page

Paste the class code into notepad and save it as caDataGrid.asp in your wwwroot folder. I use the nwind.mdb database as an example here which is found with plenty of MS products, in my case in my Visual Basic folder. I copied the database to the wwwroot folder for simplicity. Now create an asp page in your wwwroot folder with the following:<%@ LANGUAGE="VBSCRIPT" %> <% option explicit %> <% response.buffer=true %> <% dim cDataGrid, mapPath Set cDataGrid = New caDataGrid mapPath = "nwind.mdb" 'Access 2000 connection cDataGrid.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath(mapPath) cDataGrid.SqlString = "select firstname, lastname, title from employees" cDataGrid.Bind set cDataGrid = nothing 'now lets set our own columns response.write "

" Set cDataGrid = New caDataGrid cDataGrid.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath(mapPath) cDataGrid.SqlString = "select firstname, lastname, title from employees" cDataGrid.AutoColumns = false cDataGrid.AddColumn("First Name") cDataGrid.AddColumn("Last Name") cDataGrid.AddColumn("Job Title") cDataGrid.Bind set cDataGrid = nothing %> You 'reference' the class using the include capability of asp. Of course theres nothing to say you can't just paste your class code into the same page but it wouldn't be nice and neat would it? Then you declare a variable and set it as a caDataGrid object/class, assign the relevent values and bind. All of 5/6 lines of code. Very handy. If you have been paying attention you might have realised how much more you could do with this. First on the list should be better error code. For example if I set cDataGrid.AutoColumns = false and then forget to add column names then I'd have get bad output. On the more interesting side of things I could add alot of similar stuff to this class as you find with the .net datagrid - paging, background colours or even class names from a stylesheet all with some more properties and methods. More work obviously but once it's done it'll be there to use many many times. Until tomorrow when you install the .net framework and forget all about it. Anyway hope this is of use to you all. Happy Coding.

Related Content Beginning Active Server Pages A Preview of Active Server Pages+ PDF Generated by ABCpdf 5.0