NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Inserting Data Into a SQL Database

Once connected and bound to a database, you might want to add data rows to that database. This section shows how to add new rows of data to a database using a custom form for the input.

To add new rows of data to a database

  1. Import the necessary namespaces.
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SQL" %>
    <%@ Import Namespace="System.Text"%>

    Within the <script language="C#" runat="server"> tag, this example implements three functions: Page_Load, AddAuthor_Click, and BindGrid. These functions are described in more detail in the steps that follow.

    <script language="C#" runat="server">
        SQLConnection myConnection;
  2. Implement a Page_Load function that sets up the connection information for a connection to the "pubs" database. It also checks to see if this page is a PostBack, and, if not, calls a custom BindGrid function. For a description of BindGrid, see step 4.
        protected void Page_Load(Object Src, EventArgs E ) 
        {
            myConnection = new SQLConnection 
                         ("server=localhost;uid=sa;pwd=;database=pubs");
            if (!IsPostBack)
                BindGrid();
        }
  3. Implement an AddAuthor_Click function. This function does some data validation on the input form, and then builds a string containing all the fields of the input form. Next, it adds this string to the database and tests (using the try command) to see if the data was added. Finally, it rebinds the DataGrid to show the new data.
    public void AddAuthor_Click(Object sender, EventArgs E) 
        {
    1. Check that four of the input values are not empty. If any of them are 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;
              }
    2. Build a comma-delimited string of all the input form field values. Creating new parameters for the SQLCommand object and initializing them to the input form field values does this. The SQLCommand is also initialized with the new string and the connection information.
              String insertCmd = "insert into Authors values 
                         (@Id, @LName, @FName, @Phone, @Address, @City, 
                          @State, @Zip, @Contract)";
              SQLCommand myCommand = new SQLCommand(insertCmd, 
                                                          myConnection);
              myCommand.Parameters.Add(new SQLParameter("@Id", 
                                              SQLDataType.VarChar, 11));
              myCommand.Parameters["@Id"].Value = au_id.Value;
              myCommand.Parameters.Add(new SQLParameter("@LName", 
                                              SQLDataType.VarChar, 40));
              myCommand.Parameters["@LName"].Value = au_lname.Value;
              myCommand.Parameters.Add(new SQLParameter("@FName", 
                                              SQLDataType.VarChar, 20));
              myCommand.Parameters["@FName"].Value = au_fname.Value;
              myCommand.Parameters.Add(new SQLParameter("@Phone", 
                                                 SQLDataType.Char, 12));
              myCommand.Parameters["@Phone"].Value = phone.Value;
              myCommand.Parameters.Add(new SQLParameter("@Address", 
                                              SQLDataType.VarChar, 40));
              myCommand.Parameters["@Address"].Value = address.Value;
              myCommand.Parameters.Add(new SQLParameter("@City", 
                                              SQLDataType.VarChar, 20));
              myCommand.Parameters["@City"].Value = city.Value;
              myCommand.Parameters.Add(new SQLParameter("@State", 
                                                  SQLDataType.Char, 2));
              myCommand.Parameters["@State"].Value = state.Value;
              myCommand.Parameters.Add(new SQLParameter("@Zip", 
                                                  SQLDataType.Char, 5));
              myCommand.Parameters["@Zip"].Value = zip.Value;
              myCommand.Parameters.Add(new SQLParameter("@Contract", 
                                                SQLDataType.VarChar,1));
              myCommand.Parameters["@Contract"].Value = contract.Value;
    3. Connect to the database and insert the string (row) that was just built.
              SQLConnection myConnection = new SQLConnection 
                       ("server=localhost;uid=sa;pwd=;database=pubs");
              SQLCommand myCommand = new SQLCommand(insertCmd.ToString(), 
                                     myConnection);
    4. Connect to the database. This updates the database with the new row (string).
              myCommand.ActiveConnection.Open();
    5. Test to see if the new row was added and present the appropriate message box to the user.
              try 
              {
                  int rowsAffected = 0;
                  myCommand.Execute(ref rowsAffected);
                  Message.InnerHtml = "<b>Record Added</b><br>" + 
                                      insertCmd.ToString();
              }
              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";
              }
    6. Close the active connection to the database.
              myCommand.ActiveConnection.Close();
    7. Rebind to the DataGrid that contains the new row.
              BindGrid();
          }
  4. Implement a BindGrid function. This function connects to the database and implements the standard "select *" SQL query to get all of the data in the "Authors" table of the database.
    public void BindGrid() 
        {
    1. Set up the database connection to the "pubs" database located on the local computer.
              SQLConnection myConnection = new SQLConnection 
                        ("server=localhost;uid=sa;pwd=;database=pubs");
    2. Connect to the database with a "select *" query on the "Authors" table.
              SQLDataSetCommand myCommand = new SQLDataSetCommand("select 
                                           * from Authors", myConnection);
    3. Create and fill a new DataSet.
              DataSet ds = new DataSet();
              myCommand.FillDataSet(ds, "Authors");
    4. Bind MyDataGrid to the "Authors" table in the DataSet.
              MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
              MyDataGrid.DataBind();
          }
      </script>
  5. In the <body> section of the page, display the DataGrid, and create and display the input form.
    <body style="font: 10pt verdana">
    1. Open a form.
        <form runat="server">
          <h3><font face="Verdana">Inserting a Row of Data</font></h3>
    2. Create a table.
          <table width="95%">
            <tr>
              <td valign="top">
    3. Put the DataGrid in the first column of the table.
                <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"
                  MaintainState="false"
                />
    4. Create a second table column.
              </td>
              <td valign="top">
    5. 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" MaintainState="false" 
                                style="font: arial 11pt;" runat="server"/>
                    </td>
                  </tr>
                </table>
      
              </td>
            </tr>
          </table>
      
        </form>
      
      </body>

To see this example run, go to the ASP+ Quick Start and run sample DataGrid4.aspx.