Adding Data To Access Database
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Adding Data To Access Database as PDF for free.
More details
- Words: 4,931
- Pages: 24
ASP and Java Script reference
Page 1 of 1
Adding Data to Access Database To add data to a database table, you need an existing database plus the table to add the data to. Let us assume that you have Access Database file name FeedBack.mdb in the same folder as this file with the following table: tblFeeds Field Name Data Type Field Size user_id
Autonumber 8
Name
Text
45
Comments Text 200<> To add a data to the table tblFeeds, first we will have to create a form to enter the data and then open the database and make the connection. Here is the form to enter the data before adding to the database: Form Example Adding to database example <script type="text/javascript"> http://www.aspnetcenter.com
Page 1 of 1
ASP and Java Script reference
Page 2 of 2
Now, you insert the new record to the database using the information provided through EnterData.asp. Here is the code to do this: save.asp <% Dim Conn Dim Rs Dim sql 'Create an ADO connection and recordset object Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") 'Set an active connection and select fields from the database Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT name, comments FROM tblFeeds;" 'Set the lock and cursor type Rs.CursorType = 2 Rs.LockType = 3 Rs.Open sql, Conn
'Open the recordset with sql query
Rs.AddNew 'Prepare the database to add a new record and add Rs.Fields("name") = Request.Form("name") Rs.Fields("comments") = Request.Form("comments") Rs.Update 'Save the update Rs.Close Set Rs = Nothing Set Conn = Nothing %> The third field (user_no) of our table is auto generated and sequentially will accommulate it self on each addition of new record. You redirect the user to another page when the record is added to the database using <%response.redirect("view.asp")%> After the data is added to the database, the next thing you may want do is view to see what is added. The code is very similar. This code bellow displays the fields. view.asp <% Dim Conn Dim Rs Dim sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") http://www.aspnetcenter.com
Page 2 of 2
ASP and Java Script reference Page 3 of 3 Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT name, comments FROM tblFeeds;" Rs.Open sql, Conn Do While not Rs.EOF Response.Write ("============================================="&"
") Response.Write ("Name: " & "" & Rs("name") & "") Response.Write ("
") Response.Write ("Comment: " & "" & Rs("comments") & "") Response.Write ("
") Rs.MoveNext Loop Rs.Close Set Rs = Nothing Set Conn = Nothing %>
Update a record There are more than one way to do things. For this example, we are going to list items from the database so that you can select a record using radio button. Code to list records from tblFeeds Select name to update. <% Dim Conn, Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT * FROM tblFeeds;" Rs.Open sql, Conn Response.Write " User_ID is a unique field which identifies the selected record. When the user selects record and clicks on Select, the information is processed in the toUpdatT.asp file. toUpdateT.asp file allows the user edit the record
<% else Response.Write("Record does not exist") end if Conn.Close Set Conn = Nothing End If %>
Page 5 of 5
After the new data is entered, the next thing is to save the data and the following is the file to do that. updateComment.asp saves the new information <% Dim name,comments, user_id ID = Request.Form("ID") name = Request.Form("name") comments=Request.Form("comments") if name="" OR comments="" then Response.Write "A field was left empty, please try again!" Else Dim Conn ,Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "Update tblFeeds Set name='"& name & "', comments='" & comments &"' WHERE user_id=" & ID Rs.Open sql, Conn Conn.Close Set Rs=Nothing Set Conn = Nothing Response.Write "Successfully Updated" End If %>
http://www.aspnetcenter.com
Page 5 of 5
ASP and Java Script reference
Page 6 of 6
Delete a record We are going to use two files in order to delete a record. First file (toDelete.asp) is to view all the records and the second file (deleteComment.asp) is to delete selected record. Code to list records from tblFeeds
Select name to delete. <% Dim Conn, Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT * FROM tblFeeds;" Rs.Open sql, Conn Response.Write "" Rs.Close Set Rs = Nothing Set Conn = Nothing %> The purpose of this file to view records and select one. Selected record is deleted through deleteComment.asp file. deleteComment.asp file http://www.aspnetcenter.com
Page 6 of 6
ASP and Java Script reference
Page 7 of 7
<% Dim ID ID = Request.Form("ID") if ID="" then Response.Write "You did not select a name to delete!" Else Dim Conn Dim Rs Dim sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "Delete * FROM tblFeeds WHERE user_ID='" & ID Rs.Open sql, Conn Conn.Close Set Conn = Nothing Response.Write "Successfully Deleted" End If %>
Processing forms using ASP You can process HTML forms using these two powerful ASP objects, Response and Request. Response outputs the value to a page and Request retrieves values from an object. Take a look at the following example: Result form name=”userForm” method=”post” action=”userForm.asp”> Enter a user name: Enter a password:
Enter a user name: Enter a password: Send
We just created HTML form and tell the browser to process the form using the file "userForm.asp". The following is userForm.asp file that writes the values from the form.Process form Info You have typed the user name <%=Request.Form("userName")%> and the password http://www.aspnetcenter.com
Page 7 of 7
ASP and Java Script reference <%=Request.Form("password")%>.
Page 8 of 8
Form Processing Example 2 You can store value retrieved from form into variables in order to use these value whatever way you want. Take a look these at this example which is little bit more complex then previous one.
Form Example 2 Result of your information elements http://www.aspnetcenter.com
Page 8 of 8
ASP and Java Script reference elements Your name: Status: Customer Visitor Do you own any of these trucks: Land Cruiser Sequoia 4Runner Highlander Tundra Access Cab MR2 Spyder
Car of Choice: Any comments about Toyota cars:
Page 9 of 9 <% dim name, status, truck, car, comments name=Request.Form("Name") status=Request.Form("status") car=Request.Form("car") comments=Request.Form("comments") truck=Request.Form("truck") %> Your name: <%=name%>
Status: <%=status%>
Your favourite car is: <%=car%>
You currently own these trucks: <%=truck%>
Your comments about Toyota products:<%=comments%>
Submit
Type some information into the form elements and hit submit to see the code in action.
ASP Cookies A cookie is object used to identify the user by his/her computer. Each time the same computer access the page, the cookie will also be retrieved if the expiry date has future value. Creating cookies in asp is very simple. Use Response.Cookie to create a cookie or Request.Cookie to read a cookie. The following example creates a cookie named userName and assigns value: <% Response.Cookies("userName")="user123" %> The example bellow will retrieve the cookie we create above: <% user=Request.Cookies("userName") response.write("Welcome " & user) %> The result of the above example will look like this: welcome user123
http://www.aspnetcenter.com
Page 9 of 9
ASP and Java Script reference Page 10 of 10 The cookie will be deleted after your browser is closed unless you set the expiry date to a future date. The following example will keep the cookie in place for 30 days after setup date: <% Response.Cookies("userName")="user123" Response.Expires("userName").Expires = Date + 30 %>
You can also set a cookie with multiple values like the following: <% Response.Cookies("user")("userName") = "Bell" Response.Cookies("user")("firstName") = "Bellion" Response.Cookies("user")("lastName") = "Briabion" %>
The above Cookie has Keys. When you want retrieve a cookie with multiple values, you will have to loop through the values using for loop statement
Server Variables The following example dominstrates how to retrieve some of the usefull server variables. <% agent = Request.ServerVariables("http_user_agent") 'Gets the browser type IP = Request.ServerVariables ("REMOTE_ADDR") 'Retrieves the user IP Address dnsIP = Request.ServerVariables("remote_host") 'Retrieves the remote host IP Address serverName = Request.ServerVariables("server_name") 'Retrieves the page domain name referer = request.servervariables("http_referer") 'Retrieves the referer url scriptName=request.servervariables("script_name") 'Retrieves current page serverPort=request.servervariables("server_port") 'Retrieves server port serverSoftware=request.servervariables("server_software") 'Retrieves server software Url=request.servervariables("URL") 'Retrieves page url method=request.servervariables("Request_Method") 'Retrieves request mehtod .. get or post %> <% Response.Write("User Agent: "&agent &"
") Response.Write("IP Address: "&IP &"
") Response.Write("Remote host IP: "&dnsIP &"
") Response.Write("Server Domain name: "&serverName &"
") Response.Write("Referer page: "&referer &"
") Response.Write("Script Name: "&scriptName &"
") Response.Write("Server Port: "&serverPort &"
") Response.Write("Server Sortware: "&serverSoftware &"
") Response.Write("Page url: "&Url &"
") Response.Write("Request Method: "&method &"
") %>
Forms
http://www.aspnetcenter.com
Page 10 of 10
ASP and Java Script reference Page 11 of 11 Java script is used to validate forms, display information in text box or when button is clicked, and other nice activities. Form validation means checking that proper information are entered in the form fields before submission. Forms are not of much use in terms of processing and posting when you are limited to HTML and you don't have access to other script such as CGI. This discussion is limited to form validations. If you want learn how create them, check out the html form page. The following example alerts the value entered the text fields:
The only JavaScript concept of this example is OnClick. onClick event handler responds when object is clicked and executes the JavaScript code or function. In this case, it executes alert box . The alert box displays the values in the text boxes. Here is the result of this example: Type your first name: Type your last name:
The following example explains how to select and display:
http://www.aspnetcenter.com
We use onChange event handler, to display alert box with the selected value. OnChange executes the specified JavaScript code or function on the occurance of a change event. In this case, it executes an alert box that displays selected value of the select box. Here is the result of this example: Select one:
Select One
Page 11 of 11
ASP and Java Script reference
Page 12 of 12 Form Validations
The most practical business use of java script is object validations. Making sure that the user's information is as it required. This sample is java script code that checks and alerts if the fields of the form are empty. <SCRIPT LANGUAGE=JAVASCRIPT> function validate(formCheck) //Function with a parameter representing a form name. { if (formCheck.name.value =="") { alert("Please provide your name:"); formCheck.name.focus(); return false; } var mail=formCheck.email.value if (mail.indexOf("@.") == -1) { alert("Please type a valid email:"); formCheck.email.focus(); return false; } return true; }
How it works We created a function called validate which takes a parameter. This parameter represents the form name. Using if statement, we compare the text field name with empty string. If this field is equal to the empty string, then displayed alert box and set the focus to this field. We then return false to avoid the action to be continued. Email field is slightly different. For this field, we check if @ and . are provided. If not, we alert the user, set the focus and return false. If the two controls pass true value, then the function returns true. In this case, onClick event handler returns true and nothing happens. Note: If the form action proceeds to another page, use onSubmit instead of onClick. onSubmit executes the function and continues processing the file defined in the form action
Window object refers to the browser and all the items you can see within it. This lecture explains objects to create pop-up window and properties and methods available. As we all ready explained, properties are sub- objects which is part of another object and methods are ways to do things. Javascript window object properties could be some thing like window name, size, address, etc. Javascript http://www.aspnetcenter.com
Page 12 of 12
ASP and Java Script reference Page 13 of 13 window object methods are things like open, close, scroll, move, resize, alert, etc. The least you could have for window is open a blank window. Here is how you open a blank window: window.open() This opens blank window and uses all the default settings. The best way to display a window with particular size, color, menus, toolbars, objects, etc is to create a function and call it when a linke or object is clicked or onload event. The following example is pop-up window that displays another web site in smaller window: <SCRIPT LANGUAGE="javascript"> function windowE() { window.open('http://tek-tips.com/','winE', 'height=400,width=650,toolbar=no') } Click here
We just created a pop-up window with, height of 400 pixels wide and width of 650 pixels. It displays the web site www.dialtone.com. The name of the window is winE and it will not display a toolbar because we set the toolbar to no. This window is created in function. The only thing appears when this page loads is "click here" in link. This link calls the function that creates the window. Here is the result: Click here
The syntax to remember when creating pop-up window is window.open("url", "windowName", "windowAttributes"). URL you can specify any url, local or foreign. Without a url, blank window will be display. WindowName is the name of the object. WindowAttributes are arguments available such as toolbar, statusbar, scrollbars, etc. Here are list of attributes you can use to customize your pop-up window: width location scrollbars
height directories resizable
toolbar status menubar
The following example uses all the attributes listed above: <script type="text/javascript"> function openwindow() { window.open("http://www.tek-tips.com/","WinC", "toolbar=no,location=no,directories=no, status=no,menubar=no,scrollbars=no,resizable=no, copyhistory=no,width=400,height=400") http://www.aspnetcenter.com
Again this window display web site and sets all attributs to no, which means this window will not display any of those named attributes. Another thing to note is, we using button to call the function that creates the window. Bellow is the result of this example. Click to view it. Page 13 of 13
ASP and Java Script reference copyhistory=no,width=400,height=400") }
Page 14 of 14
The following complete list of properties and methods available for window objects. Property Closed defaultStatus document frames history innerHeight innerWidth length location locationbar menubar name opener outerHeight outerWidth pageXOffset pageYOffset
Description Returns boolean value to determine if a window has been closed Defines the default message displayed in a window's status bar Defines the document to displayed in a window Returns an array containing references to all the named child frames in the current window Returns the history list of visited URLs Returns the height of the window's display area Returns the width of the window's display area Returns the number of frames in the window The URL loaded into the window Windows location bar. It has the property visible. Windows menu bar. Alos has visible property. Sets or returns window's name The name of the window that opened the current window Returns the height of the outer area of the window Returns the width of the outer area of the window Return the X-coordinate of the current window Return the Y-coordinate of the current window
http://www.aspnetcenter.com
Syntax window.closed window.defaultStatus(="message") window.document window.frames(="frameId") window.history window.innerHeight = value window.innerWidth = value window.length window.location window.locationbar.visible=false window.menubar.visible=false window.name window.opener window.outerHeight window.outerWidth window.pageXOffset window.pageYOffset Page 14 of 14
ASP and Java Script reference The name of the window containing this parent particular window Returns boolean value indicating the personalbar visibility of the directories bar Returns boolean value indicating the Scrollbars visibility of the scrollbars bar Self Refers to current window The message displayed in the window's Status status bar Returns boolean value indicating visibility Statusbar of the status bar Returns boolean value indicating visibility Toolbar of the tool bar Top Returns the name of topmost window Window Returns the current window Method Description alert(message) Displays text string on a dialog box back() Loads the previous page in the window blur() Removes the focus from the window Sets the window to capture all events of a captureEvents() specified type Clears the timeout, set with the setTimeout clearTimeout() method close() Closes the window Displays a confirmation dialog box with the confirm(message) text message disableExternalCapture/ Enables/disables external event capturing enableExternalCapture focus() Gives focus to the window forward() Loads the next page in the window Invokes the event handler for the specified handleEvent(event) event moveBy(horizontal, Moves the window by the specified amount vertical) in the horizontal and vertical direction This method moves the window's left edge and top edge to the specified x and y comoveTo(x, y) ordinates open() Opens a window print() Displays the print dialog box prompt(message,defaultV Displays prompt dialog box alue) Release any captured events of the specified releaseEvents(event) type resizeBy(horizantal, Resizes the window by the specified amount vertical) resizeTo(width,height) Resizes the window to the specified width http://www.aspnetcenter.com
Page 15 of 15 window.parent window.personalbar.visible=false window.scrollbars.visible=false self.method window.method="message" window.statusbar.visible=false window.toolbar.visible=false window.top window.[property]or[method] Syntax alert("Type message here") window.back() window.blur() window.captureEvent(eventType) window.clearTimeout(timeoutID) window.close() confirm("type message here") window.disableExternalCapture( )/ window.enableExternalCapture( ) window.focus() window.forward() window.handleEvent(eventID) window.moveBy(HorValue, VerValue) moveTo(Xposition,Yposition) window.open(url, name, attributes) window.print() window.prompt("type message here",value) window.releaseEvents(eventType) window.resizeBy(HorVoue,VerValue) window.resizeTo(widthValue,heightV Page 15 of 15
ASP and Java Script reference and height Scrolls the window to the supplied coscroll(x, y) ordinates Scrolls the window's content area by the scrollBy(x, y) specified number of pixels Scrolls the window's content area by the scrollTo(x, y) specified number of cordinates setInterval(expression, Evaluates the expression every time time) milliseconds stop() Stops the windows from loading
Page 16 of 16 alue) window.scroll(xVlue,yValue) window.scrollBy(HorVlue,VerValue) window.scrollTo(xVlue,yValue) window.setIntervals(expression,millis econds) window.stop()
Working Example -Order & Credit card validation Select Toppings: Extra Cheese Select Pizza Type:
Beef
Personal
Onion
Price
Pineapple
$8.04
Pepper Anchovy Payment Method Cash
Card Number: Expiration Date:
3
2006 Clear
Your order: Personal Pizza w ith Cheese, Pineapple and Peppe Total Price $8.04 Paid w ith Cash
www.clik.to/program
Close | Home Note: This code does not check credit card expiry date. Cancelled or not issued credits cards are still valid and cannot be validated using JavaScript. <script type="text/javascript"> http://www.aspnetcenter.com
Page 16 of 16
ASP and Java Script reference Working Example -Validate user & password User Name: Password:
<script language="javascript"> _<"; function checkPassword() { password=document.form.password.value; user =document.form.user.value; if(user.length<4) { alert("User name must be 4 or more characters"); document.form.user.focus(); return false; } else if(password.length<8) { http://www.aspnetcenter.com
Page 21 of 21
ASP and Java Script reference
Page 22 of 22
alert("Password must be 8 or more characters long"); document.form.password.focus(); return false; } for(var i = 0; i < invalidChars.length; i++) { if(password.indexOf(invalidChars.charAt(i)) != -1 || user.indexOf(invalidChars.charAt(i))!=-1) { alert("Please enter valid characters only such as, 1-9, a-z, A-Z"); return false; } } alert("Right information was provided. User name: "+user+"\n Password: "+password); } //--> <Strong>Working Example -Validate user & password
Page 22 of 22
ASP and Java Script reference i = cint(i) val = i mod 2 if val = 1 then color="white" else color="lightgrey" end if i = i+1
Page 23 of 23
<% bordercolor="LIGHTGREY"> //ur data <% rs.MoveNext Loop Conn.close %>
VB Script Form Validation Form validation involves checking if the required information is provided by the user. We can make sure to see if a field is empty, figure out type of value provided, count number of characters or value, can check if special character(s) is present and more. Here is a syntax for checking a field value. if form.name.value="" then msgbox"Please enter name". This checks if the name field is empty and informs the user. if len(form.name.value) < 2 or len(form.txtname.value)>50 then msgbox "Too short or too long". This checks if the lenth of the value provided is less than 2 characters or more than 50 characters and if so prompts message. if instr(form.txtemail.value,"@") = 0 then msgbox("Invalid e-mail"). This checks if @ is present and prompts message if not. if (Not (IsNumeric(form.txtage.value)) then msgbox"Invalid age". This check if the value is not numeric and prompts message if so. To check if it's numeric, do not specify NOT before IsNumeric. form.txtage.focus . This put focus in the text box, age. form.txtage.select . This highlights the text in the text box. The following example is form that validates provided information before it summits. See forms to learning how to do forms. <script language = "vbscript"> sub cmdUserInfo_OnClick() if len(form.txtName.value)<2 or IsNumeric(form.txtName.value)then http://www.aspnetcenter.com
Notice we did not call the sub procedure onclick() method. We name the command button cmdUserInfo which is the name of the sub procedure that has the validation code. This is an event driven procedure that evaluates it's code when the command button is clicked. Page 23 of 23
ASP and Java Script reference msgbox("Type a valid name") form.txtName.focus form.txtName.select elseif len(form.txtAge.value) <2 or Not IsNumeric(form.txtAge.value) then msgbox("Type a valid age") form.txtAge.focus form.txtAge.select elseif form.txtEmail.value="" then msgbox("Enter a valid email") form.txtEmail.focus form.txtEmail.select elseif instr(form.txtEmail.value,"@") = 0 then msgbox("Type a valid email") form.txtEmail.focus else msgbox"Success!" end if end sub
http://www.aspnetcenter.com
Page 24 of 24 When the button is clicked, if statement checks the name field to make sure 2 characters or more is entered and the values are not numbers. If the name field is ok, then we do the age field same making sure that two numbers are entered. If the name, and age field are ok, then we check the email field to make sure that the @ sign is present. We prompt a proper error message each time that the condition is not true. We use table to make the form objects look friendly. The txt added to the each text field name and cmd added to the button are standard naming convention for visual basic. Here is the result of this example Name: Age: Email: reset
Submit
Page 24 of 24
Page 1 of 1
Adding Data to Access Database To add data to a database table, you need an existing database plus the table to add the data to. Let us assume that you have Access Database file name FeedBack.mdb in the same folder as this file with the following table: tblFeeds Field Name Data Type Field Size user_id
Autonumber 8
Name
Text
45
Comments Text 200<> To add a data to the table tblFeeds, first we will have to create a form to enter the data and then open the database and make the connection. Here is the form to enter the data before adding to the database: Form Example
Page 1 of 1
ASP and Java Script reference
Page 2 of 2
Now, you insert the new record to the database using the information provided through EnterData.asp. Here is the code to do this: save.asp <% Dim Conn Dim Rs Dim sql 'Create an ADO connection and recordset object Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") 'Set an active connection and select fields from the database Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT name, comments FROM tblFeeds;" 'Set the lock and cursor type Rs.CursorType = 2 Rs.LockType = 3 Rs.Open sql, Conn
'Open the recordset with sql query
Rs.AddNew 'Prepare the database to add a new record and add Rs.Fields("name") = Request.Form("name") Rs.Fields("comments") = Request.Form("comments") Rs.Update 'Save the update Rs.Close Set Rs = Nothing Set Conn = Nothing %> The third field (user_no) of our table is auto generated and sequentially will accommulate it self on each addition of new record. You redirect the user to another page when the record is added to the database using <%response.redirect("view.asp")%> After the data is added to the database, the next thing you may want do is view to see what is added. The code is very similar. This code bellow displays the fields. view.asp <% Dim Conn Dim Rs Dim sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") http://www.aspnetcenter.com
Page 2 of 2
ASP and Java Script reference Page 3 of 3 Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT name, comments FROM tblFeeds;" Rs.Open sql, Conn Do While not Rs.EOF Response.Write ("============================================="&"
") Response.Write ("Name: " & "" & Rs("name") & "") Response.Write ("
") Response.Write ("Comment: " & "" & Rs("comments") & "") Response.Write ("
") Rs.MoveNext Loop Rs.Close Set Rs = Nothing Set Conn = Nothing %>
Update a record There are more than one way to do things. For this example, we are going to list items from the database so that you can select a record using radio button. Code to list records from tblFeeds Select name to update. <% Dim Conn, Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT * FROM tblFeeds;" Rs.Open sql, Conn Response.Write " User_ID is a unique field which identifies the selected record. When the user selects record and clicks on Select, the information is processed in the toUpdatT.asp file. toUpdateT.asp file allows the user edit the record
<% else Response.Write("Record does not exist") end if Conn.Close Set Conn = Nothing End If %>
Page 5 of 5
After the new data is entered, the next thing is to save the data and the following is the file to do that. updateComment.asp saves the new information <% Dim name,comments, user_id ID = Request.Form("ID") name = Request.Form("name") comments=Request.Form("comments") if name="" OR comments="" then Response.Write "A field was left empty, please try again!" Else Dim Conn ,Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "Update tblFeeds Set name='"& name & "', comments='" & comments &"' WHERE user_id=" & ID Rs.Open sql, Conn Conn.Close Set Rs=Nothing Set Conn = Nothing Response.Write "Successfully Updated" End If %>
http://www.aspnetcenter.com
Page 5 of 5
ASP and Java Script reference
Page 6 of 6
Delete a record We are going to use two files in order to delete a record. First file (toDelete.asp) is to view all the records and the second file (deleteComment.asp) is to delete selected record. Code to list records from tblFeeds
Select name to delete. <% Dim Conn, Rs, sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "SELECT * FROM tblFeeds;" Rs.Open sql, Conn Response.Write "" Rs.Close Set Rs = Nothing Set Conn = Nothing %> The purpose of this file to view records and select one. Selected record is deleted through deleteComment.asp file. deleteComment.asp file http://www.aspnetcenter.com
Page 6 of 6
ASP and Java Script reference
Page 7 of 7
<% Dim ID ID = Request.Form("ID") if ID="" then Response.Write "You did not select a name to delete!" Else Dim Conn Dim Rs Dim sql Set Conn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("FeedBack.mdb") sql= "Delete * FROM tblFeeds WHERE user_ID='" & ID Rs.Open sql, Conn Conn.Close Set Conn = Nothing Response.Write "Successfully Deleted" End If %>
Processing forms using ASP You can process HTML forms using these two powerful ASP objects, Response and Request. Response outputs the value to a page and Request retrieves values from an object. Take a look at the following example: Result form name=”userForm” method=”post” action=”userForm.asp”> Enter a user name: Enter a password:
Enter a user name: Enter a password: Send
We just created HTML form and tell the browser to process the form using the file "userForm.asp". The following is userForm.asp file that writes the values from the form.
Page 7 of 7
ASP and Java Script reference <%=Request.Form("password")%>.
Page 8 of 8
Form Processing Example 2 You can store value retrieved from form into variables in order to use these value whatever way you want. Take a look these at this example which is little bit more complex then previous one.
This example process basic form elements
Code for formProcess.asp Result of the above code This example process basic formPage 8 of 8
ASP and Java Script reference elements Your name: Status: Customer Visitor Do you own any of these trucks: Land Cruiser Sequoia 4Runner Highlander Tundra Access Cab MR2 Spyder
Car of Choice: Any comments about Toyota cars:
Page 9 of 9 <% dim name, status, truck, car, comments name=Request.Form("Name") status=Request.Form("status") car=Request.Form("car") comments=Request.Form("comments") truck=Request.Form("truck") %> Your name: <%=name%>
Status: <%=status%>
Your favourite car is: <%=car%>
You currently own these trucks: <%=truck%>
Your comments about Toyota products:<%=comments%>
Submit
Type some information into the form elements and hit submit to see the code in action.
ASP Cookies A cookie is object used to identify the user by his/her computer. Each time the same computer access the page, the cookie will also be retrieved if the expiry date has future value. Creating cookies in asp is very simple. Use Response.Cookie to create a cookie or Request.Cookie to read a cookie. The following example creates a cookie named userName and assigns value: <% Response.Cookies("userName")="user123" %> The example bellow will retrieve the cookie we create above: <% user=Request.Cookies("userName") response.write("Welcome " & user) %> The result of the above example will look like this: welcome user123
http://www.aspnetcenter.com
Page 9 of 9
ASP and Java Script reference Page 10 of 10 The cookie will be deleted after your browser is closed unless you set the expiry date to a future date. The following example will keep the cookie in place for 30 days after setup date: <% Response.Cookies("userName")="user123" Response.Expires("userName").Expires = Date + 30 %>
You can also set a cookie with multiple values like the following: <% Response.Cookies("user")("userName") = "Bell" Response.Cookies("user")("firstName") = "Bellion" Response.Cookies("user")("lastName") = "Briabion" %>
The above Cookie has Keys. When you want retrieve a cookie with multiple values, you will have to loop through the values using for loop statement
Server Variables The following example dominstrates how to retrieve some of the usefull server variables. <% agent = Request.ServerVariables("http_user_agent") 'Gets the browser type IP = Request.ServerVariables ("REMOTE_ADDR") 'Retrieves the user IP Address dnsIP = Request.ServerVariables("remote_host") 'Retrieves the remote host IP Address serverName = Request.ServerVariables("server_name") 'Retrieves the page domain name referer = request.servervariables("http_referer") 'Retrieves the referer url scriptName=request.servervariables("script_name") 'Retrieves current page serverPort=request.servervariables("server_port") 'Retrieves server port serverSoftware=request.servervariables("server_software") 'Retrieves server software Url=request.servervariables("URL") 'Retrieves page url method=request.servervariables("Request_Method") 'Retrieves request mehtod .. get or post %> <% Response.Write("User Agent: "&agent &"
") Response.Write("IP Address: "&IP &"
") Response.Write("Remote host IP: "&dnsIP &"
") Response.Write("Server Domain name: "&serverName &"
") Response.Write("Referer page: "&referer &"
") Response.Write("Script Name: "&scriptName &"
") Response.Write("Server Port: "&serverPort &"
") Response.Write("Server Sortware: "&serverSoftware &"
") Response.Write("Page url: "&Url &"
") Response.Write("Request Method: "&method &"
") %>
Forms
http://www.aspnetcenter.com
Page 10 of 10
ASP and Java Script reference Page 11 of 11 Java script is used to validate forms, display information in text box or when button is clicked, and other nice activities. Form validation means checking that proper information are entered in the form fields before submission. Forms are not of much use in terms of processing and posting when you are limited to HTML and you don't have access to other script such as CGI. This discussion is limited to form validations. If you want learn how create them, check out the html form page. The following example alerts the value entered the text fields:
The only JavaScript concept of this example is OnClick. onClick event handler responds when object is clicked and executes the JavaScript code or function. In this case, it executes alert box . The alert box displays the values in the text boxes. Here is the result of this example: Type your first name: Type your last name:
The following example explains how to select and display:
http://www.aspnetcenter.com
We use onChange event handler, to display alert box with the selected value. OnChange executes the specified JavaScript code or function on the occurance of a change event. In this case, it executes an alert box that displays selected value of the select box. Here is the result of this example: Select one:
Select One
Page 11 of 11
ASP and Java Script reference
Page 12 of 12 Form Validations
The most practical business use of java script is object validations. Making sure that the user's information is as it required. This sample is java script code that checks and alerts if the fields of the form are empty. <SCRIPT LANGUAGE=JAVASCRIPT> function validate(formCheck) //Function with a parameter representing a form name. { if (formCheck.name.value =="") { alert("Please provide your name:"); formCheck.name.focus(); return false; } var mail=formCheck.email.value if (mail.indexOf("@.") == -1) { alert("Please type a valid email:"); formCheck.email.focus(); return false; } return true; }
How it works We created a function called validate which takes a parameter. This parameter represents the form name. Using if statement, we compare the text field name with empty string. If this field is equal to the empty string, then displayed alert box and set the focus to this field. We then return false to avoid the action to be continued. Email field is slightly different. For this field, we check if @ and . are provided. If not, we alert the user, set the focus and return false. If the two controls pass true value, then the function returns true. In this case, onClick event handler returns true and nothing happens. Note: If the form action proceeds to another page, use onSubmit instead of onClick. onSubmit executes the function and continues processing the file defined in the form action
Window object refers to the browser and all the items you can see within it. This lecture explains objects to create pop-up window and properties and methods available. As we all ready explained, properties are sub- objects which is part of another object and methods are ways to do things. Javascript window object properties could be some thing like window name, size, address, etc. Javascript http://www.aspnetcenter.com
Page 12 of 12
ASP and Java Script reference Page 13 of 13 window object methods are things like open, close, scroll, move, resize, alert, etc. The least you could have for window is open a blank window. Here is how you open a blank window: window.open() This opens blank window and uses all the default settings. The best way to display a window with particular size, color, menus, toolbars, objects, etc is to create a function and call it when a linke or object is clicked or onload event. The following example is pop-up window that displays another web site in smaller window: <SCRIPT LANGUAGE="javascript"> function windowE() { window.open('http://tek-tips.com/','winE', 'height=400,width=650,toolbar=no') } Click here
We just created a pop-up window with, height of 400 pixels wide and width of 650 pixels. It displays the web site www.dialtone.com. The name of the window is winE and it will not display a toolbar because we set the toolbar to no. This window is created in function. The only thing appears when this page loads is "click here" in link. This link calls the function that creates the window. Here is the result: Click here
The syntax to remember when creating pop-up window is window.open("url", "windowName", "windowAttributes"). URL you can specify any url, local or foreign. Without a url, blank window will be display. WindowName is the name of the object. WindowAttributes are arguments available such as toolbar, statusbar, scrollbars, etc. Here are list of attributes you can use to customize your pop-up window: width location scrollbars
height directories resizable
toolbar status menubar
The following example uses all the attributes listed above: <script type="text/javascript"> function openwindow() { window.open("http://www.tek-tips.com/","WinC", "toolbar=no,location=no,directories=no, status=no,menubar=no,scrollbars=no,resizable=no, copyhistory=no,width=400,height=400") http://www.aspnetcenter.com
Again this window display web site and sets all attributs to no, which means this window will not display any of those named attributes. Another thing to note is, we using button to call the function that creates the window. Bellow is the result of this example. Click to view it. Page 13 of 13
ASP and Java Script reference copyhistory=no,width=400,height=400") }
Page 14 of 14
The following complete list of properties and methods available for window objects. Property Closed defaultStatus document frames history innerHeight innerWidth length location locationbar menubar name opener outerHeight outerWidth pageXOffset pageYOffset
Description Returns boolean value to determine if a window has been closed Defines the default message displayed in a window's status bar Defines the document to displayed in a window Returns an array containing references to all the named child frames in the current window Returns the history list of visited URLs Returns the height of the window's display area Returns the width of the window's display area Returns the number of frames in the window The URL loaded into the window Windows location bar. It has the property visible. Windows menu bar. Alos has visible property. Sets or returns window's name The name of the window that opened the current window Returns the height of the outer area of the window Returns the width of the outer area of the window Return the X-coordinate of the current window Return the Y-coordinate of the current window
http://www.aspnetcenter.com
Syntax window.closed window.defaultStatus(="message") window.document window.frames(="frameId") window.history window.innerHeight = value window.innerWidth = value window.length window.location window.locationbar.visible=false window.menubar.visible=false window.name window.opener window.outerHeight window.outerWidth window.pageXOffset window.pageYOffset Page 14 of 14
ASP and Java Script reference The name of the window containing this parent particular window Returns boolean value indicating the personalbar visibility of the directories bar Returns boolean value indicating the Scrollbars visibility of the scrollbars bar Self Refers to current window The message displayed in the window's Status status bar Returns boolean value indicating visibility Statusbar of the status bar Returns boolean value indicating visibility Toolbar of the tool bar Top Returns the name of topmost window Window Returns the current window Method Description alert(message) Displays text string on a dialog box back() Loads the previous page in the window blur() Removes the focus from the window Sets the window to capture all events of a captureEvents() specified type Clears the timeout, set with the setTimeout clearTimeout() method close() Closes the window Displays a confirmation dialog box with the confirm(message) text message disableExternalCapture/ Enables/disables external event capturing enableExternalCapture focus() Gives focus to the window forward() Loads the next page in the window Invokes the event handler for the specified handleEvent(event) event moveBy(horizontal, Moves the window by the specified amount vertical) in the horizontal and vertical direction This method moves the window's left edge and top edge to the specified x and y comoveTo(x, y) ordinates open() Opens a window print() Displays the print dialog box prompt(message,defaultV Displays prompt dialog box alue) Release any captured events of the specified releaseEvents(event) type resizeBy(horizantal, Resizes the window by the specified amount vertical) resizeTo(width,height) Resizes the window to the specified width http://www.aspnetcenter.com
Page 15 of 15 window.parent window.personalbar.visible=false window.scrollbars.visible=false self.method window.method="message" window.statusbar.visible=false window.toolbar.visible=false window.top window.[property]or[method] Syntax alert("Type message here") window.back() window.blur() window.captureEvent(eventType) window.clearTimeout(timeoutID) window.close() confirm("type message here") window.disableExternalCapture( )/ window.enableExternalCapture( ) window.focus() window.forward() window.handleEvent(eventID) window.moveBy(HorValue, VerValue) moveTo(Xposition,Yposition) window.open(url, name, attributes) window.print() window.prompt("type message here",value) window.releaseEvents(eventType) window.resizeBy(HorVoue,VerValue) window.resizeTo(widthValue,heightV Page 15 of 15
ASP and Java Script reference and height Scrolls the window to the supplied coscroll(x, y) ordinates Scrolls the window's content area by the scrollBy(x, y) specified number of pixels Scrolls the window's content area by the scrollTo(x, y) specified number of cordinates setInterval(expression, Evaluates the expression every time time) milliseconds stop() Stops the windows from loading
Page 16 of 16 alue) window.scroll(xVlue,yValue) window.scrollBy(HorVlue,VerValue) window.scrollTo(xVlue,yValue) window.setIntervals(expression,millis econds) window.stop()
Working Example -Order & Credit card validation Select Toppings: Extra Cheese Select Pizza Type:
Beef
Personal
Onion
Price
Pineapple
$8.04
Pepper Anchovy Payment Method Cash
Card Number: Expiration Date:
3
2006 Clear
Your order: Personal Pizza w ith Cheese, Pineapple and Peppe Total Price $8.04 Paid w ith Cash
www.clik.to/program
Close | Home Note: This code does not check credit card expiry date. Cancelled or not issued credits cards are still valid and cannot be validated using JavaScript. <script type="text/javascript"> http://www.aspnetcenter.com
Page 16 of 16
ASP and Java Script reference Working Example -Validate user & password User Name: Password:
<script language="javascript"> _<"; function checkPassword() { password=document.form.password.value; user =document.form.user.value; if(user.length<4) { alert("User name must be 4 or more characters"); document.form.user.focus(); return false; } else if(password.length<8) { http://www.aspnetcenter.com
Page 21 of 21
ASP and Java Script reference
Page 22 of 22
alert("Password must be 8 or more characters long"); document.form.password.focus(); return false; } for(var i = 0; i < invalidChars.length; i++) { if(password.indexOf(invalidChars.charAt(i)) != -1 || user.indexOf(invalidChars.charAt(i))!=-1) { alert("Please enter valid characters only such as, 1-9, a-z, A-Z"); return false; } } alert("Right information was provided. User name: "+user+"\n Password: "+password); } //--> <Strong>
Page 22 of 22
ASP and Java Script reference i = cint(i) val = i mod 2 if val = 1 then color="white" else color="lightgrey" end if i = i+1
Page 23 of 23
<%
VB Script Form Validation Form validation involves checking if the required information is provided by the user. We can make sure to see if a field is empty, figure out type of value provided, count number of characters or value, can check if special character(s) is present and more. Here is a syntax for checking a field value. if form.name.value="" then msgbox"Please enter name". This checks if the name field is empty and informs the user. if len(form.name.value) < 2 or len(form.txtname.value)>50 then msgbox "Too short or too long". This checks if the lenth of the value provided is less than 2 characters or more than 50 characters and if so prompts message. if instr(form.txtemail.value,"@") = 0 then msgbox("Invalid e-mail"). This checks if @ is present and prompts message if not. if (Not (IsNumeric(form.txtage.value)) then msgbox"Invalid age". This check if the value is not numeric and prompts message if so. To check if it's numeric, do not specify NOT before IsNumeric. form.txtage.focus . This put focus in the text box, age. form.txtage.select . This highlights the text in the text box. The following example is form that validates provided information before it summits. See forms to learning how to do forms. <script language = "vbscript"> sub cmdUserInfo_OnClick() if len(form.txtName.value)<2 or IsNumeric(form.txtName.value)then http://www.aspnetcenter.com
Notice we did not call the sub procedure onclick() method. We name the command button cmdUserInfo which is the name of the sub procedure that has the validation code. This is an event driven procedure that evaluates it's code when the command button is clicked. Page 23 of 23
ASP and Java Script reference msgbox("Type a valid name") form.txtName.focus form.txtName.select elseif len(form.txtAge.value) <2 or Not IsNumeric(form.txtAge.value) then msgbox("Type a valid age") form.txtAge.focus form.txtAge.select elseif form.txtEmail.value="" then msgbox("Enter a valid email") form.txtEmail.focus form.txtEmail.select elseif instr(form.txtEmail.value,"@") = 0 then msgbox("Type a valid email") form.txtEmail.focus else msgbox"Success!" end if end sub
http://www.aspnetcenter.com
Page 24 of 24 When the button is clicked, if statement checks the name field to make sure 2 characters or more is entered and the values are not numbers. If the name field is ok, then we do the age field same making sure that two numbers are entered. If the name, and age field are ok, then we check the email field to make sure that the @ sign is present. We prompt a proper error message each time that the condition is not true. We use table to make the form objects look friendly. The txt added to the each text field name and cmd added to the button are standard naming convention for visual basic. Here is the result of this example Name: Age: Email: reset
Submit
Page 24 of 24
Related Documents
Adding Data To Access Database
November 2019 9
Adding Data To Access Database
November 2019 10
Database Access
November 2019 9
Database Remote Access (student)
April 2020 6
Adding Ed To Words
May 2020 6