gpdeering
Posts: 57
|
| Posted: 04/19/2006, 10:43 AM |
|
I'm trying to build a dependent listbox for my VB.NET app.
I tried the "Sixto" solution, but because that was developed for PHP, it doesn't work for me. After attempting the dependent listbox procedure from the Example2 pack, I abandoned that, too, after I had errors which I could not troubleshoot.
I'm in a hurry here, so I switched to another solution: use a javascript "onchange" event on the first listbox to reload the page and pass the value in the URL. Then, the second listbox should be pre-filtered by the passed value. Sounds simple. It's even a commonly-used technique (but being a newbie, I've never tried it till now).
The page successfully reloads with the appropriate URL and first listbox set to the passed value. But that's as good as it gets.
The SQL clause for my second listbox seems to ignore this parameter and show me an unfiltered list or other undesired behavior.
Perhaps my syntax is wrong.
The first time, in the Where clause, I used the "Parameter" condition type where I set "Products = {First_Listbox}".
Then, I tried an Expression condition type with the string "(Products = appFirst_Listbox.Value)". Of course, that didn't work. There was nothing in the second listbox.
Any ideas here? This shouldn't be a difficult problem. But being pressed for time, I can't think straight anymore. Any help is greatly appreciated.
--Glen
|
 |
 |
WKempees
|
| Posted: 04/19/2006, 11:26 AM |
|
@GP : Please describe the layout of your SQL table here
@Others: its a dual lookup on 1 table
@GP
So, when the form appears and a user selects a value from the first listbox,
through the onchage your page is submitted,
Q: What is on the URL, meaning what do you put on the URL, please supply the
URL
As you are on a one table lookup
In the second drop down, Datasource press the elipses ([...]) and build your
Listbox's SQL.
pick table1 and give it an alias, resulting in: table1 as table2
In the properties of your listbox the bound value should be the primary id
of table1 (as table2)
Type Integer
Text field the appropraite textfield
The Where should be something like where filtering_id = <your url supplied
field> type URL
The whole SQL looking something like
Select id, textfield from table1 as table2 where filtering_id = {URL_id}
Walter
"gpdeering" <gpdeering@forum.codecharge> schreef in bericht
news:2444676d39e4bc@news.codecharge.com...
> I'm trying to build a dependent listbox for my VB.NET app.
>
> I tried the "Sixto" solution, but because that was developed for PHP, it
> doesn't work for me. After attempting the dependent listbox procedure
> from the
> Example2 pack, I abandoned that, too, after I had errors which I could not
> troubleshoot.
>
> I'm in a hurry here, so I switched to another solution: use a javascript
> "onchange" event on the first listbox to reload the page and pass the
> value in
> the URL. Then, the second listbox should be pre-filtered by the passed
> value.
> Sounds simple. It's even a commonly-used technique (but being a newbie,
> I've
> never tried it till now).
>
> The page successfully reloads with the appropriate URL and first listbox
> set to
> the passed value. But that's as good as it gets.
>
> The SQL clause for my second listbox seems to ignore this parameter and
> show me
> an unfiltered list or other undesired behavior.
>
> Perhaps my syntax is wrong.
>
> The first time, in the Where clause, I used the "Parameter" condition type
> where I set "Products = {First_Listbox}".
>
> Then, I tried an Expression condition type with the string "(Products =
> appFirst_Listbox.Value)". Of course, that didn't work. There was nothing
> in
> the second listbox.
>
> Any ideas here? This shouldn't be a difficult problem. But being pressed
> for
> time, I can't think straight anymore. Any help is greatly appreciated.
>
> --Glen
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/19/2006, 2:04 PM |
|
Glen,
I'm not sure if you need to use custom SQL for your 2nd listbox, as the easiest way may be to just use standard listbox configuration and then configure the WHERE section of the "Data Source" property dialog. You would confgure the WHERE paramater like this:
Field Name: Products
Field Type: Integer
Conditions: =
Parameter: First_Listbox
Paramater Type: URL
When using custom SQL (WHERE Products = {First_Listbox}) you'd need to configure the parameter like this:
Variable Name: First_Listbox
Field Type: Integer
Parameter: First_Listbox
Paramater Type: URL
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gpdeering
Posts: 57
|
| Posted: 04/19/2006, 2:14 PM |
|
Thanks, Walter. (Yep, it's me again.)
As mentioned in another post very recently, I'm using an MS Access table. Rather than generalize (because it will take longer to do so), I will include the actual names of tables, fields, and controls. Unfortunately, my application is on a intranet behind a company firewall, so you'll just have to visualize.
There is a flat Access table, tblSpaceDiffLookup, with several fields: DiffLookupID (primary key), ToolID (text field), PPID (text field), measurementType, parameterType, runType, SpaceParameter, USL (double), Target (double), LSL (double), and toolGroup (text).
I am using ToolID as a "category" to filter the PPID field down to smaller set. So, the values in ToolID are not necessarily unique. PPID, when selected will, in turn, filter down the Space Parameter list. But my application page has only two listboxes: ProcessToolID and ProcessPPID. I need to use the filtered SpaceParameter list differently.
On my page, dataInput.aspx, the two listboxes in question are at the top. The first one, ProcessToolID, uses the ToolID field as its Data Source. Since I want only unique ToolID values in the dropdown list, I set the Bound Column to ToolID as well as the Text Column. (VB.NET apparently does not recognize the property "Unique" as I got errors when I tried to compile the first time. Someone told me that .NET automatically shows unique values, but if my listbox is bound to the record id, then the control is forced to show every record. Hence, both Bound and Text Column are set to ToolID.)
The SQL statement (from the Visual Query Builder) for ProcessToolID is:
SELECT ToolID
FROM tblSpaceDiffLookup
ORDER BY ToolID
The second listbox, ProcessPPID, uses PPID as its Data Source. Here again, I want only unique values, so (since I can't use the Unique property) I set Bound and Text Columns to PPID. And it's SQL statement is:
SELECT *
FROM tblSpaceDiffLookup
WHERE ToolID = '{ProcessToolID}'
ORDER BY PPID
This WHERE clause lists all the unique PPID values in the table.
I will look into your recommendations and let you know what happens. Thanks.
--Glen
|
|
|
|
WKempees
|
| Posted: 04/19/2006, 2:30 PM |
|
> Thanks, Walter. (Yep, it's me again.)
I know.
I have to re-read to grasp the complete picture, and also await your further
progress.
But a few thing take my attention : (be aware I don't do VB.NET as I stated
before)
Quote :
> As mentioned in another post very recently, I'm using an MS Access table.
> Rather than generalize (because it will take longer to do so), I will
> include
> the actual names of tables, fields, and controls. Unfortunately, my
> application is on a intranet behind a company firewall, so you'll just
> have to
> visualize.
>
> There is a flat Access table, tblSpaceDiffLookup, with several fields:
> DiffLookupID (primary key), ToolID (text field), PPID (text field),
> measurementType, parameterType, runType, SpaceParameter, USL (double),
> Target
> (double), LSL (double), and toolGroup (text).
ToolID is a text field, is it also containing text as opposed to integers?
It's not a real problem but still I'd like to know.
Quote :
>
> I am using ToolID as a "category" to filter the PPID field down to smaller
> set.
> So, the values in ToolID are not necessarily unique. PPID, when selected
> will,
> in turn, filter down the Space Parameter list. But my application page
> has only
> two listboxes: ProcessToolID and ProcessPPID. I need to use the filtered
> SpaceParameter list differently.
>
> On my page, dataInput.aspx, the two listboxes in question are at the top.
> The
> first one, ProcessToolID, uses the ToolID field as its Data Source. Since
> I
> want only unique ToolID values in the dropdown list, I set the Bound
> Column to
> ToolID as well as the Text Column. (VB.NET apparently does not recognize
> the
> property "Unique" as I got errors when I tried to compile the first time.
> Someone told me that .NET automatically shows unique values, but if my
> listbox
> is bound to the record id, then the control is forced to show every
> record.
> Hence, both Bound and Text Column are set to ToolID.)
>
> The SQL statement (from the Visual Query Builder) for ProcessToolID is:
> SELECT ToolID
> FROM tblSpaceDiffLookup
> ORDER BY ToolID
How do you like this one, for getting unique values:
SELECT DISTINCT( ToolID )
FROM tblSpaceDiffLookup
ORDER BY ToolID
I'll await your further postings.
Walter
|
|
|
|
WKempees
|
| Posted: 04/19/2006, 2:31 PM |
|
Glen,
PM me your e-mail adress.
Walter
|
|
|
|
gpdeering
Posts: 57
|
| Posted: 04/19/2006, 2:44 PM |
|
I can't explain it, but it's working now. After days of frustration, it's working. The second listbox is now being filtered by the passed value from the first listbox after the page reloads.
I'm not sure what's different. The SQL Where clause is exactly as I reported above. I tried other things before going back to this configuration. Perhaps the stars and planets are now aligned. It works as I expect it to.
Well, since I laid out my application plan earlier, can I ask another question?
The second listbox, which only shows unique values, will actually have multiple records where ToolID and PPID match. But a third parameter, measurementType will be used to make visible or not visible a specific panel (with controls) depending upon its value.
From my second listbox SQL query, I have pulled in these fields as well as others. I intend to somehow dump them to an invisible data grid where I can use them to turn on/off certain panels. And this grid will have other data, too, that needs to be used.
The question is: what's the best way to access that SQL query data? I'm thinking that an "onchange" event is the only way to do it.
Thanks so much,
--Glen
|
|
|
|
WKempees
|
| Posted: 04/19/2006, 3:29 PM |
|
I just read you solved it , but so did I4U:
On your page:
First Listbox, Unique Category Listing based on unique ToolID
Name: ProcessToolID
Caption: ToolID
.....
Data Source: tblSpaceDiffLookup
Bound Column: ToolID
Text Column : ToolID
Data Type: Text
After entering this click the Data Source, press [...] and in VQB click on
SELECT, in the right lower pane
Column/Expression Alias
Distinct(ToolID) ToolID_lkp
ToolID ToolID
press OK and back in the Listbox properties change
Bound Column: ToolID_lkp
Text Column : ToolID
Set the listbox OnChange to Submit Form.
Test your page, listbox should be there and only display unique ToolID's
(mine does)
Don't worry about Dependancy yet, but checkout the URL
......?ProcessToolID=ToolID2 (of course mine contains several ToolID1,
ToolID2 and ToolID3 's)
Next second Listbox, Show all PPID's within the choosen ToolID
Name: ProcessToolID
Caption: ToolID
.....
Data Source: tblSpaceDiffLookup press [...] go into VQB and click on SELECT,
in the right lower pane
Column/Expression Alias
Distinct(ToolID) PPID_lkp
ToolID PPID
click on WHERE,a in the right lower pane press +
Condition Type: Parameter
Field: ToolID equals(=) ProcessToolID
Type Text URL
press OK and back in the Listbox properties set
Bound Column: PPID_lkp
Text Column : PPID
Set the listbox OnChange to Submit Form.
et voilá
Now all you need to do is to have your Grid react to these set Listboxes
So in the Datasource of the Grid change the where to include
Condition Type: Parameter
Field: ToolID equals(=) ProcessToolID
Type Text URL
Or any combination you deem fit.
And yes maybe the start did align, murphy entered the room or telling it to
the cat solved it.
Meanwhile I just build it, including recreating your tables, filling them
with dummy data and got it.
Three cheers for CCS enabling us to do prototyping in a few minutes while
creating live applications in the process.
Your next question takes another reading.
Walter, my pleasure as you all bring out the beast in me (The Weasel)
Walter
|
|
|
|
WKempees
|
| Posted: 04/19/2006, 3:37 PM |
|
As I understand from the text in your previous post you are doing this on a
RecordForm.
Just as info I did it in a searchform with a resulting grid, showing noting
when no listbox values selected and showing only appropriate records when
values are selected.
Now you are on a recordform and your next problem is going to be Insert
mode, not the color representation cause that is easy enough.
In Insert mode you are submitting the form many times to get the listboxes
selected, your form is going to try and insert data into the database and
probably generating lots of errors on required fields.
This you can cater for by setting stopping the Insert from happening while
certain conditions are met.
On the Panels question, set them all to invisible
In the BeforeShow of the Page, passing through it everytime you submit,
check your DB values and set panels to visible based on those values.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/19/2006, 3:52 PM |
|
To stop the Insert see CCS Help file and navigate to: Examples and Techniques -> Programming -> Customizing the Data Source -> Prevent Operation for Individual Records via Editable Grid.
And here is the online version: http://docs.codecharge.com/studio3/html/ProgrammingTech...DOperation.html
Just change the condition and where it says Editable Grid read Form.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/20/2006, 2:22 AM |
|
In a private sessions gpdeering wrote:
Quote :
Walter,
Yes, three cheers for CCS! [..................]
Quote :
I just thought it to be a statement to be published here.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
gpdeering
Posts: 57
|
| Posted: 04/20/2006, 5:37 AM |
|
Walter,
As you now have a copy of my application, you'll see that I'm not submitting the form upon the listbox select action. I am only reloading the page via an "onchange" javascript action, but each time with a passed value in the URL to pre-filter the listbox.
After the page reloads from the second listbox, the new grid (which I just set up yesterday), will hold the results of those filters. (This grid, of course, will be on an invisible panel.)
The problem right now is that the grid wants data when the page loads for the first time. So, I get an error. I have to suppress data population of the grid until the URL passes values for both listboxes on the record form.
--Glen
|
|
|
|
gpdeering
Posts: 57
|
| Posted: 04/20/2006, 5:45 AM |
|
Never mind. It was very late last night when I was working on it. (I've been working on this application for the last three nights trying to get it done. I'm not sure what I saw anymore.)
Actually, now the grid (which will be invisible) on the page just pulls in the entire table, as it should. Each page reload from the onchange event at the listboxes should filter it down.
I'll keep you updated.
--Glen
|
|
|
|
WKempees
|
| Posted: 04/20/2006, 10:28 AM |
|
The Grid's SQL will have a WHERE clause that (at least) contains:
WHERE ToolID = {ProcessToolID} and PPID = {ProcessPIID}
if configured through the VQB they will both be URL Parameters
In the VQB you can optionally set the default values, if you set them to
"impossible values" your grid will show up initially empty.
That's how we do this.
Walter
"gpdeering" <gpdeering@forum.codecharge> schreef in bericht
news:244478253ec747@news.codecharge.com...
> Never mind. It was very late last night when I was working on it. (I've
> been
> working on this application for the last three nights trying to get it
> done.
> I'm not sure what I saw anymore.)
>
> Actually, now the grid (which will be invisible) on the page just pulls in
> the
> entire table, as it should. Each page reload from the onchange event at
> the
> listboxes should filter it down.
>
> I'll keep you updated.
>
> --Glen
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
gpdeering
Posts: 57
|
| Posted: 04/20/2006, 2:51 PM |
|
Thanks for the new tip, Walter. The "impossible value" makes my page load faster.
--Glen
|
|
|
|
|
