CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> .NET

 custom WHERE clause in SQL query type

Print topic Send  topic

Author Message
mhiles
Posted: 11/22/2004, 1:11 PM

Okay... I am trying to figure out how the Codecharge Studio IDE works with a SQL query type for a grid. I am using the following query....

select count(*) from webcase a, case_public_party_rel b
where a.case_type_cd = 'DEL'
and a.exp_cd is null
and b.case_id = a.case_id
and b.party_role_cd = 'D'

I am attempting to pass an additional WHERE criteria as an AND to this SQL statement where b.party_id = the party_id value of the querystring.

I am confused by the Parameters section of the SQL datasource, as it gives similar options as other datasource options (Table, etc...) to pass the URL querystring value, but I cannot seem to find in the code where it handles any of the WHERE clauses and/or parameters.

HELP!!
E43509

Posts: 283
Posted: 11/22/2004, 1:50 PM

On the data source

select *
from mytable
where mycol = {myvar}

myvar can be any name you want.
It must be enclosed in curly braces
if it is a string value put quotes outside the curly brace '{myvar}'
Now add a parameter
name it myvar
pick the type (text, ...)
add a parameter source name of the query string
set the type to url.

The easiest way to noodle this out is to create a sample datasource using a table with a where clause, then switch the datasource to sql and see how CCS creates the parameters for ya.
View profile  Send private message
mhiles

Posts: 2
Posted: 11/22/2004, 2:00 PM

Quote E43509:
On the data source

select *
from mytable
where mycol = {myvar}

myvar can be any name you want.
It must be enclosed in curly braces
if it is a string value put quotes outside the curly brace '{myvar}'
Now add a parameter
name it myvar
pick the type (text, ...)
add a parameter source name of the query string
set the type to url.

The easiest way to noodle this out is to create a sample datasource using a table with a where clause, then switch the datasource to sql and see how CCS creates the parameters for ya.


Thanks for the response! I tried that and I get a dialogue box that says, "One or more errors occurred during the processing of your request.

My SQL query now reads

select count(*) from webcase a, case_public_party_rel b
where a.case_type_cd = 'DEL'
and a.expunge_cd is null
and b.case_id = a.case_id
and b.party_role_cd = 'D'
and b.party_id = {PARTY_ID}

...with the param settings as being:

Variable Name: b.party_id
Variable Type: integer
Parameter Source: PARTY_ID
Parameter Type: URL

My boss thinks I am a numbskull for even attempting this simple search application using Codecharge. Here I cannot even pass a querystring to a custom SQL statement in the IDE to get a record count.

:-/ :-/
_________________
I bought a box of animal crackers. It said, "Do not eat if seal is broken." So, I opened the box and sure enough...
View profile  Send private message
peterr


Posts: 5971
Posted: 11/22/2004, 2:34 PM

1. You should fix the Variable Name. "PARTY_ID" is your variable that was placed in your SQL. Also, the Parameter Source should be the one that you see in the URL, so please make sure that it is indeed PARTY_ID.

2. Where and at what point do you see that dialog box?
I suspect that it may be shown even if you fix #1, but it may be irrelevant and your application may work just fine.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.