return the result text from a VB ADODB.Connection.Execute command 
Author Message
 return the result text from a VB ADODB.Connection.Execute command

Hi All

I am trying to run SQL scripts via VB & ADO.
Whilst I can run the scripts successfully uising the following pretty
standard VB Function, I want to be able to return the SQL result message.
I can return Errors by intercepting the Err.Description when they occur, but
not the success.
(e.g. (11 row(s) affected)).

So essentially my question is, how do you return the result text from a VB
ADODB.Connection.Execute command?

Public Function SqlRunScript(Provider As String, InitialCatalog As String, _
                                             DataSource As String, _
                                            LoginName As String, _
                                             LoginPassword As String, _
                                            strScriptFile As String) As
Boolean

    On Error GoTo ErrorHandler

    Dim strCmd As String
    Dim strBatch As String
    Dim cnnSQL As ADODB.Connection

    Set cnnSQL = New ADODB.Connection
    cnnSQL.Open "PROVIDER= " & Provider  & "Data Source=" & DataSource & _
                            ";Initial Catalog='" & InitialCatalog & _
                            "';User Id=" & LoginName & ";_
                            Password=" & LoginPassword & ";"

    Open strScriptFile For Input As #1

        Do While Not EOF(1)
            Line Input #1, strCmd

            If (Left(strCmd, 2) <> "GO") Then
                strBatch = strBatch + " " + strCmd
            Else
                If strBatch <> "" Then
                    cnnSQL.Execute strBatch
                    TxtResults = TxtResults & strBatch & vbcrlf
                    TxtResults.Refresh
                    strBatch = ""
                End If
            End If
        Loop
    Close #1

    cnnSQL.Close
    Set cnnSQL = Nothing
    SqlRunScript = True

    Exit Function
ErrorHandler:
    MsgBox "Error in SqlRunScript !" & vbCrLf & Err.Description
    TxtResults = TxtResults & Err.Description & vbcrlf
    SqlRunScript = False
    Resume Next
End Function

Thanks in advance
Brian



Sun, 27 Mar 2005 17:46:04 GMT  
 return the result text from a VB ADODB.Connection.Execute command
Hi,

Execute method of ADO Connection has parameter, which calls RecordAffected.
It returns number of affected records by last executed query. Just pass
variable as aparameter and read that variable after execution has been
completed. But in case of batch execution, that variable should contain ONLY
value of affected records by LAST statement in a batch

--
Val Mazur
Microsoft MVP


Quote:
> Hi All

> I am trying to run SQL scripts via VB & ADO.
> Whilst I can run the scripts successfully uising the following pretty
> standard VB Function, I want to be able to return the SQL result message.
> I can return Errors by intercepting the Err.Description when they occur,
but
> not the success.
> (e.g. (11 row(s) affected)).

> So essentially my question is, how do you return the result text from a VB
> ADODB.Connection.Execute command?

> Public Function SqlRunScript(Provider As String, InitialCatalog As String,
_
>                                              DataSource As String, _
>                                             LoginName As String, _
>                                              LoginPassword As String, _
>                                             strScriptFile As String) As
> Boolean

>     On Error GoTo ErrorHandler

>     Dim strCmd As String
>     Dim strBatch As String
>     Dim cnnSQL As ADODB.Connection

>     Set cnnSQL = New ADODB.Connection
>     cnnSQL.Open "PROVIDER= " & Provider  & "Data Source=" & DataSource & _
>                             ";Initial Catalog='" & InitialCatalog & _
>                             "';User Id=" & LoginName & ";_
>                             Password=" & LoginPassword & ";"

>     Open strScriptFile For Input As #1

>         Do While Not EOF(1)
>             Line Input #1, strCmd

>             If (Left(strCmd, 2) <> "GO") Then
>                 strBatch = strBatch + " " + strCmd
>             Else
>                 If strBatch <> "" Then
>                     cnnSQL.Execute strBatch
>                     TxtResults = TxtResults & strBatch & vbcrlf
>                     TxtResults.Refresh
>                     strBatch = ""
>                 End If
>             End If
>         Loop
>     Close #1

>     cnnSQL.Close
>     Set cnnSQL = Nothing
>     SqlRunScript = True

>     Exit Function
> ErrorHandler:
>     MsgBox "Error in SqlRunScript !" & vbCrLf & Err.Description
>     TxtResults = TxtResults & Err.Description & vbcrlf
>     SqlRunScript = False
>     Resume Next
> End Function

> Thanks in advance
> Brian



Sun, 27 Mar 2005 19:41:49 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. How do you execute a command-line program, and return the results into a variable

2. ADODB.Connection.Execute VS ADODB.Recordset.Update

3. command.execute vs. connection.execute

4. command.execute dosnt return a recordset to VB?

5. Problems with ADODB.Connection.Execute( query)

6. ADODB.Command asynch execute.

7. ADODB.Command asynch execute.

8. Type Mismatch Executing Adodb.command

9. ADODB.Command asynch execute.

10. ADODB.Command Output Params return null for GUID types

11. VB6 + SQLserver 2000: Connection.execute result sets

12. vbscript passing a Memo field from adodb connection returns Null after reading once

 

 
Powered by phpBB® Forum Software