Beginner's note: Basic Principles of ASP Programming

I. Common mistakes made by new users
In the forum, many posts have a common basic error in code, and the field type is incorrect.
The program is closely connected to the database. single quotation marks are used for both the text type and the time type of the database field.
For example, the following statement is modified:
Conn.exe cute "Update counts set counts = '" & counts & "'where num =" & num & "and atime ='" & now ()&"'"
The left side of the equal sign is the field name, the right side is the variable name passed in the value, and the counts field is a text type. Therefore, a single quotation mark must be added before and after the equal sign is written, and both the write and query operations are the same, in the following query statement, the num field is a number type, so there is no single quotation mark before and after it, and the atime field is a time type. Therefore, single quotation marks must be added before and after it.
The most important thing is the ID query. The ID field is unique and numeric. It is obvious that there are no single quotation marks before and after the ID number is queried.
Conn.exe cute "Update counts set counts = '" & counts & "'where id ='" & ID & "'"' incorrect syntax
Conn.exe cute "Update counts set counts = '" & counts & "'where id =" & id'

2. Access Database Connection
There are usually two ways to connect to a database. A newbie basically doesn't know which method to use, or under what circumstances, or how the two work.
① Directly connect to database files
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("database/yanhang. mdb ")

② Connect to database files through data sources
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("database/yanhang. mdb ")

Which of the two is better? Of course, it is the second one, because the first one is to directly read the database from the client browser, so the security is much worse. The second is to connect to the database through the data source, it is connected by a server data source tool and has nothing to do with the client, so the database will not be exposed to the client, and the security factor is much higher.

Application of the corresponding program of the Access Database: ① directly connect to the database file
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("database/yanhang. mdb ")
To add a statement for this database connection method:
Set rs = server. Createobject ("ADODB. recordset") '(correct syntax)
Rs. Open "select * From dndj", Conn, 1, 3
Rs. addnew
RS ("bh") = BH
RS ("BM") = BM
RS ("XM") = XM
RS ("xsq") = xsq
Rs. Update
Rs. Close
Set rs = nothing

Set rs = server. Createobject ("ADODB. recordset") '(incorrect syntax)
SQL = "insert into dndj (BH, BM, XM, xsq) values ('bh ', 'bm', 'xm ', 'xsq ')"
Rs. Open SQL, Conn, 1, 3

Application of the corresponding program of the Access Database: ② connect to the database file through the data source
Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("database/yanhang. mdb ")
To add a statement for this database connection method:
Conn.exe cute "insert into dndj (BH, BM, XM, xsq) values ('" & BH & "', '" & BM &"', '"& XM &"', '"& xsq &"') "'(correct syntax)

Set rs = server. Createobject ("ADODB. recordset") '(incorrect syntax)
SQL = "insert into dndj (BH, BM, XM, xsq) values ('bh ', 'bm', 'xm ', 'xsq ')"
Rs. Open SQL, Conn, 1, 3

Iii. Application of Double quotation marks
Usually we write a super connection like this <a href = "ABC. asp? Id = <% = RS ("ID") %> "> super connection </a>
But what if I compile this super connection into asp?
Response. Write "<a href =" "ABC. asp? Id = "& RS (" ID ") &" "> super connection </a>" '(correct syntax)
Response. Write "<a href = 'abc. asp? Id = "& RS (" ID ") &" '> super connection </a> "' (correct syntax)
Response. Write "<a href = ABC. asp? Id = "& RS (" ID ") &"> super connection </a> "'(correct syntax)

Response. Write "<a href =" ABC. asp? Id = <% = RS ("ID") %> "> super connection </a>" '(incorrect syntax)
Response. Write "<a href =" ABC. asp? Id = "& RS (" ID ") &"> super connection </a> "'(incorrect syntax)

Form compilation into ASP <input type = "text" name = "ID" value = "<% RS (" ID ") %>"/>
Response. write "<input type =" "text" "name =" "ID" "value =" & RS ("ID ") & "/>" '(correct syntax) Note: There are three double quotes
Response. Write "<input type = 'text' name = 'id' value = '" & RS ("ID") & "'/>" '(correct syntax)
Response. Write "<input type = text name = ID value =" & RS ("ID") & "/>" '(correct syntax)

Response. Write "<input type =" text "name =" ID "value =" <% = RS ("ID") %> "/>" '(incorrect syntax)
Response. Write "<input type =" text "name =" ID "value =" "& RS (" ID ") &" "/>" '(incorrect syntax)

4. Several Methods to Prevent Access Database downloads
Many dynamic sites use databases in large quantities, and databases become the core files of a site. Once the database is illegally downloaded, it is very likely that malicious people will destroy the website. Or steal data.

The following methods are applicable to users who use virtual host space and those who have control of IIS!

I. Purchasing virtual host space is suitable for scenarios with no control over IIS.
1: Use your imagination to modify the database file name
This is the most basic. I don't think there are many people who are too reluctant to change the database file name? As for what to change, you should at least make sure that the file name is complex and cannot be guessed. Of course, you cannot open the directory browsing permission in the directory where your database is located!

2: Change the database name suffix to Asa, ASP, etc.
I heard this is very popular, but I have tested it many times and found it is not ideal. If you really want to prevent downloading, you need to add some binary fields and other settings. In a word, complex and complex (if you have a lot of databases, this method is not very good)

3: Add "#" before the Database Name
You only need to add # To the front Name of the database file, and then modify the database address in the database connection file (such as Conn. asp. The principle is to download the part can only recognize # before the first name, for the next automatic removal, such as you want to download: http://bbs.bccn.net/date/#123.mdb (if there is ). Both IE and flashget are http://bbs.bccn.net/date/index.htm.

Today, I saw my brother on the 57 th floor say that adding "#" in front is basically rubbish, and then I tested it.
Use % 23 to download: http://bbs.bccn.net/date/?color=red=41023123.mdb
Later, I studied that the browser with spaces in the middle can be automatically compiled to % 20, which can also be downloaded.
I will use the # + space compilation code % 23% using MDB as the database name.
Http://bbs.bccn.net/date/%23%20.mdb
After testing, neither thunder nor normal download tools can download [/color].

4: Encrypted Database
After using access to open your database in an exclusive manner, go to tool-security-set the Database Password. After encryption, modify the database connection page, for example:
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; uid = admin; Pwd = Database Password; DBQ = database path"
After this modification, even if the database is downloaded, it cannot be opened by others (provided that the password on your database connection page is not leaked)
However, it is worth noting that the encryption mechanism of the Access database is relatively simple, and it is easy to decrypt even if a password is set. The database system creates an encryption string and stores the password entered by the user in *. the MDB file starts from the address "& h42. Therefore, a good programmer can easily create dozens of rows of small programs to easily obtain the password of any access database. Therefore, as long as the database is downloaded, its security remains unknown.

2: You have control over the host (of course, the virtual space settings can still be used here)
5. Store the database outside the web directory
If your web directory is E:/webroot, you can put the database in the E:/data folder, on the database connection page in E:/webroot.
Modify the database connection address to "../data/#123 456.mdb", so that the database can be called normally, but cannot be downloaded because it is not in the web directory! This method is also suitable for users who buy virtual space.

6. Use the ODBC data source.
In programming such as ASP, if conditions are met, try to use ODBC data sources. Do not write the database name in the program. Otherwise, the database name will be lost along with the password of ASP source code.
For example:
Conn. Open "driver = {Microsoft Access Driver (*. mdb)}; DBQ =" & server. mappath ("../123/ABC/asfadf. mdb ")
It can be seen that even if the database name gets weird, the hidden directory goes deeper, and the ASP source code is easily downloaded after the password is lost.
If you use the ODBC data source, there will be no such problem: conn. Open "ODBC-DSN name", but this is annoying, if the directory moves, you have to re-set the data source!

7. Add the extended MDB ing of database names such as MDB
This method is implemented by modifying the IIS settings. This method is suitable for friends who have control over IIS and is not suitable for users who buy virtual hosts (unless the Administrator has already set it ). I think this method is the best at present. The database of the entire site can be modified to prevent downloading. You do not need to modify the code to prevent download even if the target address is exposed.

Settings:
In IIS properties --- main directory --- configuration --- ing --- application extension, add the application parsing of the. MDB file. Note that the selected DLL (or EXE) here does not seem arbitrary. If you choose improperly, the MDB file can still be downloaded. You are advised not to select ASP. dll. You can perform multiple tests on your own.
In this way, download the database, for example, http://bbs.bccn.net/data/dvbbs6.mdb. (404 or 500 errors)

8: advantages of using. net
The wooden bird on the Internet wrote a "WBAl anti-leech tool" to prevent illegal file downloads ". I remember that some cool people in this forum once published a database anti-download plug-in, which was loaded into IIS by. dll.
However, it only prevents non-local downloads and does not provide a real anti-download function. However, this method is similar to 5th
You can modify the. NET file to disable local download!

Only 7th and 8 of these methods are uniformly modified. After a configuration is modified, the database of the entire site can be prevented from being downloaded. The other methods need to modify the Database Name and connection file respectively, it is troublesome, but this is also the only option for virtual host friends!

In fact, the 6th methods should be extended by 5th methods, which can implement special functions, but not supported. net host or for fear of setup troubles, you can still directly use 5th methods, and by default, 6th methods can still be copied to the forum or message book of the same host, then you can click Download (because the reference page is from the same host)

These methods have different lengths. Please use them on your own. These methods are not absolutely secure. website administrators also need to pay attention to the security of some systems and write ASP code. Otherwise, they may still be downloaded or modified!

Four functions intercepted by a string
1. If only the first few digits are intercepted, use left
2. If only the last few digits are intercepted, use right
3. If only a few digits are intercepted, use mid
4. delimiter truncation, use split

1. Extract the first three digits from left: Get ABC
<%
Dd = "abcdefgh"
Response. Write left (DD, 3) 'count from 1st to 3rd
%>

2. Right intercepts the last three digits to obtain fgh.
<%
Dd = "abcdefgh"
Response. Write Right (DD, 3) 'from the last one to the first 3rd
%>

3. truncate the middle three digits to obtain def.
<%
Dd = "abcdefgh"
Response. Write mid (DD, 4th) 'starts from 3rd bits and ends to bits.
%>

4. Split the content before and after the separator to obtain the AB CD EF GH
<%
Dd = "AB | cd | EF | GH"
Response. Write split (DD, "|") (0) '. The returned content is AB.
Response. Write split (DD, "|") (1 )'
Response. Write split (DD, "|") (2 )'
Response. Write split (DD, "|") (3 )'

'You can write a circular statement to display the separators one by one.
For I = 0 to 3
Response. Write split (DD, "|") (I) & "<br/>"
Next

'Separately call the content at the specified separator location
Dim DM (3) 'defines a cyclic variable
For I = 0 to 3
DM (I) = Split (DD, "|") (I)
Next

Response. Write DM (0) '. The returned content is AB.
Response. Write DM (1) 'to get the content CD
Response. Write DM (2 )'
Response. Write DM (3) '. The obtained content is GH.

'If you are not sure how many delimiters are in the DD, you cannot directly write the number following the cycle parameter. You need to count the number of delimiters.
For I = 0 to ubound (split (DD, "| "))
DM (I) = Split (DD, "|") (I)
Next
%>

The connection method of SQL database is described as follows:
① SQL2000 local database connection
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "provider = sqloledb; server = (local); database = database name; uid = user name; Pwd = password ;"

② SQL2000 database remote connection
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "provider = sqloledb; server = 200.200.200.200, 1433; database = database name; uid = username; Pwd = password ;"

③ Sql2005 local database connection
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "provider = sqlncli; server = (local); database = database name; uid = user name; Pwd = password ;"

④ Sql2005 remote database connection
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {SQL Server}; server = 200.200.200.200, 1433; database = database name; uid = username; Pwd = password ;"

Let's get started with ASP today.
No matter which programming language is used, these functions are similar.
1. Read
2. Add
3. Modify
4. Delete
5. Query
6. Statistics
As long as you can use these functions freely, you will already be able to use this language.

First, let's talk about the database:
ASP generally uses access and SQL databases
It is best for beginners to use the ACCESS database first and install an offiec to bring their own access database.
Access versions from access98 → Access2000 → access2003 → access2007
What version of offline is the access

Sqldatabase is a Microsoft product. Currently, the commonly used sqldatabase is SQL2000 → sql2005.

ASP reads data:
<%
Set rs = server. Createobject ("ADODB. recordset ")
Rs. open "select * from table name order by id desc", Conn, 'order by is used to sort IDS as sorting fields, DESC as inverted, ASC as order, and as read-only, 1, 3 is operable
Do while not Rs. EOF 'the start of the loop
Response. Write RS ("ABC") 'display data
Rs. movenext loop the next data
Loop
Rs. Close 'close the RS record
Set rs = nothing 'clear the RS record set
%>
Reading data is that simple.

 

Add new data in ASP:
Method 1 code:
<%
Set rs = server. Createobject ("ADODB. recordset ")
Rs. Open "select * from table name", Conn, 1, 3 'Here, adding new data does not need to be sorted. 1, 3 we have already said that it is the meaning of database operations.
Rs. addnew' start new data
RS ("Field 1") = "123456" 'add data to field 1
RS ("Field 2") = "123456" 'same as above
Rs. Update' starts writing data to the database.
Rs. Close 'close the RS record
Set rs = nothing 'clear the RS record set
%>
This method is suitable for any connection between access and SQL databases.
The following add statement is only applicable to the ② connection method of access, and is also applicable to connection of SQL database in any way.
Method 2 code:
<%
Conn.exe cute "insert into Table Name (Field 1, Field 2) values ('000000', '000000')" 'must be one-to-one matched between two parentheses. Separate multiple contents with commas.
%>
The preceding addition statement does not support access in the first database connection mode.
Look, we also learned how to add data.

 

ASP:
ASP modifies data mostly used to query specified data and then modifies that data.
Code of the first modification method:
<%
Set rs = server. Createobject ("ADODB. recordset ")
Rs. Open "select * from table name where id = 10", Conn, 'where indicates the data with the query condition ID equal to 10.
RS ("Field 1") = "123456" 'regardless of the value in Field 1, we change it to 123456.
RS ("Field 2") = "123456" 'same as above
Rs. Update' starts writing data to the database.
Rs. Close 'close the RS record
Set rs = nothing 'clear the RS record set
%>
The difference between modifying and adding is that Rs. addnew is missing and multiple query conditions are added. The others are identical.
Method 2 code:
<%
Conn.exe cute "Update table name set field 1 = '000000', Field 2 = '000000' where id = 10" 'multiple Content modifications are separated by commas
%>
The above modification code is the same as the above addition. The only method that does not support access is the first database connection method.

 

ASP:
The query is also used to delete data. If no query is available, all the content in the entire table is deleted. If you only need to delete one of them, you must use the query conditions.
<%
Conn.exe cute "Delete table name where id = 10" 'query and delete the data whose ID value is 10.
%>
If it is an Access database, add from, for example:
<%
Conn.exe cute "delete from table name where id = 10"
%>
Look, this Delete statement is so short that it is different from the read, add, and modify statements above.

 

If your database connection uses the access method ② or uses SQL data, it is easy to add, modify, and delete data, as shown below:

Add: conn.exe cute "insert into Table Name (Field 1, Field 2) values ('20170101', '20160301 ')"
Modify: conn.exe cute "Update table name set field 1 = '000000', Field 2 = '000000' where id = 10"
Delete: conn.exe cute "Delete table name where id = 10"

Does it look refreshing?

 

Remember, only the read, add, modify, and delete functions that do not contain query conditions can be added. The other three can contain query conditions according to their own needs.
I. query conditions can contain multiple conditions
For example, conn.exe cute "Delete table name where field 1 = '20160301' and Field 2 = '20160301' and ID = 10" are separated by and. spaces must be placed before and after and.
This means that the three conditions must be met at the same time to query the expected results.

2. the query conditions can use or.
For example: conn.exe cute "Delete table name where field 1 = '000000' or field 2 = '000000' or ID = 10" are separated by or. spaces must be placed before and after or.
This means that you can query the expected results as long as the query conditions meet one of them.

3. You can use and or for both query conditions.
Example: conn.exe cute "Delete table name where (Field 1 = '000000' or field 2 = '000000') and ID = 10"
This means that as long as the query condition meets one of Field 1 or field 2 and the ID is equal to 10, note: brackets should be enclosed.

The preceding three query conditions are suitable for reading, modifying, and deleting functions.

 

Statistics using the sum, count function
Statistical price: Sum
<%
Set rs = server. Createobject ("ADODB. recordset ")
Rs. Open "select sum (price field) as price from table name order by id desc", Conn, 'as is to assign the statistical result to the temporary variable price
Response. Write RS ("price") 'shows the total price

Rs. Close
Set rs = nothing
%>

Total count: recordcount
<%
Set rs = server. Createobject ("ADODB. recordset ")
Rs. Open "select * from table name order by id desc", Conn, 1, 1
Response. Write Rs. recordcount 'displays the total number of statistics.

Rs. Close
Set rs = nothing
%>

The code is dead, and the human brain is flexible. It depends on how you use it flexibly!