Miscellaneous code collected from elsewhere that I want to keep handy

Delete that works to delete multiple files

if request.form("df")="yes" then
if NOT request.form("dfile") ="" then
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile(Server.MapPath("../../../../_private/files/" & "thumbs/" & request.form("dTHfile")))


Set objFSO2 = Server.CreateObject("Scripting.FileSystemObject")
objFSO2.DeleteFile(Server.MapPath("../../../../_private/files/" & request.form("dfile")))
end if
end if

Table Names in Select Box to enter into a form

In this example, we have a form for uploading a new icon image with the image file to go into a folder and the image filename to go into the Table "ICONS".  We want to designate a specific table for each Icon to be associated with. This is how to display and select from the table names.

<%
'here is where you set the DSN for the application
Session("DSN") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.ServerVariables("APPL_PHYSICAL_PATH") & "..\database\yourdatabase.mdb" & ";"
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN")
Set rstSchema = DBConn.OpenSchema(20)
%>
<SELECT name="x_ICONTABLE" id="x_ICONTABLE" size=2 style= "HEIGHT: 100px; WIDTH:200px">
<%
Do Until rstSchema.EOF
'here you specify a table, should be a query this way they can defined by us first and presented to the user.
If rstSchema("TABLE_TYPE") = "TABLE" Or rstSchema("TABLE_TYPE") = "VIEW" Then
'TABLE for database tables and VIEWS for queries or views
response.write "<OPTION VALUE= """ & rstSchema("TABLE_NAME") &""">" & _
rstSchema("TABLE_TYPE") & " : " & rstSchema("TABLE_NAME") &"</option>" & vbCRLF
'nest query in here to get the fields.
End If
rstSchema.MoveNext
Loop
Set rstSchema = Nothing
DBConn.Close
Set DBConn = Nothing
%>
</SELECT>

The following is nice for when you want to use a single asp file for giving different queries. In other words, making a single page file behave like four or more separate pages!
In the example below, the first strsql differentiates according to whether the page request is for showing broodmares or for showing stallions.

The second strsql statement filters for the horses that are for sale.

The third one returns a list of all the horses regardless of gender or sale status.

Conditional SQL Statements:

if NOT request.querystring("gen")="" then
strsql = "select * from [horsedata] WHERE ACCTID ="& strAcct &" AND horsedata.gender ="""& request.querystring("gen")&""""
end if
if NOT request.querystring("sale")="" then
strsql = "select * from [horsedata] WHERE ACCTID ="& strAcct &" AND [horsedata.for-sale]="&"""Yes"""
end if
if request.querystring("sale")="" then
if request.querystring("gen")="" then
strsql = "select * from [horsedata] WHERE ACCTID ="& strAcct &""
end if
end if

Get Primary Key Autonumbered Identity of Newly Inserted Record, Access Database

This isn't perfect, because all it actually does is return the most recently created autonumber. You can't be guaranteed that this is actually the record you want. However, odds are extremely exceptionally excellent that it is!

<%

xDb_Conn_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("../../database/yourdatabase.mdb") & ";"
' Open Connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

Set oRs = conn.Execute("SELECT MAX(ACCOUNTID) FROM ACCOUNTS", , adCmdText) ' ACCOUNTID relates to your primary key field. Use your primary key field name, whether it is ID or MYID or YOURPRIMARYKEY, whatever.

Your_ID = oRs(0).Value

response.write Your_ID

oRs.Close
Set oRs = Nothing
conn.Close
Set conn = Nothing
%>

VERY simple database display from this code: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=7088

<%

set Conn=Server.CreateObject("ADODB.Connection") 'create Server Database Connection Object
set RS = server.CreateObject("ADODB.Recordset") 'create RecordSet Conn Obj
Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("../../../database/yourdb.mdb") & ";"


sql= ("SELECT [USERID], [USEREMAIL] FROM USERS WHERE [USERID]='"& Session("USER_status_UserID")&"'")


RS.Open SQL, Conn, 3, 3



if RS.EOF then 'traps for IF DB empty THEN:
Response.Write "there is no such data"
Response.End
end if


Response.Write RS("USEREMAIL")
%>
 

Filter records by Year of Date Column

If you have a date column, it may have the month, day, time, year. Perhaps you want to filter the records by the YEAR of the date only.

strsql = "SELECT DISTINCT ([HORSES].[HORSEBREED]), (SELECT COUNT (*) FROM HORSES WHERE HORSEGENDER='Mare') AS TOTAL FROM HORSES" WHERE HORSEGENDER='"& strSex &"'" & " AND (year([HORSEBDAY]))="& request.querystring("yr") &""

Show Records Only by Date Expired

"SELECT [USERS].[USERID], [USERS].[USEREMAIL], [PAYMENTS].[PAYMTID], [PAYMENTS].[PAYMENT_DATE],[PAYMENTS].[EXPIREWEB], [PAYMENTS].[ITEM_NAME], [PAYMENTS].[ITEM_NUMBER], [PAYMENTS].[QUANTITY], [PAYMENTS].[PAYER_EMAIL], [PAYMENTS].[CREDIT] FROM USERS INNER JOIN PAYMENTS ON [USERS].[USEREMAIL] =[PAYMENTS].[PAYER_EMAIL] AND ((DateDiff('d', [EXPIREWEB], Date()))<1)"

SQL Statements that I worked very hard to write

It nests a select count query inside another query. This is what the results look like:

This is how you can get Access to display how many exhibitors were in the same class as a particular exhibitor. The idea is, I've referred to four tables here: EVENTS, PLACEMENTS, EXHIBITORS and RIDER_HORSE_TEAMS. The EVENTS table is the bookmark, you might say, to which the number-in-a-class are counted.

SELECT EVENTS.CLASS, PLACEMENTS.TEAM_ID, (SELECT Count([PLACEMENTS].[TEAM_ID]) FROM PLACEMENTS WHERE [EVENTS].[EVENT_ID]= [PLACEMENTS].[EVENT_ID]) AS HOW_MANY, EXHIBITORS.FIRST_NAME, EXHIBITORS.LAST_NAME
FROM EXHIBITORS INNER JOIN (RIDER_HORSE_TEAMS INNER JOIN (EVENTS INNER JOIN PLACEMENTS ON EVENTS.EVENT_ID = PLACEMENTS.EVENT_ID) ON RIDER_HORSE_TEAMS.TEAM_ID = PLACEMENTS.TEAM_ID) ON EXHIBITORS.EXHIBITOR_ID = RIDER_HORSE_TEAMS.EXHIBITOR_ID
WHERE (((PLACEMENTS.TEAM_ID)=707) OR (PLACEMENTS.TEAM_ID)=708);

And another...

As developed in ACCESS:

SELECT DISTINCT TOP 6 SHOWPLACEMENTS04.HORSEENTRYNUMBER, SHOWPLACEMENTS04.CLASSIDNUMBER, (Avg((SHOWPLACEMENTS04.JUDGEPLACE))) AS OVERALL_PLACEMENT
FROM SHOWPLACEMENTS04
WHERE (((SHOWPLACEMENTS04.CLASSIDNUMBER)=14) AND ((SHOWPLACEMENTS04.JUDGEPLACE)<>0))
GROUP BY SHOWPLACEMENTS04.HORSEENTRYNUMBER, SHOWPLACEMENTS04.CLASSIDNUMBER
HAVING (((SHOWPLACEMENTS04.HORSEENTRYNUMBER)=[HORSEENTRYNUMBER]))
ORDER BY (Avg((SHOWPLACEMENTS04.JUDGEPLACE)));

and it went into my script like this:

sSql="SELECT DISTINCT TOP 6 SHOWPLACEMENTS.HORSEENTRYNUMBER, SHOWPLACEMENTS.CLASSIDNUMBER, AVG(SHOWPLACEMENTS.JUDGEPLACE) AS OVERALL_PLACEMENT FROM SHOWPLACEMENTS"


' Load Default Filter
sDefaultFilter = "[CLASSIDNUMBER]=" & request.querystring("classid")&" AND JUDGEPLACE<>0"
sGroupBy = "SHOWPLACEMENTS.HORSEENTRYNUMBER,SHOWPLACEMENTS.CLASSIDNUMBER"
sHaving = "(((SHOWPLACEMENTS.HORSEENTRYNUMBER)=[HORSEENTRYNUMBER]))"

' Load Default Order
sDefaultOrderBy = "AVG(SHOWPLACEMENTS.JUDGEPLACE) ASC"