5 Replies - 659 Views - Last Post: 01 August 2017 - 01:44 PM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

How to adv search?

Posted 01 August 2017 - 10:55 AM

How to adv search?

I am using VB and sql

user first has to pick color, shape and height from dropdown. Note that user can mix and match.
for example. user can pick color and leave shape empty.

for ex here are some user inputs:

Dim color as String = ""
Dim shape as String = "box"
Dim heigh as String = "2 feet"


or

Dim color as String = "red"
Dim shape as String = ""
Dim heigh as String = ""



Than I user sql query to search from database.

Dim query as String = "Select * from myTable WHERE color = @color and shape = @shape and height = @height"
cmd.Parameters.AddWithValue("@color", color)
cmd.Parameters.AddWithValue("@shape", shape)
cmd.Parameters.AddWithValue("@height", height)



Issue is that if color is empty than this query will fail. I dont want to do bunch of if statments bc what if you have 50 cols (for ex).

if color is empty than I want to skip searching though color col in database

This post has been edited by ikhlas06: 01 August 2017 - 10:56 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How to adv search?

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,052
  • Joined: 08-June 10

Re: How to adv search?

Posted 01 August 2017 - 11:01 AM

View Postikhlas06, on 01 August 2017 - 07:55 PM, said:

Issue is that if color is empty than this query will fail. I dont want to do bunch of if statments bc what if you have 50 cols (for ex).


If you create the SQL yourself, then you would have to make that many if statements.

on the other-hand-side, you could write a program that creates the query for you ...
Was This Post Helpful? 0
  • +
  • -

#3 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: How to adv search?

Posted 01 August 2017 - 12:43 PM

Dim query as String = "Select * from myTable WHERE color = @color and shape = @shape and height = @height"



Below, I could test to see if color is empty or not.
this way if color is not empty than it works fine.

But than issue is still the same, when color is empty. How can I just skip if color is empty?

 If (String.IsNullOrEmpty(color)) Then
	cmd.Parameters.AddWithValue("@color", )
 else
	cmd.Parameters.AddWithValue("@color", color)
 end if

Was This Post Helpful? 0
  • +
  • -

#4 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: How to adv search?

Posted 01 August 2017 - 12:56 PM

ok, so what if I do this? Only issue with this is that I am not sure how to add "WHERE" or "AND" string?
for example: where could be added in 1st, 2nd or 3rd if statment. this dependends on the user.

In the end I will have about 12 if statments.

 Query1 = "SELECT * FROM [myTable] "
                Dim whereS As String = " WHERE "
                Dim AndS As String = " AND "
                Dim SubQ1 As String = " [color] = '" & color & "' "
                Dim SubQ2 As String = " [shape] = '" & shape & "' "
                Dim SubQ3 As String = " [height] = '" & height & "' "


                If (Not (String.IsNullOrEmpty(Request.QueryString("color")))) Then
                    Query1 = Query1 & SubQ1
                End If
                If (Not (String.IsNullOrEmpty(Request.QueryString("shape")))) Then
                    Query1 = Query1 & SubQ2
                End If
                If (Not (String.IsNullOrEmpty(Request.QueryString("height")))) Then
                    Query1 = Query1 & SubQ3
                End If

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13485
  • View blog
  • Posts: 53,852
  • Joined: 12-June 08

Re: How to adv search?

Posted 01 August 2017 - 12:59 PM

This takes some abstract thinking. What condition would you want to add a WHERE? Which for an AND? Perhaps some relation off if a variable is empty or not?

I would probably have a new string variable. If you have a variable with a value then check if this new string is empty. If empty add a WHERE and your condition. If not throw up an AND and your condition.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6376
  • View blog
  • Posts: 25,765
  • Joined: 12-December 12

Re: How to adv search?

Posted 01 August 2017 - 01:44 PM

I'm sure there is a way of doing this as I recall, although I'm shady about the exact syntax.

It helps that the values are chosen (or not) from dropdowns. Something like (untested):

Dim query as String = "Select * from myTable WHERE color Like @color and shape Like @shape and height Like @height"
cmd.Parameters.AddWithValue("@color%", color)
cmd.Parameters.AddWithValue("@shape%", shape)
cmd.Parameters.AddWithValue("@height%", height)

If, for example, they don't choose a colour it will default to including all colours in the conditions.

It's not perfect as it stands though, "red" would include "reddish".
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1