Actualizar datos en una base de datos de SQL
En esta sección se muestra cómo utilizar una aplicación Web para actualizar campos de datos en una base de datos existente.
En el siguiente ejemplo se presenta una página con una columna en el lado izquierdo del objeto DataGrid que contiene un vínculo con el título Editar en cada fila. Al hacer clic en el vínculo, todos los campos de esa fila se convierten en cuadros de texto que contienen los datos existentes. Es posible editar los campos de manera que contengan nueva información. Cuando se termine de editar un campo, hay que hacer clic en el vínculo Actualizar para actualizar los datos de esa fila en la base de datos.
Para ver un ejemplo similar, ejecute el ejemplo DataGrid6.aspx del Tutorial de ASP.NET.
<%@ 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>
Vea también
Acceso a datos con ASP.NET | Acceso a datos con ADO.NET | System.Web.UI.WebControls (Espacio de nombres) | DataGrid (Clase)