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
|
|
|
|