Tip: Access and SQL Server's ASP code comparison

access|server|sql| Comparison | tips

Background database:
[Microsoft Access]
And
[Microsoft SQL Server]
After the replacement, the ASP code should note some of the areas to be modified:

[One] connection problem (for example)
[Microsoft Access]
Constr = "DBQ=C:\DATA\CLWZ.MDB; Driver={microsoft Access DRIVER (*.mdb)} "
[Microsoft SQL Server]
Constr = "Driver={sql Server}"; Server=host;database=mydata;uid=sa;pwd= "

[Two] similar functions (examples)

[1] DATEDIFF (DatePart, StartDate, EndDate)
The "datepart" parameter can be selected as follows:
Set description
————————————
[Microsoft Access]
Years yyyy
Quarter Q
Month m
Number of days in a year Y
Day D
Number of days in a week W
Weekly WW
Hour h
Min n
Seconds s

[Microsoft SQL Server]
Year yy, yyyy
Quarter QQ, Q
month mm, M
DayOfYear Dy, y
Day DD, D
Week wk, WW
Hour HH
Minute MI, n
Second SS, S
Millisecond MS

-------------------------

Basically, but note that when it's written,
[Microsoft Access] quotes, such as: DateDiff (' d ', EndDate, ' 2004/08/01 ')
[Microsoft SQL Server] is not required, such as: DateDiff (d,enddate, ' 2004/08/01 ')

[2] In Microsoft Access, you can use the data type functions such as CStr, and
[Microsoft SQL Server] uses the convert or cast function, such as:
Convert (Varchar,[amount]) and so on.

[3] [Microsoft SQL Server]
Take the current time with getdate and so on ...

[Three] Statements
[Microsoft SQL Server]
can use
Case
When THEN
When THEN
...
ELSE
End
Statement, and
[Microsoft Access]
Not supported.
[Microsoft Access] also does not support between statements
[Microsoft SQL Server] can write this:
[Date] between @date1 and @date2
[Four] query table
[Microsoft SQL Server]
Can be three and above table join query, and
[Microsoft Access]
It seems that only two tables join the query (pending authoritative confirmation),
and [Microsoft SQL Server] can use "*=" and "=*" connectors. [Five] except 0 questions
[Microsoft Access]
When you encounter a divisor of zero, automatically lose the relevant records, and
[Microsoft SQL Server]
An error occurs and the query is aborted. To delete code:
[Microsoft Access]
You can write this: delete * from [table]
[Microsoft SQL Server]
You can only write this: delete from [table]
Multiple * will be an error
_____________________________________
Current Date:
[Microsoft Access]
With date ()
[Microsoft SQL Server]
With GETDATE () if the database might change the type, you can
Add this in the ASP code:
If InStr (Constr, "Microsoft Access") > 0 Then
Sqlstr=[microsoft Access][sql Code]
Else
Sqlstr=[microsoft Sql Server][sql Code]
End If
(constr--connection string)
So even if you change the database, you do not have to change the database query to update the code.
Plus: Access has a true, false field record, and SQL only smallint, if there is a "field name =true" in Access, in SQL to change to "field name = 1"
Most of the free ASP programs on the Web use an Access database. However, Access database as a small and medium-sized stand-alone database system, in the capacity to undertake access, data volume of the Web site application, often overwhelmed. Generally, more than 50M of Access database performance began to decline significantly, more than 100M after the error, slow running problems will be more prominent. Although it can be as dynamic Network 7.0 after, from the point of view of the program to optimize to improve performance, but can not fundamentally solve the problem.
Then perhaps using Microsoft's SQL Server database is the most likely way, of course, can also use other such as Oracle, MySQL and so on, but as a rewrite, because of the same as Microsoft's products, to write SQL Server should be the most labor-saving way.

First, rewrite the premise:

The system has installed SQL Server2000 and SP3 patches, installed access to the Office suite, used an editor that supports plain text editing with line numbering, recommends ultra edit, and of course you can use FrontPage2003, However, the previous version of the line number display is not very useful.

Individual competency requirements: Basic ASP syntax, Access database operations, SQL Server Enterprise Manager basic operations.

II. Preparation of the database

In general, there are two scenarios:
1, the program provides the SQL database format: There is an MDF file, or provides a SQL database to create SQL script file (suffix named. sql).
If you have MDF files, you can use Enterprise Manager to attach directly, if the SQL script file is provided, then first use Enterprise Manager to create a SQL database, and then the database in Enterprise Manager Query Analyzer run this script to create a database table.
This database is basically not going to overwrite anything.
2, more is not provided by the SQL database or script files, at this time, we will do all of this, this is our main problem to solve this post. Typically such a program will provide an Access database, so you can import an Access database with Enterprise Manager, and you need to rewrite the following when you import it:
Rewrite the following section against the original access:
(1) There is no automatic field in the SQL database table, so the automatic field in Access is converted to a normal field, which needs to be manually changed to an identity type and an increment of 1.
(2) All time fields, if the default value is defined, then it must be now () and need to be changed to GETDATE ()
(3) The default value of the original field is generally not introduced automatically, you need to manually add the field from the original table.
(4) Because of the different database, Access and SQL field types have changed a lot, such as the original "whether" field will be converted to bit or int, Memo fields are converted to Longtext,text fields converted to varchar and so on, generally will not affect the program to run , if there is a problem, we'll say it in the following rewrite section.
(5) If you want to use a for SQL program, there is a stored procedure in it, so you should have the program itself to build the SQL database method: have its own SQL database file, or SQL script, if not, the way to import Access database can not establish stored procedures, You'd better discard this for SQL version of the program, use the same version of the For Access program, import the Access database, and then use the following rewrite method to change the SQL version of the program yourself.

third, the connection string rewrite

This section of the network can be referenced, respectively, for access and SQL
Dim ConnStr
If issqldatabase = 1 Then
' SQL database connection parameters: Database name, user password, username, connection name (local locally, field IP)
Dim Sqldatabasename,sqlpassword,sqlusername,sqllocalname
Sqldatabasename = "Dvbbs7"
SQLPassword = ""
sqlusername = "Dvbbs"
Sqllocalname = "(local)"
ConnStr = "Provider = SQLOLEDB; User ID = "& Sqlusername &"; Password = "& SQLPassword &"; Initial Catalog = "& Sqldatabasename &"; Data Source = "& Sqllocalname &";
Else
' Free user first use please modify the local database address and modify the database name in the data directory accordingly, such as Dvbbs6.mdb modified to dvbbs6.asp
' http://www.knowsky.com/
Db = "Data/fengerqingqing.mdb"
ConnStr = "Provider = Microsoft.jet.oledb.4.0;data Source =" & Server.MapPath (DB)
End If
On Error Resume Next
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open ConnStr

Of course, if you use SQL, the USE statement about Access can be deleted, or else it goes back to On Error Resume Next, and it becomes this way:

Dim ConnStr
' SQL database connection parameters: Database name, user password, username, connection name (local locally, field IP)
Dim Sqldatabasename,sqlpassword,sqlusername,sqllocalname
Sqldatabasename = "Dvbbs7"
SQLPassword = ""
sqlusername = "Dvbbs"
Sqllocalname = "(local)"
ConnStr = "Provider = SQLOLEDB; User ID = "& Sqlusername &"; Password = "& SQLPassword &"; Initial Catalog = "& Sqldatabasename &"; Data Source = "& Sqllocalname &";
On Error Resume Next
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open ConnStr

It can be simplified and written like this:
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "Provider = SQLOLEDB; User ID = sa; Password = 1234567; Initial Catalog = DVBBS7; Data Source = (local);
Inside the database name, data source, user, password in accordance with their own actual situation rewrite.

Iv. rewriting of procedures

There are two different situations.
1, if you are lucky to get the program for SQL, then if the above database establishment process did not encounter trouble, the program can basically run, error, but the program itself bugs, how to modify is not the content of this post discussion, will not repeat.
2, in most cases, the program itself is for access, and the program differences with for SQL are primarily the SQL query statements used in the program. Note that the SQL query statement is an integral part of the database application, and the syntax used for the for SQL or for Aceess program is roughly the same, but there are some subtle differences that make the program not generic and the main content that we need to modify. The sections that would normally be modified are as follows:
(1) Time function problem: The SQL database time function is different from access, the most common is the current time function, Access is now (), SQL is getdate (). So all places where now () are used in the WHERE clause are changed to GETDATE (), note that the now () function is also used in the ASP program itself, never change the now () function that is not used in database queries or execution statements.
(2) Time comparison function: DateDiff (' d ', ' time 1 ', ' time 2 ') This is the format that the Access query uses, the quotes in SQL should be removed, and the time format may be preceded by a #, which should also be removed. This is also referred to in the SQL statement, in the ASP statement to remain intact.
(3) The representation of a null value: In Access, determining whether a null value is generally used = "", but this is often an error in SQL, if you encounter an error or the program is not working properly, you can change to such a judgment: where (name is null)
(4) True and false value judgment: Access can be judged by =true, =false, but there will be errors in the SQL, so in the SQL query or execution of the statement in this kind of judgment to be changed to = 1, = 0. Note: Some programs, although written = "true", but because of the quotation marks, so this field is a character type, you can not change to = 1, you can stay the same.

The above is a more common rewrite of the place, there are some less common, if encountered can be discussed in this reply.

v. Debugging of the program

The previous recommended use of the editor with line number, because the above rewrite is not likely to be a direct search program source code to do, it is difficult to find the whole.
I take the way generally: Database rewrite complete, directly debug the program, error, to see the error prompts, find the corresponding file line of code, but the source is often not the line, such as the error of the statement is: Conn.execute (SQL), but this sentence itself is not wrong, The reason for the error is the SQL string inside, so look up how the SQL string is generated and modify it according to the procedure described above.

After database import, automatically add fields need to be rewritten, all numeric types need to be increased in length, preferably in decimal.

All default values are lost. Mainly numeric types and date types.

All now (), time (), date () is changed to GETDATE ().

All DateDiff (' d ', time1, time2) are to be converted to DateDiff (day, time1, time2)

It is possible that some true/false types cannot be used, to become 1/0.

Note types are used by the cast (column as varchar).

CursorType to be changed to 1, that is, to open the database to give the first numeric parameter is 1, otherwise the record may be incomplete.

IsNull (RowName) to be changed to RowName = NULL

When the AutoNumber type in the database of access is converted, SQL Server does not set it to AutoNumber, and we need to add identity to the SQL creation statement to represent the AutoNumber!

When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the datetime type is larger than the smalldatetime type. Sometimes when using a smalldatetime type, the transformation fails, and when the datetime type is used, the conversion succeeds.

The SQL statements that operate on the two databases are not identical, for example: "Delete * from user where id=10" is used when deleting records for an Access database, and the SQL Server database is deleted by: "Delete user where Id=10 ".

Date functions are not the same, in the Access database processing, you can use the date (), time () and other functions, but in the SQL Server database processing, only datediff,dateadd functions, but not the date (), time () and other functions.

In the processing of Access database, some VB functions can be used directly in SQL statements, such as the CStr () function, but not in SQL Server database processing.
The following table compares the MicrosoftAccess database (MicrosoftAccess database: A collection of data and objects, such as a table, query, or form) that is related to a specific topic or purpose. The Microsoftjet database engine is used to manage data. and MicrosoftAccess Project MicrosoftAccess project: An Access file that is connected to a Microsoftsqlserver database and used to create a client/server application. The project file does not contain any data or objects that are based on data definition, such as tables or views. Data type: A field feature that determines the type of data that a field can have. Data types include Boolean, Integer, Long, Currency, single, Double, Date, String, and Variant (default).

MicrosoftAccess data type SQL Server data type
Yes/No (yes/No data type: A field data type, for fields that have only two possible values, such as or no, true, or false.) Null values are not allowed. Bit data type: In an Access project, a data type that stores a value of 1 or 0. Accept an integer value other than 1 and 0, but always interpret it as 1. ）
Number (Number data type: A field data type in a MicrosoftAccess database used for numeric data that will be used in mathematical operations.) However, to display or calculate currency values, you should use the Currency data type. (bytes) tinyint (tinyint data type: An entry in an Access project that occupies a byte (8-bit) data type that stores integers from 0 through 255.) ）
numeric (integer) smallint (smallint data type: A 2-byte (16-bit) data type in an Access project that stores the number between -2^15 (-32,768) and 2^15-1 (32,767). ）
Number (int data type: a 4-byte (32-bit) data type in an Access project that stores the number between -2^31 (-2,147,483,648) and 2^31-1 (2,147,483,647). ）
Number (single-precision floating-point type) real (real data type: In an Access project, an approximate numeric data type with a precision of 7 bits, a positive value ranging from 1.18E-38 to 3.40E+38, and a negative value ranging from -1.18E-38 to -3.40e+ 38, you can also take 0. ）
(no equivalent data type) bigint (bigint data type: A 8-byte (64-bit) data type in an Access project, stored in -2^63 (-9,223,372,036,854,775,808) and 2^63-1 ( 9,223,372,036,854,775,807) between the numbers. ）
Number (double-precision floating-point type) float (float data type: In an Access project, an approximate numeric data type with a precision of 15 digits.) It stores a range of positive values ranging from 2.23E-308 to 1.79E+308, with a negative range of roughly -2.23E-308 to -1.79E+308 or 0. ）
Currency (Currency data type: A data type in a microsoftaccess database used for fixed-point calculations that are related to currency calculations or whose accuracy is extremely important. Money data type: In an Access project, the data type used to store currency values ranging from -922,337,203,685,477.5707 to 922,337,203,685,477.5807, with a precision of one out of 10,000 monetary units. ）

SmallMoney (smallmoney data type: A data type in an Access project that stores currency values ranging from -214,748.3648 to 214,748.3647, with a precision of one out of 10,000 monetary units. When smallmoney values are displayed, they are rounded to two decimal places. ）
Decimal/numeric value (a Decimal data type (Access database): An exact numeric data type that is used to store values -10^38-1 to 10^38-1. You can specify a range of values (maximum total number of digits) and precision (the maximum number of digits to the right of the decimal point). Decimal Data type (Access project): An exact numeric data type used to store -10^38-1 to 10^38-1 values. You can specify a range of values (maximum total number of digits) and precision (the maximum number of digits to the right of the decimal point). ）

Numeric (numeric data type: In an Access project, an exact numeric data type that values from -10^38-1 to 10^38-1. You can specify a range of values (maximum total number of digits) and precision (the maximum number of digits to the right of the decimal point). ）
Date/Time (date/Time data type: A data type in an Access database that holds date and time information.) datetime data type: In an Access project, the date and time data type, ranging from January 1, 1753 to December 31, 9999, with an accuracy of 1% seconds, or 3.33 milliseconds. ）

smalldatetime (smalldatetime data type: A date and time data type in an Access project that is not as accurate as datetime data types.) The range of data values ranges from January 1, 1900 to June 6, 2079, with a precision of one minute.
AutoNumber data type: A type of field data in a MicrosoftAccess database that automatically stores a unique number for each record when a new record is added to the table. Three types of numbers can be generated: sequential, random, and Replication IDs. (incrementing) int (int data type: a 4-byte (32-bit) data type in an Access project that stores the number between -2^31 (-2,147,483,648) and 2^31-1 (2,147,483,647). (Defines the IDENTITY property)
Text (text data type: A field data type in a MicrosoftAccess database.) The text data type can contain up to 255 characters or a smaller number of characters specified by the FieldSize property. (n) varchar (n) (varchar (n) data type: A variable-length data type in an Access project with a maximum length of 8,000 ANSI characters. )

nvarchar (n) (nvarchar (n) data type: In an Access project, a variable-length data type that can contain up to 4,000 Unicode characters. Unicode characters account for two bytes per character, and all international characters are supported.
Note (Memo data type: A field data type in a MicrosoftAccess database.) The Memo field can contain up to 65,535 characters. Text (Text data type: A variable-length data type in an Access project that can store up to 2^31-1 (2,147,483,647) characters; The default length is 16.
OLE Object data type: One of the data types of a field used for objects created in other applications that can be linked or embedded (inserted) into an Access database. Image (image data type: In an Access project, a variable-length data type that can store up to 2^31-1 (2,147,483,647) bytes of binary data. The image data type is used to store BLOBs (binary large objects), such as pictures, documents, sounds, and compiled code.
The synchronous Replication ID (also known as a globally unique identifier GUID: In an Access database, a 16-byte field that establishes a unique identifier for a synchronization replication.) GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, the GUID refers to the replication ID. (GUID)) uniqueidentifier (uniqueidentifier data type: A 16-byte globally unique identifier (GUID) in an Access project. (for SQLServer7.0 or later)
Hyperlink (Hyperlink data type: The data type of an Access database field that stores a hyperlink address.) Addresses can contain up to four parts, written in the following syntax format: displaytext#address#subaddress#. char (char data type: In an Access project, a fixed-length data type that can contain up to 8,000 ANSI characters.) ),

NCHAR (nchar data type: In an Access project, a fixed-length data type that can contain up to 4,000 Unicode characters. Unicode characters account for two bytes per character, and all international characters are supported. ), Varchar,nvarchar (the Hyperlink property is set to Yes)
(no equivalent data type) varbinary (varbinary data type: A variable-length data type in an Access project that can store up to 8,000 bytes of binary data.) ）
(no equivalent data type) smallint (smallint data type: A 2-byte (16-bit) data type in an Access project that stores numbers between -2^15 (-32,768) and 2^15-1 (32,767). ）
(no equivalent data type) timestamp (timestamp data type: In an Access project, a data type that is automatically updated for each insert or update row.) The value in the timestamp column is not datetime data, but binary (8) or varbinary (8), indicating the order in which the data was modified. ）
(no equivalent data type) Charnchar
(no equivalent data type) sql_variant (sql_variant data type: A data type in an Access project that stores values for a variety of data types except text, ntext, image, timestamp, and sql_variant types.) Used in the return value of a column, parameter, variable, or user-defined function. ）
(no equivalent data type) User defined (user-defined data type: In the Microsoftsqlserver database, allows the type definition of the data that a column contains, which is defined by the user using an existing system data type.) Rules and default values can only be bound to user-defined data types. ）

Note In an Access project or SQL Server database, the prefix "n" represents "Country/region", meaning that the data type is Unicode enabled. In an Access database, all text columns are enabled for Unicode by default.

Issues to be aware of when access turns to SQL

Many friends want to use the SQL2000 database programming method, but also suffer oneself is to learn access, to SQL is just a little understanding, here I give you the following reference---to convert access to SQL2000 methods and considerations

first, I said in the access2000,sql2000 between the conversion, the other I have not tried, I hope we have a lot of experimentation, there must be a way;

two, the method of conversion

1, open the database source in Administrative tools under Control Panel;
2, press "Add" to add a new data source, select "Driverdomicrosoftaccess" in the selection bar.
(*.mdb), when finished, a box appears,

In "Database Source" Enter the name you want to write, I named "ABC", stating that you do not need to fill out, and then, click the following options to find your database address and check (note, please back up your own Access database), and then determine.
The data source has been built here, and the rest is converted.

3, open SQL2000 Enterprise Manager, enter the database, create a new empty database "ABC";
4, select the newly established database, press the right mouse button, select "All Tasks" under "Import Data", press "next" to continue;
5, in the database source Drop-down but select "Driverdomicrosoftaccess (*.mdb)", in "User/System DSN", the selection of the "ABC" you just added, press "Next";
6, "purpose" does not need to be modified, select the server (generally for your own native "local", you can also select the server address or LAN address to determine whether your permissions can operate,), "Use Windows Authentication" refers to the operation of their own system administrator, "Using SQL Identity operation authentication "Can be used for Web site operations, recommended by the latter;"
7, select the "Use SQL Identity Operation Verification", fill in your username and password, I chose the system default number "SA", "* * * * * * *, the database select just new" ABC ", press" next ";
8, this step of the two single choice, "Copy tables and views from the data source" and "Specify the data to be transferred with a query instruction", select the former, press "next" to continue;
9, here will appear your own Access database table, press "Select All" after the next step;
The DTS Import/Export Wizard, see "Run Now" is selected to press "Next",
11, press "Finish" to continue;
12, this step you will see your data being imported into the SQL2000 inside, when the words "have successfully imported the XXX table into the database," and all the tables are preceded by a green tick, it means that all the data is successfully imported, if there is a problem in the middle or the table has a red *, Indicates that the table did not import successfully, then go back and see if your actions are correct.

third, data modification

1, because there is no "AutoNumber" inside the SQL2000, so your "AutoNumber" set of fields will become Non-empty fields, which must be manually modified by these fields, and his "mark" select "Yes", the seed is "1", the increment is "1",
2, in addition, ACCESS2000 converted to SQL2000, the original property is "Yes/no" field will be converted to a non-empty "bit", at this time you have to modify the properties you want;
3, in addition, we should pay attention to the time function of the grasp. Access is a lot different from SQL.

Iv. related Field issues

Automatic numbering type in 1.ACCESS database when you convert, SQL Server does not set it to AutoNumber, we need to add identity to the SQL creation statement to indicate AutoNumber!
2. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime type because the range of datetime is larger than the smalldatetime type. I met this situation, with the smalldatetime type, conversion failure, but with the DateTime type, conversion success.
3. The SQL statements that operate on both databases are not identical, for example: "delete*fromuserwhereid=10" is used when deleting records for an Access database, and the SQL Server database is deleted by: " Deleteuserwhereid=10 ".
4. The date function is different, in the Access database processing, you can use the date (), time () and other functions, but for SQL Server database processing, only datediff,dateadd functions, but not the date (), time () and other functions.
5. In Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, but the SQL Server database processing, but can not be used.

v. Related Statement issues

Auto-increment fields need to be overridden. AutoNumber fields that are often used in access. After import to MSSQL, he is not a self-added int, need to manually set, the number of the imported AutoNumber field is changed to "yes", "seed" and "increment" are "1" to become automatic numbering

All default values are lost. Mainly numeric types and date types

All now (), time (), date () is changed to GETDATE ()

All DateDiff (' d ', time1,time2) to be converted to DateDiff (day,time1,time2)

All DateDiff (' ww ', time1,time2) to be converted to DateDiff (week,time1,time2)

All DateDiff (' d ', time1,time2) to be converted to DateDiff (day,time1,time2)

In MSSQLServer, there are many reserved words that are not available in access, and when you import data into MSSQL, the problem comes out. MSSQL will automatically add "[Field name]" to these fields (including the table names in the database) when importing, so you must modify your script to include the corresponding field name (or table name) in brackets, or change the field name to a reserved word that is not MSSQL

In Access about the use of time, people like to use the "Select*fromaaaawhiletime=" &now (), such as SQL statements, however, in MSSQL does not have the "now" function, but use "getdate ()", Therefore, the "now" in all SQL statements must be replaced with "getdate ()".

Date functions are not the same, and functions such as date (), time () can be used in Access database processing, but
SQL Server database processing, you can only use functions such as datediff,dateadd, but not the date (), time () and other functions.

When converting to date-related fields, SQL Server defaults to the smalldatetime type, and we'd better change it to a datetime type.