VB6 Insert query
I am writing code in VB6 which inserts data into a database on an SQL 2000 server, I have no problem inserting data but I cannot read it back. Please see code below.
For some reason it does not read data and rst.RecordCount is always -1
Public cnn As ADODB.Connection
Public rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strConnect = ("DSN=Test1;Uid=menta;Pwd=e62185;")
cnn.Open strConnect
strSQL = "insert Staging (A) VALUES ('7')
rst.Open CStr(strSQL), cnn
strSQL = "select * from Staging"
rst.Open CStr(strSQL), cnn
if rst.RecordCount > 0 then
End If
[702 byte] By [
atuvy] at [2007-11-20 11:54:13]
# 2 Re: VB6 Insert query
Several things.
1. Please use CODE Tags.
2. Copy and paste your code between the code tags. If you re-type your code you are liable to make mistakes and mis-lead people.
3. This may be a typing mistake, but Insert is usually in the form of INSERT INTO TableName...
4. You are using a recordset for the execution of your Insert Command. Look to this (http://www.dev-archive.com/forum/showthread.php?t=313038&highlight=ado+INSERT) to see about executing the INSERT command from the connection.
5. It is generally not a good idea to put username and passwords into post. Get into the habit of scrubbing all info like this when posting to forums. In your case, it won't make much difference, however, in a future case it is possible it might.
You SELECT code looks like if might be OK.
# 5 Re: VB6 Insert query
u have to use cursor at side of the client
rst.CursorLocation=adUseClient
rst.Open CStr(strSQL), cnn
if rst.RecordCount > 0 then
End If
# 8 Re: VB6 Insert query
what do you mean by "use curser"
When you open a recordset, you can fetch the data in two ways, viz dynamic and static.
In static fetching you get the copy of whole data at client side so that you can not see changes made by the other user. In client side cursor you can get recordcount while in server side cursors data is fetched dynamically so that you can see changes made by other users. For dynamic data all queries, search and filter are executed on the server hence it is slow.
# 11 Re: VB6 Insert query
Yes, it works now
I need to assign a value to one of the fields in the record but I am getting an error message. I assume it is because of adOpenStatic, is that correct, how do I enter the data?
rst.Open strSQL, cnn, adOpenStatic
If rst.RecordCount > 0 Then
rst.MoveFirst
rst.Fields("XXXXX") = 2
End If
atuvy at 2007-11-9 19:42:07 >
