ADO in server-side JScript

I don't know how many of you out there are having the same types of problems that I am. Frankly, I'm a Java/JavaScript programmer and web developer, who, when I learned about ASP technology, thought that this was the greatest thing since sliced bread. The only problem is that ALL of the on-line documentation, and most of the print documentation, is written in VBScript. Now don't get me wrong, VBScript is a fine language, but it just looks wrong without the neat little semi-colons at the end of each line. So with that in mind, I am embarking on a rather ambitious project in concert with 4guys. Basically, (no pun intended) I want to provide you all with translations of common functions so that those of us who prefer to use JavaScript on their ASP pages can.

First up is the ADODB.Connection object. This is instantiated very simply, and is probably the easiest conversion that I'm going to be doing.

<% @language = javascript%>
<%
var connection = Server.CreateObject("ADODB.Connection");
var DSNstr = "myDSN";
connection.Open(DSNstr);
%>

[other code here]

<%
//Now let's close the connection so that
//we aren't eating too many system resources.
connection.Close();
%>

Okay, so that's all there is to it. Really, the only difference here is that you can declare and initialize the variables at the same time. Next, let's pull out some information from the database that we just opened.

<%
var record = Server.CreateObject("ADODB.Recordset");
var SQLstr = "SELECT recordOne, recordTwo FROM tableOne";
/*
for notes on writing SQL queries see the wonderful article at
http://www.geocities.com/ResearchTriangle/Node/9672/sqltut.html
*/
record.Open(SQLstr, connection);
%>

[other code here]

<%
//just like we did with the connection, we have to
//close and clean out the recordset.
record.Close();
record = null;
%>

Now that we have the recordset open, let's put it into a combobox. This actually proved to be the most difficult task that I have tried to accomplish, but the solution is so elegantly simple that I was hitting myself over the head and saying "duh" when I figured it out. So, without further ado...

<select name=records>
<%
while (!record.EOF){  %>
  <option value="<%=record("recordOne")%>">
     <%=record("recordTwo")%>
  </option>
<%record.MoveNext();
} %>
</select>

Alright, now let's do the reverse process: putting data into a database. There are numerous articles on 4guys already about validating data entered into a form, so I'm going to assume that the data has already been validated and is ready to be put into the database.

<%
var connection = Server.CreateObject("ADODB.Connection"); 
var DSNstr = "myDSN"
connection.Open(DSNstr);

//yep, you gotta have a database open
//to put data into it.

SQLstr = "INSERT INTO tableOne ([record one], " +
         "recordTwo) VALUES (' " + recordOne + " ', ' "
         + recordTwo + " ')";

/*Notes on the SQL string: If the record name in the
database table has any spaces it must be contained in the
square brackets, else it doesn't need to be in the brackets.
The actual values that are entered are using a lot of
concatenation, which was a little confusing when I first
looked at it, but after playing around with SQL for a little
bit, it makes more sense.  Oh yes, around any values that
are going to be entered into text fields there must be single
quotes ' ', values entered into numeric fields don't require
them.*/

connection.Execute(SQLstr);
%>

One last thing that I am going to include for all you JavaScript programmers out there. I recently had to create a couple of forms for a major corporate client, which consisted of timesheets. Now handling that much numeric information, 150 elements worth on one form, 215 on the other is quite a challenge on a normal "get" or even a "post" message. (In a case like this, though, using the Post Method is preferred, because it can pass a larger amount of data than the get method.) Putting this information into an array would seem to be the simplest way to handle the quantity that we're dealing with. The only trouble is finding a way to place all of the information into the array, so here it is:

<%
var responseArray;
responseArray = new Array(Request.Form.Count);

var keyNum = 1;
var keyName;

while (keyNum < Request.Form.Count + 1){
   keyName = Request.Form.Key(keyNum);
   responseArray[keyNum - 1] = Request.Form(keyName);
   keyNum++;
}
%>

Now that you have all of the data in an array, which incidentally has the same subscript numbers as the elements on the page they came from, you can play with the data any way that you please. I believe a brief discussion of the Request object is in order, though, to help understand what just happened. When you submit a form using the Post or the Get method, the page passes a long string of data to the server which is accessible to the URL referenced by the action tag of the form. By using the Post method, the string is included in the HTTP header and can be accessed with the Request.Form object. Using the Get method includes the string as part of the URL and can be accessed by using the Request.QueryString object. Normally, you should use the Get Method when retrieving data only. Use the Post Method when you are going to be permanently changing something (i.e. a database).

I think that about does it for now. More of these VBScript to JavaScript conversion pages will be written as time and demand allow.

P.S. Sometimes I feel like there aren't very many of us out here that are actually writing in JavaScript for the server-side scripting in ASP. If you are one of the few, and you have any specific questions, please try to contact me on the WebMessage Board that 4guys has provided. I try to check it regularly.

Happy Programming!!!!! -- Paradox

  .