Updating Data in a SQL Database
The following code example demonstrates how to use a Web application to update data fields in an existing database.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
Dim myConnection As SqlConnection
' Create a connection to the "pubs" SQL database located on the
' local computer.
Sub Page_Load(Src As Object, E As EventArgs)
myConnection = New SqlConnection("server=localhost;" _
& "database=pubs;Trusted_Connection=Yes")
' Determine whether this page is a postback. If it is not a
' postback, call BindGrid.
If Not IsPostBack Then
BindGrid()
End If
End Sub
' Create an index to the DataGrid row that is clicked and
' call BindGrid.
Sub MyDataGrid_Edit(sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
' Cancel resets the index to the row's previous settings.
Sub MyDataGrid_Cancel(sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
' When the Update link is clicked, build a SQL UPDATE command,
' connect to the database, update the row's information in the
' database, and rebind the DataGrid to show the updated information.
Public Sub MyDataGrid_Update(sender As Object, _
E As DataGridCommandEventArgs)
Dim updateCmd As String = "UPDATE Authors SET au_id = @Id," _
& " au_lname = @LName, au_fname = @FName, phone = @Phone," _
& " address = @Address, city = @City, state = @State," _
& " zip = @Zip, contract = @Contract WHERE au_id = @Id;"
Dim myCommand As SqlCommand = New SqlCommand(updateCmd, _
myConnection)
myCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@LName", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@FName", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@Phone", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@Address", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@City", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@State", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@Zip", _
SqlDbType.VarChar))
myCommand.Parameters.Add(New SqlParameter("@Contract", _
SqlDbType.VarChar))
' Initialize the SqlCommand "@ID" parameter to the ID of the row
' that must be clicked.
myCommand.Parameters("@Id").Value = _
MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
' Create an array of column names.
Dim cols() As String = {"@Id","@LName","@FName","@Phone", _
"@Address", "@City","@State","@Zip","@Contract"}
' Skipping the first, second, and last columns, iterate through the
' columns, checking for empty values. If an empty value is found,
' display a message box. Also initialize the SqlCommand
' parameter values.
Dim numCols As Integer = E.Item.Cells.Count
Dim i As Integer
Dim colvalue As String
Dim txtBox As Textbox
For i = 2 To numCols-1
txtBox = E.Item.Cells(i).Controls(0)
colvalue = txtBox.Text
If (i<6 And colvalue = "")
Message.InnerHtml = "ERROR: Null values not allowed for " _
& "Author ID, Name or Phone"
Message.Style("color") = "red"
Exit Sub
End If
myCommand.Parameters(cols(i-1)).Value = colvalue
Next i
' Append the last field, converting true/false values to 0/1.
txtBox=E.Item.Cells(numCols-1).Controls(0)
If (String.Compare(txtBox.Text, "true", true) = 0) Then
myCommand.Parameters("@Contract").Value = "1"
Else
myCommand.Parameters("@Contract").Value = "0"
' Connect to the database and update the information.
myCommand.Connection.Open()
' Test whether the data was updated, and display the
' appropriate message to the user.
Try
myCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated.</b><br>"
MyDataGrid.EditItemIndex = -1
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists" _
& " with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record," _
& " please ensure the fields are correctly filled out."
Message.Style("color") = "red"
End If
End Try
' Close the connection.
myCommand.Connection.Close()
' Rebind the DataGrid to show the updated information.
BindGrid()
End If
End Sub
' The BindGrid procedure connects to the database and implements
' a SQL SELECT query to get all the data in the "Authors" table.
Public Sub BindGrid()
Dim myConnection As SqlConnection = _
New SqlConnection("server=localhost;" _
& "database=pubs;Trusted_Connection=Yes")
Dim myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
& " FROM authors", myConnection)
Dim ds As DataSet= New DataSet()
myCommand.Fill(ds)
MyDataGrid.DataSource = ds
MyDataGrid.DataBind()
End Sub
</script>
<%-- Display the data in the body of the page. --%>
<body style="font: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Updating a Row of Data.</font></h3>
<span id="Message" EnableViewState="false"
style="font:arial 11pt;" runat="server"/><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="800"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="au_id"
>
<Columns>
<ASP:EditCommandColumn EditText="Edit" CancelText="Cancel"
UpdateText="Update"/>
</Columns>
</ASP:DataGrid>
</form>
</body>
</html>
[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Text"%>
<html>
<script language="C#" runat="server">
SqlConnection myConnection;
protected void Page_Load(Object Src, EventArgs E)
{
// Create a connection to the "pubs" SQL database located on the
// local computer.
myConnection = new SqlConnection ("server=localhost;" +
"database=pubs;Trusted_Connection=Yes");
// Determine whether the page is a postback. If it is not a
// postback, call BindGrid.
if (!IsPostBack) BindGrid();
}
// Create an index to the DataGrid row that is clicked and
// call BindGrid.
public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)
{
MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;
BindGrid();
}
// Cancel resets the index to the row's previous settings.
public void MyDataGrid_Cancel(Object sender,
DataGridCommandEventArgs E)
{
MyDataGrid.EditItemIndex = -1;
BindGrid();
}
// When the Update link is clicked, build a SQL UPDATE command,
// connect to the database, update the row's information in the
// database, and rebind the DataGrid to show the updated information.
public void MyDataGrid_Update(Object sender,
DataGridCommandEventArgs E)
{
String updateCmd = "UPDATE Authors SET au_id = @Id," +
" au_lname = @LName, au_fname = @FName, phone = @Phone," +
" address = @Address, city = @City, state = @State," +
" zip = @Zip, contract = @Contract WHERE au_id = @Id;";
SqlCommand myCommand = new SqlCommand(updateCmd, myConnection);
myCommand.Parameters.Add(new SqlParameter("@Id",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@LName",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@FName",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@Phone",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@Address",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@City",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@State",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@Zip",
SqlDbType.VarChar));
myCommand.Parameters.Add(new SqlParameter("@Contract",
SqlDbType.VarChar));
// Initialize the SqlCommand "@Id" parameter to the ID of the
// row that must be clicked.
myCommand.Parameters["@Id"].Value =
MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
// Create an array of column names.
String[] cols = {"@Id","@LName","@FName","@Phone",
"@Address", "@City","@State","@Zip","@Contract"};
// Iterate through the columns, checking for empty values.
// Skip the first, second, and last columns. If an empty value
// is found, display an error message box. Also initialize the
// SqlCommand parameter values.
int numCols = E.Item.Cells.Count;
for (int i=2; i<numCols-1; i++)
{
String colvalue = ((TextBox)E.Item.Cells[i].Controls[0]).Text;
if (i<6 && colvalue == "")
{
Message.InnerHtml = "ERROR: Null values not allowed for" +
" Author ID, Name or Phone";
Message.Style["color"] = "red";
return;
}
myCommand.Parameters[cols[i-1]].Value = colvalue;
}
// Append the last field, converting true/false values to 0/1.
if (String.Compare(((TextBox)E.Item.Cells
[numCols-1].Controls[0]).Text, "true", true)==0)
myCommand.Parameters["@Contract"].Value = "1";
else
myCommand.Parameters["@Contract"].Value = "0";
// Connect to the database and update the information.
myCommand.Connection.Open();
// Test whether the data was updated and display the
//appropriate message to the user.
try
{
myCommand.ExecuteNonQuery();
Message.InnerHtml = "<b>Record Updated.</b><br>";
MyDataGrid.EditItemIndex = -1;
}
catch (SqlException e)
{
if (e.Number == 2627)
Message.InnerHtml = "ERROR: A record already exists" +
" with the same primary key";
else
Message.InnerHtml = "ERROR: Could not update record," +
" please ensure the fields are correctly filled out";
Message.Style["color"] = "red";
}
// Close the connection.
myCommand.Connection.Close();
// Show the updated information.
BindGrid();
}
// The BindGrid procedure connects to the database and implements
// a SQL SELECT query to get all data in the "Authors" table.
public void BindGrid()
{
SqlConnection myConnection = new SqlConnection(
"server=localhost;database=pubs;Trusted_Connection=Yes");
SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT * FROM authors", myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds);
MyDataGrid.DataSource=ds;
MyDataGrid.DataBind();
}
</script>
<%-- Display the data in the body of the page. --%>
<body style="font: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Updating a Row of Data.</font></h3>
<span id="Message" EnableViewState="false"
style="font:arial 11pt;" runat="server"/><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="800"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="au_id"
>
<Columns>
<ASP:EditCommandColumn EditText="Edit" CancelText="Cancel"
UpdateText="Update"/>
</Columns>
</ASP:DataGrid>
</form>
</body>
</html>
See Also
Accessing Data with ASP.NET | Accessing Data with ADO.NET | System.Web.UI.WebControls Namespace | DataGrid Class