Dim sql As String
Dim rs As Recordset
Dim y As StringSet rs = cn.Execute("select 额定寿命 from 刀具表 where 刀具ID=Text1.Text")
y = rs!刀具ID
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
但是老是提示我:实时错误'424': 要求对象
按下 调试后,黄色指针指示这句话:Set rs = cn.Execute("select 额定寿命 from 刀具表 where 刀具ID=Text1.Text")
谁能帮忙看下...什么原因
Dim rs As Recordset
Dim y As Stringsql =("select 额定寿命 from 刀具表 where 刀具ID=" & Text1.Text
Set rs = cn.Execute(sql)y = rs!刀具ID
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
应该是记录集对象没有定义
Dim sql As String
Dim rs As new ADODB.Recordset
Dim y As Stringsql =("select 额定寿命 from 刀具表 where 刀具ID=" & Text1.Text
Set rs = cn.Execute(sql)y = rs!刀具ID
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages另外查一下CN是否有赋值
唯一不同的是 黄色指针指向 Set rs = cn.Execute(sql)
On Error GoTo err
Set cn = CreateObject("ADODB.connection")
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security info=False;User ID='" & DbUserName & "';Password='" & DbUserPwd & "';Initial Catalog='" & DbName & "';Data Source='" & SvrName & "'"
cn.CommandTimeout = 30
cn.CursorLocation = 3
cn.Open
Exit Sub
err:
Set cn = Nothing
Show_Info "Open_Cn", err
End SubPublic Sub Open_Rs(rs, cn)
On Error GoTo err
Set rs = CreateObject("ADODB.RecordSet")
rs.ActiveConnection = cn
rs.LockType = 3
rs.CursorType = 3
Exit Sub
err:
Set rs = Nothing
Show_Info "Open_Rs", err
End Sub
初始化之后用rs.open sql
我的公共模块已有对数据库的操作定义了.如下:Public Mssages As String'连接函数
Public Function DBConnection(cn As Connection) As Boolean
On Error GoTo Err_Connection
Set cn = New Connection
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=db_aisin-hongda;Data Source=ZL"
DBConnection = cn.State
Exit FunctionErr_Connection:
Set cn = Nothing
Mssages = Err.Description
DBConnection = False
End Function'数据库操作函数:'数据库操作函数(用于SELECT查询操作)
Public Function DBQuerySQL(rs As Recordset, ByVal sql As String) As Long
On Error GoTo Err_DBQuerySQL Dim cn As Connection
If DBConnection(cn) Then '用上面的连接函数连接数据库
Set rs = cn.Execute(sql)
If rs.BOF Then
DBQuerySQL = 0 '没有数据返回
Else
DBQuerySQL = 1 '正常返回数据
End If
Else
Mssages = "连接数据库不成功 " & vbCrLf & Mssages
DBQuerySQL = -1 '连接不成功
End If
DBQuerySQL_Exit:
Set cn = Nothing
Exit FunctionErr_DBQuerySQL:
DBQuerySQL = -1 '错误返回-1
Mssages = Err.Description
Resume DBQuerySQL_Exit
End Function
Public Function DBExecuteSQL(ByVal sql As String) As Long
On Error GoTo Err_DBExecuteSQL Dim cn As Connection
Dim row As Long
If DBConnection(cn) Then
cn.Execute sql, row
DBExecuteSQL = row '返回所影响的行数
Else
Mssages = "连接数据库不成功 " & vbCrLf & Mssages '针对0的情况,不会影响-1
DBExecuteSQL = -1
End If
DBExecuteSQL_Exit:
Set cn = Nothing
Exit FunctionErr_DBExecuteSQL:
DBExecuteSQL = -1 '错误返回-1
Mssages = Err.Description
Resume DBExecuteSQL_Exit
End Function
DBConnection
'你没有cn.open
按调试后提示黄色部分:rs.Open "select 额定寿命 from 刀具表 where 刀具ID='" & Text1.Text & "' ", cn, 1, 3总体程序如下:Private Sub Command1_Click()
Dim sql As String
Set rs = New adodb.Recordset
rs.Open "select 额定寿命 from 刀具表 where 刀具ID='" & Text1.Text & "' ", cn, 1, 3
Y = rs!额定寿命sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & Y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
Unload Me
End Sub
Dim sql As String
dim rs as recordset
select case DBQuerySQL(rs,sql) '用你模块中的查询函数
case 0
msgbox "没有数据"
exit sub
case -1 '出错了
msgbox Mssages
exit sub
case 1
Y = rs!额定寿命
set rs=nothing
end select
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & Y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
Unload Me
End Sub
Private Sub Command1_Click()
Dim sql As String
dim rs as recordset
dim y as string
sql="select 额定寿命 from 刀具表 where 刀具ID='" & Text1.Text & "'"
select case DBQuerySQL(rs,sql) '用你模块中的查询函数
case 0
msgbox "没有数据"
exit sub
case -1 '出错了
msgbox Mssages
exit sub
case 1
Y = rs!额定寿命
set rs=nothing
end select
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & Y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
Unload Me
End Sub
Private Sub Command1_Click()
Dim sql As String
dim rs as recordset
dim y as string
sql="select 额定寿命 from 刀具表 where 刀具ID='" & Text1.Text & "'"
select case DBQuerySQL(rs,sql) '用你模块中的查询函数
case 0
msgbox "没有数据"
exit sub
case -1 '出错了
msgbox Mssages
exit sub
case 1
Y = rs!额定寿命
set rs=nothing
end select
sql = "insert into 刀具记录表(刀具ID,生产线编号,额定寿命,实际使用次数,更换上时刻) values ('" & Trim(Text1.Text) & "','" & Trim(Form1.Text1.Text) & "','" & Y & "','0','" & Trim(Now) & "')"
If DBExecuteSQL(sql) = -1 Then MsgBox Mssages
Unload Me
End Sub
Public Sub Open_Cn(cn)
On Error GoTo err
Set cn = CreateObject("ADODB.connection")
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=db_aisin-hongda;Data Source=ZL"
cn.CommandTimeout = 30
cn.CursorLocation = 3
cn.Open
Exit Sub
err:
Set cn = Nothing
End SubPublic Sub Open_Rs(rs, cn)
On Error GoTo err
Set rs = CreateObject("ADODB.RecordSet")
rs.ActiveConnection = cn
rs.LockType = 3
rs.CursorType = 3
Exit Sub
err:
Set rs = Nothing
End Sub'使用
private sub Simple_Test()
on error goto err
dim cn as object
dim rs as object
dim strsql as string
call open_cn(cn)
call open_rs(rs,cn)
strsql = "select 额定寿命 from 刀具表 where 刀具ID=Text1.Text"
rs.open strsql
'结果返回后的操作
set rs = nothing
set cn = nothing
exit sub
err:
set cn = nothing
set rs = nothing
end sub