Date: Jan 15,2003 when I started creating these Programs
Developer : Tariq Chaudhary
 Database Driven Login using ASP,SQL,SQL Server 2000,Access,HTML. |
|
Inserting data into database table using ASP,SQL,HTML. |
|
Displaying Records from the Database using ASP,SQL,HTML. |
|
Deleting records from Database using ASP,SQL,HTML. |
|
Updating Database Records using ASP,SQL,HTML. |
|
Searching Database Records using ASP,SQL,HTML. |
|
Populating dropdown listbox and displaying Data. |
|
Displaying Records from the Database using Next and Prvious Links. |
|
Deleting Multiple Records from the Database using Check Boxes. |
|
Performing search using Drop down list box and a Text Box using two database tables. |
|
Extracting Data From SQL Server and Displaying it on the browser using XML. |
|
Performing search using two Drop down list boxes and a Text Box using three SQL Server database tables. |
Database Driven Login (ASP,SQL Server 2000,SQL,Javascript)
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<SCRIPT LANGUAGE="javascript">
<!--
function validate()
{
if (form1.username.value == "" || form1.password.value == "")
{
alert("You left user name or password blank,so try again")
return false;
}
else
document.form1.submit();
return true;
}
//-->
</SCRIPT>
</HEAD>
<BODY>
<FORM action="ProceedLogin.asp" method="post" name=form1>
<Div align="center">
<table border="1" width="25%">
<tr align="center"><td>
Username:
<INPUT type=text name="username">
Password:</b>
<INPUT type="password" name="password">
<INPUT type="button" value="Login" name="login" onClick="validate();">
</td>
</tr>
</table>
</Div>
</FORM>
</BODY>
</HTML>
<%@ Language=VBScript %>
<%Response.Buffer=true%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
'Username, Password
Dim conn,rs,strsql
Set conn = server.CreateObject("ADODB.Connection")
Set rs = server.CreateObject("ADODB.Recordset")
'DSN less connection
'for access
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "Data Source=" & Server.MapPath("login.mdb")
conn.open
' DSN connection
conn.Open("DSN=test;uid=administrator;pwd=admin") 'for SQL Server
strsql = "Select * From tblLogin where Username = '" & _
Request.Form("username") & "' and Password = '" & _
Request.Form("password") & "'"
Set rs = conn.Execute (strsql)
If (not rs.BOF) and (not rs.EOF) then
Response.Redirect "welcome.asp"
else
Response.Redirect "loginform.asp"
end if
'close the recordset
rs.close
Set rs = nothing
'close the connection
conn.close
Set conn = nothing
%>
</BODY>
</HTML>
**********************************End of Template***************************************************** Top
Tools: Visual InterDev 6.0,SQL,HTML,SQL Server 2000, access and IIS 5.0
Inserting data into database using ASP
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<FORM action="Insert.asp" method=POST name=form1>
<table border="1">
<tr>
<td align="left">
First Name:<INPUT type="text" name="Fname" size="20">
Last Name:<INPUT type="text" name="LName" size="20">
</td>
</tr>
<tr>
<td align="left">
Address :<INPUT type="text" name="Address" size="20">
User id:<INPUT type="text" name="userid" size="5">
</td>
</tr>
<tr>
<td><center>
<INPUT type="submit" value=" Add " id=submit1 name=submit1></center>
</td>
</tr>
</table>
</FORM>
</body>
</html>
Output
<%@ Language=VBScript %>
<!-- The following will perform the validation -->
If request.form("Fname")="" Then
response.write "<center><b>You must enter your first name."
response.write "<form id=form1 name=form1>"
response.write "<input type='button' value='Retry' onclick=history.back() id='button'1 name='button'1>"
response.write "</form>"
response.End
Else
fname = request.form("fname")
End If
Output
If
request.form("Lname")="" Then
response.write "<center><b>You must enter your last name."
Response.write "<form id=form1 name=form1>"
response.write "<input type='button' value='Retry'onclick=history.back() id='button'1 name='button'1>"
response.write "</form>"
response.end
Else
Lname = request.form("lname")
End If
If request.form("Address")="" Then
response.write "<center><b>You must enter addresss."
response.write "<form id=form1 name=form1>"
response.write "<input type='button' value='Retry' onclick=history.back() id='button'1 name='button'1>"
response.write "</form>"
response.end
Else
address = request.form("address")
End If
If request.form("userid")="" Then
response.write "<center><b>You must enter your id."
response.write "<form nid=form1 name=form1>"
response.write "<input type='button' value='Retry' onclick=history.back()id='button'1 name='button'1>"
response.write "</form>"
response.end
Else
userid = request.form("userid")
End If
%>
<%
Dim conn,sSQL
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
'Conn.Open ("DSN=tcdatasource")'for access database
'Conn.Open ("DSN=tariq;UID=good;PWD=good") 'for sql Server database
sSQL = "INSERT INTO tbladdress([fname],[lname],[address],[userid])VALUES ('"&fname&"','"&lname&"','"&address&"',"&userid&")"
Set RS = conn.execute(sSQL)
Response.Redirect("displayrecs1.asp")
%>
</body>
**********************************End of Template***************************************************** Top
Displaying Data from database using ASP
<!-- the name of following template is "displayrecs1.asp" -->
<%@ Language=VBScript%>
<HTML>
<%
Dim Rs,objConn,sSQL
Set objConn = Server.Createobject("Adodb.connection")
objconn.open "mytest"
Set RS = Server.CreateObject ("ADODB.Recordset")
sSQL= "Select * from tbladdress"
Set RS =objconn.execute(sSQL)
If Rs.EOF then
Response.Write "There are no records in the database"
Response.Write "<Br>Please check back later?"
Else
%>
<BR><BR><BR><BR><BR>
<center>
<table width="600" border="2" celladding="10" bordercolor="maroon">
<tr align="left">
<th>First Name</th>
<th>Last Name</th>
<th>Address </th>
<th>User ID </th>
<th>Action </th>
</tr>
<%Do while not Rs.EOF %>
<tr>
<td>
<font face="arial"><%=rs("fname") %></font>
</td>
<td>
<font face="arial"><%=rs("lname") %></font>
</td>
<td>
<font face ="arial"><%=rs("address") %></font>
</td>
<td>
<font face ="arial"><%=rs("userid")%><font></td>
<input type="hidden" name="id" value="<%=rs("id")%>">
<td><a href="updateRec.asp?ID=<%=rs("id")%>">Edit</a>
<a href="delete.asp?ID=<%=rs("id")%>">Delete</a>
</td>
</tr>
<% Rs.movenext
loop
End if
Rs.Close
Set Rs =nothing
objconn.close
Set objconn =nothing
%>
</table>
</center>
<p> Do you want to Insert a New Record
<A href="firstform.asp">Click to Insert a new record</a></p>
</html>
Output
</body>
</HTML>
**********************************End of Template***************************************************** Top
Deleting data from database using ASP
<%@ Language=VBScript %>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<b>You chose this record to delete!!!</B>
<%
Id =Request.querystring("Id")
Dim conn,sSQL,Rs,Fname,lname,address,userid,id
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
sSQL = "Select * from tbladdress Where id = "&Id&" "
Set RS = conn.execute(sSQL)
%>
<!-- updaterec.asp is calling this template-->
<form action="deleteUser.asp" Method="post" id="form1" name="form1">
<center>
<TABLE WIDTH="45%" BORDERCOLOR=yellow BORDER=1 CELLSPACING=1 CELLPADDING=1 bgcolor="LightSkyBlue">
<tr>
<td>
First Name: <Input type="Text" Name="FName" Value="<%=RS("Fname")%>">
</td>
<td>
Last Name:<Input type="Text" Name="LName" Value="<%=RS("Lname")%>">
</td>
</tr>
<tr>
<td>Address:<Input type="Text" Name="Address" Value="<%=RS("address")%>">
<Input type="submit" Name="Delsubmit" Value="Delete">
</td>
</tr>
<td><input type="hidden" Name="Id" Value="<%=id%>"></td>
</TABLE>
</center>
</form>
<%
Rs.Close
Set rs =nothing
conn.Close
Set conn = nothing
%>
</BODY>
</HTML>
<%@ Language=VBScript %>
<% Option Explicit %>
<HTML>
<head><title>Edit User</title>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<STRONG><MARQUEE> <%=Now()%></MARQUEE>
<P></STRONG>
<%
Dim Conn,RS,sSQL,fname,lname,address,id,userid
fname=Request.Form("fname")
lname=Request.Form("lname")
address=request.Form("address")
id =Request.Form("id")
'userid=Request.Form("userid")
Set Conn = Server.createobject("Adodb.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
Conn.Open "mytest"
sSQL = " Delete * from tbladdress Where id = "&id&" "
Set RS = Conn.execute(sSQL)
%>
<P>you have Deleted the record with id =<%=Request.form("id")%>
</P>
</body>
</html>
**********************************End of Template***************************************************** Top
Updating data from database using ASP
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<b>This template will allow you update this record!!!</B>
<%
id =Request.querystring("id")
Dim conn,sSQL,Rs,Fname,lname,address,userid,id
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
sSQL = "Select * from tbladdress where id =" &Id&" "
Set RS = conn.execute(sSQL)
%>
<!-- updaterec.asp is calling this template-->
<form action="updateuser.asp" Method="post">
<TABLE WIDTH="45%" BORDERCOLOR=yellow BORDER=1 CELLSPACING=1 CELLPADDING=1>
<tr>
<TD>First Name: <Input type="Text" Name="FName" Value="<%=RS("Fname")%>"></TD>
<TD>Last Name:<Input type="Text" Name="LName" Value="<%=RS("Lname")%>"> </TD>
</tr>
<tr>
<TD>Address:<Input type="Text" Name="Address" Value="<%=RS("address")%>">
<Input type="submit" Name="Delsubmit" Value="Update">
</TD>
</tr>
<td><input type="hidden" Name="id" Value="<%=id%>"></td>
</TABLE>
</form>
<%
Rs.Close
Set rs =nothing
conn.Close
Set conn = nothing
%>
</BODY>
</HTML>
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
id =Request.form("id")
fname =Request.Form("fname")
lname =Request.Form("lname")
address =Request.Form("address")
Dim conn,sSQL,Rs
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
sSQL = "Update tbladdress Set fname='"&fname&"',lname='"&lname&"',address ='"&address&"' where id ="&Id&" "
Set RS = conn.execute(sSQL)
%>
<b>The record with id: <%=id%> had been updated</b><A href="displayrecs1.asp">Main</A> </P>
</BODY>
</HTML>
**********************************End of Template***************************************************** Top
Searching database using ASP and SQL
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<form action="searchResults.asp" Method="post" id=form1 name=form1>
<center>
<TABLE WIDTH="35%" BORDERCOLOR="Blue" BORDER=1 CELLSPACING=1 CELLPADDING=1>
<th><font color="maroon" size="6"> Search Database</font></th>
<tr>
<TD>First Name: <Input type="Text" Name="FName">
</TD>
</tr>
<tr>
<TD><Input type="submit" Name="SearchData" Value="Search">
<Input type="reset" Name="Resetbtn" Value="Clear">
</td>
</tr>
</TABLE></center>
</form>
</BODY>
</HTML>
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY >
<%
Dim conn,sSQL,Rs
fname = Request.Form("fname")
If Fname = "" Then
Response.Write("No Search string entered")
response.write "<form id=form1 name=form1>"
response.write "<input type='button' value='Back' onclick=history.back() id='button'1 name='button'1>"
response.write "</form>"
response.End
End if
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
sSQL =" Select fname,lname,address From tbladdress WHERE Fname LIKE '"&fname& "' Order by Fname,lname"
Set RS = conn.execute(sSQL)
%>
<center><table border="1" cellspacing=2 cellpadding=2>
<TR bgcolor="yellow">
<td>First Name</td>
<td>Last Name</td>
<td>Address</td>
</tr>
<%
While NOT rs.EOF
Response.Write ("<TR><TD>")
Response.Write rs.Fields("fname")
Response.Write("</td>")
Response.Write("<td>")
Response.Write rs.Fields("lname")
Response.Write("<td>")
Response.Write rs.Fields("address")
Response.Write("</TD></TR>")
rs.MoveNext
Wend
rs.close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
</table>
<STRONG><a href="searchdata.asp">Back</a></center>
</STRONG>
</BODY>
</HTML>
**********************************End of Template***************************************************** Top
Populating the dropdown listbox from the database using ASP,SQL,
javascript and HTML
<html>
<head>
<title>Drop Down List box</title>
<script language="javascript">
function go()
{
if(document.frm1.s1.options[document.frm1.s1.selectedIndex].value=="ww")
{
alert("You did not make any selection,so try again?");
return false;
}
else
document.frm1.userId.value=
document.frm1.s1.options[document.frm1.s1.selectedIndex].value
document.frm1.submit();
return true;
}
</script>
</head>
<body>
<%
Dim Conn,RS,sSQL,id,fname,lname,address,userid
Set Conn = Server.createobject("Adodb.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
Conn.Open "DSN=mytest"
sSQL = " Select id,fname,lname,userid from tbladdress "
Set RS = Conn.execute(sSQL)
%>
<center>
<!--<form name ="displaylist" action="displaylist_Contents.asp">-->
<form name ="frm1" action="test1.asp" method="post">
<input type="hidden" Name="userId" Value="<%=rs("userid")%>">
<Select Name="s1">
<option value="ww" Selected>---Make a Selection----</option>
<%Do While Not rs.EOF %>
<option Value="<%=rs("userid")%>"><%=rs("Fname")%>
<%
rs.moveNext
Loop
rs.close
Set rs =nothing
%>
</Select>
<br><br><br>
<input type="button" Value="Display" Name="btnsub" onClick="go();">
</form>
</center>
</body>
</html>
<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY >
<%
' id= Request.Form("id")'if you want to pass id then use this line
userid= Request.form("userid")
Dim conn,sSQL,Rs
Set Conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
conn.open("DSN=mytest") 'for access database
sSQL =" Select distinct id, fname,lname,address,userid From tbladdress WHERE userid="&userid&" "
Set RS = conn.execute(sSQL)
%>
<center><table border="4" cellspacing=2 cellpadding=2>
<tr bgcolor="yellow">
<td>First Name</td>
<td>Last Name</td>
<td>Address</td>
<td>User id</td>
</tr>
<%
While NOT rs.EOF %>
<tr>
<td> <font face ="arial"><%=rs("fname") %></font></td>
<td> <font face ="arial"><%=rs("lname") %></font></td>
<td><font face ="arial"><%=rs("address") %></font></td>
<td><font face ="arial"><%=rs("userid")%></font></td>
</tr>
<%
rs.MoveNext
Wend
rs.close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
</table>
</Center>
</BODY>
</HTML>
**********************************End of Template***************************************************** Top
Displaying Records using Next and Previous Link(s)ASP,SQL
<%@ Language=vbscript%>
<% Option Explicit %>
<%
'Option Explicit forces you to declare your variables.
' You should always use this for debugging purposes.
%>
<%
'<!--#INCLUDE VIRTUAL="adovbs.inc" -->
'ADOVBS.INC is needed for named constants.
%>
<html>
<body bgColor="PaleGoldenrod" aLink="MidnightBlue">
<%
const adUseClient=3
Dim Connect_String
Dim MyConn
Dim Page_Size 'variable which holds the number of records to be viewed per page.
Dim Current_Page 'variable which keeps track of which page is the current page.
Dim RS,SQL
Dim Page_Count 'page_count is a variable which stores the number of pages
' that can be viewed.
'MyConn.open "mytest"
Connect_String="mytest"
Page_Size = 5 'here we set the number of records viewed per page to 5.
If Request("Page")="" Then
Current_Page = 1
Else
Current_Page = CInt(Request("Page")) 'the CInt function converts the value to an integer.
End If
Set MyConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
'MyConn.open "mytest"
MyConn.Open Connect_String 'Myconn.open "mytest"
RS.CursorLocation = adUseClient
RS.PageSize = Page_Size
'below change the statement to reflect your query
SQL = "SELECT * FROM tbladdress"
RS.Open SQL, MyConn
', adOpenStatic, adLockReadOnly, adCmdText
Page_Count = RS.PageCount
If 1 > Current_Page Then Current_Page = 1
If Current_Page > Page_Count Then Current_Page = Page_Count
RS.AbsolutePage = Current_Page
%>
<Center>
<table width="400" border="2" celladding="10" bordercolor="maroon" bgcolor=LightSteelBlue>
<tr align="Middle">
<th>First Name</th>
<th>Last Name</th>
<th>Address </th>
<th>User ID </th>
</tr>
<%
Do While RS.AbsolutePage = Current_Page AND Not RS.EOF %>
<tr>
<td align="Middle"> <font face ="arial"><%=rs("fname") %></font></td>
<td align="Middle"> <font face ="arial"><%=rs("lname") %></font></td>
<td align="Middle"><font face ="arial"><%=rs("address") %></font></td>
<td align="Middle"><font face ="arial"><%=rs("userid")%></font></td>
<input type="hidden" name="id" value="<%=rs("id")%>">
</tr>
<%
RS.MoveNext
Loop
'clean up
RS.Close
Set RS = Nothing
MyConn.Close
Set MyConn = Nothing
Response.Write "<br>"
'below is the page navigation.
%>
</table></center><br>
<%
If Current_Page <Page_Count Then %>
<center><a href="nextprebuttons.asp?Page=<%=Current_Page + 1%>"><b>Next>></a>
</center>
<%
End IF
%>
<%
If Current_Page <> 1 Then
%>
<center>
<a href="nextprebuttons.asp?Page=<%=Current_Page -1%>"><b><< Previous</a></center>
<%
End If
%>
</body>
</html>
<br><br>
<center>Page <%=Current_Page%> of <%=Page_Count%></center>
**********************************End **************************************************** Top
Deleting multiple database Records using Check Boxes
Date: June 27,2003
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
Dim Rs,objConn,sSQL
Set objConn = Server.Createobject("Adodb.connection")
objconn.open "mytest"
Set RS = Server.CreateObject ("ADODB.Recordset")
sSQL= "Select * from tbladdress"
Set RS =objconn.execute(sSQL)
If Rs.EOF then
Response.Write "There are no records in the database"
Response.Write "<Br>Please check back later?"
else
%>
<form action="deleteuser.asp" method="post">
<center>
<table width="600" border="2" celladding="0" bordercolor="maroon">
<tr align=left>
<th>check box</th>
<th>First Name</th>
<th>Last Name</th>
<th>Address </th>
<th>User ID </th>
</tr>
<% Do while not Rs.EOF %>
<tr>
<td><input type=checkbox Name="delcheck" value="<%=rs("id")%>"></td>
<td> <font face ="arial"><%=rs("fname") %></font></td>
<td> <font face ="arial"><%=rs("lname") %></font></td>
<td><font face ="arial"><%=rs("address") %></font></td>
<td><font face ="arial"><%=rs("userid")%></font></td>
<input type="hidden" name="id" value="<%=rs("id")%>">
<!-- <td> <a href="deleteuser.asp?ID=<%=rs("id")%>">Delete</a></td>-->
</tr>
<% Rs.movenext
loop
end if
Rs.Close
Set Rs =nothing
objconn.close
Set objconn =nothing
%>
<tr><td><input type="submit" Name="delsubmit" value="delete"></td>
</tr>
</table>
</form>
</center>
<p> Do you want to Insert a New Record
<A href="firstform.asp">Click to Insert</a></p>
</body>
</html>
OutPut
<%@ Language=VBScript %>
<% Option Explicit %>
<HTML>
<head><title>Edit User</title>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<STRONG><MARQUEE> <%=Now()%></MARQUEE>
<P></STRONG>
<%
Dim Conn,RS,sSQL,fname,lname,address,id,userid,delcheck
fname=Request.Form("fname")
lname=Request.Form("lname")
address=request.Form("address")
id =Request.Form("id")
'userid=Request.Form("userid")
delcheck =Request.Form("delcheck")
Set Conn = Server.createobject("Adodb.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
Conn.Open "mytest"
sSQL = " Delete from tbladdress Where id IN("&delcheck&")"
Set RS = Conn.execute(sSQL)
%>
<P>you have Deleted the record with id =<%=Request.form("id")%> <br>
<%response.redirect "displayrecs1.asp"%>
</body>
</html>
**********************************End of Template***************************************************** Top
Performing Search using Database Driven Drop down list box and a text Box
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Drop Down List box</title>
<!--One drop down box and a text box to perform search on two tables--->
<script language="javascript">
function go()
{
if(document.frm1.s1.options[document.frm1.s1.selectedIndex].value=="ww" )
{
alert("You did not make any selection,enter search key word,so try again?");
return false;
}
if (frm1.title.value == "")//if text box is empty
{
alert("You did not ,enter search key word,so try again?");
document.frm1.title.focus();
return false();
}
else
document.frm1.catid.value=
document.frm1.s1.options[document.frm1.s1.selectedIndex].value
document.frm1.submit();
return true;
}
</script>
</head>
<body>
<%
Dim Conn,RS,sSQL,catid,category,title,id
Set Conn = Server.createobject("Adodb.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
' Conn.Open ("DSN=test")
Conn.Open ("DSN=tariqch;UID=good;PWD=good") 'for sql Server database
sSQL = " Select catid,category from tblcategory "
Set RS = Conn.execute(sSQL)
%>
<center>
<form name ="frm1" action="test1.asp" method="post">
<table height="20%" width="20" border="1">
<Input type="hidden" Name="catid" Value="<%=rs("catid")%>">
<tr><th>Category</th></tr>
<tr>
<td>
<Select Name="s1">
<option value="ww" Selected>---Make a Selection----</option>
<%Do While Not rs.EOF %>
<option Value="<%=rs("catid")%>"><%=rs("category")%>
<%
rs.moveNext
Loop
rs.close
Set rs =nothing
%>
</Select></td>
</tr>
<tr bgcolor="lightyellow"><th>Enter Key word</th></tr>
<tr>
<td>
<Input type="text" Name="title" size="15"></td>
</tr>
<tr> <td>
<input type="button" Value=" Search " Name="btnsub" onClick="go();">
<input type="reset" Value=" Clear " Name="Reset">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY background="bg.jpg">
<%
catid= Request.form("catid")
category= Request.Form("category")
title=Request.Form("title")
description =Request.Form("description")
id=Request.Form("id")
Dim conn,sSQL,Rs',catid,subcatid
Set conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
Conn.Open ("DSN=tariqch;UID=good;PWD=good") 'SQL Server 2000
'Conn.open ("DSN=tariqch")
sSQL ="Select tblcategory.category,tblsubcat.title,tblsubcat.description" &_
" from tblcategory,tblsubcat" &_
" Where tblcategory.catid = "&request("s1")&" and tblcategory.catid = tblsubcat.catid" &_
" and tblsubcat.title like '%"&Replace(title,"""","""")&"%'"
Set RS = conn.execute(sSQL)
%>
<center><table border="4" cellspacing=2 cellpadding=2>
<tr bgcolor="yellow">
<td><b>Category</b></td>
<td><b>title</b></td>
<td><b>Description</b></td>
</tr>
<% While NOT rs.EOF %>
<tr>
<td> <font face ="arial"><%=rs("category") %></font></td>
<td> <font face ="arial"><%=rs("title") %></font></td>
<td> <font face ="arial"><%=rs("Description") %></font></td>
</tr>
<%
rs.MoveNext
Wend
rs.close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
</table>
<a href="simplelistbox.asp"><b>Back</b></a>
</Center>
</BODY>
</HTML>
Extracting Data From SQL Server and Displaying it on the Web using XML
Steps:
1. Create a directory like test under C:\inetpub\wwwroot
2. Go to Programs/Microsoft SQL Server and click on Configure SQL XML Support in IIS.
3. Right Click on Default Web site and create New Virtual Directory like mytest
(you will see different tabs when you will right click and will choose New)
4. Assign full path to this virtual directory(mytest)
5. Choose your Server and enter database name.
6. Go to SQL Server Enterprise Manger and click on Security and then Logins and create a user ,if you have done already ,Then user must have access to this database and sever lever permessions.
7. Open your browser and in the address bar after localhost/virtual directory name type sql statment start with ?sql=sql statement for xml aut &root=Customers
and hit enter, then you will see some thing similar to fig below.
**********************************End of Template***************************************************** Top
Performing Search using Database Driven Two Drop down list boxes and a text Box
<%@ Language=javaScript %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Two Drop Down List boxes and a text box</title>
<!--three SQL Server database tables--->
<!-- (a)tblMcategory(b)tblstates(c)tbldetails --->
<script language="javascript">
function go()
{
if(document.frm1.s1.options[document.frm1.s1.selectedIndex].value=="ww" )
{
alert("You did not make any selection from the category,enter search key word,so try again?");
return false;
}
if (frm1.title.value == "")//if text box is empty
{
alert("You did not ,enter search key word,so try again?");
document.frm1.title.focus();
return false();
}
else
document.frm1.cat_id.value=
document.frm1.s1.options[document.frm1.s1.selectedIndex].value
document.frm1.submit();
return true;
}
function sel2()
{
if(document.frm1.s2.options[document.frm1.s2.selectedIndex].value=="xx" )
{
alert("You did not make any selection from state category,enter search key word,so try again?");
return false;
}
if (frm1.title.value == "")//if text box is empty
{
alert("You did not ,enter search key word,so try again?");
document.frm1.title.focus();
return false();
}
else
document.frm1.state_id.value=
document.frm1.s2.options[document.frm1.s2.selectedIndex].value
document.frm1.submit();
return true;
}
</script>
</head>
<body bgcolor="lightblue">
<%
Dim Conn,RS,sSQL,catid,category,title,state,id
Set Conn = Server.createobject("Adodb.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
' Conn.Open ("DSN=test") for access database
Conn.Open ("DSN=tariqch;UID=administrator;PWD=admin") 'for sql Server database
sSQL = " Select cat_id,category from tblMcategory "
Set RS = Conn.execute(sSQL)
%>
<center>
<form name ="frm1" action="result_Multiboxes.asp" method="post">
<table height="20%" width="20" border="1">
<Input type="hidden" Name="cat_id" Value="<%=rs("cat_id")%>">
<tr bgcolor="lightyellow"><th>Category</th><th>State</th></tr>
<tr>
<td>
<Select Name="s1">
<option value="ww" Selected>---Make a Selection----</option>
<%Do While Not rs.EOF %>
<option Value="<%=rs("cat_id")%>"><%=rs("category")%>
<%
rs.moveNext
Loop
rs.close
Set rs =nothing
%>
</Select></td>
<%
'Conn.Open ("DSN=test;UID=test;PWD=test") 'for sql Server database sSQL = " Select * from tblstates "
Set RS = Conn.execute(sSQL)
%>
<td>
<Input type="hidden" Name="state_id" Value="<%=rs("state_id")%>">
<Select Name="s2">
<option value="xx" Selected>---Make a Selection----</option>
<%Do While Not rs.EOF%>
<option Value="<%=rs("state_id")%>"><%=rs("state")%>
<%
rs.moveNext
Loop
rs.close
Set rs =nothing
%>
</Select>
</td>
</tr>
<tr bgcolor="lightyellow"><th>Enter Key word</th></tr>
<tr>
<td>
<Input type="text" Name="title" size="15"></td>
</tr>
<tr> <td>
<Input type="button" Value=" Search " Name="btnsub" onClick="go();sel2();">
<Input type="reset" Value=" Clear " Name="Reset">
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
<%@ Language=javaScript %>
<HTML>
<HEAD>
</HEAD>
<BODY background="bg.jpg">
<%
'this is being called by multiboxes_textbox.asp
cat_id= Request.form("cat_id")
state_id = Request.form("state_id")
category= Request.Form("category")
title=Request.Form("title")
despt =Request.Form("despt")
state =Request.Form("state")
id=Request.Form("id")
Dim conn,sSQL,Rs,cat_id,state_id,state
Set conn = Server.createobject("AdoDB.connection")
Set RS = Server.CreateObject ("ADODB.Recordset")
Conn.Open ("DSN=test;UID=test;PWD=test") 'SQL Server 2000
'Conn.open ("DSN=test") for access database
sSQL ="Select tblMcategory.category,tbldetails.title,tbldetails.despt,tblstates.state" &_
" from tblMcategory,tblstates,tbldetails" &_
" Where tblMcategory.cat_id = "&request("s1")&" and tblMcategory.cat_id = tbldetails.cat_id" &_
" and tblStates.state_id= "&request("s2")&" "&_
" and tblStates.state_id = tbldetails.state_id" &_
" and tbldetails.title like '%"&Replace(title,"""","""")&"%'"
Set RS = conn.execute(sSQL)
%>
<% if rs.recordcount is 0 Then %>
<script>
alert(" no such record")
</script>
<%
response.redirect "multiboxes_textbox.asp"
ELSE
%>
<center><table border="4" cellspacing=2 cellpadding=2>
<tr bgcolor="yellow">
<td><b>Category</b></td>
<td><b>title</b></td>
<td><b>Description</b></td>
<td><b>State</b></td>
</tr>
<% While NOT rs.EOF %>
<tr>
<td> <font face ="arial"><%=rs("category") %></font></td>
<td> <font face ="arial"><%=rs("title") %></font></td>
<td> <font face ="arial"><%=rs("Despt") %></font></td>
<td> <font face ="arial"><%=rs("State")%></font></td>
</tr>
<%
rs.MoveNext
Wend
rs.close
Set rs = Nothing
conn.close
Set conn = Nothing
%>
<% end if%>
</table>
<a href="multiboxes_textbox.asp"><b>Back</b></a>
</Center>
</BODY>
</HTML>
**********************************End of Template***************************************************** Top