CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 Using a parameter input in an expression

Print topic Send  topic

Author Message
longleyr

Posts: 6
Posted: 12/15/2014, 6:21 PM

I have a complex query that is part parameter and part expression. I need to use the date1 parameter from the parameter portion of the query as part of the expression. In SQL view it looks like '(date1)' in two places but when it executes the '(date1)' that is part of the expression is read as a string and not the value of date1 that is in the parameter created portion of the SQL statement. How do I get the date1 value to be read as part of the expression?
View profile  Send private message
eratech


Posts: 513
Posted: 12/16/2014, 10:20 PM

Make sure it's in it's curly braces.

For example I use somethng like this all the time, which uses the parameter in the expression and the result:

WHERE organisations.id = (case when {organisation_id}=0 then organisations.id else {organisation_id} end)

Or have I mis-understood your situation? If so, please provide some example code.

E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
longleyr

Posts: 6
Posted: 12/17/2014, 7:08 PM

It still doesn't seem to recognize it. I tried {date1}, date1, and '{date1}'
I added a debugging script and it doesn't see to recognize it as value, it just thinks it is a string.

The only difference is that I get a syntax error with {date1} and I get a conversion error with '{date1}'
Either way I'm still stuck
View profile  Send private message
eratech


Posts: 513
Posted: 12/17/2014, 10:43 PM

Can you paste in some of your SQL and I'll give it a try?

Also - are you using MS SQL or MySQL or another DB? That might affect it.

In thing to consider - I have had success with complex queries by making a view first that does the hard stuff and selecting from that. If you are using CodeCharge Studio you can also get data from a stored procedure. Just a suggestion.

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
longleyr

Posts: 6
Posted: 12/18/2014, 3:52 AM

this is SQL Server

Here's the relevant portion of the SQL which if I plug in values works fine in SQL server directly. Note that I'm using the date parameter twice because I have to check against two different conditions so a view is out of the question.

WHERE (change_log.log_date='{date1}' and (infochange=1)) and (cases.caseid NOT IN (select caseid from change_log-where log_date<='{date1}' and info change<>1))


So basically I'm checking that one condition exists in the time specified and that another condition doesn't exist..

In the generated code the first '{date1}' is a parameter within the application and the second is part of an expression that I created manually. In the generated output, the first '{date1}' is replaced with the date from the form input, and the second one still says '{date1}'
View profile  Send private message
eratech


Posts: 513
Posted: 12/18/2014, 8:16 PM

That is really odd - as you can see from my example, I use duplicates (and sometimes triplicate) versions of parameters in my SQL without problems.

The {date1} should be replaced by the code before it generates it so all should be good.

One suggestion to try - create a {date2} but give it the same source as {date1} and use it in the second spot.

Have you also tried setting the 'debug' flag to output all the SQL run on a page, just to confirm?

I can't recall the ASP file name but it's probably in the Common files called 'db_mssql.asp' or similar (PHP is db_mysqli.php for my MySQL project).

Edit and change value of '$Debug' from 0 to 1, then publish, and all the queries will be printed before the page. Obviously, change it back when you put it live.

E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
longleyr

Posts: 6
Posted: 12/19/2014, 3:28 AM

I didn't set to debug but I did add a response.write to output the SQL which does the same thing. I've tried different names for the value and this is just one of two different pages with different named values that I get the same results.
View profile  Send private message
eratech


Posts: 513
Posted: 12/21/2014, 8:00 PM

Just wondering - are you using the "Query type" (aka "Data Source Type") of 'Table' or 'SQL'?

I know these sort of things work in 'SQL' mode, but sometimes I have added WHERE criteria in "Table" mode that make CCS cry a little. I've switched it to 'SQL' and had more luck.

Guessing a little here.

E


_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
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.