Compartir a través de


Insertar datos en una base de datos SQL

En esta sección se muestra cómo agregar nuevas filas de datos a una base de datos mediante un control DataGrid y un formulario HTML personalizado que acepta datos proporcionados por el usuario.

Para ver un ejemplo similar, ejecute el ejemplo DataGrid4.aspx del Tutorial de ASP.NET.

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

<html>
<script language="VB" runat="server" >
   Dim MyConnection As SqlConnection

   Sub Page_Load(Src As Object, e As EventArgs)
      ' Create a connection to the "pubs" SQL database located on 
      ' the local computer.
      myConnection = New SqlConnection("server=localhost;" _ 
         & "database=pubs;Trusted_Connection=Yes")
      ' Check whether this page is a  postback. If it is not 
      ' a  postback, call a custom BindGrid function.
      If Not IsPostBack Then 
         BindGrid()
      End If
   End Sub

   ' Implement an AddAuthor_Click function. This function does some data 
   ' validation on the input form and builds a string containing all the 
   ' fields of the input form.  Then it adds this string to the database 
   ' and tests (using the try command) whether the data was added. 
   ' Finally, it rebinds the DataGrid to show the new data.
   Sub AddAuthor_Click(Sender As Object, e As EventArgs) 
      Dim myCommand As SqlCommand
      Dim insertCmd As String
      ' Check that four of the input values are not empty. If any of them
      '  is empty, show a message to the user and rebind the DataGrid.
      If (au_id.Value = "" Or au_fname.Value = "" Or au_lname.Value = "" _
         Or phone.Value = "") Then
         Message.InnerHtml = "ERROR: Null values not allowed for " _
            & "Author ID, Name or Phone"
         Message.Style("color") = "red"
         BindGrid()
         Exit Sub
      End If
      ' Build a SQL INSERT statement string for all the input-form
      ' field values.
      insertCmd = "insert into Authors values (@Id, @LName, @FName," _ 
         & "@Phone, @Address, @City, @State, @Zip, @Contract);"
      ' Initialize the SqlCommand with the new SQL string.
      myCommand = New SqlCommand(insertCmd, myConnection)
      ' Create new parameters for the SqlCommand object and
      ' initialize them to the input-form field values.
      myCommand.Parameters.Add(New SqlParameter("@Id", _
         SqlDbType.VarChar, 11))
      myCommand.Parameters("@Id").Value = au_id.Value
      myCommand.Parameters.Add(New SqlParameter("@LName", _
         SqlDbType.VarChar, 40))
      myCommand.Parameters("@LName").Value = au_lname.Value
      myCommand.Parameters.Add(New SqlParameter("@FName", _
         SqlDbType.VarChar, 20))
      myCommand.Parameters("@FName").Value = au_fname.Value
      myCommand.Parameters.Add(New SqlParameter("@Phone", _
         SqlDbType.Char, 12))
      myCommand.Parameters("@Phone").Value = phone.Value
      myCommand.Parameters.Add(New SqlParameter("@Address", _ 
         SqlDbType.VarChar, 40))
      myCommand.Parameters("@Address").Value = address.Value
      myCommand.Parameters.Add(New SqlParameter("@City", _
         SqlDbType.VarChar, 20))
      myCommand.Parameters("@City").Value = city.Value
      myCommand.Parameters.Add(New SqlParameter("@State", _
         SqlDbType.Char, 2))
      myCommand.Parameters("@State").Value = state.Value
      myCommand.Parameters.Add(New SqlParameter("@Zip", _
         SqlDbType.Char, 5))
      myCommand.Parameters("@Zip").Value = zip.Value
      myCommand.Parameters.Add(New SqlParameter("@Contract", _
         SqlDbType.VarChar,1))
      myCommand.Parameters("@Contract").Value = contract.Value
      myCommand.Connection.Open()
      ' Test whether the new row can be added and  display the 
      ' appropriate message box to the user.
      Try 
         myCommand.ExecuteNonQuery()
         Message.InnerHtml = "<b>Record Added</b><br>" & insertCmd
      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 add record, please " _
               & "ensure the fields are correctly filled out"
            Message.Style("color") = "red"
         End If
     End Try

     myCommand.Connection.Close()
     BindGrid()
   End Sub
    
   ' BindGrid connects to the database and implements a SQL 
   ' SELECT query to get all the data in the "Authors" table 
   ' of the database.
   Sub BindGrid() 
      Dim myConnection As SqlConnection
      Dim myCommand As SqlDataAdapter 
      ' Create a connection to the "pubs" SQL database located on 
      ' the local computer.
      myConnection = New SqlConnection("server=localhost;" _ 
         & "database=pubs;Trusted_Connection=Yes")
      ' Connect to the SQL database using a SQL SELECT query to get all 
      ' the data from the "Authors" table.
      myCommand = New SqlDataAdapter("SELECT * FROM authors", _
         myConnection)
      ' Create and fill a new DataSet.
      Dim ds As DataSet = New DataSet()
      myCommand.Fill(ds)
      ' Bind the DataGrid control to the DataSet.
      MyDataGrid.DataSource = ds
      MyDataGrid.DataBind()
   End Sub
</script>

<body style="font: 10pt verdana">
  <form runat="server">
    <h3><font face="Verdana">Inserting a Row of Data</font></h3>
    <table width="95%">
      <tr>
        <td valign="top">
          <ASP:DataGrid id="MyDataGrid" runat="server"
            Width="700"
            BackColor="#ccccff" 
            BorderColor="black"
            ShowFooter="false" 
            CellPadding=3 
            CellSpacing="0"
            Font-Name="Verdana"
            Font-Size="8pt"
            HeaderStyle-BackColor="#aaaadd"
            EnableViewState="false"
          />
        </td>
        <td valign="top">
          <table style="font: 8pt verdana">
            <tr>
              <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">
                Add a New Author:</td>
            </tr>
            <tr>
              <td nowrap>Author ID: </td>
              <td><input type="text" id="au_id" value="000-00-0000" 
                runat="server"></td>
            </tr>
            <tr>
              <td nowrap>Last Name: </td>
              <td><input type="text" id="au_lname" value="Doe" 
                runat="server"></td>
            </tr>  
            <tr nowrap>
              <td>First Name: </td>
              <td><input type="text" id="au_fname" value="John" 
                runat="server"></td>
            </tr>
            <tr>
              <td>Phone: </td>
              <td><input type="text" id="phone" value="808 555-5555" 
                runat="server"></td>
            </tr>
            <tr>
              <td>Address: </td>
              <td><input type="text" id="address" 
                value="One Microsoft Way" runat="server"></td>
            </tr>
            <tr>
              <td>City: </td>
              <td><input type="text" id="city" value="Redmond" 
                runat="server"></td>
            </tr>
            <tr>
              <td>State: </td>
              <td>
                <select id="state" runat="server">
                  <option>CA</option>
                  <option>IN</option>  
                  <option>KS</option>  
                  <option>MD</option>  
                  <option>MI</option>  
                  <option>OR</option> 
                  <option>TN</option>  
                  <option>UT</option>  
                </select>
              </td>
            </tr>
            <tr>
              <td nowrap>Zip Code: </td>
              <td><input type="text" id="zip" value="98005"
                 runat="server"></td>
            </tr>
            <tr>
              <td>Contract: </td>
              <td>
                <select id="contract" runat="server">
                  <option value="0">False</option>
                  <option value="1">True</option>
                </select>
              </td>
            </tr>
            <tr>
              <td></td>
              <td style="padding-top:15">
                <input type="submit" OnServerClick="AddAuthor_Click"
                  value="Add Author" runat="server">
              </td>
            </tr>
            <tr>
              <td colspan="2" style="padding-top:15" align="center">
                <span id="Message" EnableViewState="false" 
                  style="font: arial 11pt;" runat="server"/>
              </td>
            </tr>
          </table>

        </td>
      </tr>
    </table>

  </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");
      // Check to see  whether this page is a postback. If it is not 
      // a postback, call a custom BindGrid function.
      if (!IsPostBack) BindGrid();
   }
   // Implement an AddAuthor_Click function. This function does some data 
   // validation on the input form and builds a string containing all the 
   // fields of the input form. Then it adds this string to the database 
   // and tests (using the try command) whether the data was 
   // added. Finally, it rebinds the DataGrid to show the new data.
   public void AddAuthor_Click(Object sender, EventArgs E) 
   {
      // Check that four of the input values are not empty. If any of them
      // is empty, show a message to the user and rebind the DataGrid.
      if (au_id.Value == "" || au_fname.Value == "" || 
         au_lname.Value == "" || phone.Value == "") 
      {
         Message.InnerHtml = "ERROR: Null values not allowed for" +
            " Author ID, Name or Phone";
         Message.Style["color"] = "red";
         BindGrid();
         return;
      }
      // Build a SQL Insert statement string for all the input-form
      // field values.      
      String insertCmd = "insert into Authors values (@Id," +
         " @LName, @FName, @Phone, @Address, @City, @State," + 
         " @Zip, @Contract)";
      // Initialize the SqlCommand with the new SQL string 
      // and the connection information.
      SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
      // Create new parameters for the SqlCommand object and
      // initialize them to the input-form field values.
      myCommand.Parameters.Add(new SqlParameter("@Id",
         SqlDbType.VarChar, 11));
      myCommand.Parameters["@Id"].Value = au_id.Value;
         myCommand.Parameters.Add(new SqlParameter("@LName", 
         SqlDbType.VarChar, 40));
      myCommand.Parameters["@LName"].Value = au_lname.Value;
         myCommand.Parameters.Add(new SqlParameter("@FName", 
         SqlDbType.VarChar, 20));
      myCommand.Parameters["@FName"].Value = au_fname.Value;
         myCommand.Parameters.Add(new SqlParameter("@Phone", 
         SqlDbType.Char, 12));
      myCommand.Parameters["@Phone"].Value = phone.Value;
      myCommand.Parameters.Add(new SqlParameter("@Address", 
         SqlDbType.VarChar, 40));
      myCommand.Parameters["@Address"].Value = address.Value;
      myCommand.Parameters.Add(new SqlParameter("@City", 
         SqlDbType.VarChar, 20));
      myCommand.Parameters["@City"].Value = city.Value;
      myCommand.Parameters.Add(new SqlParameter("@State", 
         SqlDbType.Char, 2));
      myCommand.Parameters["@State"].Value = state.Value;
      myCommand.Parameters.Add(new SqlParameter("@Zip", 
         SqlDbType.Char, 5));
      myCommand.Parameters["@Zip"].Value = zip.Value;
      myCommand.Parameters.Add(new SqlParameter("@Contract", 
         SqlDbType.VarChar,1));
      myCommand.Parameters["@Contract"].Value = contract.Value;
      myCommand.Connection.Open();
      // Test whether the new row can be added and  display the 
      // appropriate message box to the user.
      try 
      {
         myCommand.ExecuteNonQuery();
         Message.InnerHtml = "<b>Record Added</b><br>" + insertCmd;
      }
      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 add record, please " +
              " ensure the fields are correctly filled out";
            Message.Style["color"] = "red";
      }
      myCommand.Connection.Close();
      BindGrid();
   }
   // BindGrid connects to the database and implements a SQL
   // SELECT query to get all the data in the "Authors" table 
   // of the database.
   public void BindGrid() 
   {
      //  Create a connection to the "pubs" SQL database located on 
      // the local computer.
      SqlConnection myConnection = new SqlConnection("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");
      // Connect to the database with a SELECT query on the 
      // "Authors" table.
      SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM" +
         " Authors", myConnection);
      // Create and fill a new DataSet.
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      // Bind MyDataGrid to the DataSet.
      MyDataGrid.DataSource=ds;
      MyDataGrid.DataBind();
   }
</script>

<%-- Display the DataGrid in the body of the page, and 
   create and display the input form. --%>
<body style="font: 10pt verdana">
   <form runat="server">
      <h3><font face="Verdana">Inserting a Row of Data</font></h3>
      <table width="95%">
         <tr>
            <td valign="top">
               <%-- Put the DataGrid in the first column. --%>
               <ASP:DataGrid id="MyDataGrid" runat="server"
                  Width="700"
                  BackColor="#ccccff" 
                  BorderColor="black"
                  ShowFooter="false" 
                  CellPadding=3 
                  CellSpacing="0"
                  Font-Name="Verdana"
                  Font-Size="8pt"
                  HeaderStyle-BackColor="#aaaadd"
                  EnableViewState="false"
               />
            </td>
            <%-- Create a second table column. --%>
            <td valign="top">
               <%-- Put the input form (a second table) in the second 
                  column. --%>
               <table style="font: 8pt verdana">
                  <tr>
                     <td colspan="2" bgcolor="#aaaadd" 
                        style="font:10pt verdana">
                        Add a New Author:
                     </td>
                  </tr>
                  <tr>
                     <td nowrap>Author ID:</td>
                     <td><input type="text" id="au_id" value="000-00-0000"
                        runat="server"></td>
                  </tr>
                  <tr>
                     <td nowrap>Last Name:</td>
                     <td><input type="text" id="au_lname" value="Doe" 
                        runat="server"></td>
                  </tr>  
                  <tr nowrap>
                     <td>First Name:</td>
                     <td><input type="text" id="au_fname" value="John"
                        runat="server"></td>
                  </tr>
                  <tr>
                     <td>Phone: </td>
                     <td>
                        <input type="text" id="phone" value="808 555-5555"
                           runat="server">
                     </td>
                  </tr>
                  <tr>
                     <td>Address:</td>
                     <td>
                        <input type="text" id="address" value =
                           "One Microsoft Way" runat="server">
                     </td>
                  </tr>
                  <tr>
                     <td>City:</td>
                     <td><input type="text" id="city" value="Redmond"
                        runat="server"></td>
                  </tr>
                  <tr>
                     <td>State: </td>
                     <td>
                        <select id="state" runat="server">
                           <option>CA</option>
                           <option>IN</option>  
                           <option>KS</option>  
                           <option>MD</option>  
                           <option>MI</option>  
                           <option>OR</option> 
                           <option>TN</option>  
                           <option>UT</option>  
                        </select>
                     </td>
                  </tr>
                  <tr>
                     <td nowrap>Zip Code:</td>
                     <td><input type="text" id="zip" value="98005"
                        runat="server"></td>
                  </tr>
                  <tr>
                     <td>Contract: </td>
                     <td>
                        <select id="contract" runat="server">
                           <option value="0">False</option>
                           <option value="1">True</option>
                        </select>
                     </td>
                  </tr>
                  <tr>
                     <td></td>
                     <td style="padding-top:15">
                        <input type="submit" OnServerClick=
                           "AddAuthor_Click" value="Add Author" 
                           runat="server">
                     </td>
                  </tr>
                  <tr>
                     <td colspan="2" style="padding-top:15"
                        align="center">
                        <span id="Message" EnableViewState="false"
                           style="font: arial 11pt;" runat="server"/>
                     </td>
                  </tr>
               </table>

            </td>
         </tr>
      </table>

   </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)