在通用声明里:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlStr As String
Private Sub Command3_Click()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider="
sqlStr = "select Max(p_cardid) from sf_psc_person"
rs.CursorLocation = adUseClient
rs.Open sqlStr, cn, adOpenKeyset, adLockPessimistic
rs.MoveFirst
'一些数据集的操作代码
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing '修改数据库相应字段
Set cn = New ADODB.Connection
cn.Open "Provider。。"
sqlStr = "update sf_psc_person set p_card='" + pcard + "'"
cn.Execute (sqlStr)
cn.Close
Set cn = Nothing
end sub
我在调试的时候,发现第二条sql语句竟还是第一条时候的,也就是“select Max(p_cardid) from sf_psc_person”,这是怎么回事?怎么改呢?
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlStr As String
Private Sub Command3_Click()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider="
sqlStr = "select Max(p_cardid) from sf_psc_person"
rs.CursorLocation = adUseClient
rs.Open sqlStr, cn, adOpenKeyset, adLockPessimistic
rs.MoveFirst
'一些数据集的操作代码
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing '修改数据库相应字段
Set cn = New ADODB.Connection
cn.Open "Provider。。"
sqlStr = "update sf_psc_person set p_card='" + pcard + "'"
cn.Execute (sqlStr)
cn.Close
Set cn = Nothing
end sub
我在调试的时候,发现第二条sql语句竟还是第一条时候的,也就是“select Max(p_cardid) from sf_psc_person”,这是怎么回事?怎么改呢?
debug.print sqlStr
cn.Execute (sqlStr)
看一下sqlStr是什么?
从代码上分析,是不可能出现这样的情况的。你后面的变量做了重新赋值的。sqlStr = "update sf_psc_person set p_card='" + pcard + "'"你这句话是不是执行失败了,然后ON ERROR RESUME NEXT了??
没看到pcard 的定义。
而且我认为极有可能是因为这个原因引起的,要解决也很容易,要么将Dim sqlStr As String
定义为本sub中的,或重新在本sub中定义一个新的str变量,重新赋值。
你的代码中有句
'一些数据集的操作代码天知道这里面会不会递归本sub或者执行半道,又跳出本sub跑去作什么了。
Dim sql1 As String, sql2 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=。"
sql1 = "select Max(p_cardid) from sf_psc_person"
rs.CursorLocation = adUseClient
rs.Open sql1, cn, adOpenKeyset, adLockPessimistic
rs.MoveFirst
pcardId = rs.Fields(0)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
'修改数据库相应字段
Set cn = New ADODB.Connection
cn.Open "Provider=......."
sql2 = "update sf_psc_person set p_cardid='" + pcardId + "'"
cn.Execute (sql2)
cn.Close
Set cn = Nothing
end sub这样的话,sql2就为空了!
确认下是执行前sql2变空了,还是执行的时候sql2变空了即用如下代码测试:dim sql2 as stringmsgbox sql2
sql2 = "update sf_psc_person set p_cardid='" + pcardId + "'"
msgbox sql2
cn.Execute (sql2)
msgbox sql2
在于pcardId = rs.Fields(0)
这句肯定是rs.Fields(0)为null导致碰到null的变量都变成null或之前的值
Private Sub Command3_Click()
Dim sql1 As String, sql2 As String
dim pcardIdxxx as string '<-------------- Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=。"
sql1 = "select Max(p_cardid) from sf_psc_person"
rs.CursorLocation = adUseClient
rs.Open sql1, cn, adOpenKeyset, adLockPessimistic
rs.MoveFirst
pcardIdxxx = rs.Fields(0)'<------------------
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing '修改数据库相应字段
Set cn = New ADODB.Connection
cn.Open "Provider=......."
sql2 = "update sf_psc_person set p_cardid='" & pcardIdxxx & "'" '<-------------
msgbox sql2'<--------------
cn.Execute (sql2)
cn.Close
Set cn = Nothing
end sub
你这样看看Private Sub Command3_Click()
Dim sql1 As String, sql2 As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=。"
sql1 = "select Max(p_cardid) from sf_psc_person"
rs.CursorLocation = adUseClient
rs.Open sql1, cn, adOpenKeyset, adLockPessimistic
rs.MoveFirst
pcardId = rs.Fields(0)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing '修改数据库相应字段
Set cn = New ADODB.Connection
cn.Open "Provider=......."
sql2 = "update sf_psc_person set p_cardid='" & pcardId & "'"
debug.print sql2
cn.Execute (sql2)
cn.Close
Set cn = Nothing
end sub