ACCESS and SQL Server through ADO in ASP

I recently participated in an ASP project, and this is my first web programming. ASP, one of the 3 P projects, should be a very old technology, but it also hinders my accumulation and learning, especially in web programming. Here I would like to share with you how to ACCESS and SQLSERVER via ADO on the ASP Server: ADO is an ACCESS database

I recently participated in an ASP project, and this is my first web programming. ASP, one of the 3 P projects, should be a very old technology, but it also hinders my accumulation and learning, especially in web programming. Here I would like to share with you how to ACCESS and SQL SERVER via ADO on the ASP SERVER: ADO is an ACCESS database

I recently participated in an ASP project, and this is my first web programming. ASP, one of the 3 P projects, should be a very old technology, but it also hinders my accumulation and learning, especially in web programming. Here I would like to share with you how the ASP Server PassADO AccessACCESS and SQL SERVER: ADO is AccessThe data programming interface in the database is an Active-x component of Microsoft and will be automatically installed along with IIS; IIS (Internet informations service) will be installed during ASP programming) first, ensure that ACCESS and SQL server are correctly installed on the OS. Because SQL SERVER is not installed on the home computer AccessThe ACCESS2003 database is used as an example to test several self-written databases. AccessFunction 1. Pass. Udl file to obtain the database connection string

1) create a txt file and change the suffix. udl; double-click to open the udl file, and then we can obtain the connection string of the specific database as needed. The interface after opening udl-1 is shown:

Figure-1

2) Here we select the Microsoft. Jet. OLEDB.4.0 database provider.AccessAccess, click Next, and the interface is shown in-2:

Figure 2

3) after you enter or select the database path, keep other options as default. After you click test connection, a message box Indicating "test connection successful" is displayed if no problem exists;

Click "OK" and open the udl file using UE or other text editing tools to obtain the connection string connecting to the access database;

-3: The connection string here is Provider = Microsoft. Jet. OLEDB.4.0; Data Source = D: \ IIS \ webapps \ My_Test \ GXY_DB1.mdb; Persist Security Info = False

Figure-3

2. PassCreate related database operation functions by connecting strings

1 .) create ProDatBase. asp file, and insert an empty ASP code segment <%>. In this code segment, three global variables are declared. These variables are used to store ADO record set objects, ADO connection objects, and connection strings respectively;

Assign the obtained connection string to g_ConStr.

Dim g_Rs,g_Con,g_ConStr
g_ConStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\IIS\webapps\My_Test\GXY_DB1.mdb;Persist Security Info=False"

2) create a data connection and disconnect Function

'Connection database function ConnectDataBase () set g_Con = server. createObject ("ADODB. connection ") on error resume nextg_Con.mode = 3' set the connection mode to read and write g_Con.open g_ConStrif err <> 0 thenConnectDataBase = falseresponse. write (err. description) else ConnectDataBase = trueend ifend function 'disconnect the database function DisconnectDataBase () on error resume nextg_Con.closeset g_Con = nothingif err <> 0 thenDisconnectDataBase = falseresponse. write (err. description) else DisconnectDataBase = trueend ifend function

3) Create the insert record Function

'Insert record function InsertRecord (table, sqlFields, sqlValues) if ConnectDataBase () thensql = "insert into [" + table + "] (" + sqlFields + ") values ("+ sqlValues +") "on error resume nextg_Con.Execute sqlif err <> 0 thenresponse. write (err. description) end ifDisconnectDataBase () end ifend function

4.) Create an update record Function

'Update record function UpdateRecord (table, sqlFields, sqlValues, strCondition) if ConnectDataBase () thensql = "update [" + table + "] set" SQL _fd = split (sqlFields ,", ") SQL _fv = split (sqlValues,", ") for I = 0 to ubound (SQL _fd) if I <> ubound (SQL _fd) thensql = SQL &" & SQL _fd (I) & "=" & SQL _fv (I) & "," elsesql = SQL & "& SQL _fd (I) &" = "& SQL _fv (I) & "" end ifnextsql = SQL & "where" & strCondition & "" on error resume nextg_Con.Execute sqlif err <> 0 thenresponse. write (err. description) end ifDisconnectDataBase () end ifend function

5) Create and delete a record Function

'Function DeleteRecord (table, strCondition) if ConnectDataBase () thensql = "delete from" + table + "where" & strCondition & "" on error resume nextg_Con.Execute sqlif err <> 0 thenresponse. write (err. description) end ifDisconnectDataBase () end ifend function

6.) create a record to obtain the Function

'Obtain the record function GetRecords (table, strCondition) if ConnectDataBase () based on the Condition () thensql = "select * from [" + table + "]" if strCondition <> "" thensql = SQL & "where" & strCondition & "end ifon error resume nextset g_Rs = Server. createObject ("ADODB. recordset ") g_Rs.Open SQL, g_Conif err <> 0 thenresponse. write (err. description) end ifend function

7) create a resource release function

function ReleaseResource()on error resume nextg_Rs.closeset g_Rs=nothingDisconnectDataBase()if err<>0 thenresponse.Write(err.description)end ifend function

3. Test the created database function. Here, we use the table Test_table in the GXY_DB1 database as an example. The fields and all records of this table are shown in-4 and figure-5:

Figure-4 (id auto-increment) figure-5 (no records)

Preparation: Create a ProDataBase_Test.asp file, add statements containing the ProDataBase. asp file, and insert an empty asp code block. <%>

1) insert record function test: Insert 10 records, name and age from name0 and 15 years old to name9 and 24 years old

Test code:

<%for i=0 to 9str="'name"&i&"',"&i+15&""InsertRecord  "Test_Table","name,age",strnext%>

Test result-6:

Figure 6

2) retrieve records function test: get records between 18 and 23 and display them on the webpage

Test code:

Test_Table
 
 
  
  <%GetRecords  "Test_Table","age between 18 and 23"response.Write("
  
  
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ")do until g_Rs.eofresponse.Write(" 
   
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ") g_Rs.movenextloopReleaseResource()%> 
  
 
   
") response.write(x.name) response.Write("
") response.write(x.value) response.Write("
 
 

Test result-7:

Figure-7

3) Update record function test: change the name of the record with name = name7 to newname, age to 99, and display all records on the webpage.

Test code:

Test_Table
 
 
  
  <%UpdateRecord "Test_Table","name,age","'newname',99","name='name7'"GetRecords  "Test_Table",""response.Write("
  
  
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ")do until g_Rs.eofresponse.Write(" 
   
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ") g_Rs.movenextloopReleaseResource()%> 
  
 
   
") response.write(x.name) response.Write("
") response.write(x.value) response.Write("
 
 

Test result-8:

Figure-8

4) Delete record function test: delete records older than 18 years old, and display all records obtained on the webpage

Test code:

Test_Table
 
 
  
  <%DeleteRecord "Test_Table","age>18"GetRecords  "Test_Table",""response.Write("
  
  
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ")do until g_Rs.eofresponse.Write(" 
   
     ")for each x in g_Rs.fields response.Write(" 
    ")next response.Write(" 
   ") g_Rs.movenextloopReleaseResource()%> 
  
 
   
") response.write(x.name) response.Write("
") response.write(x.value) response.Write("
 
 

Test result-9:

Figure-9

4. Conclusion:

All the above functions have been tested on win7 and xp platforms, as well as access2003 and SQL server2008 express (included in vs2010), and can run normally; the following is a connection string connecting to SQL SERVER: (also)PassIf a udl file is created, select SQL server native client 10.0 as the data provider)

g_ConStr="Provider=SQLNCLI10.1;Persist Security Info=False;User ID=gxy;Password=54321;Initial Catalog=GXY_DB1;Data Source=(local);

The first time I posted a blog post, I could not write it again. vbscript was just introduced, and function writing was not efficient and concise... And so on ..... If there are any mistakes or omissions, please give me some advice.