Private Sub Command1_Click()
Dim str As String
Dim i As Integer
sSQL = "select * from goods order by id desc"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Not Rs.EOF Then
' MsgBox "编号 [ " & Trim(Text1.Text) & " ] 已存在,自动编辑下一个!", vbInformation, "提示窗口"
str = Rs.Fields("id")
i = Val(str)
i = i + 1
str = CStr(i)
sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL
' Exit Sub
End If
Unload Me
Form2.Show
MsgBox "数据添加成功"
End Sub
这段程序为什么运行起来,加数据到数据库要点两下按钮才能吧数据加进去,谁能帮我下,谢谢!!!
Dim str As String
Dim i As Integer
sSQL = "select * from goods order by id desc"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Not Rs.EOF Then
' MsgBox "编号 [ " & Trim(Text1.Text) & " ] 已存在,自动编辑下一个!", vbInformation, "提示窗口"
str = Rs.Fields("id")
i = Val(str)
i = i + 1
str = CStr(i)
sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL
' Exit Sub
End If
Unload Me
Form2.Show
MsgBox "数据添加成功"
End Sub
这段程序为什么运行起来,加数据到数据库要点两下按钮才能吧数据加进去,谁能帮我下,谢谢!!!
conn.Execute sSQL
这里下断点看看第一次点的时候能运行到不不能运行到说明是上面代码的问题
If Not Rs.EOF Then你只判断了not Rs.EOF的情况
也就是能找到相同内容的情况当你新增的时候肯定就是Rs.EOF加个else运行Rs.EOF的代码就可以了
If Not Rs.EOF Then 这个如果没有会怎么样我觉得好像没影响啊你把这个和下面的end if去掉看看
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'函数功能:连接指定的数据库
'参数说明:cnnP:数据库连接对象;
' :adoP:数据集存储对象;
' :strPath:数据库路径;
' :strPassword:数据库密码;
'返回说明:True:连接成功 False:连接失败
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Public Function funConnectDataBase(cnnP As ADODB.Connection, adoP As ADODB.Recordset, ByVal strPath As _
String, ByVal strPassword As String) As Boolean
On Error GoTo errFunction
Set cnnP = New ADODB.Connection
Set adoP = New ADODB.Recordset
cnnP.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnP.Open "Data Source = " & strPath & ";jet oledb:database password=" & strPassword
funConnectDataBase = True
Exit Function
errFunction:
funConnectDataBase = False
End Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'函数功能:关闭数据库连接对象和数据文件的关联
'参数说明:cnnP:数据库连接对象;
' :adoP:数据库存储对象;
'返回说明:True:关闭连接成功 False:关闭连接失败
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Public Function funCloseDataBase(cnnP As ADODB.Connection, adoP As ADODB.Recordset) As Boolean
On Error GoTo errFunction
Set adoP = Nothing
Set cnnP = Nothing
funCloseDataBase = True
Exit Function
errFunction:
funCloseDataBase = False
End Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'过程功能:对指定的对象执行指定的SQL语句
'参数说明:cnnP:ADO连接对象
' :adoP:ADO记录集对象
' :strSql:SQL语句
' :bolQueryRecord:是否是查询记录集
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%Public Sub subExcuteSQL(cnnP As ADODB.Connection, adoP As ADODB.Recordset, strSql As String, bolQueryRecord _
As Boolean)
If bolQueryRecord Then '如果是查询记录集
adoP.Open strSql, cnnP, adOpenStatic, adLockBatchOptimistic
Else
cnnP.Execute strSql
End If
End Sub
Dim str As String
Dim i As Integer
sSQL = "select max(id) as id from goods"
If Rs.State <> 0 Then Rs.Close
rs.open sSQL,conn,adopenkeyset,adlockreadonly
if rs.recordcount=0 then
i=0
else
if not isnull(rs!id) then
i=rs!id
else
i=0
end if
end if
rs.close
str = CStr(i+1)
sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL
Unload Me
Form2.Show
MsgBox "数据添加成功"
End Sub
Dim str As String
Dim i As Integer
sSQL = "select * from goods order by id desc"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Not Rs.EOF Then
' MsgBox "编号 [ " & Trim(Text1.Text) & " ] 已存在,自动编辑下一个!", vbInformation, "提示窗口"
str = Rs.Fields("id")
i = Val(str)
i = i + 1
str = CStr(i)
sSQL = "insert into goods("
sSQL = sSQL & "id" & ","
sSQL = sSQL & "factory" & ","
sSQL = sSQL & "model" & ","
sSQL = sSQL & "price" & ","
sSQL = sSQL & "counts" & ")"
sSQL = sSQL & "values('"
sSQL = sSQL & str & "','"
sSQL = sSQL & (Combo1.Text) & "','"
sSQL = sSQL & (Combo2.Text) & "','"
sSQL = sSQL & (Text2.Text) & "','"
sSQL = sSQL & (Text3.Text) & "')"
conn.Execute sSQL
'sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL
' Exit Sub
End If
Unload Me
Form2.Show
MsgBox "数据添加成功"End Sub
这是现在我用的程序,大家帮忙解决下
'sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL 楼主执行了两遍当然会点一次插2条一样的记录了啊!~ 去掉一个
Dim str As String
Dim i As Integer
sSQL = "select max(id) as id from goods"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Rs.RecordCount <= 0 Then
i = 0
Else
If Not IsNull(Rs!id) Then
i = Rs!id
Else
i = 0
End If
End If
Rs.Close
i = i + 1
str = CStr(i)
sSQL = "insert into goods("
sSQL = sSQL & "id" & ","
sSQL = sSQL & "factory" & ","
sSQL = sSQL & "model" & ","
sSQL = sSQL & "price" & ","
sSQL = sSQL & "counts" & ")"
sSQL = sSQL & "values('"
sSQL = sSQL & str & "','"
sSQL = sSQL & (Combo1.Text) & "','"
sSQL = sSQL & (Combo2.Text) & "','"
sSQL = sSQL & (Text2.Text) & "','"
sSQL = sSQL & (Text3.Text) & "')"
conn.Execute sSQL
Unload Me
Form2.Show
MsgBox "数据添加成功"End Sub
Dim str As String
Dim i As Integer
sSQL = "select * from goods order by id desc"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Not Rs.EOF Then
' MsgBox "编号 [ " & Trim(Text1.Text) & " ] 已存在,自动编辑下一个!", vbInformation, "提示窗口"
str = Rs.Fields("id")
i = Val(str)
i = i + 1
str = str & Format$(i,"000;")
sSQL = "insert into goods("
sSQL = sSQL & "id" & ","
sSQL = sSQL & "factory" & ","
sSQL = sSQL & "model" & ","
sSQL = sSQL & "price" & ","
sSQL = sSQL & "counts" & ")"
sSQL = sSQL & "values('"
sSQL = sSQL & str & "','"
sSQL = sSQL & (Combo1.Text) & "','"
sSQL = sSQL & (Combo2.Text) & "','"
sSQL = sSQL & (Text2.Text) & "','"
sSQL = sSQL & (Text3.Text) & "')"
conn.Execute sSQL
'sSQL = "insert into goods values('" & str & "','" & Combo1.Text & "','" & Combo2.Text & "','" & Text2.Text & "','" & Text3.Text & "')"
conn.Execute sSQL
' Exit Sub
End If
Unload Me
Form2.Show
MsgBox "数据添加成功"
End Sub
Dim str As String
Dim i As Integer
sSQL = "select max(id) as id from goods"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Rs.RecordCount <= 0 Then
i = 0
Else
If Not IsNull(Rs!id) Then
i = Rs!id
Else
i = 0
End If
End If
Rs.Close
i = i + 1
str = str & i
sSQL = "insert into goods("
sSQL = sSQL & "id" & ","
sSQL = sSQL & "factory" & ","
sSQL = sSQL & "model" & ","
sSQL = sSQL & "price" & ","
sSQL = sSQL & "counts" & ")"
sSQL = sSQL & "values('"
sSQL = sSQL & str & "','"
sSQL = sSQL & (Combo1.Text) & "','"
sSQL = sSQL & (Combo2.Text) & "','"
sSQL = sSQL & (Text2.Text) & "','"
sSQL = sSQL & (Text3.Text) & "')"
conn.Execute sSQL
Unload Me
Form2.Show
MsgBox "数据添加成功"
End Sub
把 Dim i As Integer 改成 Dim i As long 试试
有可能这个原因Private Sub Command1_Click()
Dim str As String
Dim i As long
sSQL = "select max(id) as id from goods"
If Rs.State <> 0 Then Rs.Close
Set Rs = conn.Execute(sSQL)
If Rs.RecordCount <= 0 Then
i = 0
Else
If Not IsNull(Rs!id) Then
i = Rs!id
Else
i = 0
End If
End If
Rs.Close
i = i + 1
str = CStr(i)
sSQL = "insert into goods("
sSQL = sSQL & "id" & ","
sSQL = sSQL & "factory" & ","
sSQL = sSQL & "model" & ","
sSQL = sSQL & "price" & ","
sSQL = sSQL & "counts" & ")"
sSQL = sSQL & "values('"
sSQL = sSQL & str & "','"
sSQL = sSQL & (Combo1.Text) & "','"
sSQL = sSQL & (Combo2.Text) & "','"
sSQL = sSQL & (Text2.Text) & "','"
sSQL = sSQL & (Text3.Text) & "')"
conn.Execute sSQL
Unload Me
Form2.Show
MsgBox "数据添加成功"End Sub