Database query (Full Version)

All Forums >> [Web Development] >> Expression Web Help



Message

Trandel -> Database query (3/26/2008 10:27:25)

Hi,

I have got a dropdown list working for a Gridview with an Access database. The dropdown list is populated from the same table as the gridview.

Now I want to do:

1. Use a dropdown list populated from a master table of towns to find records in the detail table.

2. I also need to be able to look for the dropdown list argument in either one of two fields in the detail record.

i.e. The master table has details of all suburbs and towns. The detail record has two fields for them but sometimes the town is placed in the suburb field. (the Zip Code identifies the suburb)

From what I can see the dropdown list has to be on the same field as the the gridview?

I have tried the dropdown list with its datasource pointing to the master table and the gridview pointing to the detail table but get no response. As far as two items in the select clause go there does not seem to be an OR facility available.

Any ideas extremely welcome as I am going around in circles.

Regards

Tony Randell



rdouglass -> RE: Database query (3/28/2008 9:05:37)

quote:

and the gridview pointing to the detail table but get no response.


I don't use EW but I do use VS and that uses the Gridviews and such so things should be similar.

Is your Gridview datasource using the Dropdown as a query parameter? The code should look something like this. (This is exactly as on one of my live pages):

<asp:SqlDataSource ID="ds_ServiceTypes" runat="server" ConnectionString="<%$ ConnectionStrings:SF %>"
SelectCommand="SELECT [ServiceTypeID], [ServiceType] FROM [tlkp_ServiceTypes] WHERE ([ServiceTypeTypeID] = @ServiceTypeTypeID) ORDER BY [ServiceType]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" DefaultValue="0" Name="ServiceTypeTypeID"
PropertyName="SelectedValue" Type="Int64" />
</SelectParameters>
</asp:SqlDataSource>

See the bold line? That's the one that says 'use the dropdown'. Does yours look any thing like that?



Trandel -> RE: Database query (3/29/2008 1:13:08)

Thanks for your reply.

I have got the dropdown to work now. It looks up a master table and selects detail records from their own table. The code you referred to in my app is:

<asp:AccessDataSource runat="server" ID="AccessDataSource2" DataFile="App_Data/saqcc.mdb" SelectCommand="SELECT [Initials], [Surname], [Employer], [Suburb], [SubDesc], [Town] FROM [AP-Details] WHERE ([Suburb] = ?)">
<SelectParameters>
<asp:controlparameter PropertyName="SelectedValue" Type="String" Name="Suburb" ControlID="DropDownList1" />
</SelectParameters>
</asp:AccessDataSource>


What I would like to be able to do now is to search the town column as well as the suburb col with the same value from the dropdownlist?




rdouglass -> RE: Database query (3/30/2008 8:18:13)

quote:

What I would like to be able to do now is to search the town column as well as the suburb col with the same value from the dropdownlist?


I don't think I'm clear. Are you saying you want that data source to search using 2 different dropdowns as the criteria items?



Trandel -> RE: Database query (3/30/2008 14:18:53)

Hi,

I want to search two diffferent columns from the single dropdown argument. e.g. look for New York in both suburb and town.

Regards

T



rdouglass -> RE: Database query (3/30/2008 14:58:36)

quote:

<asp:AccessDataSource runat="server" ID="AccessDataSource2" DataFile="App_Data/saqcc.mdb" SelectCommand="SELECT [Initials], [Surname], [Employer], [Suburb], [SubDesc], [Town] FROM [AP-Details] WHERE ([Suburb] = ?)">
<SelectParameters>
<asp:controlparameter PropertyName="SelectedValue" Type="String" Name="Suburb" ControlID="DropDownList1" />
</SelectParameters>
</asp:AccessDataSource>


Something like this maybe?

<asp:AccessDataSource runat="server" ID="AccessDataSource2" DataFile="App_Data/saqcc.mdb" SelectCommand="SELECT [Initials], [Surname], [Employer], [Suburb], [SubDesc], [Town] FROM [AP-Details] WHERE ([Suburb] = @Suburb) OR ([Town] = @Suburb)">
<SelectParameters>
<asp:controlparameter PropertyName="SelectedValue" Type="String" Name="Suburb" ControlID="DropDownList1" />
</SelectParameters>
</asp:AccessDataSource>

See what I did? Since we're using the same dropdown, I really didn't need to define a second select parameter but rather just reused it in the query.

Does that help any?



Trandel -> RE: Database query (3/31/2008 1:45:54)


Hi

Thanks a ton - it works just great. I didnt realise that I could change the ? to an @name.

Regards

T



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375