执行以下SQL语句时出现“类型不匹配”
txtsQl = "delete from bustab_cashincome where id=" & CLng(frmSell.msgList.TextMatrix(intcount, 1))
Set mrc = ExecuteSQL(txtsQl, Msgtext)
其中ID为在某ACCESS数据表的字段。类型为自动编号,但仍然可以删除该条记录。
MSGLIST为表格控件。CLng(frmSell.msgList.TextMatrix(intcount, 1))的取值正确。
txtsQl = "delete from bustab_cashincome where id=" & CLng(frmSell.msgList.TextMatrix(intcount, 1))
Set mrc = ExecuteSQL(txtsQl, Msgtext)
其中ID为在某ACCESS数据表的字段。类型为自动编号,但仍然可以删除该条记录。
MSGLIST为表格控件。CLng(frmSell.msgList.TextMatrix(intcount, 1))的取值正确。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Combo1_Click()
cn.execute "delete from bustab_cashincome where id = " & frmSell.msgList.TextMatrix(intcount, 1)
End SubPrivate Sub Form_Load()
cn.ConnectionString = "DSN=SCM_ACCESS;pwd=ORIENTSCM;uid=ADMIN"
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim l As Integer
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
If SQL = "" Then MsgBox ("SQL为空")
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
Set ExecuteSQL = cnn
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & " 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
MsgBox (MsgString)
Resume ExecuteSQL_Exit
End Function
Dim cnn As new ADODB.Connection
Cnn.ConnectionString =ConnectString
cnn.Open
txtsQl = "delete * from bustab_cashincome where id=" & CLng(frmSell.msgList.TextMatrix(intcount, 1)) ' 这句话如果没问题的话
cnn.Execute txtsQl
cnn.close
set cnn=nothing
txtsQl = "delete * from bustab_cashincome "& _
"where id="& frmSell.msgList.TextMatrix(intcount, 1) &"
"where id='"& frmSell.msgList.TextMatrix(intcount, 1) &"'"
加个单引号
ExecuteSQL是一个返回记录集的函数,怎么能给他赋cnn呢?
改成Set ExecuteSQL = nothing