问个查询语句....

apppple21 2008-11-27 10:36:06



Dim sql As String

Dim rs As Recordset

Dim y As String



Set 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")
谁能帮忙看下...什么原因

...全文

126 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

apppple21 2008-11-28

打赏
举报

谢谢VBMAN,AISAC,zuoxingyu和老yi...问题已解决~~~我用的是VBMAN的思路~~额~~结贴

jhone99 2008-11-27

打赏
举报

Set rs = cn.Execute("select 额定寿命 from 刀具表 where 刀具ID='" & Text1.Text & "' ")

apppple21 2008-11-27

打赏
举报

[Quote=引用 8 楼 AisaC 的回复:]
VB code
DBConnection
'你没有cn.open
[/Quote]
啊啊啊 ~~~~
该放在哪里来着....
ps:这东西也是朋友帮我弄的...

神马都能聊 2008-11-27

打赏
举报



DBConnection

'你没有cn.open

apppple21 2008-11-27

打赏
举报

谢谢AISAC,这段定义CN的函数是不是可以写在公共模块中?
我的公共模块已有对数据库的操作定义了.如下:



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 Function



Err_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 Function



Err_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 Function



Err_DBExecuteSQL:

    DBExecuteSQL = -1    '错误返回-1

    Mssages = Err.Description

    Resume DBExecuteSQL_Exit

End Function

神马都能聊 2008-11-27

打赏
举报





Public Sub Open_Cn(cn)

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 Sub



Public 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

apppple21 2008-11-27

打赏
举报

定义了CN之后提示实时错误'3704' 对象关闭时,不允许操作...
是不是要重新定义呢..

apppple21 2008-11-27

打赏
举报

是不是CN也需要定义的?

apppple21 2008-11-27

打赏
举报

好象还是一样哦...
唯一不同的是黄色指针指向 Set rs = cn.Execute(sql)

zuoxingyu 2008-11-27

打赏
举报

搞错了
应该是记录集对象没有定义



Dim sql As String

Dim rs As new ADODB.Recordset

Dim y As String



sql =("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是否有赋值

zuoxingyu 2008-11-27

打赏
举报





Dim sql As String

Dim rs As Recordset

Dim y As String



sql =("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

神马都能聊 2008-11-27

打赏
举报



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 Sub



Public 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

vbman2003 2008-11-27

打赏
举报



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

vbman2003 2008-11-27

打赏
举报



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

vbman2003 2008-11-27

打赏
举报





Private Sub Command1_Click()

    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

filec75 2008-11-27

打赏
举报

ding

apppple21 2008-11-27

打赏
举报

按老yi的程序,实时错误'3001' 参数类型不正确,或不在可以接受的范围之内,或与其他参数冲突.
按调试后提示黄色部分: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