Notes when using access in asp.net 2.0 for development

Source: http://www.study-code.com/dotnet/aspnet/67492.htm

 

1. to grant access permissions to the database, right-click the access database on my computer, click the "properties" menu, select the "Security" tab, and select the Users Group, grant full control permissions

If there is no security tab, click "Tools"> "Folder Options" in the menu to go to the "View" tab and remove the check box before "use simple file sharing ".

2. When connecting to the database, if you want to change the database name at any time, and do not re-compile the website, you can record the database path in the web. config file:

<ConnectionStrings>
<Add name = "aceConnectionString" connectionString = "directory \ database name. mdb" providerName = "System. Data. OleDb"/>
</ConnectionStrings>
Use these two methods:

Public static string connectString = "Provider = Microsoft. Jet. OleDb.4.0; Data Source =" + HttpContext. Current. Server. MapPath (ConfigurationManager. ConnectionStrings ["aceConnectionString"]. ConnectionString );

Public static string GetDB ()
{
Return HttpContext. Current. Server. MapPath (ConfigurationManager. ConnectionStrings ["aceConnectionString"]. ConnectionString );
}

If the AccessDataSource control is used on each page, this control does not read the value by itself. You can use AdsAd. DataFile = NnllClass. GetDB () on the Page_Load page to assign a value to it.

Protected void Page_Load (object sender, EventArgs e)
{
AdsAd. DataFile = NnllClass. GetDB ();
}
3. If AccessDataSource is used to add, delete, and modify records, pay attention to parameter issues. There are some websites that report errors to some parameters, the answer is that the access database should use the concatenated SQL statement, but the parameter cannot be used. This is incorrect. The access database can also use the parameter transfer method to add, delete, modify, and query the database.

That is, because vs.net 2005 cannot be used as a child when ms SQL is used, parameters are automatically retrieved. Therefore, you must manually add all parameters. Be careful when adding parameters, no less, no more, and no messy order

The AccessDataSource control is found to correspond to the input parameters according to the order of the bound fields. That is to say, if you are in FormView, it is bound like this:

<Asp: TextBox ID = "myField1TextBox" runat = "server" Text = '<% # Bind ("myField1") %> 'maxlength = "20"> </asp: textBox>
<Asp: TextBox ID = "myField2TextBox" runat = "server" Text = '<% # Bind ("myField2") %> 'maxlength = "20"> </asp: textBox>
When defining parameters, we should define them like this:

<InsertParameters>
<Asp: Parameter Name = "myField1" Type = "String" Size = "20" ConvertEmptyStringToNull = "False"/>
<Asp: Parameter Name = "myField2" Type = "String" Size = "20" ConvertEmptyStringToNull = "False"/>
</InsertParameters>
Do not confuse the order. If the data format is not the same, an error will be reported. I forgot the specific error information.

When defining parameters, you should also note that parameter names should not be written incorrectly or missing

When using SQL database, if the parameter name is incorrect or the number of parameters is incorrect, an error message is displayed, that is, access database. In this case, no error is reported, if the parameter name is incorrect, it indicates that n parameters have no value. If a parameter is missing, it will update the database for you, it corresponds to the value of the parameter in the binding order. At this time, if the value following the parameter is greater than the size of the field corresponding to the preceding parameter, the error message "the field is too small" will be obtained.

 

<InsertParameters>
<Asp: Parameter Name = "myField1" Type = "String" Size = "20" ConvertEmptyStringToNull = "False"/>
<Asp: Parameter Name = "myField2" Type = "String" Size = "20" ConvertEmptyStringToNull = "False"/>
</InsertParameters>
If one

<Asp: Parameter Name = "myField3" Type = "String" Size = "200" ConvertEmptyStringToNull = "False"/>
During the update, the value of myField3 will be written into myField2. If the length of the value of myField3 is greater than 20, "the field is too small" will be reported.

3. If you are not used to the AccessDataSource control, bind your own data and provide some methods:

# Region return database address
Public static string GetDB ()
{
Return HttpContext. Current. Server. MapPath (ConfigurationManager. ConnectionStrings ["aceConnectionString"]. ConnectionString );
}
# Endregion

# Region open the database: SqlConnection OpenConnect ()
Public OleDbConnection OpenConnect (Page aPage, bool setMsg)
{
Try
{
OleDbConnection dbConnect = new OleDbConnection (connectString );
If (! DbConnect. State. Equals (ConnectionState. Open ))
{
DbConnect. Open ();
}
Return dbConnect;
}
Catch (OleDbException ex)
{
If (setMsg)
{
SetMsgJS (aPage. Master, "failed to connect to database:" + ex. Message. Replace ("\ n", ""). Replace ("\ r ",""));
}
Return null;
}
}
# Endregion

# Region shut down the database: void CloseConnect (SqlConnection dbConnect)
Public void CloseConnect (OleDbConnection dbConnect)
{
If (dbConnect. State. Equals (ConnectionState. Open ))
{
DbConnect. Close ();
}
}
# Endregion

# Region reads data and returns DataSet
Public DataSet GetDataSet (Page aPage, OleDbConnection dbConnection, CommandType commandType, string commandText, out string msg, bool setMsg)
{
Msg = "";

// Create and set SqlCommand
OleDbCommand dbCommand = new OleDbCommand ();
DbCommand. Connection = dbConnection;
DbCommand. CommandType = commandType;
DbCommand. CommandText = commandText;
// Create a SqlDataAdapter and obtain data
DataSet ds = new DataSet ();
Try
{
OleDbDataAdapter dataAdapter = new OleDbDataAdapter (dbCommand );
DataAdapter. Fill (ds );
}
Catch (InvalidOperationException eo)
{
Msg = "failed to read data:" + eo. message. replace ("\ n ",""). replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
}
Catch (OleDbException ex)
{
Msg = "failed to read data:" + ex. message. replace ("\ n ",""). replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
}

// Return data
Return ds;
}
# Endregion

# Region reads data and returns SqlDataReader
Public OleDbDataReader GetDataReader (Page aPage, OleDbConnection dbConnection, CommandType commandType, string commandText, OleDbParameter [] p, out string msg, bool setMsg)
{
Msg = "";
// Set the command
OleDbCommand dbCommand = new OleDbCommand ();
DbCommand. Connection = dbConnection;
DbCommand. CommandType = commandType;
DbCommand. CommandText = commandText;
// Set parameters
If (p! = Null)
{
Foreach (OleDbParameter pi in p)
{
DbCommand. Parameters. Add (pi );
}
}
// Read the first result set
OleDbDataReader dbReader = null;
Try
{
DbReader = dbCommand. ExecuteReader ();
}
Catch (InvalidOperationException eo)
{
Msg = "failed to read data:" + eo. message. replace ("\ n ",""). replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
}
Catch (OleDbException ex)
{
Msg = "failed to read data:" + ex. message. replace ("\ n ",""). replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
}
Return dbReader;
}
# Endregion

# Region Execute SQL commands or stored procedures
Public int execCommand (Page aPage, OleDbConnection dbConnection, CommandType commandType, string commandText, OleDbParameter [] p, out string msg, bool setMsg)
{
Bool needCloseDB = false;
Int row = 0;
Msg = "";
// Create and set SqlConnection
If (dbConnection = null)
{
NeedCloseDB = true;
DbConnection = OpenConnect (aPage, setMsg );
If (dbConnection = null) return-1;
}
// Create and set SqlCommand
OleDbCommand dbCommand = new OleDbCommand ();
DbCommand. Connection = dbConnection;
DbCommand. CommandType = commandType;
DbCommand. CommandText = commandText;
// Set parameters
If (p! = Null)
{
Foreach (OleDbParameter pi in p)
{
DbCommand. Parameters. Add (pi );
}
}
// Execute the command
Try
{
Row = dbCommand. ExecuteNonQuery ();
}
Catch (InvalidOperationException eo)
{
Msg = eo. Message. Replace ("\ n", ""). Replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
Row =-1;
}
Catch (OleDbException ex)
{
// Execution error
Msg = ex. Message. Replace ("\ n", ""). Replace ("\ r", "") + "\ n ";
If (setMsg)
{
SetMsgJS (aPage. Master, msg );
}
Row =-1;
}
// Close the database
If (needCloseDB)
{
CloseConnect (dbConnection );
}
Return row;
}
# Endregion
The above method uses a SetMsgJS method, which is used to set information for the master page.

Public static void SetMsgJS (MasterPage masterPage, string msg)
{
Literal lt = (Literal) masterPage. FindControl ("LiteralMsg ");
If (lt! = Null)
{
Lt. Text + = msg;
}
}
This is defined on the master page:

<Script type = "text/javascript">
Var msg = "";
Msg = "<asp: Literal ID =" LiteralMsg "runat =" server "> </asp: Literal> ";
If (msg! = "")
{
Alert (msg );
}
</Script>