Expertrating - ASP.Net Tutorial, ADO.NET

Home > Courses, Tutorials & eBooks > ASP.NET Tutorial > ADO.NET

ASP.NET Tutorial - ADO.NET

Page 9 of 14

In the above example there are two TextBox controls named txtbookName and txtAuthor. When the Add Books! Button, are clicked the Button Click subroutine is executed, and the values entered into the two TextBox controls are inserted into the Books table of MS Access database which is located at c:\Books.mdb.

Updating Database Records

To update the database record in the table the UPDATE command is used as: -

UPDATE table_name SET columnl = valuel, column2 = value2... WHERE condition

Suppose you want to update a table Book in which you want to Update the name of the Author from “John” to “David”. The code used would be like: -

Update books

Set Author=’David’

Where Author=’John’

To use UPDATE COMMAND follow these steps: -

1.Create and open a database connection.

2.Create a database command that represents the SQL Update statement to execute.

3.Execute the command with the ExecuteNonQuery () method.

The following example shows how the books are updated.

Example 29 SqlUpdateDemo.aspx

<%@ Import Namespace="System.Data.SqlClient" %>

Dim conNorthwind As SqlConnection

Dim strUpdate As String

Dim cmdUpdate As SqlCommand

conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" )

strUpdate = "Update Books Set Author='David' Where Author='John'"

cmdUpdate = New SqlCommand( strUpdate, conNorthwind )

conNorthwind. Open ()

cmdUpdate. ExecuteNonQuery ()

conNorthwind. Close ()

Author Updated in the Table Books!

The output of this example is shown below: -

The first statement in the above example imports the necessary namespace for working with the SQL ADO.NET classes.

Next, a database connection is initialized with the correct connection string for the local server. In the statement that follows, passing a SQL Update command and SqlConnection to the constructor for the class creates an instance of the SqlCommand class.

The SQL Update command is executed when the ExecuteNonQuery () method of the SqlCommand class is called. At this point, the Update statement is transmitted to SQL Server and executed.

The example above shows that the code above works only with Microsoft SQL Server (version 7.0 and higher). If you want to update a record in a Microsoft Access database table, then you need to use the ADO.NET classes from system.Data.OleDb namespace rather than the System.Data.SqlClient namespace as shown below: -

Example 30 OleDbUpdateDemo.aspx

<%@ Import Namespace="System.Data.OleDb" %>

Dim conAuthors As OleDbConnection

Dim strUpdate As String

Dim cmdUpdate As OleDbCommand

conAuthors= New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Books.mdb")

strUpdate = "Update Books Set Author='David' Where Author='John'"

cmdUpdate = New OleDbCommand( strUpdate, conAuthors )

conAuthors. Open ()

cmdUpdate. ExecuteNonQuery ()

conAuthors. Close ()

Author Updated in the Table Books!

The output of this example is as shown below:-

The first statement in the above example imports the necessary namespace for working with the OleDb ADO.NET classes. Next, a database connection is initialized with the correct connection string for the local server. In the statement that follows, passing a SQL Update command and OleDbConnection to the constructor for the class creates an instance of the OleDbCommand class.

The SQL Update command is executed when the ExecuteNonQuery () method of the OleDbCommand class is called. At this point, the Update statement is transmitted to MS Access and executed.

ExpertRating ASP.NET Certification - Get Certified Now!!