Classic ASP and a Form

Forms are a common way to collect data on websites. A form can be made up of a number of controls; it is the input controls are used to collect user input. Once a user fills out a form there something is usually done with the data. I often save the information to a database. Instead of scripting out and insert statement you can easily pass ‘loop’ through each of the field values and build a statement. This would assume the field names in the database table match those of the input field names on the form.
<%
STR_SQL= "INSERT INTO data ("
STR_VALUES = "VALUES ('"
For each inputField in Request.Form
STR_SQL = STR_SQL & inputField & ", "
For each inputValue in Request.Form(inputField)
STR_VALUES = STR_VALUES & inputValue & "', '"
Next
Next

STR_SQL = STR_SQL & "REMOTE_ADDR, Date_Time) "
STR_VALUES = STR_VALUES & Request.ServerVariables("REMOTE_ADDR") & "', '" & Now() & "')"

STR_SQL= STR_SQL & STR_VALUES

‘DataConn is an existind data connection and post_RS is a recordset
Set post_RS = DataConn.Execute(STR_SQL)
%>

You could also list the information on the POST page:
<%
For each inputField in Request.Form
For each inputValue in Request.Form(inputField)
Response.write(inputField & “: “ & inputValue)
Next
Next

%>

A tale of ASP, Excel and a RecordSet

I’ve worked on a number of classic’ ASP (VBScript) sites that needed to dynamically create an Excel spreadsheet containing information contained in a Recordset. As with anything, software development included, there are a number of ways to do the same thing. One approach I have taken is to pass a Recordset to a CreateExcelWorkbook function. Simplifying things a bit, while also displaying some other things you can do with the worksheet:
<% function CreateExcelWorkbook(rs)
'### Relative path for saving Excel file

EXCEL_PATH = "c:\Inetpub\wwwroot\excel\"

'### Field type constants
adEmpty = 0
adSmallInt = 2
adInteger = 3
adSingle = 4
adDouble = 5
adCurrency = 6
adDate = 7
adBSTR = 8
adError = 10
adBoolean = 11
adDecimal = 14
adTinyInt = 16
adUnsignedTinyInt = 17
adUnsignedSmallInt = 18
adUnsignedInt = 19
adBigInt = 20
adUnsignedBigInt = 21
adFileTime = 64
adGUID = 72
adBinary = 128
adChar = 129
adWChar = 130
adNumeric = 131
adUserDefined = 132
adDBDate = 133
adDBTime = 134
adDBTimeStamp = 135
adChapter = 136
adPropVariant = 138
adVarNumeric = 139
adVarChar = 200
adLongVarChar = 201
adVarWChar = 202

'### Generate filename based upon time stamp
sMonth = cStr(Month(Now()))
sYear = cStr(Year(Now()))
sDay = cStr(Day(Now()))
sHour = cStr(Hour(Now()))
sMin = cStr(Minute(Now()))
sSec = cStr(Second(Now()))
filename = sYear + sMonth + sDay + sHour+ sMin+ sSec + ".xls"

'### Create Excel object
Set objExcel = server.CreateObject("Excel.Application")
objExcel.Application.Visible = false
objExcel.Application.SheetsInNewWorkbook = 1
objExcel.Application.DisplayAlerts = false
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Sheets(1)

iCol = 1
'### Create a header row of the field names
for i= 0 to rs.Fields.Count - 1
objSheet.Cells(1,cInt(iCol)).Value= rs.Fields.Item(i).Name
objSheet.Cells(1,cInt(iCol)).Font.Bold = -1
iCol= iCol + 1
next

iCol = 1
while not rs.EOF
for i = 0 to rs.Fields.Count - 1
objSheet.Cells(iRow,i+1).Value = rs(i)
'objSheet.Cells(iRow,i+1).Value = rs("fieldname")
'### Format cell based upon field type
Select Case rs.Fields.Item(i).Type
Case adCurrency,adDouble
objSheet.Cells(iRow,i+1).Select
objSheet.Cells(iRow,i+1).Style = "Currency"
Case adDBDate,adDBTime,adDBTimeStamp
objSheet.Cells(iRow,i+1).NumberFormat = "m/d/yy h:mm;@"
End Select
next

iRow = iRow + 1
rs.movenext
wend

'### Some column sizing
objSheet.Columns("A:E").EntireColumn.AutoFit
objSheet.Columns("F:F").ColumnWidth = 5.00
objSheet.Columns("G:G").ColumnWidth = 5.00

objSheet.Cells(2,1).Select
'### Freeze header row
objExcel.ActiveWindow.FreezePanes = -1

objWorkbook.SaveAs EXCEL_PATH + filename, FileFormat=xlNormal
Set objSheet = Nothing
objWorkbook.Close False
Set objWorkbook = Nothing
objExcel.quit
Set objExcel = Nothing

'### Return the filename
CreateExcelWorkbook = filename
end function
%>

You can use this function in an ASP page:
<% on error resume next
STR_SQL = "SELECT * FROM tblData”
Set DataConn= Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30

‘###CONN_STRING is the connection string to your data
DataConn.open CONN_STRING

Set Data_RS = DataConn.Execute(STR_SQL)
Data_RS.MoveFirst
if not Data_RS.EOF then
filename = CreateExcelWorkbook(Data_RS)
end if

Data_RS.close
set Data_RS = nothing

DataConn.Close
set DataConn = nothing

‘### open the Excel spreadsheet
Response.redirect("excel/" + filename)
%>


I also have another 3 part post that deals with working with Excel documents.

Reading Stuff

Information in this document subject to change without notice.
All Software source code published is for demonstration and knowledge sharing purposes only. The Code is supplied "as is" without warranty as to result, performance or merchantability. Use at your own risk.
The opinions expressed herein are the opinions of the author and do not reflect those of any other entity.