Expertrating - ASP.Net Tutorial, ADO.NET

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

ASP.NET Tutorial - ADO.NET

Page 10 of 14

Parametric UPDATE

Practically Parametric UPDATE is used in the form of a web i.e. the parameters are passed from outside.

One difference between the SQL Update and Insert commands is that the SQL Update command affects more than one record at a time. When you execute an Update command, it changes every record that satisfies the command where clause.

You can determine the number of records affected by an Update command within an ASP.NET page by holding the value returned by the ExecuteNonQuery () method. The following example demonstrates it: -

Example 31 SqlDbParameterUpdate.aspx

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

Sub Button_Click (s As Object, e As EventArgs)

Dim conNorthwind As SqlConnection

Dim strUpdate As String

Dim cmdUpdate As SqlCommand

Dim intRecordCount As Integer

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

strUpdate = "Update Books Set Author=@author_name Where bookName=@book_name"

cmdUpdate = New SqlCommand( strUpdate, conNorthwind )

cmdUpdate.Parameters.Add( "@book_name", txtBookName.Text )

cmdUpdate.Parameters.Add( "@author_name", txtAuthorName.Text )

conNorthwind. Open ()

intRecordCount = cmdUpdate.ExecuteNonQuery()

conNorthwind. Close ()

lblAuthor. Text = intRecordCount & " records updated!"

End Sub

</Script>

<html>

<body>

<h3>Update Book's Name</h3>

<br>

<asp:TextBox

ID="txtBookName"

Runat="Server" />

<p>

<br>

<b>Author's Name</b>

<br>

<asp:TextBox

ID="txtAuthorName"

Runat="Server" />

<p>

<asp:Button Text="Update Author" OnClick="Button_Click" Runat="Server" />

<p>

<asp:Label ID="lblAuthor" Runat="Server" />

</form>

</body>

</html>

The output of this example is as shown below:

The above example has two TextBox controls: one in a Book’s name and the other in Author’s Name. When you click the Update Author! Button, the Button Click subroutine is executed, and the phone number for the real author is updated.

Finally, the number of records updated is retrieved from the ExecuteNonQuery () method of the SqlCommand class. This result is assigned to a Label control lblAuthor.

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

Finally, the number of records updated is retrieved from the ExecuteNonQuery () method of the SqlCommand class. This result is assigned to a Label control lblAuthor.

ExpertRating ASP.NET Certification - Get Certified Now!!