如何将A中A1字段的值，更新为B表中ID值最大的B1字段的值？

Question

求助：

将mainb表中yidengyifeng字段的值，更新为，jiating表中xingming字段值相同的、ID值最大的、jiating_xingming的值

例如：

更新前
表jiating
ID   xingming   jiating_xingming
1     白云区        555
2     白云区        666

表mainb
xingming    yidengyifeng 
 白云区    （空或已有数据）

表jiating添加一组数据后
ID   xingming   jiating_xingming
1     白云区        555
2     白云区        666
3     白云区        777

同时，表mainb对应更新为
xingming    yidengyifeng 
 白云区         777
 

《》《》《》《》《》《》《》《》《》《》《》《》《》

补充：

一个Asp+Access网站
update.asp  是数据输入页面
update_s.asp  是过程页面
view.asp  是显示页面
对应一个数据库有多个表，
其中一个表mainb（包含xingming和yidengyifeng等字段），
另一个表是jiating（包含ID和xingming和jiating_xingming等字段）

在update.asp页面输入数据提交后，jiating表自动增加ID添加对应数据。

在view.asp页面中单独调用jiating中ID值最大的jiating_xingming值，正常，显示的是ID最大的jiating_xingming值。

    <%       '家庭主要成员 
set rs_jiating=server.createobject("adodb.recordset")
sql_jiating="select top 1 * from jiating where xingming_id="&requestid&" order by id desc"
rs_jiating.open sql_jiating,conn,1,2%>

	<tr>
      <td align="right" bgcolor="#FFFFFF">家庭成员姓名：</td>
      <td colspan="5" bgcolor="#FFFFFF" class="style1"><%=rs_jiating("jiating_xingming")%></td>
    </tr>

但是在update_s.asp页面中，想把ID最大的jiating_xingming值更新到mainb表的yidengyifeng中就不行，显示的是全部jiating_xingming值

我用的代码是

set rs=server.CreateObject("adodb.recordset")
sql="select * from maintb where id="&rcid
rs.open sql,conn,1,3
set rs_jiating=server.createobject("adodb.recordset")
sql_jiating="select top 1 * from jiating where xingming_id="&requestid&" order by id desc"
rs_jiating.open sql_jiating,conn,1,1
rs("yidengyifeng")=request("jiating_xingming")
rs.update
rs.close
rs_jiating.close

yidengyifeng值显示的结果是：777,666,555

我想要的yidengyifeng值是：777

《》《》《》《》《》《》《》《》《》《》《》《》《》《》《》

update.asp页面中相关的代码是这样的

<table width="99%" border="0" align="center" cellpadding="2" cellspacing="1" bgcolor="#009966" id="jiating_table">
     <tr  >
       <td align="center" bgcolor="#DDF9F3" width="8%">进度周报</td>
       <td align="center" bgcolor="#DDF9F3" width="9%"><input type="button" name="Submit222222232" value="新增一行" class="buttonadd" onClick="addline_jiating()"   style="width: 60"/></td>
      <td width="14%" align="center" bgcolor="#DDF9F3">周报时间</td>
      <td width="18%" align="center" bgcolor="#DDF9F3" >本周产值</td>
      <td width="22%" align="center" bgcolor="#DDF9F3" >累计完成百分比</td>
      <td width="18%" align="center" bgcolor="#DDF9F3" >本周完成情况</td>
      <td width="18%" align="center" bgcolor="#DDF9F3">下周计划</td>
     </tr>
	  <%       '家庭主要成员 
 set rs_jiating=server.createobject("adodb.recordset")
'sql_jiating="select * from jiating where xingming_id="&requestid&" order by id asc"  //id正向排序改成反向
sql_jiating="select * from jiating where xingming_id="&requestid&" order by id desc"
'sql_jiating="select top 1 * from jiating where xingming_id="&requestid&" order by id desc"
rs_jiating.open sql_jiating,conn,1,2%>
		<% do while not rs_jiating.eof %>
     <tr>
       <td align="center" bgcolor="#F1FCFA"><input name="linenum_jiating" type="hidden" id="linenum_jiating" value="oneline" /></td>
       <td align="center" bgcolor="#F1FCFA"><input name="isdel_jiating" type="checkbox" id="isdel_jiating" value="<%=rs_jiating("id")%>" title="打勾在点击“提交修改”后，可立即删除该行">删除
       <td align="center" bgcolor="#F1FCFA"><input name="jiating_xingming" type="text"   class="inp" id="jiating_xingming" onFocus="this.style.borderColor='#FF33CC'"  onMouseOver="this.style.borderColor='#33CC33'"  onMouseOut="this.style.borderColor='#84a1bd'" value="<%=rs_jiating("jiating_xingming")%>" size="13" /></td>
       <td align="center" bgcolor="#F1FCFA"><input name="jiating_chushengsj" type="text"   class="inp" id="jiating_chushengsj" onFocus="this.style.borderColor='#FF33CC'"  onMouseOver="this.style.borderColor='#33CC33'"  onMouseOut="this.style.borderColor='#84a1bd'" value="<%=rs_jiating("jiating_chushengsj")%>" size="13" /></td>
       <td align="center" bgcolor="#F1FCFA"><input name="jiating_yubenrenguanxi" type="text"   class="inp" id="jiating_yubenrenguanxi" onFocus="this.style.borderColor='#FF33CC'"  onMouseOver="this.style.borderColor='#33CC33'"  onMouseOut="this.style.borderColor='#84a1bd'" value="<%=rs_jiating("jiating_yubenrenguanxi")%>" size="13" /></td>
       <td align="center" bgcolor="#F1FCFA"><input name="jiating_tel" type="text"   class="inp" id="jiating_tel" onFocus="this.style.borderColor='#FF33CC'"  onMouseOver="this.style.borderColor='#33CC33'"  onMouseOut="this.style.borderColor='#84a1bd'" value="<%=rs_jiating("jiating_tel")%>" size="13" /></td>
       <td align="center" bgcolor="#F1FCFA"><input name="jiating_danwei" type="text"   class="inp" id="jiating_danwei" onFocus="this.style.borderColor='#FF33CC'"  onMouseOver="this.style.borderColor='#33CC33'"  onMouseOut="this.style.borderColor='#84a1bd'" value="<%=rs_jiating("jiating_danwei")%>" size="13" /></td>
     </tr>
	 	 	 	 <% rs_jiating.movenext
		   loop
			%>
   </table>

update_s.asp  页面中相关的代码是这样的：

'-----家庭主要成员
   '删除
nn=request.form("isdel_jiating").count
ReDim arr_jiaoyan_isdel(nn)
allid=""
For i=1 To nn
	allid=allid & request.form("isdel_jiating")(i) & ","
Next
allid="(" & left(allid,Len(allid)-1) & ")"
sql_jiating="delete * from jiating where id in " &  allid
'Response.write sql_jiating
set rs_jiating=server.CreateObject("adodb.recordset")
rs_jiating.open sql_jiating,conn,1,2
set rs_jiating=nothing

'添加
mm=request.form("thisid_jiating").count
redim arr_jiating_xingming(mm)
redim arr_jiating_chushengsj(mm)
redim arr_jiating_yubenrenguanxi(mm)
redim arr_jiating_tel(mm)
redim arr_jiating_danwei(mm)

For i = 1 To mm
 arr_jiating_xingming(i)=trim(request.form("jiating_xingming")(i))
 arr_jiating_chushengsj(i)=trim(request.form("jiating_chushengsj")(i))
 arr_jiating_yubenrenguanxi(i)=trim(request.form("jiating_yubenrenguanxi")(i))
 arr_jiating_tel(i)=trim(request.form("jiating_tel")(i))
 arr_jiating_danwei(i)=trim(request.form("jiating_danwei")(i))

If request.form("thisid_jiating")(i)="add_jiating" then
if arr_jiating_xingming(i)<>"" or arr_jiating_chushengsj(i)<>"" or arr_jiating_yubenrenguanxi(i)<>"" or arr_jiating_tel(i)<>"" or arr_jiating_danwei(i)<>""  then
sql_jiating="select * from jiating where id is null"
set rs_jiating=server.CreateObject("adodb.recordset")
rs_jiating.open sql_jiating,conn,1,2
rs_jiating.addnew
rs_jiating("xingming_id")=id_px
rs_jiating("xingming")=xingming 

rs_jiating("jiating_xingming")=arr_jiating_xingming(i)
rs_jiating("jiating_chushengsj")=arr_jiating_chushengsj(i)
rs_jiating("jiating_yubenrenguanxi")=arr_jiating_yubenrenguanxi(i)
rs_jiating("jiating_tel")=arr_jiating_tel(i)
rs_jiating("jiating_danwei")=arr_jiating_danwei(i)

rs_jiating.update
rs_jiating.close
set rs_jiating=nothing
end If
Else
	sql_jiating="select * from jiating where id="& request.form("thisid_jiating")(i)
	set rs_jiating=server.CreateObject("adodb.recordset")
	rs_jiating.open sql_jiating,conn,1,2
		rs_jiating("xingming_id")=id_px
		rs_jiating("xingming")=xingming 

		rs_jiating("jiating_xingming")=arr_jiating_xingming(i)
		rs_jiating("jiating_chushengsj")=arr_jiating_chushengsj(i)
		rs_jiating("jiating_yubenrenguanxi")=arr_jiating_yubenrenguanxi(i)
		rs_jiating("jiating_tel")=arr_jiating_tel(i)
		rs_jiating("jiating_danwei")=arr_jiating_danwei(i)
		rs_jiating.update
		rs_jiating.close
		set rs_jiating=nothing
End if
Next


set rs=server.CreateObject("adodb.recordset")
sql="select * from maintb where id="&rcid
rs.open sql,conn,1,3
set rs_jiating=server.createobject("adodb.recordset")
sql_jiating="select top 1 * from jiating where xingming_id="&requestid&" order by id desc"
rs_jiating.open sql_jiating,conn,1,1
rs("yidengyifeng")=request("jiating_xingming")
rs.update
rs.close
rs_jiating.close

Answer 1

根据你提供的代码，我猜测你在更新mainb表的yidengyifeng字段时，使用了request("jiating_xingming")，这个在更新时是不正确的。正确的做法是，使用rs_jiating("jiating_xingming")来获取ID值最大的jiating_xingming字段的值，因为你已经在update.asp页面中调用了ID值最大的jiating_xingming值，可以直接传递到update_s.asp页面，在更新时直接使用即可。另外，在执行更新操作前，需要将rs_jiating的指针移动到第一条记录，否则可能会更新到错误的记录。更新代码应该修改为：

set rs=server.CreateObject("adodb.recordset")
sql="select * from maintb where id="&rcid
rs.open sql,conn,1,3
set rs_jiating=server.createobject("adodb.recordset")
sql_jiating="select top 1 * from jiating where xingming_id="&requestid&" order by id desc"
rs_jiating.open sql_jiating,conn,1,1
rs_jiating.movefirst
rs("yidengyifeng")=rs_jiating("jiating_xingming")
rs.update
rs.close
rs_jiating.close