Hello,

If there an easy way to get a completed SQL statement back after parameter substitution? I.e., I want to keep a logfile of all the SQL this program runs.

Or if I want to do this, will I just want to get rid of Parameters, and do the whole query the old school way, in one big string?

Simple Example: I want to capture the output:

SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

.. from this code:

    Dim subCatSQL As String = "SELECT subcatId FROM EnrollmentSubCategory WHERE catid = @catId"
    Dim connectionString As String = "X"
    Dim conn As New SqlConnection(connectionString)
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    Dim cmd As New SqlCommand(subCatSQL, conn)
    With cmd
        .Parameters.Add(New SqlParameter("@catId", SqlDbType.Int, 1)) 
    End With

    Console.WriteLine("Before: " + cmd.CommandText)
    cmd.Prepare()
    Console.WriteLine("After: " + cmd.CommandText)

I had assumed Prepare() would do the substitutions, but apparently not.

Thoughts? Advice? Thanks in advance.