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 correspondence between the access field type and 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.

Steps for converting SQL database to access database: 1. Create an access database: Create an access database and table in access. the correspondence between the access field type and 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.

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

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