>>>>>>>>>asp101.com/samples

------------------SEARCH----------------------------------
<%
' Declare our variables... always good practice!
Dim strURL     ' The URL of this page so the form will work
               ' no matter what this file is named.

Dim cnnSearch  ' ADO connection
Dim rstSearch  ' ADO recordset
Dim strDBPath  ' path to our Access database (*.mdb) file

Dim strSQL     ' The SQL Query we build on the fly
Dim strSearch  ' The text being looked for

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

' Retreive the term being searched for.  I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")

' Since I'm doing this all in one page I need to see if anyone
' has searched for something.  If they have we hit the DB.
' O/W I just show the search form and quit.

%>
<p>Search our sample db by first or last name.  (% returns all)</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try 'am' or 'er' for an example]</p>
<%
If strSearch <> "" Then
	' MapPath of virtual database file path to a physical path.
	' If you want you could hard code a physical path here.
	strDBPath = Server.MapPath("database.mdb")


	' Create an ADO Connection to connect to the sample database.
	' We're using OLE DB but you could just as easily use ODBC or a DSN.
	Set cnnSearch = Server.CreateObject("ADODB.Connection")

	' This line is for the Access sample database:
	'cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

	' We're actually using SQL Server so we use this line instead:
	cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
		& "Initial Catalog=samples;User Id=samples;Password=password;" _
		& "Connect Timeout=15;Network Library=dbmssocn;"

	' Build our query based on the input.
	strSQL = "SELECT last_name, first_name, sales " _
		& "FROM [sample] " _
		& "WHERE last_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
		& "OR first_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
		& "ORDER BY last_name;"

	' Execute our query using the connection object.  It automatically
	' creates and returns a recordset which we store in our variable.
	Set rstSearch = cnnSearch.Execute(strSQL)

	' Display a table of the data in the recordset.  We loop through the
	' recordset displaying the fields from the table and using MoveNext
	' to increment to the next record.  We stop when we reach EOF.
	' For fun I'm combining some fields and showwing you can do more then
	' just spit out the data in the form it is in in the table.
	%>
	<table border="1">
	<tr>
	<th>Name</th>
	<th>Sales</th>
	</tr>
	<%
	Do While Not rstSearch.EOF
		%>
		<tr>
		<td><%= rstSearch.Fields("first_name").Value %> <%= rstSearch.Fields("last_name").Value %></td>
		<td><%= rstSearch.Fields("sales").Value %></td>
		</tr>
		<%

		rstSearch.MoveNext
	Loop
	%>
	</table>
	<%
	' Close our recordset and connection and dispose of the objects
	rstSearch.Close
	Set rstSearch = Nothing
	cnnSearch.Close
	Set cnnSearch = Nothing
End If

' That's all folks!  See it's really not all that hard.
%>



---------------------------------DELETE----------------------------------------------

<%' Defining some constants to make my life easier! (Same as Sample 1)
' Begin Constant Definition
	
	' DB Configuration constants
	' Fake const so we can use the MapPath to make it relative.
	' After this, strictly used as if it were a Const.
	Dim DB_CONNECTIONSTRING

	' ODBC
	'DB_CONNECTIONSTRING = "DRIVER={Microsoft Access Driver (*.mdb)};" _
	'	& "DBQ=" & Server.Mappath("./db_scratch.mdb") & ";"

	' OLE DB
	DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" _
		& "Data Source=" & Server.Mappath("db_scratch.mdb") & ";"

	' We don't use these, but we could if we neeeded to.
	'Const DB_USERNAME = "username"
	'Const DB_PASSWORD = "password"

	'Now we override the above settings to use our SQL server.
	'Delete the following line to use the sample Access DB.
	DB_CONNECTIONSTRING = "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
		& "Initial Catalog=samples;User Id=samples;Password=password;" _
		& "Connect Timeout=15;Network Library=dbmssocn;"

	' ADODB Constants
	' You can find these in the adovbs.inc file
	' Do a search for it and it should turn up somewhere on the server
	' If you can't find it you can download our copy from here:
	'     http://www.asp101.com/samples/download/adovbs.inc
	' It may not be the most recent copy so use it at your own risk.
' End Constant Definition
%>
	<!-- #INCLUDE FILE="adovbs.inc" -->
<%
Dim I               ' Standard looping var
Dim iRecordToDelete ' Id of deleted record
Dim strSQL          ' String variable for building our query

'We're going to keep this as simple as we can.
'  1. Create a Recordset object
'  2. Connect the Recordset to the table
'  3. Find the record to delete
'  4. Delete It!
'  5. Update the table
'  6. Close the Recordset

'Step 1:
	Dim objRecordset
	Set objRecordset = Server.CreateObject("ADODB.Recordset")

'Step 2:
	' Get the Id of the record to delete 
	iRecordToDelete = Request.QueryString("id")
	iRecordToDelete = Replace(iRecordToDelete, "'", "''")
	
	' If the record ID passed in isn't a number, we set it to
	' one so we don't cause SQL query errors.  I use 0 since I
	' know there's no record in the DB with an id of 0
	If IsNumeric(iRecordToDelete) Then
		iRecordToDelete = CLng(iRecordToDelete)
	Else
		iRecordToDelete = 0
	End If
	
	' I'm prebuilding our SQL query so it's easier to print
	' out in case we need to debug later.  I'm using a query
	' that will return just the record we want to delete.
	strSQL = "SELECT * FROM scratch WHERE id=" & iRecordToDelete & ";"

	' The syntax for the open command is
	' recordset.Open Source, ActiveConnection, CursorType, LockType, Options 
	objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText

'Step 3:		
	' Not really much to do here!
	' We're looking to delete the only record from the current recordset.
	' The first one is the one to delete.

	' Note: If the data was a little more important (or of any value at all
	' to us!), we'd probably check some other criteria or at least check to see
	' if it's the oldest record in the recordset.  Since it's not and we really
	' don't care, here goes!

'Step 4:
	'Make sure we actually have a record to delete.
	If Not objRecordset.EOF Then
		objRecordset.MoveFirst
		objRecordset.Delete adAffectCurrent

		' You can also delete groups of records which satisfy the filter
		' property setting if you're doing this in batch mode.  For this
		' situation we're just killing the one record so we don't bother.
	End If

'Step 5:
	' We don't need to do the update unless we batch it like mentioned above!
	'objRecordset.UpdateBatch
	
	' Show a message saying we deleted a record
	If iRecordToDelete <> 0 Then
		Response.Write "Record id " & iRecordToDelete & " deleted!"
	End If

'Step 6:
	' Finally we close the recordset and release the memory used by the
	' object variable by setting it to Nothing (a VBScript keyword)
	objRecordset.Close
	Set objRecordset = Nothing



'********************************
' This is the end of the sample!
'********************************

'Show Table
	' Feel free to skip this area. (Ignore the man behind the curtain!)
	' I'm just showing the Table so you have something to look at when
	' you view the sample.
	Dim objCleanUpRS
	Dim iRecordCount

	strSQL = "SELECT * FROM scratch ORDER BY id;"

	Set objCleanUpRS = Server.CreateObject("ADODB.Recordset")
	objCleanUpRS.Open strSQL, DB_CONNECTIONSTRING, adOpenStatic, adLockReadOnly, adCmdText

	Response.Write "<table border=""1"" cellspacing=""2"" cellpadding=""2"">" & vbCrLf
	Response.Write vbTab & "<tr>" & vbCrLf
	Response.Write vbTab & vbTab & "<th>id</th>" & vbCrLf
	Response.Write vbTab & vbTab & "<th>text_field</th>" & vbCrLf
	Response.Write vbTab & vbTab & "<th>integer_field</th>" & vbCrLf
	Response.Write vbTab & vbTab & "<th>date_time_field</th>" & vbCrLf
	Response.Write vbTab & "</tr>" & vbCrLf

	If Not objCleanUpRS.EOF Then
		objCleanUpRS.MoveFirst
		'Show data
		Do While Not objCleanUpRS.EOF
			Response.Write vbTab & "<tr>" & vbCrLf
			For I = 0 To objCleanUpRS.Fields.Count - 1
				Response.Write vbTab & vbTab & "<td><a href=""db_del.asp?id=" & objCleanUpRS.Fields("id").Value & """>" & objCleanUpRS.Fields(I) & "</a></td>" & vbCrLf
			Next
			Response.Write vbTab & "</tr>" & vbCrLf
			objCleanUpRS.MoveNext
		Loop
	End If
	Response.Write "</table>" & vbCrLf

	' Get recordcount so we know if we need to clean up.
	iRecordCount = objCleanUpRS.RecordCount

	objCleanUpRS.Close
	Set objCleanUpRS = Nothing


' Now this is REALLY behind the curtain!

' Normally I'd cut you off right here and do the rest behind the scenes; however,
' since this has to do with the DB you were just writing to, I'll give you a
' treat and let you see some of our administative / housekeeping code!

' Now we clean up!
' Basically, to keep things manageable, I'm checking the DB to keep it over
' 2 records.
If iRecordCount <= 2 Then
	Set objCleanUpRS = Server.CreateObject("ADODB.Recordset")

	objCleanUpRS.Open strSQL, DB_CONNECTIONSTRING, adOpenStatic, adLockPessimistic, adCmdText

	For I = 1 to 2
		objCleanUpRS.AddNew
		objCleanUpRS.Fields("text_field") = CStr(WeekdayName(WeekDay(Date())))
		objCleanUpRS.Fields("integer_field")   = CInt(Day(Now()))
		objCleanUpRS.Fields("date_time_field") = Now()
	Next
	objCleanUpRS.Update

	objCleanUpRS.Close
	Set objCleanUpRS = Nothing
End If
%>