ASPSearch! Purple= HTML Code Black= HTML Text Green= Server-side ASP code Red= My Comments <%@ LANGUAGE="VBSCRIPT" %> <html> <head> <title>Da Search Results!</title> </head> <body> | Here is the simple stuff! Line one tells the IIS server to expect some VBScript ahead and the rest of it just builds the HTML file that will be displayed. | <% Dim SqlJunk
Set dbGlobalWeb = Server.CreateObject("ADODB.Connection") dbGlobalWeb.Open("Employees")SqlJunk = "SELECT * FROM Employees" If Request.Form("TypeSearch") = "FirstName" Then SqlJunk = SqlJunk & " WHERE FirstName LIKE '%" &_ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "LastName" Then SqlJunk = SqlJunk & " WHERE LastName LIKE '%" & _ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Title" Then SqlJunk = SqlJunk & " WHERE Title LIKE '%" & _ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Division" Then SqlJunk = SqlJunk & " WHERE Division LIKE '%" & _ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Phone" Then SqlJunk = SqlJunk & " WHERE Phone LIKE '%" & _ Request.Form("DaInBox") & "%'" End If If Request.Form("TypeSearch") = "Email" Then SqlJunk = SqlJunk & " WHERE EMail LIKE '%" &_ Request.Form("DaInBox") & "%'" End If | We start off the ASP code by defining a variable called "SqlJunk" which I will use later on to build a string which I will plug into a SQL statement that defines the Recordset. The next paragraph "Set dbGlobalWeb...." creates an ADODB connection. After I create an object, I have to open the database that I will be using. I tell it to open a connection I have called Employees.
Here is where you have to do a little server administration: Make a DSN through the ODBC icon in the Control Panel called "Employees." If you're saying, "huh?" right now, just follow directions and you'll understand as you go along. The DSN tab is the second tab from the left and you will need to "Add" a new DSN to an Access database. Find the Access MDB file I called "Employees" and point your "Employees" DSN to the "employees.mdb" file I have supplied for you. (By the way, you could have named your DSN "KrustyTheClown" and it would work just fine as long as you change the "dbGlobalWeb.Open("Employees") to dbGlobalWeb.Open("KrustyTheClown")) The next paragraph fills the variable("SqlJunk") with the first part of your SQL statement. The statement says, "SELECT every field in the "Employees" table. (When I reread this I realized that many people might be getting confused here because I named my DSN "Employees", my database "Employees" and the table within the database "Employees." Force yourself to look at the source code closely to figure out which "Employees" I'm talking about.) I could claim that I intentionally did this to force you to learn but for some reason I have a tendency to code subsets of things using the same names. It works for me! The next six "If...Then" statements could have been written as a "Select...Case"- oh well. Initially I only wrote it for two fields and then someone said, "you should make it so they can search all the the fields so I just cut and pasted the rest. Hey, it works, alright?! (The &_ just continues the code on a new line like VB) Anyway, the first line of every If statement Requests the Form field I call "TypeSearch" and asks if it's equal to a value- "FirstName", "LastName","Title","Division", etc. The second line of every "If" statement says, "If the above line was true, Then add the rest of the appropriate SQL string code onto the end of the existing "SqlJunk" string." The second half of my SQL statement that I'm sticking into the "SqlJunk" variable completes the SQL statement that I will use to find the records that match your search criteria. The SQL statement now says, "SELECT/Find every record in the Employees table WHERE the "such&such" field has data that is similar/LIKE the input data the user has put in the box called "DaInBox." You'll notice that the Request.Form("DaInBox") is surrounded by &,%,",'. Ok here's what they're for: The % is used by SQL as wildcards like DOS used the *. The & is used to paste the two separate strings to make one long string in the SqlJunk variable. The ' holds the wildcards and the " holds the statement. Each "If" statement concludes with an "End If." | Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset") rsGlobalWeb.Open SqlJunk, dbGlobalWeb, 3,3 %> | Here is where I create the ADODB.Recordset! It seems like I've done a lot at this point but really all I've done is made a connection to a database and stick pieces of an SQL statement into a variable I call SqlJunk. In the process of defining what my recordset is going to be, I'm also going create and name my recordset variable(rsGlobalWeb) that I will use to extract the field data within my recordset. The second line on this page defines the attributes of your ADODB.Recordset object that I have named rsGlobalWeb. This line says, "Open the rsGlobalWeb recordset with the following SQL statement contained within the SqlJunk variable using the connection object we established earlier called dbGobalWeb." The 3 stuff just opens the database with "optimistic" options- meaning it allows multiple connections and assumes no cruddy people are going to try to hack your data. | <% If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then %> <h2 align="center">We did not find a match!</h2> <%Else%> | What this code says is, "If the recordset(rsGlobalWeb) is at the beginning(BOF) and end(EOF) of the recordset at the same time Then logically there is no recordset and logically there is no match! If there is something Else other than nothing then go to the next line. | | <%If Not rsGlobalWeb.BOF Then%> <h2>Here are the results of your search:</h2> <table BORDER="0" width="100%" cellpadding="3"> <tr> <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Name </font></th> <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Title </font></th> <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Division </font></th> <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Phone </font></th> <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">E-Mail </font></th> </tr> <% Do While Not rsGlobalWeb.EOF %> <tr> <td><%=rsGlobalWeb("FirstName")%>  <%=rsGlobalWeb("LastName")%> </td> <td><%=rsGlobalWeb("Title")%> </td> <td><%=rsGlobalWeb("Division")%> </td> <td><%=rsGlobalWeb("Phone")%> </td> <td><a href="mailto:<%=rsGlobalWeb("Email")%>"><%=rsGlobalWeb("Email")%></a> </td> </tr> <% rsGlobalWeb.MoveNext Loop %></table> <%End If%><%End If%> | The first line here basically says, "If the recordset is not at the BOF or "Beginning Of File" Then execute this HTML code which defines the header information for the query results." We make a pretty table with field headers that corresponds to the fields in the database. <%Do While Not....%>Now we tell it to execute the following code as long as we are not at the end of the recordset(EOF). Now the ASP code starts sticking records underneath the field headers and extracts the field data from the recordset until it reaches the end of the records it has found. I extract the field data within the database using <%=rsGlobalWeb("FieldName")%> The Loop statement just tells ASP to keep going back up to the Do While statement until it is finished. Finally, remember to End your If's. | <% rsGlobalWeb.Close dbGlobalWeb.Close %> </body> </html> | I close the recordset and the connection to the database and I finish the HTML coding for the results page. |
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles More By aspfree developerWorks - FREE Tools! | Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance. FREE! Go There Now!
| | | | CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP. FREE! Go There Now!
| | | | WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies. FREE! Go There Now!
| | | | Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement. FREE! Go There Now!
| | | | Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms. FREE! Go There Now!
| | | | Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services. FREE! Go There Now!
| | | | In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!
| | | | As organizations have grown increasingly dependent on online software, the risk of malicious attacks has also become far more serious. Fortunately, well-governed organizations can protect their Web applications by injecting vulnerability assessments and ethical hacks into their software development and delivery processes. This paper describes 12 of the most common hacker attacks and provides basic rules that you can follow to help create more hack-resistant Web applications. FREE! Go There Now!
| | | | The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size. FREE! Go There Now!
| | | | IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community. FREE! Go There Now!
| | | | All FREE IBM® developerWorks Tools! | |