Modify SQL Query in Crystal Reports 5.0?

[Jeff Byrne]

Hello:

I have Crystal Reports Professional 5.0. I'm using the API to preview
and print reports. I would like to change the SQL Query statement at
run time. According to the documentation this should be no problem...
except I can't get it to work. I can read in the existing Query just
fine. I add some additional lines to the WHERE clause and user
PESetSQLQuery to update the query. PESetSQLQuery returns success (1),
the new query is not being used. I am not modifying the select list
in any way. Below is a snippet of code:

Private Declare Function PEGetSQLQuery Lib "crpe32.dll" (ByVal
printJob%, TextHandle As Long, TextLength%) As Integer

Private Declare Function PESetSQLQuery Lib "crpe32.dll" (ByVal
printJob%, ByVal QueryString$) As Integer

Private Declare Function PEGetHandleString Lib "crpe32.dll" (ByVal
TextHandle As Long, ByVal Buffer$, ByVal BufferLength%) As Integer

--------------------------------------------------------------------------------------------------------------

' Get the SQL Query String Handle
iResult = PEGetSQLQuery(iNewPrintJob, lStringHandle, iStringLen)
If iResult = 0 Then ' Error - Cannot Get SQL Query
<Error handling code goes here>
End If

' Get the SQL String from the handle
sSQL = String(iStringLen, vbNullChar) ' Initialize
iResult = PEGetHandleString(lStringHandle, sSQL, iStringLen)
If iResult = 0 Then ' Error - Cannot Get SQL Query
<Error handling code goes here>
End If
MsgBox sSQL

sSQL = sSQL & " and (User_Client.Client# = " & g_lActiveClientId & ")
"
sSQL = sSQL & " Order By Users.LName"
Debug.Print sSQL
' Write saved string to report
iResult = PESetSQLQuery(iNewPrintJob, sSQL)
MsgBox iResult

As you can see this is really straight forward: Read existing query,
add something to the WHERE clause, send the new query to the report
engine.

The report prints as if no changes were made. Furthermore, if I read
in the query again (after PESetSQLQuery) I get the *original* query.
I don't know if I should be getting that or the new one. Something is
getting sent since If I send a garbage string to PESetSQLQuery it
locks up the application.

I can make these modifications to the SQL Query in the Report Designer
and they work correctly.

So.... my question is: Has anyone got this to work? My code
produces no errors. It acts like it completely ignores the modified
query I'm sending it. Am I doing something wrong?

- Jeff Byrne
jeff....@infores.com
jby...@ais.net

[Jeff Byrne]

On Thu, 21 Nov 1996 18:09:24 GMT, jby...@ais.net (Jeff Byrne) wrote:

Hello Again:

I have a fix, but I don't like it and I'm not sure why it works. If I
copy the string to a textbox and back - everything works like it
should. Here is a snippet:

>
>' Get the SQL Query String Handle
>iResult = PEGetSQLQuery(iNewPrintJob, lStringHandle, iStringLen)
>If iResult = 0 Then ' Error - Cannot Get SQL Query
> <Error handling code goes here>
>End If
>
>' Get the SQL String from the handle
>sSQL = String(iStringLen, vbNullChar) ' Initialize
>iResult = PEGetHandleString(lStringHandle, sSQL, iStringLen)
>If iResult = 0 Then ' Error - Cannot Get SQL Query
> <Error handling code goes here>
>End If
>MsgBox sSQL

' If I add this here it works!?
Text1.Text = sSQL
sSQL = Text1.Text

>
>sSQL = sSQL & " and (User_Client.Client# = " & g_lActiveClientId & ")
>"
>sSQL = sSQL & " Order By Users.LName"
>Debug.Print sSQL
>' Write saved string to report
>iResult = PESetSQLQuery(iNewPrintJob, sSQL)
>MsgBox iResult
>

I tried different things. I copied from one string to another and
that was not enough. However, copying to a textbox and back does the
trick? The DLL call takes a pointer to a string. The variable is
declared as BY VAL in the declaration. The only thing I can figure is
that a VB string is not passing correctly. Sending it through a
textbox must do something to it.

Does anyone have any ideas what is going on?

[Jeff Byrne]

Howdy:

I have solved my problem and understand what was happening. The
string that was returned from the Crystal Reports DLL had a NULL
character attached to the end. That is why when I appended to my
query I got the same results. (Crystal stopped reading as soon as it
found the NULL (ie. the original string))

I tracked down the problem by comparing the string length before and
after putting it in a textbox to see if there were any differences.
Sure enough, one byte less after sending to the textbox.

So this was easy to fix. Just chop off the last character after I get
the string from the DLL.

Thanks to anyone who was thinking about my problem.

- Jeff Byrne
jeff....@infores.com
jby...@ais.net