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
<%@ 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;
protected void Page_Load(Object Src, EventArgs E ) { myConnection = new SQLConnection ("server=localhost;uid=sa;pwd=;database=pubs"); if (!IsPostBack) BindGrid(); }
public void AddAuthor_Click(Object sender, EventArgs E) {
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; }
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;
SQLConnection myConnection = new SQLConnection ("server=localhost;uid=sa;pwd=;database=pubs"); SQLCommand myCommand = new SQLCommand(insertCmd.ToString(), myConnection);
myCommand.ActiveConnection.Open();
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"; }
myCommand.ActiveConnection.Close();
BindGrid(); }
public void BindGrid() {
SQLConnection myConnection = new SQLConnection ("server=localhost;uid=sa;pwd=;database=pubs");
SQLDataSetCommand myCommand = new SQLDataSetCommand("select * from Authors", myConnection);
DataSet ds = new DataSet(); myCommand.FillDataSet(ds, "Authors");
MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView; MyDataGrid.DataBind(); } </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" MaintainState="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" 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.