p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 2.0 Basics (http://p2p.wrox.com/forumdisplay.php?f=136)
-   -   Trying to do a sql web query/filter (http://p2p.wrox.com/showthread.php?t=60287)

u2clone July 19th, 2007 02:51 PM

Trying to do a sql web query/filter
 
OK, I am new to most all of this, but have taught myself enough to get to this point and get stuck. I am using c# and accessing a SQL 2005 DB.

I have a search feature on the home page similar to what is on the Remax.com website where the user selects a few things, hits the submit button and then is taken to a new page with the matching results.

Here is what I have for the "Search" form so far
        <form id="Form2" runat="server" method="post">
            Country:<br />
                <asp:DropDownList ID="Country_Select" runat="server" DataSourceID="SqlDataSource1" DataTextField="Country" DataValueField="CountryID" Width="160px" ></asp:DropDownList>
            <br />
            State: (if Country selected is the United States)<br />
                <asp:DropDownList ID="State_Select" runat="server" DataSourceID="SqlDataSource2" DataTextField="State" DataValueField="StateID" Width="160px"></asp:DropDownList>
            <br />
            Price Range:<br />
                <asp:DropDownList ID="Price_Select" runat="server" DataSourceID="SqlDataSource3" DataTextField="PriceRange" DataValueField="PriceID" Font-Names="arial" Font-Size="9pt" Font-Bold="true" Width="160px"></asp:DropDownList>
            <br />
            <asp:Button ID="Button1" runat="server" Width="151px" OnClick="SearchTest" />

        </form>



    </div>
    </form>
    &nbsp;&nbsp;
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Database2 %>"
        SelectCommand="SELECT * FROM [Lux_Country]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Database2 %>"
        SelectCommand="SELECT * FROM [Lux_States]"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:Database2 %>"
        SelectCommand="SELECT * FROM [Lux_Price]"></asp:SqlDataSource>

How do I get the three variables to carry to the new page and what do I need to do for it to pick them up as well?

I'm lost and have tried many things to no avail, but this is what I get for teaching myself I guess. This is why I am not posting the codebehind as well. I can't find a matching example in my ASP.NET 2.0 with C# book.

Help!


planoie July 19th, 2007 03:17 PM

You could take all the desired values and build a querystring for the new page, then redirect to it. Something like this (broken into several lines to make reading easier):
Code:

strNewPage = "searchpage.aspx?country={0}&state={1}&price={2}"
strNewPage = string.Format(strNewPage, Country_Select.SelectedValue, State_Select.SelectedValue, Price_Select.SelectedValue)
Response.Redirect(strNewPage)

Then on the page load of the "searchpage.aspx" page you get those values off the querystring and perform the actual search.

-Peter

u2clone July 19th, 2007 03:34 PM

Is the SelectedValue the item from the DataValueField?

I also do already have the results page primed for a query string pull so this would work if I can track what you are saying. If the SelectedValue is the DataValueField, will it pull the word in the field or the item from the database table. I tried another way and pulled the actual word.

Oh I should also ask if this is the code I put on the OnClick event, correct?


planoie July 19th, 2007 03:42 PM

Yes, this would be the code for the button click event.

dropdownlist.SelectedValue is the list item value (in your case, yes, the bound value for the field in DataValueField)
dropdownlist.SelectedItem.Text would give you the bound value from the DataTextField field

http://msdn2.microsoft.com/en-us/lib...pdownlist.aspx

-Peter

u2clone July 19th, 2007 04:13 PM

Thanks, I'll give it a shot.

I thought the DropDownList was already bound with the SqlDataSource. After looking at the URL you posted I imagine I still have some coding to do. Looks complex, but I may be able to figure it out.

Wish me luck


u2clone July 19th, 2007 05:50 PM

Still having issues...I will get this at some point.

is strNewPage a VB or C#? I am using C#.

Also, do I need to create all that databinding code that is on that link you posted or is it much easier to do than that.

I so suck. Can't you tell I am a total newbie now.


planoie July 20th, 2007 07:55 AM

Now now, no self deprecation allowed!

Yes you are new, but you are expressing interest and trying to learn. That's what I like to see.

I posted the dropdownlist doc link just so you could see all the stuff that it has. You already have your dropdownlist populating from the database. That part is fine.

The next step is to pass the selected values to the next page which knows nothing about the datasource or DDLs from the previous page. You pass the three values to the page thru the querystring. Then (presumably) you need to query some OTHER data based on your search options.

It sounds like you have this page with the DDLs all set aside from the button click which does the redirect.

strNewPage is just a variable name. You didn't specify what language you are working in in the original post so I made it kind of psuedo-generic. I think all you need to do is this in your button click handler (in C# now that I know):
Code:

string strNewPage = "searchpage.aspx?country={0}&state={1}&price={2}";
strNewPage = string.Format(strNewPage, Country_Select.SelectedValue, State_Select.SelectedValue, Price_Select.SelectedValue);
Response.Redirect(strNewPage);

At this point you need to write the logic for the search results page you are redirecting to.

-Peter

u2clone July 20th, 2007 08:07 AM

OK, getting closer. I have this so the when clicking the button I get the proper query string in the link. The issue is that for the variables it is acutally returning the following string:

?countryid=CountryID&stateid=StateID&priceid=Price ID

The CountryID, StateID, and PriceID are supposed to be numbers that are in the database that it should be pulling from. Here is the codebehind:

public partial class forms_formtest : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Country_Select.DataSource = SqlDataSource1;
            Country_Select.DataTextField = "Country";
            Country_Select.DataValueField = "CountryID";
            Country_Select.DataBind();

        }

    }
    protected void SearchTest(object sender, EventArgs e)
    {
        string strNewPage;
        strNewPage = "~/luxlistings/default.aspx?countryid={0}&stateid={1}&priceid={2} ";
        strNewPage = string.Format(strNewPage, Country_Select.DataValueField, State_Select.DataValueField, Price_Select.DataValueField);
        Response.Redirect(strNewPage);



    }
}

I only did the Country in the DataBind as a test and it did the same thing that the others did which are run by the dropdown list code as shown in the first post.

How do I get it to pull the actual CountryID et al numbers from the database? It is pulling the Country on the Search page, why not when I execute the command. This is the only issue that I think is left.

Help. .. :(


planoie July 20th, 2007 08:35 AM

I gave you the exact code to use.

<dropdownlist>.SelectedValue returns the "selected value".

The "DataValueField" of the drop down list is the property used to tell the drop down list control what data field or property of the object specified in "DataSource" the drop down list option value should be bound to. (This is why I linked you to the Docs so you could read about the various properties you are using to understand what they do.)

Let's try again:

strNewPage = string.Format(strNewPage, Country_Select.SelectedValue, State_Select.SelectedValue, Price_Select.SelectedValue);

-Peter

u2clone July 20th, 2007 08:51 AM

Sorry. Just a challenge teaching yourself. My only real background was HTML as of mid-May. Since then I have tackled .NET, C#, and now SQL learning just enough to be dangerous. SO needless to say I got thrown into the deep end and told to swim. Most of the .NET I am getting and the SQL queries aren't all that hard, it is tying them together with .NET and C# that messes with my head.

Thanks so much for the help, I'm sure I will need more as I try to evolve this search into something much more robust and start toss in the if "something" then "some query" elseif "somthing else" then "some other query" and on and on.

It worked. I am soooooooo excited!!!! Now I can make it look pretty again.

Uh-oh, I just remembered. I put the search tool in an iframe on the home page (the whole 'you can't have more than one form run to the server') Is there a better way, because only what is in the frame will change (I didn't realize this when I set it all up). I am sure there is a simple solution.

Sorry, thought I was done for the day on issues.



All times are GMT -4. The time now is 07:01 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.