I need help with connecting to a database

naasinc · Sep 3, 2004

I need help connecting to my database because when I go to select from my drop down list and then hit submit, I get a blank page. I have includes my code for both my connOpen.asp and also my code for the search page. If someone can please help me find what I am missing that would be so great. Thanks in Advance.
P.S. I only want to read the database not write to it!
CODE FOR connOpen.asp
<%
strConnect="Provider=Microsoft.Jet.OLEDB.4.0; DataSource=C:\Mydatabases\phone.mdb;"
%>
CODE FOR SEARCH PAGE:
<%
OPTION EXPLICIT
DIM strConnect
%>

<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DBQ=C:\Mydatabases\phone.mdb;DRIVER={MS Access (*.mdb)}"
objConn.Open
%>
<%
DIM objRecordSet
Set objRecordSet=Server.CreateObject("ADODB.Recordset")
objRecordset.open"Employees",strConnect
%>
<%
DIM loc_id
loc_id = Request.QueryString("location")
%>
<html>
<body bgcolor="gold">
</body>
<h1 align="center"> Phone List Directory </h1>
<p align="center"> Please input the person's first name, last name or location that you are searching for.</p>
<p align="center"> You can search for someone with just the first name, last name or get a list of everyone in a certain location.</p>

<body>
<%
DIM SQL, RS
DIM first_name, last_name

IF loc_id <> "" THEN
SQL = "SELECT first_name, last_name FROM Employee_table WHERE location = '" & loc_id & "'"
SET RS = MyConnectionObject.Execute(SQL)

IF NOT RS.EOF THEN
first_name = RS("first_name")
last_name = RS("last_name")
END IF

RS.Close
SET RS = Nothing
END IF
%>

<html>
<body>
<form action="connOpen.asp" method="get">
First Name: <input type="text" name="first_name"><br>
Last Name:<input type="text" name="last_name"><br>
<form name="location_form" method="get">
<select name="Location">
<option value="" selected>Select Location</option>
<option value="MAE">MAE</option>
<option value="LCQ">LCQ</option>
<option value="GSO">GSO</option>
<option value="DOTHAN">DOTHAN</option>
<option value="FLTSTR">FLTSTR</option>
<option value="SOCAL">SOCAL</option>
<option value="SAA">SAA</option>
<option value="KC-10">KC-10</option>
<option value="KC-135">KC-135</option>
<option value="ROAD">ROAD</option>
<option value="ENGLAND">ENGLAND</option>
<option value="OFFICE">OFFICE</option>
<input type="submit" value="SEARCH">
</form>
</form>
</body>
</html>

Veep · Sep 3, 2004

Play around with this. It should get you closer to where you want to be.

Code:

<%
OPTION EXPLICIT
DIM strConnect,objConn,flag
	strConnect="Provider=Microsoft.Jet.OLEDB.4.0; DataSource=C:\Mydatabases\phone.mdb;" _
	& "User Id=admin;Password="	
	DIM loc_id,SearchMethod,first_name,last_name
	If request.Form("location") <> "" then
		loc_id = Request.form("location")
		SearchMethod = "Loc"
	else 
		first_name = request.Form("first_name")
		last_name = request.Form("last_name")
		SearchMethod = "Name"
	End If
	DIM SQL,RS
	Select Case SearchMethod
		CASE "Loc"
			SQL = "SELECT first_name, last_name FROM Employee_table WHERE location = '" & loc_id & "'"
		CASE "Name"
			SQL = "SELECT first_name, last_name FROM Employee_table WHERE " _
			& "first_name like '%" & first_name & "%' OR last_name like '%" & last_name & "%'"
	End Select
	
%>
<html>
<body bgcolor="gold">
<body>
<h1 align="center"> Phone List Directory </h1>
<p align="center"> Please input the person's first name, last name or location that you are searching for.</p>
<p align="center"> You can search for someone with just the first name, last name or get a list of everyone in a certain location.</p>

<form action="<%=request.ServerVariables("SCRIPT_NAME")%>" method="post">
<input type="hidden" name="flag" value="1">
First Name: <input type="text" name="first_name"><br>
Last Name:<input type="text" name="last_name"><br>
<select name="Location">
<option value="" selected>Select Location</option>
<option value="MAE">MAE</option>
<option value="LCQ">LCQ</option>
<option value="GSO">GSO</option>
<option value="DOTHAN">DOTHAN</option>
<option value="FLTSTR">FLTSTR</option>
<option value="SOCAL">SOCAL</option>
<option value="SAA">SAA</option>
<option value="KC-10">KC-10</option>
<option value="KC-135">KC-135</option>
<option value="ROAD">ROAD</option>
<option value="ENGLAND">ENGLAND</option>
<option value="OFFICE">OFFICE</option>
</select>
<input type="submit" value="SEARCH" name="btnSubmit">
</form>
<%
IF request.Form("flag") <> "" then
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open strConnect 
	Set RS = objConn.execute(SQL)
	IF NOT RS.EOF THEN
		response.Write("<table border='1'><tr><td>First Name</td><td>Last Name</td></tr>")
		do while not rs.eof
			first_name = rs("first_name")
			last_name = rs("last_name")
			response.Write("<tr><td>" & first_name & "</td><td>" & last_name & "</td></tr>")
			rs.movenext
		loop
		response.Write("</table>")
	end if
	set rs=nothing
	objConn.close
	set objConn = nothing
End If
%>
</body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I need help with connecting to a database

naasinc

Technical User

Veep

Programmer

Similar threads

Part and Inventory Search

Sponsor