Easy to teach you how to convert SQL to ACCESS

Steps for converting SQL database to access database:

1. Create an access database: Create an access database and table in access. The access field type corresponds to the field type in SQL.

2. When creating a table in Access, pay attention to its reserved keywords. For example, if a field in the Password table uses a keyword, a series of statement errors will occur. For example, update T_Users setUsername = @ Username, Password = @ Password where UserId = @ UserId "indicates" System. data. oleDb. oleDbException: The UPDATE statement syntax is incorrect ". for more reserved keywords, refer to the Internet.

3. import data from SQL to the access database.

Note: sql2008 can only be exported to the database of access2007.

Changes to Asp.net

1. Modify the connection string:

Change <add name = "DBStr" connectionString = "data source =.; Database = shelian; integratedsecurity = true"/>

<Addname = "DBStr" connectionString = "Provider = Microsoft. Jet. OLEDB.4.0; DataSource = | DataDirectory | shelian. mdb" providerName = "System. Data. OleDb"/>

Tip: "Jet. OLEDB.4.0" corresponds to access2003, "| DataDirectory |" indicates that the database is in the App_Date directory.

2. Import using System. Data. OleDb; namespace.

Change SqlConnection, SqlCommand, SqlParameter, SqlDataAdapter, SqlParameter, SqlDataReader, and SqlType starting with SQL to OleDbConnection, OleDbCommand, OleDbParameter, OleDbDataAdapter, OleDbParameter, OleDbDataReader, and OleDbType.

3. modify. . Net file.

New SqlParameter ("@ CategoryName", SqlDbType. NVarChar, 100 ),

NewSqlParameter ("@ ParentId", SqlDbType. NVarChar, 50 ),

NewSqlParameter ("@ Path", SqlDbType. NVarChar, 200 ),

NewSqlParameter ("@ Depth", SqlDbType. Int, 4 ),

NewSqlParameter ("@ ChildIds", SqlDbType. NVarChar, 50 ),

NewSqlParameter ("@ IsActive", SqlDbType. Bit, 1 ),

NewSqlParameter ("@ ArticleNum", SqlDbType. Int, 4 ),

NewSqlParameter ("@ Readme", SqlDbType. NVarChar, 200 ),

NewSqlParameter ("@ CategoryId", SqlDbType. Int, 4 )};

Changed:

New OleDbParameter ("@ CategoryName", OleDbType. LongVarWChar, 100 ),

New OleDbParameter ("@ ParentId", OleDbType. LongVarWChar, 50 ),

New OleDbParameter ("@ Path", OleDbType. LongVarWChar, 200 ),

New OleDbParameter ("@ Depth", OleDbType. Integer, 4 ),

NewOleDbParameter ("@ ChildIds", OleDbType. LongVarWChar, 50 ),

New OleDbParameter ("@ IsActive", OleDbType. Boolean, 1 ),

New OleDbParameter ("@ ArticleNum", OleDbType. Integer, 4 ),

New OleDbParameter ("@ Readme", OleDbType. LongVarWChar, 200 ),

New OleDbParameter ("@ CategoryId", OleDbType. Integer, 4 )};

4. Modify the query statement:

4.1 single quotes of true or false will be removed, such as HomeShowImg = 'true' to HomeShowImg = true

4.2! = 0 to <> 0, such as IsPicNews! = 0-> IsPicNews <> 0

4.3 = ''is null. And LogImagePath! = '-> LogImagePath is not nul

4.4 NewsId = '"+ newsiid +"' "; changed to NewsId =" + newsiid + "";

5. when the update statement is correct but the content cannot be updated and VS does not prompt an error, you can consider whether it is the order of parameters in OleDbParameter, oleDbParameter parameters must be in the same order as those in the update statement.

For example, string plain text = "updateT_FriendlyLink set IsShown = @ IsShown, LinkUrl = @ txtUrl, LinkText = @ txtTitle whereId = @ Id ";

OleDbParameter [] ps = {

NewOleDbParameter ("@ Id", Id ),

NewOleDbParameter ("@ IsShown", IsShown ),

NewOleDbParameter ("@ txtUrl", txtUrl ),

NewOleDbParameter ("@ txtTitle", txtTitle ),

};

This can be run in SQLServer, but must be changed

OleDbParameter [] ps = {

NewOleDbParameter ("@ IsShown", IsShown ),

NewOleDbParameter ("@ txtUrl", txtUrl ),

NewOleDbParameter ("@ txtTitle", txtTitle ),

New OleDbParameter ("@ Id", Id ),

};

6. Access does not support: select @ IDENTITY

7. Access does not support OW_NUMBER () OVER Aggregate functions. You can use "select. * from (select top {0} * from t_news {2} {3}) a leftjoin (select top {1} * from t_news {2} {3}) B on. newsId = B. newsId where iif (B. newsId, '0', '1') = '1' ", endIndex, startIndex-1, swhere, sorder); alternative.

8. No top 0 or a negative number is allowed in Access. For example, if select top 0 * fromt_news where isshown = true, an error is returned: "The SELECT clause contains a reserved word, spelling error, or missing parameter, or incorrect punctuation. "

Description of Access database Field Types and comparison with SQL

Text nvarchar (n)

Note ntext

Number (long integer) int

Integer smallint

Number (single precision) real

Number (Double Precision) float

Number (byte) tinyint

Currency money

Date smalldatetime

Boolean

Appendix: the script for converting to SQL.

Alter table tbALTER COLUMN aa Byte number [Byte]

Alter table tb alter column aa Long number [Long integer]

Alter table tb alter column aa Short number [integer]

Alter table tb alter column aa Single number [Single precision

Alter table tb alter column aa Double number [Double Precision]

Alter table tb alter column aa Currency

Alter table tb alter column aa Char text

Alter table tb alter column aa Text (n) Text, where n indicates the field size

Alter table tb alter column aa Binary

Alter table tb alter column aa Counter automatic number

Alter table tb alter column aa Memo remarks

Alter table tb alter column aa Time date/Time

In the design view of a table, each field has a design type. Access allows nine data types: Text, remarks, values, date/time, and goods.

Currency, automatic number, YES/NO, OLE object, hyperlink, query wizard.

Text: This type allows a maximum of 255 characters or numbers. The default size of Access is 50 characters, and the system only saves

Without saving empty characters in unused positions in text fields. You can set the "field size" attribute to control the maximum length of characters that can be entered.

Note: This type is used to save long texts and numbers. It allows fields to store up to 64000 characters of content. But Access is not

You can sort or index remarks, but you can sort and index text fields. Although text can be searched in the remarks field, it is not as good

Search for indexed text fields quickly.

Number: this field type can be used to store numeric data for arithmetic calculation. You can also set the "field size" attribute to define a specific

Can be set to "Byte", "integer", "Long Integer", "single precision ",

"Double Precision", "synchronous copy ID", and "decimal number. In Access, the default value is "Double Precision ".

Date/time: this type is used to store date, time, or date and time. Each date/time field requires 8 bytes to store space.

Currency: This type is a special type of digital data. It is equivalent to the numeric field type with double-precision attributes. Input data to the currency Field

You do not need to enter a comma (,) in the renminbi symbol or a thousand characters. Access automatically displays the renminbi symbol and comma, and adds two decimal places to the currency field.

When the fractional part is more than two digits, Access rounds the data. The precision is 15 digits to the left of the decimal point and 4 digits to the right.

Automatic number: This type is special. Each time a new record is added to a table, Access automatically inserts a unique sequence or random number, that is

Specifies a value in the dynamic number field. Once the automatic number is specified, it is connected to the record permanently. If the deleted table contains an automatic number word

After a record of a segment, Access does not re-number the Field automatically numbered in the table. When a record is added, Access no longer uses the deleted

The value of the field is automatically numbered, but the value is re-assigned according to the increasing law.

Yes/No: this field is set for a field that contains only two different optional values. By using the format feature of yes/no data type, you can select the "yes" or "no" field.

OLE object: this field allows a separate "Link" or "embedded" OLE object. When adding data to the OLE object field, you can link or embed the OLE object in the Access Table to the object created by other OLE protocol programs, such as Word documents, EXCEL workbooks, images, sounds, or other binary data. The maximum value of the OLE object field is 1 GB, which is mainly restricted by disk space.

Hyperlink: this field is mainly used to save the hyperlink, including the text used as the hyperlink address or the combination of characters and numbers stored in the form of text. When you click a hyperlink, the WEB browser or Access will reach the specified target based on the hyperlink address. A hyperlink can contain up to three parts: one is the text displayed in a field or control, the other is the path to a file or page, and the other is the address in a file or page. The easiest way to insert a hyperlink address in this field or control is to click the hyperlink command in the Insert menu.

Query wizard: this field type provides a list of created fields. You can select the content listed in the List as the content of the added field.