IDC资讯中心
前面几个例子学习了从database里面读数据，下面我们学习将数据插入到数据库中，可以向页中添加简单的输入窗体，并在窗体提交事件处理程序中执行插入命令。与前两个示例一样，使用命令对象的 parameters 集合填充命令的值。注意，在试图插入到数据库中之前，还要检查以确保所需的值非空。这将防止与数据库的字段约束意外冲突。还需在 try/catch 块的内部执行插入命令，以防插入行的主键已经存在。
先看aspx文件：
<body ms_positioning=”gridlayout”>
<form runat=”server” id=”form1″>
<h3><font face=”宋体”>插入具有验证的数据行</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=”宋体” font-size=”8pt” headerstyle-backcolor=”#aaaadd” enableviewstate=”false” />
</td>
<td valign=”top”>
<table style=”font: 9pt 宋体”>
<tr>
<td colspan=”2″ bgcolor=”#aaaadd” style=”font:10.5pt 宋体”>添加新作者：</td>
</tr>
<tr>
<td nowrap>作者 id：</td>
<td>
<input type=”text” id=”au_id” value=”000-00-0000″ runat=”server” name=”au_id”>
<asp:requiredfieldvalidator id=”au_idreqval” controltovalidate=”au_id” display=”static” font-name=”verdana” font-size=”12″ runat=”server”> *</asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td nowrap>姓氏：</td>
<td>
<input type=”text” id=”au_lname” value=”doe” runat=”server” name=”au_lname”>
<asp:requiredfieldvalidator id=”au_lnamereqval” controltovalidate=”au_lname” display=”static” font-name=”verdana” font-size=”12″ runat=”server”> *</asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td nowrap>名字：</td>
<td>
<input type=”text” id=”au_fname” value=”john” runat=”server” name=”au_fname”>
<asp:requiredfieldvalidator id=”au_fnamereqval” controltovalidate=”au_fname” display=”static” font-name=”verdana” font-size=”12″ runat=”server”> *</asp:requiredfieldvalidator>
</td>
</tr>
<tr>
<td>电话：</td>
<td><nobr> <input type=”text” id=”phone” value=”808 555-5555″ runat=”server” name=”phone”>
<asp:requiredfieldvalidator id=”phonereqval” controltovalidate=”phone” display=”static” font-name=”verdana” font-size=”12″ runat=”server”> *</asp:requiredfieldvalidator></nobr>
</td>
</tr>
<tr>
<td>地址：</td>
<td><input type=”text” id=”address” value=”one microsoft way” runat=”server” name=”address”></td>
</tr>
<tr>
<td>城市：</td>
<td><input type=”text” id=”city” value=”redmond” runat=”server” name=”city”></td>
</tr>
<tr>
<td>州：</td>
<td>
<select id=”state” runat=”server” name=”state”>
<option selected>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>邮政编码：</td>
<td><input type=”text” id=”zip” value=”98005″ runat=”server” name=”zip”></td>
</tr>
<tr>
<td>协定：</td>
<td>
<select id=”contract” runat=”server” name=”contract”>
<option value=”0″ selected>假</option>
<option value=”1″>真</option>
</select>
</td>
</tr>
<tr>
<td></td>
<td style=”padding-top:15px”>
<input type=”submit” value=”添加作者” runat=”server” id=”submit1″ name=”submit1″>
</td>
</tr>
<tr>
<td colspan=”2″ style=”padding-top:15px” align=”middle”>
<span id=”message” enableviewstate=”false” runat=”server”>
<asp:regularexpressionvalidator id=”regularexpressionvalidator1″ aspclass=”regularexpressionvalidator” controltovalidate=”zip” validationexpression=”[0-9]{5}” display=”dynamic” font-name=”arial” font-size=”11″ runat=”server”>* 邮政编码必须是 5 位数字<br></asp:regularexpressionvalidator>
<asp:regularexpressionvalidator id=”phoneregexval” controltovalidate=”phone” validationexpression=”[0-9]{3} [0-9]{3}-[0-9]{4}” display=”dynamic” font-name=”arial” font-size=”11″ runat=”server”>* 电话号码格式必须为：xxx xxx-xxxx <br></asp:regularexpressionvalidator> <asp:regularexpressionvalidator id=”au_idregexval” controltovalidate=”au_id” validationexpression=”[0-9]{3}-[0-9]{2}-[0-9]{4}” display=”dynamic” font-name=”arial” font-size=”11″ runat=”server”>* 作者 id 必须是数字：xxx-xx-xxxx <br></asp:regularexpressionvalidator></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
aspx.cs文件
using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
namespace study
{
/// <summary>
/// datagrid_5 的摘要说明。
/// </summary>
public class datagrid_5 : system.web.ui.page
{
protected system.web.ui.webcontrols.datagrid mydatagrid;
protected system.web.ui.webcontrols.requiredfieldvalidator au_idreqval;
protected system.web.ui.webcontrols.requiredfieldvalidator au_lnamereqval;
protected system.web.ui.webcontrols.requiredfieldvalidator au_fnamereqval;
protected system.web.ui.webcontrols.requiredfieldvalidator phonereqval;
protected system.web.ui.webcontrols.regularexpressionvalidator regularexpressionvalidator1;
protected system.web.ui.webcontrols.regularexpressionvalidator phoneregexval;
protected system.web.ui.webcontrols.regularexpressionvalidator au_idregexval;
protected system.web.ui.htmlcontrols.htmlinputtext au_id;
protected system.web.ui.htmlcontrols.htmlinputtext au_lname;
protected system.web.ui.htmlcontrols.htmlinputtext au_fname;
protected system.web.ui.htmlcontrols.htmlinputtext phone;
protected system.web.ui.htmlcontrols.htmlinputtext address;
protected system.web.ui.htmlcontrols.htmlinputtext city;
protected system.web.ui.htmlcontrols.htmlselect state;
protected system.web.ui.htmlcontrols.htmlinputtext zip;
protected system.web.ui.htmlcontrols.htmlselect contract;
protected system.web.ui.htmlcontrols.htmlinputbutton submit1;
protected system.web.ui.htmlcontrols.htmlgenericcontrol message;
sqlconnection myconnection;
private void page_load(object sender, system.eventargs e)
{
myconnection = new sqlconnection(“user id=sa;password=;initial catalog=pubs;data source=jeff”);
if (!ispostback)
bindgrid();
}
#region web form designer generated code
override protected void oninit(eventargs e)
{
//
// codegen：该调用是 asp.net web 窗体设计器所必需的。
//
initializecomponent();
base.oninit(e);
}
/// <summary>
/// 设计器支持所需的方法 – 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void initializecomponent()
{
this.submit1.serverclick += new system.eventhandler(this.submit1_serverclick);
this.load += new system.eventhandler(this.page_load);
}
#endregion
private void submit1_serverclick(object sender, system.eventargs e)
{
message.innerhtml = “”;
if (page.isvalid)
{
string insertcmd = “insert into authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values (@id, @lname, @fname, @phone, @address, @city, @state, @zip, @contract)”;
sqlcommand mycommand = new sqlcommand(insertcmd, myconnection);
mycommand.parameters.add(new sqlparameter(“@id”, sqldbtype.nvarchar, 11));
mycommand.parameters[“@id”].value = au_id.value;
mycommand.parameters.add(new sqlparameter(“@lname”, sqldbtype.nvarchar, 40));
mycommand.parameters[“@lname”].value = au_lname.value;
mycommand.parameters.add(new sqlparameter(“@fname”, sqldbtype.nvarchar, 20));
mycommand.parameters[“@fname”].value = au_fname.value;
mycommand.parameters.add(new sqlparameter(“@phone”, sqldbtype.nchar, 12));
mycommand.parameters[“@phone”].value = phone.value;
mycommand.parameters.add(new sqlparameter(“@address”, sqldbtype.nvarchar, 40));
mycommand.parameters[“@address”].value = address.value;
mycommand.parameters.add(new sqlparameter(“@city”, sqldbtype.nvarchar, 20));
mycommand.parameters[“@city”].value = city.value;
mycommand.parameters.add(new sqlparameter(“@state”, sqldbtype.nchar, 2));
mycommand.parameters[“@state”].value = state.value;
mycommand.parameters.add(new sqlparameter(“@zip”, sqldbtype.nchar, 5));
mycommand.parameters[“@zip”].value = zip.value;
mycommand.parameters.add(new sqlparameter(“@contract”, sqldbtype.nvarchar,1));
mycommand.parameters[“@contract”].value = contract.value;
mycommand.connection.open();
try
{
mycommand.executenonquery();
message.innerhtml = “<b>已添加记录</b><br>” + insertcmd + “<p>”;
}
catch (sqlexception ex)
{
if (ex.number == 2627)
message.innerhtml = “错误：已存在具有相同主键的记录<p>”;
else
message.innerhtml = “错误：未能添加记录，请确保正确填写了字段<p>”;
message.style[“color”] = “red”;
}
mycommand.connection.close();
}
bindgrid();
}
public void bindgrid()
{
sqldataadapter mycommand = new sqldataadapter(“select * from authors”, myconnection);
dataset ds = new dataset();
mycommand.fill(ds, “authors”);
mydatagrid.datasource=ds.tables[“authors”].defaultview;
mydatagrid.databind();
}
}
}
