CodeCharge Studio
search Register Login  

Visual Web Reporting

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

YesSoftware Forums -> Archive -> GotoCode Archive

 Search Checkbox always puts "=null" in query

Print topic Send  topic

Author Message
John Kallies
Posted: 11/17/2003, 1:29 PM

-- Using CC 2.2.2.40, Win2K Pro, IIS 5, SQL 2K SP3a,
-- generating ASP 3.0 w/ Templates.

The table in question:
CREATE TABLE [dbo].[DBMS_SYSTEM] (
[SYSTEM_NUM] [int] IDENTITY (1, 1) NOT NULL ,
[SYSTEM_NAM] [nvarchar] (50) NOT NULL ,
[IS_CLUSTR_FLG] [bit] NOT NULL ,
[IP_ADDR_TXT] [varchar] (15) NOT NULL
GO

I've got a Search record where I'm trying allow the user to view records based on the true/false values in IS_CLUSTR_FLG (in the DBMS, the values are 0=false,1=true, if that needs clarification).

No matter what I try, the query that gets generated (viewed via a SQL Profiler trace) is:

SELECT
SYSTEM_NUM,
SYSTEM_NAM,
IS_CLUSTR_FLG,
IP_ADDR_TXT
FROM DBMS_SYSTEM
WHERE IS_CLUSTR_FLG = Null
ORDER BY SYSTEM_NAM

(SQL formatting applied by me after the fact)

I need the query's WHERE clause to be either

WHERE IS_CLUSTR_FLG = 0

...or...

WHERE IS_CLUSTR_FLG = 1

In my Data Source window, the "Where" parameterization is
"AND IS_CLUSTR_FLG = {s_IS_CLUSTR_FLG}" (not a LIKE, and using equality)

I understand the limited implementation of an HTML checkbox (can't represent checked, unchecked, and not modified), and that unmodified checkboxes don't pass a value through the form. So I tried using a ListBox with a ListOfValues (0;No;1;Yes) as a workaround, but it still passes an "= Null" to the query.

I saw a previous thread started by "alasso" on 10/8/2003 1:28:27 PM. It doesn't seem to be the same problem, as I'm trying to build my WHERE clause, and alasso was trying to persist the control's setting. I'm no stranger to ASP, and have a strong competency in SQL Server. I haven't had time to learn PHP yet, so I can not evaluate the PHP solution offered in alasso's thread. As it stands, I can't see how to fix this.

I read Nicole's post on 4/20/2002 3:23:43 AM. I think she is close to my question. But there's got to be an out-of-the-box way to do this, as boolean data types are not uncommon. I would really prefer to not modify the generated code, as I may re-platform to .NET later.

Any help? Is this a bug? If it were, I think I would see more feedback about it. I'm an intermediate CC user, so I'm sure I'm missing something simple. As you can tell, I've done gone as far as I can on my own, and (hopefully) provided enough info to answer my question. Thanks in advance for your response.

Regards,
John
ryan
Posted: 11/17/2003, 3:38 PM

Hi,

I tried solving your issue. Basically I have records in table

ID====================Bln=================Record
1=====================False===============Ryan
2=====================True================Charles
3=====================False===============Kingfin

I have a search form, s_Record as textbox, and s_Bln as checkbox (no custom coding)

And the grid form parameters I used are

Bln={s_Bln} And
Record like '%{s_Record}%'

Bln is of Boolean Data type and I modified that parameter by adding 0 on the radio button 'Use default value if parameter is Empty'

I hope that helps :(
John Kallies
Posted: 11/18/2003, 12:12 PM

I started hard-coding the HTML template with a work around that is only appropriate for a boolean value (the HTML template will be the same regardless of what platform I gen to):

<select name="{s_IS_CLUSTR_FLG_Name}" class="BlueNoteSelect">
<option selected>Select Value</option>
<OPTION VALUE="0">No</OPTION> -- <OPTION> added by me
<OPTION VALUE="1">Yes</OPTION> -- <OPTION> added by me
<!--{s_IS_CLUSTR_FLG_Options}--> -- comment tags added by me
</select>

I HTML commented out the generated options added by CC, attemping to bypass the empty <OPTION> tags generated by CC. After I tweaked the HTML template, CC began generating correct HTML -- inside my newly-placed comments (?). It's like it found a discrepancy somewhere in it's metadata, and resolved some previously-unresolved data point.

So I've got the general gist of it working. I'd like to know series of events caused CC to resolve whatever was in error. Repeatedly solving this issue may reveal a clue.

Thanks for looking into it...

jk

   


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

MS Access to Web

Convert MS Access to Web.
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.