conn.execute "insert into yourtable ..." set rs=conn.execute ("select * from yourtable")
你要干嘛?"Insert Into"语句不是可以增加一条记录吗?
我是想返回刚插入的AUTO ID号,这样很方便。不然的话,我得这样: rs.open "select * from mytable where id<0" '返回一空记录集rs.addnew ... rs.update 得到rs("id"),这样很麻烦
Insert Into是不会返回记录集的
如果是sql数据库,可以这样:Dim rs As New ADODB.Recordset rs.Open "INSERT INTO jobs(a,b,c) VALUES ('Accountant',12,125)", cnDatabase rs.Open " SELECT @@IDENTITY AS 'Identity'", cnDatabase Debug.Print rs!Identity
我建议你还是把你的程序改一改,使它能对你表中的id进行控制。 插入后是不会返回结果集的。 以下代码可供参考,很好用的。 Public Function GetNewId() As Integer sqlstmt = "SELECT ID FROM Table ORDER BY Id" Set Rs2 = Con.Execute(sqlstmt) With Rs2 If .EOF Or .BOF Then GetNewId = 1 Exit Function Else .MoveFirst i = 1 End If While Not .EOF If !ID = i Then i = i + 1 .MoveNext Else GetNewId = i Rs2.Close Exit Function End If Wend Rs2.Close End With GetNewId = i End Function
set rs=conn.execute ("select * from yourtable")
你要干嘛?"Insert Into"语句不是可以增加一条记录吗?
rs.open "select * from mytable where id<0" '返回一空记录集rs.addnew
...
rs.update
得到rs("id"),这样很麻烦
rs.Open "INSERT INTO jobs(a,b,c) VALUES ('Accountant',12,125)", cnDatabase
rs.Open " SELECT @@IDENTITY AS 'Identity'", cnDatabase
Debug.Print rs!Identity
插入后是不会返回结果集的。
以下代码可供参考,很好用的。
Public Function GetNewId() As Integer
sqlstmt = "SELECT ID FROM Table ORDER BY Id"
Set Rs2 = Con.Execute(sqlstmt)
With Rs2
If .EOF Or .BOF Then
GetNewId = 1
Exit Function
Else
.MoveFirst
i = 1
End If
While Not .EOF
If !ID = i Then
i = i + 1
.MoveNext
Else
GetNewId = i
Rs2.Close
Exit Function
End If
Wend
Rs2.Close
End With
GetNewId = i
End Function