Handy Dandy CommandText

This might sound lazy, but when you're working with OLEDBConnection  command object, and you've added your parameters, and you have the command text a string with loads of @paramname's in, and it goes wrong!!! Often with not enough feedback.
This might be firing at an mdb or SQL backend...is there a handy dandy extension that allows you to flip those values into a string so I can test it in a QBE grid or a SQL server management window, where I get more feedback?
I know I should make my own, but I wondered if anyone had something out there...as it seems such a common hassle.
Silas2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
i have something that may be of use....
currently converting an old asp app that uses ADODB. which doesnt use parameters well so i have made some extensions that imitate parameters (poorly).
it does create a string that I too paste into SQLM to test and check....

   ''' <param name="paramValue">String value.  can be ISNULL or ISNOTNULL.</param> 
    <Extension()>
    Public Sub SetParam(ByRef aString As String, ByVal paramName As String, paramValue As String)
        If paramValue = "ISNULL" Then
            'have to isolate the operator and parameter. assume spaced nicely
            Dim sp1 As Integer = aString.IndexOf(paramName)
            Dim sp2 As Integer = aString.LastIndexOf(" ", sp1 - 2)
            aString = aString.Replace(aString.Substring(sp2, sp1 - sp2 + paramValue.Length + 1), " IS NULL")
        ElseIf paramValue = "ISNOTNULL" Then
            'have to isolate the operator and parameter. assume spaced nicely
            Dim sp1 As Integer = aString.IndexOf(paramName)
            Dim sp2 As Integer = aString.LastIndexOf(" ", sp1 - 2)
            aString = aString.Replace(aString.Substring(sp2, sp1 - sp2 + paramValue.Length + 1), " IS NOT NULL")
        Else
            aString = aString.Replace(paramName, String.Format("'{0}'", paramValue))
        End If
    End Sub
    <Extension()>
    Public Sub SetParam(ByRef aString As String, ByVal paramName As String, paramValue As Long)
        aString = aString.Replace(paramName, String.Format("{0}", paramValue))
    End Sub
    <Extension()>
    Public Sub SetParam(ByRef aString As String, ByVal paramName As String, paramValue As Date)
        aString = aString.Replace(paramName, String.Format("'{0:yyyyMMdd}'", paramValue))
    End Sub
    <Extension()>
    Public Sub SetParam(ByRef aString As String, ByVal paramName As String, paramValue As Single)
        aString = aString.Replace(paramName, String.Format("{0}'", paramValue))
    End Sub

Open in new window

       'Update PIF_Categorise Table
        SQLQuery = "UPDATE PIF_Categorise " & _
            "SET Category09_ID = @maxCategory09 , StaffID_Who_Categorised = @StaffId, Date_Categorised = @DateC, AdditionalNotes = @pifNotes" & _
            "WHERE PIF_ID= @PifID AND QC = @QCid ;"
        SQLQuery.SetParam("@maxCategory09", maxCategory09)
        SQLQuery.SetParam("@StaffId", Master.PPStaffID)
        SQLQuery.SetParam("@DateC", Today)
        SQLQuery.SetParam("@pifNotes", pifNotes)
        SQLQuery.SetParam("@PifID", PIFID)
        SQLQuery.SetParam("@QCid", QC)

        raf = SetFormsRS(SQLQuery)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
One easy way: fire Profiler, and steal the actual query (that's when it's sql server, obviously)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.