Online Database Editing Program, Part 2

By Richard Chisholm

In Part 1 we examined how to create an ASP page with two frames, one at 100% and the other at 0%. We also discussed the initial form page. In this part we will discuss the remainder of the application!

- continued -

Recall that once the form is submitted, the logic.asp page runs. First, it checks against a hard coded array of authorized users (I did this because only a few people use this, so it is the most efficient way). (Read Are Session Variables Really Evil? for more information!) I won't go into it here, but if the user is not authorized an alert function tells them such, and the login text is reloaded. Otherwise a Sub named Database is called, which uses the FileSystemObject to find all databases in a directory (hard coded into the script, but usually all databases are in the same place). (To learn more about the FileSystemObject be sure to read the FileSystemObject F.A.Q.!) Here is the ASP/JavaScript used to create the displayed radio-button list of databases:

<% strPath = Server.MapPath("../Databases/") ' Must be hard coded Set objFSO = Server.CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files 'objItem.Name, objItem.Size,objItem.Type If objFile.Type = "MDB File" Then strName = objFile.Name%> text= text + '<input type=radio value='; text= text + '<%=Server.URLEncode(strName)%> name=R1'; text= text + ' onClick="top.logic.get()">'; text= text + '<%=strName%><br>'; <%End if Next%> top.display.text.innerHTML = 'nothing'; top.display.text.innerHTML = text; </script>

In the above, the for/if loops find the database files, and create the HTML in a variable named text. Then we dump the HTML in text into the text span on the display page, and you get a radio-button list of databases. Note two things:

Server.URLEncode

2.) When a radio button is clicked on, a JavaScript function relays the form to the processing page with the top.logic.get() command. For those of you unfamiliar with JavaScript & frames, this command is saying "go to the frames document (the top of the page hierarchy), then go to the logic frame to find the JS function.

I should also go over the get() code, because there are a few things to point out:

function get() { var data = 'nothing'; for (var i=0;i<top.display.login.R1.length;i++){ if (top.display.login.R1[i].checked){ data = top.display.login.R1[i].value; break;}} if (i==0 && data=='nothing'){data = top.display.login.R1.value;} var loc = 'logic.asp?type=table&db=' + data; top.logic.location.href = loc; }

First, the for...loop will run through the radio buttons and grab the value for the checked one. However, this does not work when there is only 1 button, hence the if statement following it. In the last line the code is reloading the logic.asp page and giving it new data, and the following is done in the Table function:

<% Sub Table '************************************************* Dim oConn, objSchema, strData, strPath, strName strData = Request.QueryString("db") strPath = Server.MapPath("../Databases/" & strData) Session("database") = strPath Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strPath Set objSchema = oConn.OpenSchema(adSchemaPrimaryKeys) %>

Here the database is saved into a session variable, including its path. The line with Server.MapPath is one of two places in these files where you will need to hard code something (the other being already mentioned). This is necessary since the location of the databases will vary depending on the server. This part uses the OLEDB 4.0 Jet because the type of schema used here doesn't allow ODBC. (To learn more about OLEDB, be sure to check out OLEDB For Me.) So be sure you have this installed, or you will get a Provider error. (If you don't have this, you can go to MS's web site to download it. The URL is http://www.microsoft.com/ado/.) This particular schema looks up the primary keys on all the tables in the database, which we need identify since Roman's editor requires the primary key column. However, there is one problem: just because a column is a primary key doesn't necessarily mean it will work, so make sure before hand that all your tables have AutoNumber in the primary key. I did find a way to check for AutoNumbers, but it involves using another schema and sometimes generates a runtime error. Email me if you want to know... Here is the JavaScript to write the radio buttons with the table names:

var text; text = 'Please select the table you wish to edit.</p>'; <% Do Until objSchema.EOF strName = objSchema("TABLE_NAME") If Not Instr(1,strName,"MSys") then%> text= text + '<input type=radio value="<%=Server.URLEncode(strName)%>,'; text= text + '<%=Server.URLEncode(objSchema("Column_Name"))%>"'; text= text + 'name=R1 onClick="top.logic.get()">'; text= text + '<%=strName%><br>'; <% End If objSchema.MoveNext Loop%> top.display.text.innerHTML = text;

Again the program builds a list of radio boxes using all the tables in the selected database. This time both the table name and the primary key column are embedded in the value="" field of each box. This will be passed back to logic.asp when the user selects a table. At this point we are almost ready to load Roman's db editor, but first the user encounters one last page. This one parses the two values in the last submission and saves them into the Session, and then generates a drop-down box that gives them three choices of how they want to enter the editor:

This is especially helpful for large databases, and when you need to edit specific records. If the user wants to search, they select that choice, enter a parameter, and hit submit; otherwise they can just select one of the other two choices, hit submit, and they're in. Now that we are finally in Roman's db editor, here are the significant changes.

Added a search box at the top of the page
Commented out the section where Session variables are assigned from the Querystring
Now using DSN-less db connection instead of DSN
Added a "search" option, which runs inside the editList() function

Here is the search coded added to editList()

<% if strAction = "search" then Dim strParam, objSchema strParam = Request.QueryString("parameter") sql = "SELECT * FROM " & strMyTable & " WHERE " Set objSchema = objConn.OpenSchema(adSchemaColumns) Do until objSchema.EOF if objSchema("Table_Name") = strMyTable AND objSchema("Data_Type") = "130" then sql = sql & "[" & objSchema("Column_Name") & "] LIKE '%" & strParam & "%' OR " objSchema.MoveNext else objSchema.MoveNext end if Loop sql = Mid(sql,1,len(sql)-3) Set objRS = objConn.Execute(sql) objSchema.Close set objSchema = nothing else Set objRS = objConn.Execute("SELECT * FROM " & strMyTable) end if %>

Ok, here is what's going on. If the action is to search, we grab the search parameter from the Querystring and start our select statement, and if not then the select statement to list every record is run. This is where it gets a little tricky. We need to search ALL the columns in this table to be sure we have complete results, but we also need to do it on the fly. This is another place where the Schema object comes in handy. One problem with adSchemaCollumns is that although it does work, unfortunately it also pulls out every column in a database, not just a specific table. So we get around this by checking the table name and the data type, so that the SQL statement only searches columns with text in them. The 130 is the constant for text fields in Access (found in the Appendix to ADO 2.0 Programmers Reference). When the Do...Loop is finished, we cut off the last three characters, being " OR" and our SELECT statement is executed. (To learn more about database schemas, be sure to read Listing the Tables and Columns of a Database.)

I have tried to cover all of the non-basic stuff here in this article. The included pages also include a lot of JavaScript, but nothing more advanced than retrieving form values and using the innerHTML property. So, to wrap things up, this can be a very useful tool. I realize that some of the coding can get hairy and down right ugly, but if you plan on having other people use it then the effort is worthwhile because to an average user the transitions are seamless. I haven't explained everything, but what I didn't cover here should be easy to figure out. Hopefully this app will also increase your understanding about schemas and how they can be used, a part of ASP/ADO that doesn't get a lot of coverage. I have left all of my commented-out debugging/Response.Write code, which may also help you delve into things like schemas.

Happy Programming!

Read Part 1

Attachments:

Download the support articles for this article in zip format

Read Roman Koch's Article

Richard Chisholm is the Webmaster of a large California law firm, as well as an independent developer.