This article does not expell how to display data in the database, only propose how to increase, delete, modify data, and put forward in subsequent articles on how to display data.
1. Define oledbcommand type variables: MyCommand
To increase, delete, modify the database. We also need to define an OLEDBCommand or SQLCOMMAND object according to the type of MyConnectio (please note that if MyConnection is the OLEDBConnection type, then you can only use oleDbcommand; if MyConnection is the SqlConnection type, then then you can only use Sqlcommand. It is assumed that myConnection is an OLDBConnection class).
method one
You can drag and drop an OLEDBCommand like drag and drop myConnection and name mycommand.
Method Two
In (associated file). Protected system.data.oledb.oledbconnection myconnection; below:
protected system.data.oledb.oledbcommand mycommand;
In private vidinnetic = news.myconnection = news.myconnection = newsystem.data.oledb.oledbconnection (); below the next line:
THIS.MYCOMMAND = new system.data.oledb.oledbcommand ();
You can complete the definition of MyCommand
Description: MyCommand's role is used to execute SQL commands
Second, use the defined MyConnectio and MyCommand to increase, delete, modify the database
First we need to connect and open a database (for the connection and open operations of the database, please see our previous article). Open the database: MyConnectio.Open ();
Then we need to specify the SQL command to be executed for MyCommand:
Mycommand.commandtext = "delete from admin";
Then we need to specify a data source for MyCommand (execute sql commands to that database): myCommand.connection = myconnection; then we execute myCommand command: mycommand. ExecutenonQuery (); if we are executing also "delete from admin";
After the "INSERT INTO Admin (AA ',' BB ')" is then performed, then we only need to specify myCommand again to specify the SQL command to be executed: mycommand.commandtext = "INSERT INTO Admin (admin_code, admin_pwd VALUES ('aa', 'bb'), then execute mycommand. ExecutenonQuery (); (Because the database is not closed, we don't need MYCONNECTIO.Open (); and the same reason is not to change the data source of MyCommand so we don't need to specify myCommand.connection = myConnection;
Below we will explain in detail how to increase, delete, modify the database in page_load (), and finally we will summarize the usage of ExecuteNonQuery (), ExecuteScalar (), ExecuteReader.
1, add new records
Private void Page_load (Object Sender, System.Eventargs E) {
MyConnection.Open (); 'Open Database
Mycommand1.commandtext = "INSERT INTO Admin VALUES ('AADDQ', 'AS', 'SS')
MyCommand1.connection = myconnection;
MyCommand1.executenonQuery (); 'Due to add a record, return 1
// or mycommand1.executeReader (); first add a record, then return a system.data.oledb.oledbDataReader type object, the object is: EOF
// or mycommand1. ExecuteScalar (); first add a record, return unprotected objects
MyConnection.Close ();
}
2. Delete existing data
Private Void Page_Load (Object Sender, System.EventArgs E)
{
MyConnection.Open (); 'Open Database
Mycommand1.commandtext = "delete * from admin";
MyCommand1.connection = myconnection;
MyCommand1.executenonQuery (); 'Due to the N-recorded record, returns N
// or mycommand1.executeReader (); first delete N records, then return a system.data.oledb.oledBDataReader type object, the object is: EOF
// or mycommand1. ExecuteScalar (); first delete N records, return unprotected objects
MyConnection.Close ();
}
3, modify existing data
Private Void Page_Load (Object Sender, System.EventArgs E)
{
MyConnection.Open (); 'Open Database
MyCommand1.commandtext = "Update admin set admin_code = '212', admin_pwd = '43 'where admin_code = '23';
MyCommand1.connection = myconnection;
MyCommand1.executenonQuery (); 'Due to the modified record, returns N
// or mycommand1.executereader (); first modify 1 record, then return a system.data.oledb.oledbDataReader type object, the object is: EOF
// or mycommand1. ExecuteScalar (); first modify 1 record, return unprotected objects
MyConnection.Close ();
}
Third, the difference from mycommand's executenonQuery (), ExecuteScalar (), ExecuteReader method:
1, executenonQuery (): Perform SQL, return a integer variable, if SQL is operated on the database, then the number of records affects the record, if it is sql = "CREATE TABLE LOOKUPCODES (Code_ID Smallint Identity (1, 1 Primary key clustered, code_desc varchar (50) NOT NULL "The method returns -1 after the table is created.
For example: Private void Page_Load (Object Sender, System.EventArgs E)
{
MyConnection.Open (); 'Open Database
Mycommand1.commandtext = "CREATE TABLINT Identity (1, 1) Primary Key Clustered, Code_Desc Varchar (50) Not Null"; MyCommand1.connection = MyConnection;
MyCommand1.executenonQuery (); first build a lookupcodes table, then return -1
// or mycommand1.executeReader (); first build a lookupcodes table, then return a system.data.oledb.oledbDataReader type object, the object is: EOF
// or mycommand1. ExecuteScalar (); first build a lookupcodes table, return unprotected objects
MyConnection.Close ();
}
2, executescalar (): Execute SQL, (if SQL is a query select) Returns the first line of the query results, if (if SQL is not querying SELECT), return unprinted objects, because the object is not substantial So the return result cannot be toString (), not equals (null), that is, the return result does not have any effect.
3, ExecuteReader method Execute SQL, (if SQL is a query select) Returns the collection of query results, the type is system.data.oledb.oledBDataReader, you can get the query data by this result. If (if SQL is not query select), return a collection of system.data.oledb.oledbdataReader type without any data (EOF)
Fourth, summary:
There are many ways to operate in the ASP.NET, and those who have unified targets may take different ways, as if there are people in the ASP like to use RS.Addnew, some people like to use "Insert Into", Mainly to see personal habits, of course, different methods can have a big difference, this can only rely on our accumulation of experience in the usual study. In addition, the ASP.NET page provides a method similar to the following:
OLEDBCOMMAND2.Parameters ("au_id"). Value = TextBox1.text
OLEDBCOMMAND2.Parameters ("au_lname"). Value = TextBox2.text
OLEDBCOMMAND2.Parameters ("au_fname"). Value = TextBox3.Text
OLEDBCommand2.Parameters ("Phone"). Value = TextBox4.text
OLEDBCOMMAND2.Parameters ("address"). Value = textbox5.text
OLEDBCommand2.Parameters ("city"). Value = textbox6.text
OLEDBCommand2.Parameters ("st"). Value = textBox7.textoledbcommand2.parameters ("zip"). Value = TextBox8.Text
OLEDBCOMMAND2.Parameters ("contract"). Value = checkbox1.checked
cmdResults = OLEDBCommand2.executenonQuery ()
