How to add, delete and modify SQL server with asp as the interface.

World Wide Web (WWW)

URL-Uniform Resource Locator (Uniform Resource Locator)

Hypertext Transfer Protocol (Hypertext Transfer Protocol)

Hypertext Markup Language (Hypertext Markup Language)

XML- Extensible Markup Language (Extensible Markup Language)

Odbc- Open Database Connection (Open Database Connection Standard)

Dynamic link library (DLL)

DAO-data access object (data connection object)

RDO-remote data objects (remote data objects)

Ado-Microsoft ActiveX data object (dynamic data object)

SQL Structured Query Language (Structured Query Language)

Universal gateway interface (common gateway interface)

Application programming interface

ASP-Dynamic Server Page (Dynamic Service Page)

Personal homepage (personal homepage)

JSP-Java server page

Cold Fusion

SSI- server-side inclusion (server-side inclusion)

Document object model (document object model)

Iis-internet information server (network information service)

IP-Internet Protocol (IP)

Transmission control protocol (TCP)

IE-Internet Explorer (browser)

CSS-Cascading Style Sheet (Cascading Style Sheet)

VBScript-Microsoft Visual Basic Script Version

integrated services digital network (ISDN)

net

ftp

Simple mail transfer protocol

MMC-Management Console

web design

The network provides a graphical user interface. When browsing documents on the Internet, these documents and their links form a huge information network.

Web originated from March 1989, which is a distributed hypermedia system with master-slave structure developed by CERN (Particle Physics Rebel Laboratory).

The network is an ocean of information resources, and there are three elements to ensure that people can swim in this ocean conveniently:

Uniform resource naming scheme (URL)

L the way to access resources, that is, the protocol (HTTP)

L The method of free access between resources, namely Hypertext (HTTP)

With the development of Internet, Web technology has gone through three stages:

L the first generation, providing management and access (display) to static documents (information)

The second generation is combined with database system to provide access and display of dynamic documents (information).

In addition to dynamic document generation and access, the third generation also provides online transaction processing capability based on Web.

Disadvantages of static web pages:

1. Backstage database is not supported.

2. Unable to update website information in time and effectively.

3. The dynamic display effect cannot be achieved.

ASP is a Web application development technology which was launched by Microsoft at the end of 1996 and runs on the server side. ASP is neither a language nor a development tool, but an easy-to-learn and easy-to-use server-side scripting language environment contained in IIS/PWS, which can integrate scripting language into HTML homepage. Its main function is to provide a powerful method and technology for generating dynamic and interactive Web server applications.

The features of ASP are as follows:

1.ASP can mix HTML, DHTML, Java applet, ActiveX, VBScript,

2. There is no need to compile, it is in plain text format.

3. Good security

4. Object-oriented

5. Extensible server-side functions

6. Extensible scripting language

7. Independent of browser

8.ASP provides six built-in global objects for developers to call directly without declaration.

9.ASP provides five important server components.

The process of accessing an HTML page:

1. Enter the URL address of the HTML file in the address bar of the client browser, and then press Enter to send a web page request.

2. The browser sends a web page request to IIS/PWS server.

3.IIS/PWS server receives the request and judges that it is an HTML file request by extension. Htm or. html。

The 4.IIS/PWS server takes the corresponding HTML file from the disk or memory and sends it back to the browser.

The 5.HTML file is interpreted by the user's browser, and the result is displayed in the browser window.

The process of accessing ASP web pages:

1. Enter the URL address of the ASP file to be requested in the address bar of the client browser, and then press Enter to send the ASP request.

2. The browser sends an ASP page request to IIS/PWS server.

The 3.IIS/PWS server receives the request and identifies the asp file according to the extension. ASP。

4.IIS/PWS server obtains the corresponding ASP file from disk or memory.

5.IIS/PWS server sends ASP files to a specific file library named asp.dll, which is similar to engine engine.

6.asp.dll engine interprets ASP files from beginning to end, and generates corresponding dynamic HTML pages according to the command requirements in ASP files.

The 7.HTML page is sent back to the client browser.

8. Use the client browser to interpret and execute the HTML page, and display the results on the client browser.

Compared with HTML, ASP has three important features:

1.ASP can contain a server-side scripting language, that is, a web page that can create dynamic content.

2.ASP can make scripts more powerful by using built-in objects.

3.ASP uses components to enable users to work with databases, send e-mails or access file systems.

Six built-in objects provided by ASP:

1. Response: Send information to the browser.

2.Request: used to read the request information from the browser, and can also be used to read the information of the HTML form.

3. Server: Allows scripts to generate instances of active server components.

4. Application: used to store and read the application information that users like. For example, this object can be used to transfer information used by users between different connectors of a website.

5.Session: It is used to store and read the session information of a specific connector, for example, it can store the access information of the connector to the website.

6.ObjectContext: used to control ASP transactions and managed by Microsoft Transaction Server (MTS).

Five important components provided by ASP

1.ADO: used to read or store data in Microsoft SQL Server and Microsoft Access databases.

2. File system: Provides methods and properties that can be used to access the computer file system.

3. Content link: Display different HTML content according to the performance of different browsers.

4. Browser function: used to link different HTML pages to make them easier to operate.

5. Advertisement rotator: used to display banner advertisements on web pages, which can control the display frequency of different advertisements.

Basic knowledge of ASP

The extension of asp file is. ASP, which can include HTML markup, text and scripting language, has scripting language code contained in ""

In VBScript: comments can be Rem,' and so on.

& lt% Option Explicit%> means that all variables in VBScript must be declared before they can be used, otherwise an error will occur.

This file contains:

& lt！ -# include file = " myfirstasp . ASP "-& gt；

& lt！ -# include file = " myfirstxt . txt "-& gt；

Understand the basic usage of VBScript script;

1. Operator

2. Data type

3. Constants, variables and arrays

4. Conditional statement (if ... then end if, if ... then ... otherwise end if)

5. Loop statement (for ... Next, for each ... Next, do a while ... loop)

6. Process

7. Basic functions: divided into mathematics, character string, date and time, type conversion, formatting and judgment.

A) Mathematics: abs (), sqr (), rnd (random number), round (rounding) …

B) String classes: len (), left (), right (), mid (), strverse (), split (), trim ().

C) date and time category: datevalue (), timevalue (), year (), month, day (), hour (), minute (), second (), timeserial (), date (), time (), now (), datepart.

D) Type conversion classes: cdate (), cint (), clng (), CSTR (), STR (), val (), int (), fix ().

E) Format category: format date and time, format number and format percentage. ...

F) judgment classes: isdate, isempty, isnull, isnumeric, isobject …

G) Information dialog function: InputBox, MsgBox…

ASP built-in object description:

Response:

Syntax format: Response. Set | Property | method (variable)

The attributes of the response are: buffer, character set, content type, expiration, absolute expiration and status.

The response methods are: add header, appendtolog, binarywrite, clear, end, flush, redirect and write.

Response set: Cookie.

Request:

Syntax format:

Request. Set (variable)

Collection: form, querystring, servervariable, cookie, client certificate.

1.Form: Syntax format: request.form (element) [(index). Count].

2.QueryString: syntax format: request. querystring (variable) [(index) |。 Count]

3.ServerVariable: syntax format: Request. ServerVariable (server environment variable)

4.Cookie: syntax format: request. Cookie [(key)|. Attribute]

Use Cookies collection:

Response. Cookies(CookieName)[(key)|。 attribute]=CookieValue

Request. Cookies(CookieName)[(key)|。 Attribute]

Application:

Syntax format: application. Property | Method | Event | Collection.

Application methods: Lock (), UnLock ()

Events of application: application _ onstart, application _ onend.

A collection of applications: content, static objects.

Attribute of application: application ("variable name") = variable name or setting application ("object instance name") = object.

In fact, it is to define global variables for multiple users to use.

Instance name (in turn, restore)

Session:

Syntax format: conversation. Method | Event | Collection | Properties

Session method: above () is used to clear the Session object.

Session events: Session_OnStart, session _ ended.

Conversation sets: content, static objects

Session property: SessionID, timeout.

Users can also customize the Session attribute, which is actually a variable, that is, the session variable we mentioned earlier.

session(" VariableName ")= VariableName

Setsession ("objectname") = objectname (in turn, it is to restore variables).

Life Cycle and Propagation of Network Variables

Global variables, session variables, web page variables

Pass variables between web pages by request. QueryString collection.

An application or session object that uses ASP passes variables between web pages.

Comparison between sessions and applications:

1. Both allow users to define properties, that is, define application variables and session variables.

2. Both can be used to access variables and object instances in an object.

3. Both have life cycles. The session starts when the new connector is connected for the first time, and ends when the connector has not requested any information for a period of time. When IIS/PWS starts to execute and the first connector appears, the application starts, and when there is no connector request information or IIS/PWS closes within a certain period of time, the application ends.

4. Both are objects used by ASP files. The application object is the object used by all web page connectors, and the session object is the only object of each connector.

5. Both of them have OnStart and OnEnd event codes, but they happen at different times. When an Application and a Session application are started at the same time, ASP will execute the Application_OnStart first, and then the Session _ OnStart. If the application and session are terminated at the same time, the Session_OnEnd will be executed first, and then the Application_OnEnd will be executed.

Initialization of Global.asa file and application:

Global.asa file:

Website counters or online statistics, etc.

Server objects, ODBC and ADO components;

Grammatical format. Property | method

Server properties: ScriptTimeout

Server methods: createobject, htmlencode, urlencode, mappath.

The server's CreateObject method is the most practical and powerful function in ASP. It can create instances of ActiveX components registered on the server and is the source of creating ActiveX objects.

Server. CreateObject ("component name or component registration name")

Set myconn = server.createobject ("adodb.connection") connects object instances.

Set myads = server.createobject ("mswc.adrotator") advertising object instance.

ODBC: It is a universal interface for connecting databases and a database connection standard developed and advocated by Microsoft.

ADO: It is a built-in component of ASP server and a very important component to realize Web database operation.

ADO component is a powerful component, which consists of the following seven interface components:

1. Connection component

2. Recordset component

3. Command component

4. Parameter object

5. Attribute object

6. Wrong object

7. Field objects

The first three components need to be created using a server. CreateObject methods before you can use them.

Connection: Syntax format: set connection object name = server.createobject ("adodb.connection").

For example, set conn = server.createobject ("adodb.connection").

Method of connecting objects:

1. Open format: name of connection object. Open the Connection Database Information String [; User account] [; User password]. This method is used to open a database and establish a connection with it. There are two ways:

A) Open the database with DSN method: This method first establishes the data source name (DSN) of the corresponding database with ODBC, and then opens the corresponding database through the data source name. The following code can open the database book.mdb corresponding to the data source name qhbook. & lt% Set Conn=Server。 CreateObject("ADODB。 connection”)Conn . Open”qh book； mydatazhxing " % & gt

B) Open the database with full path DSN method. If you have not established an ODBC data source before connecting to the database, you can also establish a connection to the database by directly specifying the ODBC driver name. At this time, the "connection database information string" must start with driver and become "Driver = {ODBC Driver name }；; Dbq = "& server. MapPath ("database name"). As follows:

& lt% Set Conn=Server。 CreateObject("ADODB。 Connect ")

conn . Open " Driver = { Microsoft Access Driver(*。 MDB)}； Dbq = "& server. MapPath(" book . MDB ")% & gt；

If it is SQL Server type, the driver is Driver = {SQL Server }；; If it is Oracle type, then: driver = {Microsoft odbc for Oracle}.

2. Close: Close the established connection object and its related objects. & lt% connect . Close % & gt； & lt% Set Conn = nothing % & gt

3. Implementation:

Format 1: name of the connection object. To execute (an SQL instruction).

Format 2: Name of the connection object. Execute (data table name).

As follows:

& lt% SQL = " Select * from book item " Set RS = Conn . Execute(SQL)% & gt； Or:

& lt% Set RS = Conn . Execute(" book item ")% & gt；

4. Tenes, Bei Jing

5. Entrusting affairs

6. Roll back the transaction

Recordset component:

The above knowledge of Conn.Open method opens and connects the corresponding database, which usually consists of one or more tables, so to access the data tables in it, a Recordset object must be established before various operations can be performed on it.

1. Create a recordset object.

The method is as follows:

set Recordset object name = server . createobject(" adodb . Recordset ")。

Set recordset object name = connection. Execute ("data table name")

Set recordset object name = connection. Execute (SQL instruction)

The following code:

& lt%

Set Conn = server. CreateObject("ADODB。 Connect ")

Open "qhbookmydatazhxing"

Set rs=Conn.Execute("bookitem ")

%> or

& lt%

Set Conn = server. CreateObject("ADODB。 Connect ")

conn . Open " Driver = { Microsoft Access Driver(*。 MDB)}； Dbq = "& server. MapPath("book.mdb ")

Set rs = server. CreateObject("ADODB。 Recordset ")

Exec=Select * from bookitem

Ruby Connecticut Open Executive Committee

% & gt

Method of Recordset Object:

Open: format 1: rs.openexec, conn, 1, 1 (recordset type (0, 1, 2,3), locking type (1,2,4)).

Format 2: rs.openbookitem, Connecticut, 1, 1.

Recordset type: 0: read-only, and the current data record can only be moved down.

1: read-only, and the current data record can be moved freely.

2. Read and write, and the current data record can be moved freely.

3: Readable, the current data record can be moved freely (Note)

Lock Type: 1: Default value, used to open read-only data records.

2. Pessimistic locking

3. Optimistic locking

4. Optimistic batch locking

More complicated ~

Recordset.open Source，ActiveConnection，CursorType，LockType，Options

The meaning of each parameter is:

Source: It can be SQL query, data table or stored procedure.

ActiveConnection: It can be an open connection or a connection string.

CursorType: cursor type, which is defined as follows

0(adopenforward): You can only move forward in the records in the recordset, but it is the fastest.

1(adOpenKeyset): you can move freely in the recordset, and other users' modifications to the record are visible, but the records added by other users are invisible, and the deleted record field values cannot be used.

2(adOpenDynamic): You can move freely in the recordset, and all records added, deleted and modified by other users can be seen, but the speed is the slowest.

3(adOpenStatic): You can move freely in the recordset, and the records added, deleted and modified by other users are invisible.

Lock Type: Concurrency Control

0(adLockReadOnly): Recordset records are read-only.

1(adlock pessimism): As long as the recordset is open, no one else can edit the records in the recordset.

2 (adlockoptimal): When a record in the recordset is updated, the record is locked.

3 (adlockbatchoptive): Lock when updating records in batch mode.

Option: Specify the type of source transfer command.

1(adCmdText):SQL statement

2(adCmdTable): the name of the data table.

4(adCmdStoredProc): stored procedure

8(ADC unknown): Unknown type.

Closing method

& lt% rs。 close

Set rs = nothing % & gt

How to move the pointer of a recordset object:

MoveFirst

MoveLast

next step

Move the previous one

Move digital record, start:

Operation method of recordset object:

Addnew: this method can add records to the Recordset object.

Delete: Delete the records in the recordset object.

Update: Updates the current record of the recordset object.

CancelUpdate: Cancel the update, and the method will not take effect until it is updated.

UpdateBatch: When the lock type is 4, this method is used to save modifications to one or more records.

Properties of recordset objects

Absolute page

absolute position

Active connection

Basic oxygen converter

eof

bookmark

Cache size

Cursor positioning

Cursor type

Edit mode

filter

Lock type

MaxRecords

number of pages

page size

Record count

condition

source

Field objects and collections

Field properties:

Name, value, type, …

Field methods: AppendChunk, GetChunk

On-site acquisition:

Output data table title:

For I=0 to rs.fields.count- 1

Response.write " & ltTD >； & amprs.fields(i)。 Name and name. “& lt/TD >； (Note: The last one is about getting the number of form elements, which is similar to this one. Request. Form. Count)

then

The value of each record in the output table:

For I=0 to rs.fields.count- 1

response.write " & ltTD >； & amprs.fields(i)。 Value and value. “& lt/TD >；

then

Query database with ADO and SQL

Establish database and data source:

In this example, Microsoft Access 2000 is used to establish a library management database. The database is called book.mdb, in which the data table is bookitem and there is only one table.

Structure of bookitem table:

Book_id (number): long integer, primary key.

Book name, book author, book price (currency), book publishing house and book date (date).

The DSN for establishing this database is called qhbook, and the account number and password are internet.

Use Select to select all records:

Format: select [Top N] field list from the data table name (* means all fields, using multiple fields, separated by numbers).

1.Select * from bookitem

2. Select the book name, book author and book price from bookitem.

3.select * From Bookitem Where Book _ name = ' Lonely Sheep '

Select * from bookmark where book _ namelike "%principle%" (wildcards% and _, where% stands for any number and _ stands for 1. )

Select * from bookitem, where book_price is between 20.00 and 23.00.

select * from book item where book _ price & gt； =20.00 and book _ price < = 23.00.

Select * from bookmark, where book _ price is between 12.00 and 20.00, and book _ namelike'% system%'.

select * from bookitem where book _ date & gt； = # 2000/3/ 1 # and book _ namelike "%principal%"

Conditional expressions after Where can be filtered by operators such as logic, comparison, like, between … and, in/not, in, is null/is not null.

Select the [Top N] field list from the data table name, where the conditional expression

4. Select the [Top N] field list from the data table name, in which the conditional expressions are sorted in descending order according to the field list [Desc]:

select * from book item order By book _ date desc，book_price Desc

select * from bookitem Where book _ date & gt； =#2000/3/ 1# Sort by book_name

select Top 4 * from book item where book _ date & gt； =#2000/3/ 1#

Insert record:

Format 1: Insert data table name [(field name list)] value (field value list).

Format 2: rs. Open SQL in Connecticut, 2, 3

Add new content

Assign a value to each field.

Update (this method is most commonly used, especially when used in combination with a form to add records)

For example:

Insert bookmark (book name, book author, book price) values ('book title',' author',' price')

Delete record: