ansaurus

Question

VB execute() problem

Answer 1

A:

The problem is that your Success message is displayed regardless of whether or not your Update actually updated.

I believe you can call the conDB.RecordsAffected function to return the number of records that were updated. Check the return value of this function and display the appropriate message.

Try something like this:

conDB.Execute (strSQL)
If (conDB.RecordsAffected > 0) Then
    Sheet1.Cells(intStart, 5) = "Success"
Else
    Sheet1.Cells(intStart, 5) = "Failed"
End If

Jay Riggs 2010-08-12 04:36:09

I get the following error.Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

misguided 2010-08-12 04:43:55

@misguided - I might have blown it on the type returned by RecordsAffected (I don't have access to a machine with VB/VBA). I updated, let us know if that works.

Jay Riggs 2010-08-12 04:49:08

Thanks Jay . But I still get the same error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

misguided 2010-08-12 04:56:45

@mis - On which line are you getting the error?

Jay Riggs 2010-08-12 05:05:35

Getting error on {code} If (conDB.RecordsAffected() > 0) Then {code}I beleive it has something to do with working on conDB , because before asking this question I was trying {code}conDB.Execute(strSQL).RecordCount{code} which was throwing a similar error.

misguided 2010-08-12 05:08:56

Do you really have a semicolon after your first line there?

Jay Riggs 2010-08-12 05:16:18

@misguided: That could be because you typed `conDB.RecordsAffected()` as against `conDB.RecordsAffected`. You are calling it as if `RecordsAffected` is a method whereas it is a property. Remove the `()` after `RecordsAffected`.

shahkalpesh 2010-08-12 05:20:00

@ Jay No, i did not give the semicolon. Is it required?@shahkalpesh tried it without () still the same error.

misguided 2010-08-12 05:34:40

Answer 2

+1 A:

dim recordsAffected as long

conDB.Execute strSQL, recordsAffected 
Sheet1.Cells(intStart, 5) = IIF(recordsAffected  > 0, "Success", "Failure")

shahkalpesh 2010-08-12 06:52:54

Hi , conDB.Execute(strSQL,recordsAffected) gives a syntax error. Also FYI Dim conDB As New ADODB.Connection

misguided 2010-08-13 01:51:20

@misguided: Remove the brackets from the line containing `Execute`. See the modified code above.

shahkalpesh 2010-08-13 06:53:09

it did resolve the issue . I have accepted the anwer. Thanks.

misguided 2010-08-27 05:52:08

ansaurus

tags:

views:

answers:

VB execute() problem

related questions