• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

ADODB, get the result of a 'Delete'

I have the following commad that works, but I would like to retrieve the results.

Can't I change the last line to:

result = oCmd.Execute

    Dim oConn As ADODB.Connection
    Dim oSqlCmd As ADODB.Command
    Dim oRec As New ADODB.Recordset

    Set oConn = New ADODB.Connection
    sConn = "Driver={SQL Server};" & _
           "Server=" & SrvIP & ";" & _
           "Database=" & AppNm & ";" & _
           "Uid=" & Uid & ";" & _
           "Pwd=" & Pwd & ""

    oConn.Open (sConn)
    Dim oCmd As New ADODB.Command  ' Create a new SqlCommand
    oCmd.ActiveConnection = oConn
    Dim sTxtCmd As String
    sTxtCmd = "Delete from myTbl Where Date != '2012/07/18'"
    oCmd.CommandTimeout = 0
    oCmd.CommandType = adCmdText
    oCmd.CommandText = sTxtCmd

Open in new window

  • 2
  • 2
2 Solutions
Try this.
    oCmd.Execute result

    MsgBox result & " records deleted"

Open in new window

Kevin CrossChief Technology OfficerCommented:
Hi. What are you expecting as the result? With a DELETE, there is no recordset to return. You have to use the parameter RecordsAffected (IIRC). Depending on the version of SQL Server you are using, you can look into use OUTPUT (ref: http://msdn.microsoft.com/en-us/library/ms177564.aspx) to return the deleted rows.

EDIT: I just saw imnorie's response, which should be a good example of the RecordsAffected parameter syntax and usage.
sidwelleAuthor Commented:
Imnorie, Thanks for the help.  That was too easy.

Question: will the variable returned ever be anything than an integer ?
Should 'records affected' be dimensioned as a Long or Variant ?

As far as I know it always returns a number.

It should be dimensioned as Long.
sidwelleAuthor Commented:
Thanks for the help.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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