I am designing a web site I have two drop down lists make and model the user chooses to selections i.e make and model clicks the button and it shows what the user has selected in a gridview i.e Ford Fiesta but now I want to extend this so I will have another
4 drop down lists not bound to the database 1 for colour 1 for no of doors etc
How do I search the database for make and model plus what the user selects in the other drop downs, the other drop downs are not required so if the user selects a make and model and colour but not a number of doors i want it to show the make model and colour
that the user has selected
I'm not sure I totally understand but I'll take a stab at it.
Your idea seems fine, just add more to the sql query -- where [doors] = (value) AND where [color] = (anothervalue) AND ...etc.
Couple ways I see to do this, do a click event and have it dynamicly build the query ...something like
dim querystring as string
querystring = "Select stuff FROM Table WHERE [required data] "
if dropdownX != nothing then querystring += "AND WHERE [color] = (somevalue)"
if dropdownY != nothing then querystring += "AND WHERE [doors] = (anothervalue)"
you could also use an autopostback and call a sub to build it on the fly instead of using a click event -- but its however you want to do it [:)]
if ddlColor.selectedValue=1 andAlso ddlDoors.selectedValue = 1 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue &
"' and doors='" & ddlDoors.SelectedValue &
"' "
Elseif ddlColor.selectedValue=1 andAlso ddlDoors.selectedValue = 0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue &
"'"
Elseif ddlColor.selectedValue=0 andAlso ddlDoors.selectedValue = 1 Then' 0 meaning none, the user didnt choose anything
optionalCmd= "and doors='" & ddlDoors.SelectedValue &
"' "
End if
SqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue &
"'" and make='" & DropDownList1.SelectedValue &
"' " & optionalCmd
if ddlColor.selectedValue<>0 andAlso ddlDoors.selectedValue <>0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue &
"' and doors='" & ddlDoors.SelectedValue &
"' "
Elseif ddlColor.selectedValue<>0 andAlso ddlDoors.selectedValue = 0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue &
"'"
Elseif ddlColor.selectedValue=0 andAlso ddlDoors.selectedValue <>0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= "and doors='" & ddlDoors.SelectedValue &
"' "
End if
SqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue &
"'" and make='" & DropDownList1.SelectedValue &
"' " & optionalCmd
This is the code I have behind my page now basically saying if the colour = 0 then return nothing else if anything else i.e 1 or 2 return the selected value.
Protected
Sub button1_Click(ByVal sender
As Object,
ByVal e As System.EventArgs)
Handles Button1.Click
Dim ddlcol
As String =
""
If ddlColor.SelectedValue =
"0" Then
ddlcol =
"and colour='"
ElseIf ddlColor.SelectedValue <> 0
Then
ddlcol =
" and color = '" & ddlColor.SelectedValue
End If
SqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue &
"'" & ddlcol
SqlDataSource3.DataBind()
End Sub
I now get this error
Server Error in '/WebSite1' Application.
Unclosed quotation mark after the character string ''.
Incorrect syntax near ''.
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Unclosed quotation mark after the character string ''.
Incorrect syntax near ''.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
How does it associated or translate the number to a colour stored in the database ?
Kevin.Illing...
Member
100 Points
75 Posts
Web form
Apr 05, 2007 12:29 PM|LINK
I am designing a web site I have two drop down lists make and model the user chooses to selections i.e make and model clicks the button and it shows what the user has selected in a gridview i.e Ford Fiesta but now I want to extend this so I will have another 4 drop down lists not bound to the database 1 for colour 1 for no of doors etc
How do I search the database for make and model plus what the user selects in the other drop downs, the other drop downs are not required so if the user selects a make and model and colour but not a number of doors i want it to show the make model and colour that the user has selected
here is my code that i am using now
<%
@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %><!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><
html xmlns="http://www.w3.org/1999/xhtml" ><
head runat="server"> <title>Untitled Page</title></
head><
body> <form id="form1" runat="server"> <div> <br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="make" DataValueField="make"> </asp:DropDownList><br /> <br /> <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2" DataTextField="model" DataValueField="model"> </asp:DropDownList><br /> <br /> <asp:DropDownList ID="DropDownList3" runat="server"> <asp:ListItem Selected="True">Please select</asp:ListItem> <asp:ListItem>Blue</asp:ListItem> <asp:ListItem>Red</asp:ListItem> </asp:DropDownList><br /> <br /> <asp:Button ID="Button1" runat="server" Text="Button"/> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [make] FROM [used]"></asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT [model], [make] FROM [used] WHERE ([make] = @make)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="make" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" EnableViewState="true"> </asp:SqlDataSource> <br /> </div> <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource3"> </asp:GridView> </form></
body></
html>And this is the code behind
Partial
Class Default2 Inherits System.Web.UI.Page
Protected Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ClickSqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue & "'"SqlDataSource3.DataBind()
End SubEnd
ClassFubarian
Member
220 Points
124 Posts
Re: Web form
Apr 05, 2007 12:57 PM|LINK
I'm not sure I totally understand but I'll take a stab at it.
Your idea seems fine, just add more to the sql query -- where [doors] = (value) AND where [color] = (anothervalue) AND ...etc.
Couple ways I see to do this, do a click event and have it dynamicly build the query ...something like
dim querystring as string
querystring = "Select stuff FROM Table WHERE [required data] "
if dropdownX != nothing then querystring += "AND WHERE [color] = (somevalue)"
if dropdownY != nothing then querystring += "AND WHERE [doors] = (anothervalue)"
you could also use an autopostback and call a sub to build it on the fly instead of using a click event -- but its however you want to do it [:)]
Kevin.Illing...
Member
100 Points
75 Posts
Re: Web form
Apr 05, 2007 03:20 PM|LINK
Fubarian
Member
220 Points
124 Posts
Re: Web form
Apr 05, 2007 03:23 PM|LINK
Kevin.Illing...
Member
100 Points
75 Posts
Re: Web form
Apr 10, 2007 08:23 AM|LINK
JRICE
Contributor
2180 Points
672 Posts
Re: Web form
Apr 10, 2007 10:25 AM|LINK
dim optionalCmd as string=""
if ddlColor.selectedValue=1 andAlso ddlDoors.selectedValue = 1 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue & "' and doors='" & ddlDoors.SelectedValue & "' "
Elseif ddlColor.selectedValue=1 andAlso ddlDoors.selectedValue = 0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue & "'"
Elseif ddlColor.selectedValue=0 andAlso ddlDoors.selectedValue = 1 Then' 0 meaning none, the user didnt choose anything
optionalCmd= "and doors='" & ddlDoors.SelectedValue & "' "
End if
SqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue & "'" and make='" & DropDownList1.SelectedValue & "' " & optionalCmd
HTH
JRICE
Contributor
2180 Points
672 Posts
Re: Web form
Apr 10, 2007 10:26 AM|LINK
dim optionalCmd as string=""
if ddlColor.selectedValue<>0 andAlso ddlDoors.selectedValue <>0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue & "' and doors='" & ddlDoors.SelectedValue & "' "
Elseif ddlColor.selectedValue<>0 andAlso ddlDoors.selectedValue = 0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= " and color = '" & ddlColor.SelectedValue & "'"
Elseif ddlColor.selectedValue=0 andAlso ddlDoors.selectedValue <>0 Then' 0 meaning none, the user didnt choose anything
optionalCmd= "and doors='" & ddlDoors.SelectedValue & "' "
End if
SqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue & "'" and make='" & DropDownList1.SelectedValue & "' " & optionalCmd
HTH
Kevin.Illing...
Member
100 Points
75 Posts
Re: Web form
Apr 10, 2007 02:37 PM|LINK
This is the code I have behind my page now basically saying if the colour = 0 then return nothing else if anything else i.e 1 or 2 return the selected value.
Protected
Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim ddlcol As String = "" If ddlColor.SelectedValue = "0" Thenddlcol =
"and colour='" ElseIf ddlColor.SelectedValue <> 0 Thenddlcol =
" and color = '" & ddlColor.SelectedValue End IfSqlDataSource3.SelectCommand =
"select * from [used] where model = '" & DropDownList2.SelectedValue & "'" & ddlcolSqlDataSource3.DataBind()
End SubI now get this error
Server Error in '/WebSite1' Application.
Unclosed quotation mark after the character string ''.
Incorrect syntax near ''.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Unclosed quotation mark after the character string ''.
Incorrect syntax near ''.
Source Error:
How does it associated or translate the number to a colour stored in the database ?
JRICE
Contributor
2180 Points
672 Posts
Re: Web form
Apr 10, 2007 06:32 PM|LINK
You shouldn't write here anything, coz ur command will search for an empty color, so it wont get anything, set color is comming after "and"
ElseIf ddlColor.SelectedValue <> 0 Then
ddlcol =" and color = '" & ddlColor.SelectedValue
End If
HTH
Please don't hesitate for further questions
JRICE
Contributor
2180 Points
672 Posts
Re: Web form
Apr 10, 2007 06:34 PM|LINK
You shouldn't write here anything, coz ur command will search for an empty color, so it wont get anything, set color is comming after "and"
ElseIf ddlColor.SelectedValue <> 0 Then
ddlcol =" and color = '" & ddlColor.SelectedValue
End If
HTH
Please don't hesitate for further questions