SQL 语句 问题

westmood

语句的目的是想记录 某一段 sql 语句 进入 ACCESS 数据库.

mystrsql = "INSERT INTO [OperateLog]([operatedate],[username],[userIP],[usercomputername],[actiontype],[SQL_S]) VALUES ('" & Now() & "','" & myusername & "','" & userIP & "','" & usercomputername & "','" & actiontype & "','" & SQL_S & "')"

?mystrsql
INSERT INTO [OperateLog]([operatedate],[username],[userIP],[usercomputername],[actiontype],[SQL_S]) VALUES ('2013/9/9 17:11:18','XSA CI / Jack Ou','','','Login','SELECT * FROM [Useraccount] WHERE (usercode = 'XSACIJO')')

但当执行下面的语句时,出错.

cnn.Execute mystrsql

请问如何处理?

我只想把 SELECT * FROM [Useraccount] WHERE (usercode = 'XSACIJO') 记录 到 数据库.

    myData = DB_path
    mytable = "OperateLog"
    cnnStr = "Provider=microsoft.jet.oledb.4.0;" _
           & "Data Source=" & myData & ";" _
           & "Jet OLEDB:Database Password=" & DB_password & ";"
    On Error Resume Next
    With cnn
        .ConnectionString = cnnStr
        .Open
    End With
    On Error GoTo 0
    If cnn.State <> adStateOpen Then
        MsgBox "Database access failed! Pls contact your administrator!!", vbCritical, "PPR"
        Exit Sub
    End If
    mystrsql = "INSERT INTO [OperateLog]([operatedate],[username],[userIP],[usercomputername],[actiontype],[SQL_S]) VALUES ('" & Now() & "','" & myusername & "','" & userIP & "','" & usercomputername & "','" & actiontype & "','" & SQL_S & "')"
    cnn.Execute mystrsql

westmood

我想问一下是不是不允许把SQL语句放入数据库中？但它们只是TEXT啊。。晕。

amazeyeli

INSERT INTO [OperateLog]([operatedate],[username],[userIP],[usercomputername],[actiontype],[SQL_S])
SELECT '2013/9/9 17:11:18','XSA CI / Jack Ou','','','Login',* FROM [Useraccount] WHERE (usercode = 'XSACIJO')

最好不要用*,就用[Useraccount]里的那个字段名

westmood

amazeyeli 发表于 2013-9-10 14:39
INSERT INTO [OperateLog]([operatedate],,,,[actiontype],[SQL_S])
SELECT '2013/9/9 17:11:18','XSA CI  ...

但那是变量来的,获取自之前的操作,我把一些操作的SQL记录下来,主要是想看那些人在做什么,当一个日志来看..

mystrsql = "INSERT INTO [OperateLog]([SQL_S]) VALUES ('SELECT * FROM [Useraccount]WHERE (usercode = 'test')')"

这么简单都不能过机啊...

其实我只是想记录SQL的操作

在例子里,我想把 SELECT * FROM [Useraccount]WHERE (usercode = 'test') 记录 在数据库里.

amazeyeli

INSERT INTO [OperateLog]([SQL_S])
SELECT 字段名 FROM [Useraccount]
WHERE (usercode = 'test')

values后面只能接值!

westmood

amazeyeli 发表于 2013-9-10 15:15
INSERT INTO [OperateLog]([SQL_S])
SELECT 字段名 FROM
WHERE (usercode = 'test')

那意思就是说我不能把 SELECT * FROM [Useraccount]WHERE (usercode = 'test') 这个看作是一段字符,然后 insert 进 数据库吗?