Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

VB6 wih SQL 2005

if i run this code in vb6 to remove few rows from the SQL2005 database, its entering in to a unstoppable loop. Please help me to resolve this

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset



conn.Open "Provider = sqloledb;" & "Data Source=10.10.10.1;" & "Initial Catalog=Master;" & "User ID=sa;" & "Password=password;"
rs.Open "select * from applications", conn, adOpenStatic, adLockOptimistic, adCmdText

rs.MoveFirst
Do Until rs.EOF
If rs.Fields(1) = HID Then
rs.Delete

End If
rs.MoveNext
Loop


All other connectivity with the same connection is working fine. While trying to delete this problem occurs..

0
Intelops
Asked:
Intelops
1 Solution
 
IntelopsAuthor Commented:
HID value i am getting from other one query..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would it be possible to combine the 2 queries:

DELETE applications
  WHERE HID in ( SELECT HID FROM othertable WHERE ... ) 

Open in new window


or something along those lines?
using the recordset.delete is normally not the most efficient method ...
0
 
Alpesh PatelAssistant ConsultantCommented:
open a new command with the ID in place of rs.Delete
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
IntelopsAuthor Commented:
Can you explain deeply please..
0
 
kamindaCommented:
As you are changing the same recordset by deleting itself may be causing this problem. Use
Instead of rs.Delete use
Conn.Execute "DELETE FROM applications WHERE FIELD1 = '" & HID & "'"
0
 
IntelopsAuthor Commented:
The answer was perfect
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now