Easy to teach you SQL transfer access

SQL database go to access database steps:

1. Create an Access database: An Access database and tables are established in access, and the Access field type corresponds to the type of field in SQL.

2. Building a table in Access should pay attention to its reserved keywords: for example password if a field in a table uses a keyword, it can result in a series of statement errors. For example, update t_users setusername= @Username, password= @Password where userid= @UserId "prompt at this point" Syntax error for System.Data.OleDb.OleDbException:UPDATE statement ". More reserved Keywords can refer to the Internet.

3. Import data from SQL to an Access database.

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

asp.net changes to be made

1. Modify the connection string:

<add name= "Dbstr" connectionstring= "Data source=.;D Atabase=shelian;integratedsecurity=true "/> Changed to

<addname = "Dbstr" connectionString = "provider=microsoft.jet.oledb.4.0;datasource=| Datadirectory|shelian.mdb "providername =" System.Data.OleDb "/>

Hint: "jet.oledb.4.0" corresponds to the access2003 version, "| Datadirectory| " Indicates that the database is in the App_date directory.

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

will begin with SQL SqlConnection, SqlCommand, SqlParameter, SqlDataAdapter, SqlParameter, SqlDataReader, SqlType to OleDbConnection, OleDbCommand, Oledbparameter,oledbdataadapter, OleDbParameter, OleDbDataReader, OleDbType.

3. Change. NET file, the type of field in the table that you want to manipulate in the

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)};

Change into:

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 word statement style:

4.1 will remove the single quotes true or false, such as homeshowimg= ' true ' to homeshowimg=true

4.2!=0 to <>0, such as Ispicnews!=0-> ispicnews

4.3 = ' change to IS null. ' and Logimagepath!= '-> Logimagepath is not nul

4.4 NewsId = ' "+ newsiid +" ' "; change to NewsId =" + Newsiid + "";

5. When the UPDATE statement is correct but the content cannot be updated and VS is not prompted for error, consider whether the corresponding order of the parameters in the OleDbParameter is a problem, and the order of the OleDbParameter parameters is consistent with the order appearing in the UPDATE statement.

For example: string cmdtext = "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 SQL Server, but must be changed in Access

Oledbparameter[]ps ={

Newoledbparameter ("@IsShown", Isshown),

Newoledbparameter ("@txtUrl", Txturl),

Newoledbparameter ("@txtTitle", Txttitle),

New OleDbParameter ("@Id", Id),

};

6.Access not supported: SELECT @ @IDENTITY

7.Access does not support Ow_number () over aggregate functions. You can use ' Select A.* from ' (select Top{0} * from t_news {2} {3}) a leftjoin when paging (select top {1} * from t_news {2}{3}) b on A.ne Wsid=b.newsid where IIF (b. NewsId, ' 0 ', ' 1 ') = ' 1 ', Endindex, StartIndex-1, Swhere, Sorder);

8. You cannot have a negative number for top 0 or top in Access. For example, select top 0 * fromt_news where isshown=true will have an error: The SELECT clause contains a reserved word, misspelled or missing parameter, or the punctuation mark is incorrect. "

Access database field type description and control relationship to SQL

Text nvarchar (n)

Note ntext

Number (long int) int.

Number (integer) smallint

Digital (single precision) real

Number (double precision) float

Number (bytes) tinyint

Currency money

Date smalldatetime

Boolean bit

Attached: script converted to SQL.

ALTER TABLE tbalter COLUMN aa byte number [bytes]

Alter TABLE TB ALTER COLUMN AA long digit [long integer]

Alter TABLE TB ALTER COLUMN AA short number [int]

Alter TABLE TB ALTER COLUMN AA single number [precision

Alter TABLE TB ALTER COLUMN AA double digit [double precision]

Alter TABLE TB ALTER COLUMN AA Currency Currency

Alter TABLE TB ALTER COLUMN AA Char text

Alter TABLE TB alter COLUMN AA text (n), where n represents the field size

Alter TABLE TB ALTER COLUMN AA Binary binary

Alter TABLE TB ALTER COLUMN AA Counter automatic numbering

Alter TABLE TB ALTER COLUMN AA Memo remarks

Alter TABLE TB ALTER COLUMN AA Date/Time

In the Design view of a table, each field has a design type, and access allows nine types of data: text, notes, values, date/time, goods

Currency, AutoNumber, 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 saves only the input to the field

character without saving the null character on the unused position in the text field. You can set the FieldSize property to control the maximum length of characters that can be entered.

Note: This type is used to hold longer text and numbers that allow a field to store content up to 64,000 characters long. But Access does not

You can sort or index a Memo field, but you can sort and index the text fields. Although you can search for text in the Memo field, it is not as

You can search faster in indexed text fields.

Number: This type of field can be used to store numeric data for arithmetic calculations, and the user can also set the FieldSize property to define a specific

numeric type, any font specified as a numeric data type can be set to "byte", "integer", "Long Integer", "single precision Number",

The five types of double precision, synchronous Replication ID, decimal. Typically, in Access, the default is double precision.

Date/Time: This type is used to store date, time, or date time together, each date/Time field requires 8 bytes to store space.

Currency: This type is a special type of numeric data type, equivalent to a numeric field type with a double attribute. Enter data into a currency field

, you do not have to type a comma between the renminbi symbol and the thousand, Access automatically displays the RMB symbol and comma, and adds two decimal places to the Currency field.

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

AutoNumber: This type is special, and each time you add a new record to the table, Access automatically inserts a unique order or random number, which is

Specify a value in the Number field. Once the AutoNumber is specified, it is permanently connected to the record. If you delete a table that contains an AutoNumber word

After a record of a segment, Access does not renumber the table AutoNumber field. When you add a record, access no longer uses the deleted

The numeric value of the AutoNumber field, which is reset by the increment rule.

Yes/No: This field is for fields that are set up to contain only two different optional values in a field, and the user can select a Yes/No field through the format attribute of the Yes/No data type.

OLE object: This field refers to a field that allows you to "link" or "Embed" OLE objects individually. When you add data to an OLE Object field, you can link or embed an OLE object in an Access table by using an object created in another OLE protocol, such as a Word document, Excel spreadsheet, image, sound, or other binary data. OLE Object fields can be up to 1GB, which is primarily limited by disk space.

Hyperlinks: This field is primarily used to hold hyperlinks, contain text that is a hyperlink address, or a combination of characters and numbers stored as 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 that is displayed in the field or control, the other is the path to the file or page, and the third is the address in the file or page. The easiest way to insert a hyperlink address in this field or control is to click the Hyperlink command on the Insert menu.

Lookup Wizard: This field type provides the user with a list of the contents of the field, which you can select from the list as the content to add to the field.