ASP Code Examples - SQL

ASP

Active Server Pages (ASP) is Microsoft's first server-side script engine for dynamically-generated web pages. It is marketed as an add-on to Internet Information Services (IIS). Programming ASP websites is made easier by various built-in objects. http://en.wikipedia.org/wiki/Active_Server_Pages

asp > Code Examples > SQL

> All_Form.ASP

All_Form.ASP <% Option Explicit %> <% Response.Expires=0 %> <HTML> <HEAD></HEAD> <BODY BGColor=White Text=Black> <STYLE> .btn {Width:100%} </STYLE> <% Dim Page ' Local var For page # Dim cn ' Connection object Dim rs ' Recordset object Dim Action ' Button pressed Dim PageSize ' How far To page Dim UpdSQL, MySQL ' String To hold SQL Dim i ' Loop counter Dim item, value ' Used To retrieve changed fields Dim issueUpdate ' After Save button press, any changes to make? Action = Request.Form("NavAction") If Request.Form("Page") <> "" Then Page = Request.Form("Page") Else Page = 1 End If If Request.Form("PageSize") <> "" Then PageSize = Request.Form("PageSize") Else PageSize = 5 End If Set cn = Server.CreateObject("ADODB.Connection") cn.Open Application("guestDSN") ' Get initial recordset Set rs = Server.CreateObject("ADODB.Recordset") MySQL = "SELECT * FROM AUTHORS" rs.PageSize = PageSize rs.Open MySQL, cn, adOpenKeyset, adLockOptimistic Select Case Action Case "Begin" Page = 1 Case "Back" If (Page > 1) Then Page = Page - 1 Else Page = 1 End If rs.AbsolutePage = Page Case "Forward" If (CInt(Page) < rs.PageCount) Then Page = Page + 1 Else Page = rs.PageCount End If rs.AbsolutePage = Page Case "End" rs.AbsolutePage = rs.PageCount Case "Save" ' Grab the proper record, Then update ' This routine is hard coded For AU_ID as the key field. ' To alter this to work With another DB Table you will need to ' Use the proper primary key instead of AU_ID. rs.Close MySQL = "SELECT * FROM AUTHORS WHERE au_id = '" & Request.Form("Au_id") & "'" rs.MaxRecords = 1 rs.Open MySQL, cn, adOpenStatic, adLockOptimistic UpdSQL = "UPDATE AUTHORS " issueUpdate = False For i = 0 To (rs.Fields.Count - 1) item = rs.Fields(i).Name value = Request.Form(item) ' Only update items that have changed If (rs(i) <> value) Then If issueUpdate = False Then UpdSQL = UpdSQL & "SET " Else UpdSQL = UpdSQL & "," End If issueUpdate = True Select Case VarType(rs.Fields(i)) ' Determine datatype For proper SQL UPDATE syntax ' NOTE: Not all data types covered Case vbString, vbDate UpdSQL = UpdSQL & item & "='" & value & "'" Case vbNull Case vbInteger UpdSQL = UpdSQL & item & "=" & value Case vbBoolean If value Then UpdSQL = UpdSQL & item & "= 1" Else UpdSQL = UpdSQL & item & "= 0" End If End Select End If Next UpdSQL = UpdSQL & " WHERE au_id = '" & Request.Form("Au_id") & "'" If issueUpdate Then cn.Execute UpdSQL Set rs = cn.Execute(MySQL) End If Case "New" ' response.write "New" rs.AddNew Case "Bookmark" Session("myBookMark") = rs.BookMark Case "Goto" If Not IsNull(Session("myBookMark")) Then rs.BookMark = Session("myBookMark") End If Case Else rs.MoveFirst End Select %> <CENTER>   <TABLE Align=Center border=1 BGColor=Navy BorderColorDark=Navy BorderColorLight=Aqua BorderColor=Blue>  <TH Colspan=2> <FONT Color=White Size=+2><CENTER>Navigating Example</CENTER></FONT> </TH>  <TR><TD>   <FORM Action=all_form.asp Method="POST"> <TABLE Align=Left BORDER=0 BGColor=Gray Text=White> <% For i = 0 To rs.Fields.Count - 1 %> <TR><TD><B><%= rs.Fields(i).Name %></B></TD> <TD><Input Type=Text Name="<%= rs.Fields(i).Name %>" Value="<%= rs(i) %>" /></TD> </TR> <% Next %> </TABLE> </form></TD> <TD BGColor=Black Width=100>   <Input Type="Hidden" Name="PageSize" Value="1" /> <Input Type="Hidden" Name="Page" Value="<%= Page %>" />  <Input Type="Submit" Name="NavAction" Value="Begin" Class=Btn /><BR /> <Input Type="Submit" Name="NavAction" Value="Back" Class=Btn /><BR /> <Input Type="Submit" Name="NavAction" Value="Forward" Class=Btn /><BR /> <Input Type="Submit" Name="NavAction" Value="End" Class=Btn /><P> <Input Type="Submit" Name="NavAction" Value="Save" Class=Btn /><BR /> <Input Type="Submit" Name="NavAction" Value="New" Class=Btn /></p><P> <Input Type="Submit" Name="NavAction" Value="Bookmark" Class=Btn /><BR /> <Input Type="Submit" Name="NavAction" Value="Goto" Class=Btn /></p><P> </p></TD> </TR> </TABLE> </CENTER><P><CENTER>  <IFRAME width=70% height=180 src="list.asp?auid=<%= rs( </Include/code.asp?source=/ado/samples/list.asp?auid=<%= rs(>"au_id") %>" FrameBorder=1 Scrolling=No> <FRAME width=70% height=180 src="list.asp?auid=<%= rs( </Include/code.asp?source=/ado/samples/list.asp?auid=<%= rs(>"au_id") %>" /> </IFRAME> </center> </BODY> </HTML></p>

Main Menu

main menu

ASP

asp > Code Examples > SQL