VB icon

Create your own Grid

Email
Submitted on: 1/5/2015 5:52:00 PM
By: Sumit Dhingra (from psc cd)  
Level: Intermediate
User Rating: By 3 Users
Compatibility: ASP (Active Server Pages), HTML, VbScript (browser/client side)
Views: 511
 
     The code in here is an example of how you can display and manipulate multiple records through asp.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: Create your own Grid
' Description:The code in here is an example of how you can display and manipulate multiple records through asp.
' By: Sumit Dhingra (from psc cd)
'
' Inputs:Nothing
'
' Returns:Nothing
'
' Assumes:The code works against the Pubs database that comes with SQL Server.
Customizations Required :
Change the Connection String. You may even use the descriptive connection string instead of using UDL file.
Can put the queries in a stored procedure and then call the stored procedure from the web page.
Can add Insert/Delete functionality too.
'**************************************

<%@ Language="VBScript" %>
<%
	option explicit
	Response.Buffer = True
			
	Dim StrConn
	Dim objConnection
	Dim AuthorsRS, PubsRS
	Dim au_id, Saveflg, Items, Title_Id, stor_Id, Ord_Num, Qty
	Dim SQL
	Dim i, Counter
	
	'Setting up the Connection
	StrConn = "File Name=C:\Test\Pubs.udl"
	Set objConnection = Server.CreateObject("ADODB.Connection")
	objConnection.ConnectionString = StrConn
	' open the connection
	objConnection.Open
	
	' Get the Form Values
	SaveFlg = Request.Form("hSaveFlg")
	au_id = Request.Form("hau_id")	
	Items = Request.Form("hItems")	
	
	If au_id = "" or ISNULL(au_id) then
		au_id = "%"
	End If
	
	'---------------------------- Saving the Info to the Database ---------------------------------------
	If SaveFlg = "1" Then
		Counter = 1
		Do While Counter <= (Items - 1)
			If Request.Form("Select"&Counter) = "on" Then
				Ord_Num = Request.Form("Ord_num"&Counter)
				Stor_Id = Request.Form("Stor_Id"&Counter)								
				Title_Id = Request.Form("Title_Id"&Counter)
				Qty = Request.Form("Quantity"&Counter)																				
				
				' Saving the Info now
				SQL = "Update Sales Set Qty = " & qty & " Where stor_id = " & stor_id &_
				" and ord_num = '" & ord_num & "' and title_id = '" & title_id & "'"
				objConnection.Execute(SQL)				
				
			End If
			Counter = counter + 1
		Loop
	End If
	
	
	' Get the Data for displaying on the Page.
	Set AuthorsRS= objConnection.Execute("Select au_id, au_fname + ' ' + au_lname as Author From Authors")
	AuthorsRS.MoveFirst
	
	SQL = "select au_fname, au_lname, s.ord_num, t.title, s.qty, s.stor_id, s.title_id " &_
	"from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join sales s " &_
	"on ta.title_id = s.title_id inner join titles t on s.title_id = t.title_id " &_
	"where a.au_id like '" & au_id &	"' order by a.au_fname, au_lname, s.ord_num, s.title_id"
	
	Set PubsRS= objConnection.Execute(SQL)
	PubsRS.MoveFirst
%>
<HTML>
<HEAD>
<TITLE>Pubs Grid</TITLE>
<!-- Script to check the fields to see if they are valid -->
<SCRIPT Language="JavaScript">
<!--
function SaveForm()
{
	document.PubsGrid.hSaveFlg.value = 1;
	document.PubsGrid.submit();
}
function SelectAll()
{
	for(i=1; i < document.PubsGrid.hItems.value; i++)
	{
		eval("document.PubsGrid.Select" + i + ".checked = true;");
	}
}
function ClearAll()
{
	for(i=1; i < document.PubsGrid.hItems.value; i++)
	{
		eval("document.PubsGrid.Select" + i + ".checked = false;");		
	}
}
function ViewAuthor()
{
	document.PubsGrid.hau_id.value = document.PubsGrid.Author.value;
	document.PubsGrid.submit();
}
-->
</SCRIPT>
</HEAD>
<BODY>
<Form method="POST" name="PubsGrid" action="Grid.asp"> 
	<Table border="1" cellspacing="0" cellpadding="2">
		<tr>
			<td colspan=4>
				Author : 						
				<%
					Response.Write "<Select Name=Author onChange='ViewAuthor()'>"										
					Response.Write "<option value=All>All</option>"
					Do While Not AuthorsRS.Eof
							Response.Write "<option value=" & AuthorsRS("au_id").Value 
							If AuthorsRS("au_id").Value = au_id Then Response.Write " " & "Selected" end If
							Response.Write ">" & AuthorsRS("Author").Value & "</option>"
							AuthorsRS.MoveNext
					Loop
					Response.Write "</Select><P>"
				%>
			</td>
		</tr>
		<tr height="30">
			<td colspan="4"><Input Type=Radio Name=All onClick="return SelectAll()">  Select All
			     <Input Type=Radio Name=All onClick="return ClearAll()">  Clear All
			</td>
		</tr>					
		<tr>
			<td>Select</td>
			<td>Order #</td>
			<td>Title</td>
			<td>Quantity</td>
		</tr>
		<%
			i = 1
			Do While Not PubsRS.Eof
				Response.Write "<tr>" 
				Response.Write "<td> <Input Type=CheckBox Name=Select" & i & "></td>"
				Response.Write "<td>"
				Response.Write "<Input Type=Hidden Name=Stor_Id" & i & " Value=" & PubsRS("Stor_Id") & ">"
				Response.Write "<Input Type=Hidden Name=Title_Id" & i & " Value=" & PubsRS("title_Id") & ">"
				Response.Write "<Input Type=Text Name=Ord_Num" & i & " Value=" & PubsRS("Ord_num") & ">"
				Response.Write "</td>"
				Response.Write "<td>" & PubsRS("Title") & "</td>"
				Response.Write "<td> <Input Type=Text Name=Quantity" & i & " Value=" & PubsRS("Qty") & "></td>"				
				Response.Write "</tr>"
				i = i + 1
				PubsRS.MoveNext
			Loop
		%>
	</Table>
	<input type="Button" value ="Save" name="submit_keyword" onClick="return SaveForm()">
	<input type="Hidden" name="hSaveFlg">	
	<input type="Hidden" name="hau_id" value="<%=au_id%>">		
	<input type="Hidden" name="hItems" value="<%=i%>">	
</Form>
<%
PubsRS.Close
%>
</BODY>
</HTML>


Other 1 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.