我现在有数据库a中有两个表 jl和tj (记录和统计)jl表中有字段id,num,time time为关键字 记录系统当前时间
tj表中有字段id,num id为关键字全部是文本类型 我用VB已经在text输入时候限制了类型
3个Text分别为 Text1、Text2分别对应两个表中的id,num字段一个确定按钮command1我想在text中输入数据分别进入两个表jl表好说数据进入tj表的时候 先查询tj表的id 如果数据库中存在id=text1的时候 就把id=text1这段数据中的num字段中的值加上此时text2的值再送入当前数据段中num字段如果不存在id=text1则将当前text1,text1数据插入到表tj中
例如:tj表中已经有数据123,20
当前text1内容为123、text2为50
查询表tj的id为123的数据 然后把里面的num20+50再送入这段数据中 就成了123,70
当前text1内容为111、text2为50
查询表tj的id为111的数据 没有 就插入表中 就成了 123,20
111,50
我写了以下代码 总是调试不过 弹那个重复关键字错误代码请高人帮忙解决下Set conn = CreateObject("ADODB.Connection")
connSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\a.mdb"
conn.Open connSTR
Set rs = CreateObject("adodb.recordset")
sql = "select * from jl"
rs.Open sql, conn, 1, 3
rs.AddNew
rs("id") = Text1.Text
rs("num") = Text2.Text
rs("time") = Date & " " & Time
rs.Update
rs.Close
Set rs = NothingSet rs1 = CreateObject("adodb.recordset")
sql = "select * from tj"
rs1.Open sql, conn, 1, 3
x = Text1.Text
lbsql = "select * from qk where trim(id)='" & x & "' Order by id"
If lbsql <> Null Then
Adodc1.Refresh
Text1.Text = "" & x
DataGrid1.SetFocus
y = jl.num
z = sj.num
a = y + z
rs.Update
rs1("num") = a
rs1.Update
Elsers1.AddNew
rs1("id") = Text1.Text
rs1("num") = Text2.Text
rs1.Update
Text1.Text = ""
Text2.Text = ""rs1.CloseSet rs1 = Nothingconn.CloseSet conn = NothingEnd If
tj表中有字段id,num id为关键字全部是文本类型 我用VB已经在text输入时候限制了类型
3个Text分别为 Text1、Text2分别对应两个表中的id,num字段一个确定按钮command1我想在text中输入数据分别进入两个表jl表好说数据进入tj表的时候 先查询tj表的id 如果数据库中存在id=text1的时候 就把id=text1这段数据中的num字段中的值加上此时text2的值再送入当前数据段中num字段如果不存在id=text1则将当前text1,text1数据插入到表tj中
例如:tj表中已经有数据123,20
当前text1内容为123、text2为50
查询表tj的id为123的数据 然后把里面的num20+50再送入这段数据中 就成了123,70
当前text1内容为111、text2为50
查询表tj的id为111的数据 没有 就插入表中 就成了 123,20
111,50
我写了以下代码 总是调试不过 弹那个重复关键字错误代码请高人帮忙解决下Set conn = CreateObject("ADODB.Connection")
connSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\a.mdb"
conn.Open connSTR
Set rs = CreateObject("adodb.recordset")
sql = "select * from jl"
rs.Open sql, conn, 1, 3
rs.AddNew
rs("id") = Text1.Text
rs("num") = Text2.Text
rs("time") = Date & " " & Time
rs.Update
rs.Close
Set rs = NothingSet rs1 = CreateObject("adodb.recordset")
sql = "select * from tj"
rs1.Open sql, conn, 1, 3
x = Text1.Text
lbsql = "select * from qk where trim(id)='" & x & "' Order by id"
If lbsql <> Null Then
Adodc1.Refresh
Text1.Text = "" & x
DataGrid1.SetFocus
y = jl.num
z = sj.num
a = y + z
rs.Update
rs1("num") = a
rs1.Update
Elsers1.AddNew
rs1("id") = Text1.Text
rs1("num") = Text2.Text
rs1.Update
Text1.Text = ""
Text2.Text = ""rs1.CloseSet rs1 = Nothingconn.CloseSet conn = NothingEnd If
不明白
Adodc1.Refresh
Text1.Text = "" & x
DataGrid1.SetFocus
y = jl.num
z = sj.num
a = y + z
rs.Update
rs1("num") = a
rs1.Update
这几句语句的作用又是什么?
其实这个问题是很简单的啊。Set conn = CreateObject("ADODB.Connection")
connSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\a.mdb"
conn.Open connSTR
Set rs = CreateObject("adodb.recordset")
sql = "select * from jl"
rs.Open sql, conn, 1, 3
rs.AddNew
rs("id") = Text1.Text
rs("num") = Text2.Text
rs("time") = Date & " " & Time
rs.Update
rs.Close
Set rs = Nothing
Set rs1 = CreateObject("adodb.recordset")
x = Text1.Text
lbsql = "select * from TJ where trim(id)='" & x & "' Order by id"
rs1.open lbsql,conn,1,3
if rs1.bof then '没有这个记录号
conn.execute "update tj set num=num+"+text2+"where id='"+text1+"'"
Else
conn.execute "insert tj(id,num) values('"+text1+"',"+text2+")
End If Text1.Text = ""
Text2.Text = "" rs1.Close Set rs1 = Nothing conn.Close Set conn = Nothing
lbsql = "select * from TJ where trim(id)='" & x & "' Order by id"
rs1.open lbsql,conn,1,3
if rst.RecordCount>0 then
conn.execute "update tj set num=num+"+text2+"where id='"+text1+"'"
Else
conn.execute "insert tj(id,num) values('"+text1+"',"+text2+")
End If
这样来判断!rs1.RecordCount看看有多少记录,大于0或等于1就是有了,累加就行了,否则就插入!
lbsql = "select * from TJ where trim(id)='" & x & "' Order by id"
rs1.open lbsql,conn,1,3
if rs1.RecordCount>0 then
conn.execute "update tj set num=num+"+text2+"where id='"+text1+"'"
Else
conn.execute "insert tj(id,num) values('"+text1+"',"+text2+")
End If
如果为True,则插入,否则Update就可以。
connSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\a.mdb"
conn.Open connSTR
Set rs = CreateObject("adodb.recordset")
'Query record with a where substatement
sql = "select * from jl where = '" & Text1.Text & "'"
rs.Open sql, conn, 1, 3 If rs.EOF Then
rs.AddNew
rs("id") = Text1.Text
rs("num") = Text2.Text
rs("time") = Date & " " & Time
Else
rs("num") = CStr(Val(rs("num")) + Val(Text2.Text))
End Ifconn.Close
Set conn = Nothing
connSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\a.mdb"
conn.Open connSTR
Set rs = CreateObject("adodb.recordset")
'Query record with a where substatement
sql = "select * from jl where = '" & Text1.Text & "'"
rs.Open sql, conn, 1, 3 If rs.EOF Then
rs.AddNew
rs("id") = Text1.Text
rs("num") = Text2.Text
Else
rs("num") = CStr(Val(rs("num")) + Val(Text2.Text))
End If rs("time") = Date & " " & Time
rs.Update
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
conn.execute "update tj set num=num+"+text2+"where id='"+text1+"'"
Else
conn.execute "insert tj(id,num) values('"+text1+"',"+text2+")
End If 上面的条件下反了,哈哈!应该是下面的:
if rs1.bof then '没有这个记录号
conn.execute "insert tj(id,num) values('"+text1+"',"+text2+")
Else
conn.execute "update tj set num=num+"+text2+"where id='"+text1+"'"
End If
2,你的判断语句有问题Dim Conn As New ADODB.Connection
Dim SQL As String
Dim ConnStr As String
Dim RS1 As New ADODB.Recordset
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB.mdb;Persist Security Info=False"
SQL = "select * from DB where id = '" & Text1.Text & "'"
Conn.Open ConnStr
If RS1.State <> 0 Then RS1.Close
RS1.Open SQL, Conn
If RS1.EOF Then '判断TJ表中是否有重复的ID,如果没有则 执行下面的新增操作
Conn.Execute "insert into db (id,num) values ('" & Text1.Text & "','" & Text2.Text & "')"
Else '如果TJ表中有重复的ID,则修改TJ表中重复ID项的记录,使其其中的NUM+TEXT2.TEXT
Conn.Execute "update DB set num = num + " & Trim(Text2.Text) & " where id = '" & Text1.Text & "'"
End If
MsgBox "更新成功"
RS1.Close
Set RS1 = Nothing先去看看吧 上述代码已经测试过 一切OK
你只要改成你自己需要的OK了有点粗心 …………^_^