|
如何用最简单的语句提交大量表单对象的值存储到数据库？说道这个问题可能大家经常遇到，感到很头痛，但是又没办法，只能慢慢写，笔者以提交一张应聘表单的提交为例给大家介绍如何用最简短的语句来达到目的
脚本运行通过环境环境：
iis5.0+sqlserver2000（当然也可以access2000等）
如有任何问题或建议请发email：chenxingbai@21cn.com
生成表结构的脚本
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yingpin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yingpin]
GO
CREATE TABLE [dbo].[yingpin] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[yp_ name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[sex] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[birthday] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[health] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[hunyin] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[zhengzhi] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[xueli] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[zhicheng] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[english] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[phone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[yp_ address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[zip] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[colledge] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[zhuanye] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bumen] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[gangwei1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[gangwei2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[x_shijian] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[x_address] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[x_zhiwu] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[g_shijian] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[g_address] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[g_zhiwu] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[h_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[h_guanxi] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[h_danwei] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[h_zhiwu] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[pingjia] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[shexiang] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[beizhu] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[shijian] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[yingpin] WITH NOCHECK ADD
CONSTRAINT [DF_yingpin_shijian] DEFAULT (getdate()) FOR [shijian],
CONSTRAINT [PK_yingpin] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
Asp脚本addyp.asp
<!--#include file="lib/config1.asp"-->
<%
if request("action")="add" then
dim str,strvalue,strname,i,str1
str="yp_name,sex,birthday,health,hunyin,zhengzhi,xueli,zhicheng,english,email,phone,yp_address,zip,colledge,zhuanye,bumen,gangwei1,gangwei2,x_shijian,x_address,x_zhiwu,g_shijian,g_address,g_zhiwu,h_name,h_guanxi,h_danwei,h_zhiwu,pingjia,shexiang,beizhu"
str1="x_shijian,x_address,x_zhiwu,g_shijian,g_address,g_zhiwu,h_name,h_guanxi,h_danwei,h_zhiwu"
strname=split(str,",")
for i=lbound(strname) to ubound(strname)
if i<>ubound(strname) then
if instr(str1,strname(i))=0 then
if trim(request.Form(strname(i)))="" then
response.write "<script>alert('发生错误，请将数据填写完整！');history.back(1);</script>"
response.End()
end if
sql=sql&"'"&trim(request.Form(strname(i)))&"',"
else
j=1
much=trim(request.Form(cstr(strname(i)&j)))
for j=2 to 5
much=much&"|"&trim(request.Form(cstr(strname(i)&j)))
next
sql=sql&"'"&much&"',"
end if
else
if trim(request.Form(strname(i)))="" then
response.write "<script>alert('发生错误，请将数据填写完整！');history.back(1);</script>"
response.End()
end if
sql=sql&"'"&trim(request.Form(strname(i)))&"'"
end if
next
sql="insert into yingpin ("&str&") values ("&sql&")"
conn.Execute(Sql)
if Err <> 0 Then
response.write "<script>alert('发生错误，请重新操作！');history.back(1);</script>"
else
response.write "<script>alert('已经成功提交应聘信息，\n我们会尽快和你联系！');window.location='addyp.asp';</script>"
end If
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>gsjj</title>
<link type="text/css" rel="stylesheet" href="main.css">
</head>
<body bgcolor="#0066CC" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" style="background-attachment:fixed;">
<table width="90%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top" class="gray">
<p align="center"><font color="#66FF00">:::::::诚邀加盟:::::::</font></p>
<form action="addyp.asp?action=add" method="post" name="ts">
<table width=100% border=1 align="center" cellpadding=0 cellspacing="0" bordercolorlight="#000000" bordercolordark="#ffffff">
<tbody>
<tr>
<td height="35" colspan="5"><font color="#FFFFFF"><span
id=fps7>姓 名
<input
name="name" onFocus="this.select()"
onMouseOver="this.focus()" size="12"
>
<font color="#FF0000"> *</font> 性别
<input
name="sex" onFocus="this.select()"
onMouseOver="this.focus()" size="4"
type=text>
<font color="#FF0000">*</font>出生年月
<input name="birthday"
type=text id="birthday"
onFocus="this.select()"
onMouseOver="this.focus()" size="16"
>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="36" colspan="5"><font color="#FFFFFF"><span
id=fps7>健康状况
<input
name="health" onFocus="this.select()"
onMouseOver="this.focus()" size="8"
>
<font color="#FF0000">*</font> 婚姻状况
<input
name="hunyin" onFocus="this.select()"
onMouseOver="this.focus()" size="6"
type=text>
<font color="#FF0000">*</font> 政治面貌
<input
name="zhengzhi" onFocus="this.select()"
onMouseOver="this.focus()" size="16"
type=text>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="34" colspan="5"><font color="#FFFFFF">最高学历<span id=fps7>
<input
name="xueli" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
<font color="#FF0000">*</font>职称
<input
name="zhicheng" onFocus="this.select()"
onMouseOver="this.focus()" size="8"
type=text>
<font color="#FF0000">*</font>外语水平
<input
name="english" onFocus="this.select()"
onMouseOver="this.focus()" size="16"
type=text>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="30" colspan="5"><font color="#FFFFFF">E - MAIL<span id=fps7>
<input
name="email" onFocus="this.select()"
onMouseOver="this.focus()" size="30"
>
<font color="#FF0000">*</font>电话
<input
name="phone" onFocus="this.select()"
onMouseOver="this.focus()" size=16
>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="28" colspan="5" valign="middle"> <font color="#FFFFFF"><span id=fps7>联系地址</span><span id=fps7>
<input
name="address" onFocus="this.select()"
onMouseOver="this.focus()" size="30"
>
<font color="#FF0000">*</font>邮政编码
<input
name="zip" onFocus="this.select()"
onMouseOver="this.focus()" size="16"
type=text>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="29" colspan="5"><font color="#FFFFFF"> <span
id=fps7>毕业院校
<input
name="colledge" onFocus="this.select()"
onMouseOver="this.focus()" size="30"
>
<font color="#FF0000">*</font>专业
<input
name="zhuanye" onFocus="this.select()"
onMouseOver="this.focus()" size=16
>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td height="27" colspan="5"><font color="#FFFFFF">应聘部门<span
id=fps7>
<input
name="bumen" onFocus="this.select()"
onMouseOver="this.focus()" size=10
>
</span><span
id=fps7><font color="#FF0000">*</font></span>应聘岗位<span
id=fps7>
<input
name="gangwei1" onFocus="this.select()"
onMouseOver="this.focus()" size=12
>
</span><span
id=fps7><font color="#FF0000">*</font></span>第二应聘岗位<span
id=fps7>
<input
name="gangwei2" onFocus="this.select()"
onMouseOver="this.focus()" size=12
>
<font color="#FF0000">*</font> </span></font></td>
</tr>
<tr>
<td width="5%" height="203" rowspan="6" align="center" valign="middle"><font color="#FFFFFF">学<br>
<br>
习<br>
<br>
经<br>
<br>
历 </font></td>
<td height="25" colspan="4"><font color="#FFFFFF">起止时间学校
职务</font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="x_shijian1" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="x_address1" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="x_zhiwu1" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="x_shijian2" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="x_address2" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="x_zhiwu2" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="x_shijian3" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="x_address3" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="x_zhiwu3" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="x_shijian4" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="x_address4" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="x_zhiwu4" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="x_shijian5" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="x_address5" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="x_zhiwu5" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr>
<td colspan="5">
</tbody>
<tr>
<td height="203" rowspan="6" align="center" valign="middle"><font color="#FFFFFF">工</font><font color="#FFFFFF"><br>
<br>
作<br>
<br>
经<br>
<br>
历 </font></td>
<td height="24" colspan="4"><font color="#FFFFFF">起止时间供职单位
职务</font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="g_shijian1" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="g_address1" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="g_zhiwu1" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="g_shijian2" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="g_address2" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="g_zhiwu2" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="g_shijian3" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="g_address3" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="g_zhiwu3" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="g_shijian4" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="g_address4" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="g_zhiwu4" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td colspan="4" align="left"><font color="#FFFFFF"><span
id=fps7>
<input
name="g_shijian5" onFocus="this.select()"
onMouseOver="this.focus()" size="18"
>
<input
name="g_address5" onFocus="this.select()"
onMouseOver="this.focus()" size="２０"
>
<input
name="g_zhiwu5" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr>
<td height="190" rowspan="6" align="center" valign="middle"><font color="#FFFFFF">家<br>
庭<br>
成<br>
员<br>
及<br>
主<br>
要<br>
社<br>
会<br>
关<br>
系 </font></td>
<td height="23" colspan="4"><font color="#FFFFFF">姓名 与本人关系
工作单位 职务</font></td>
</tr>
<tr align="center">
<td width="17%" height="20" align="center" valign="middle"><font color="#FFFFFF"><span id=fps7>
<input
name="h_name1" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="19%" align="center" valign="middle"><font color="#FFFFFF"><span id=fps7>
<input
name="h_guanxi1" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="41%" align="center" valign="middle"><font color="#FFFFFF"><span id=fps7>
<input
name="h_danwei1" onFocus="this.select()"
onMouseOver="this.focus()" size="28"
>
</span></font></td>
<td width="18%" align="center"><font color="#FFFFFF"><span id=fps7>
<input
name="h_zhiwu1" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td height="20" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_name2" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="19%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_guanxi2" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="41%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_danwei2" onFocus="this.select()"
onMouseOver="this.focus()" size="28"
>
</span></font></td>
<td width="18%" align="center"><font color="#FFFFFF"><span id=fps7>
<input
name="h_zhiwu2" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td height="20" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_name3" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="19%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_guanxi3" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="41%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_danwei3" onFocus="this.select()"
onMouseOver="this.focus()" size="28"
>
</span></font></td>
<td width="18%" align="center"><font color="#FFFFFF"><span id=fps7>
<input
name="h_zhiwu3" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td height="20" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_name4" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="19%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_guanxi4" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="41%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_danwei4" onFocus="this.select()"
onMouseOver="this.focus()" size="28"
>
</span></font></td>
<td width="18%" align="center"><font color="#FFFFFF"><span id=fps7>
<input
name="h_zhiwu4" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr align="center">
<td height="20" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_name5" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="19%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_guanxi5" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
<td width="41%" align="center" valign="middle"><font color="#FFFFFF"><span
id=fps7>
<input
name="h_danwei5" onFocus="this.select()"
onMouseOver="this.focus()" size="28"
>
</span></font></td>
<td width="18%" align="center"><font color="#FFFFFF"><span id=fps7>
<input
name="h_zhiwu5" onFocus="this.select()"
onMouseOver="this.focus()" size="10"
>
</span></font></td>
</tr>
<tr>
<td align="center"><font color="#FFFFFF">自<br>
<br>
我<br>
<br>
评<br>
<br>
价 </font>
<td colspan="4" align="center"><textarea name="pingjia" cols="60" rows="5"></textarea>
<font color="#FFFFFF"><span
id=fps7><font color="#FF0000">*</font></span></font> <tr>
<td height="24" colspan="5"><font color="#FFFFFF">对未来工作的设想或其它需要补充的内容
：</font>
<tr align="center">
<td colspan="5"> <textarea name="shexiang" cols="60" rows="5"></textarea>
<font color="#FFFFFF"><span
id=fps7><font color="#FF0000">*</font></span></font> <tr>
<td height="28" colspan="5"><font color="#FFFFFF">备注（工作地点、薪金期望等）:
</font>
<tr align="center">
<td colspan="5"><textarea name="beizhu" cols="60" rows="5"></textarea>
<font color="#FFFFFF"><span
id=fps7><font color="#FF0000">*</font></span></font> <tr>
<td colspan="5"><font color="#FFFFFF"> </font>
<tr align="center">
<td height="23" colspan="5"><font color="#FFFFFF"><span id=fps7>
<input name="button" type="submit" value="提交">
<input name="reset" type="reset" value="重新填写">
</span></font>
</table>
<br>
<br>
</form>
</td>
</tr>
</table>
</body>
</html>
|
