Updating SQL Server Data

Another type of query that you will often need to run on your ASP.NET pages that include database connectivity are Update queries. Update queries allow you to modify existing records in an SQL Server database table. This technique shows you how to run an Update query from your ASP.NET code.

The page presented with this technique allows the visitor to give all the employees in the Employees table a raise. The visitor enters the factor for the raise and every employee has their salary increased by that factor.

Defined on the ASP.NET page is this TextBox control for entering the raise amount:

<asp:textbox id="txtRaise" runat="Server"/>

Also a Button control is defined:

<asp:button id="butOK" text=" OK " onclick="SubmitBtn_Click" runat="server" />

When that Button control is clicked this code block fires:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)

Within that procedure, you will need a Connection object and a Command object:

Dim DBConn as SQLConnection Dim DBUpdate As New SQLCommand

You start by connecting to your SQL Server database:

DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;"
& "User Id=sa;" _
& "Password=yourpassword;")

Next, you place the text of the Update query into the CommandText property of the Command object:

DBUpdate.CommandText = "Update Employees Set " _
& "Salary = Salary * " & txtRaise.Text

The Command object will connect to the database through the Connection object:

DBUpdate.Connection = DBConn DBUpdate.Connection.Open

The Update query can then be executed:

DBUpdate.ExecuteNonQuery()