Member Avatar for abathurst

What I am trying to do is have a user enter a word into a text box and retrieve the information from a SQL database where the 'entered word' eqauls that a column. I have this working perfectly but now I would like the user to be able to enter multiple words to search for. The trouble I'm having is that it's looking for all of the words in a single entry.

Here is my vb.net code to pass the textbox entry to the stored procedure:

Session("Suburb") = TbSuburbs.Text
Dim parameterSuburb As SqlParameter = New SqlParameter("@suburb", SqlDbType.VarChar, 50)
parameterSuburb.Value = Session("Suburb")
myCommand.SelectCommand.Parameters.Add(parameterSuburb)

And the stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Search_Realestate] 
(
	
	@RegID as varchar(50),
	@category as varchar(50),
	@price_min as int,
	@price_max as int,
	@bed as varchar(50),
	@car as varchar(50),
	@bath as varchar(50),
	@suburb as varchar(100)
) 

as

select 
	headline as headline,
	subNumber as subNumber,
	streetNumber as streetNumber,
	street as street,
	suburb as suburb,
	'$' + convert (varchar (50),price,105) as price,
	bedrooms as bedrooms,
	bathrooms as bathrooms,
	garages as garages,
	description as description,
	image1 as URL1, 
	category as category,
	
	Ref_No as RefNo,
	Rowguid
	from Realestate
	Where @RegID = 'Admin' and category = @category and price >= @price_min and price <= @price_max
	and bedrooms >= @bed and bathrooms >= @bath and garages >= @car
	and suburb LIKE @suburb

So any help with this would be great. I would like to eventually like to give the textbox an auto complete element to it, so if you know any good tutorials for that I would appreciate it.

Edited by abathurst because: n/a
  • 2 Contributors
  • 3 Replies
  • 273 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by abathurst

Dani AI

Generated

Quick diagnosis: passing the whole textbox string into a single varchar parameter and using IN(@suburb) or a single LIKE @suburb will not match multiple separate suburb values. IN(@suburb) expects either multiple SQL literals or separate parameters; a comma-separated string in one parameter is treated as one value. is right that LIKE can help for patterns, but it only matches what you pass — to search for multiple distinct keywords you need multiple predicates (or a table of values).

A practical, simple fix (keep everything parameterized) is to split the textbox on commas/spaces in VB, add one parameter per suburb, and build the WHERE clause with ORs. Example pattern:

' split input
Dim suburbs = TbSuburbs.Text.Split(","c) _
    .Select(Function(s) s.Trim()) _
    .Where(Function(s) s <> "") _
    .ToArray()

' build parameters and WHERE parts
Dim whereParts As New List(Of String)
For i = 0 To suburbs.Length - 1
    Dim param = "@s" & i
    whereParts.Add("suburb = " & param)   ' or "suburb LIKE " & param for partial matches
    cmd.Parameters.AddWithValue(param, suburbs(i))
Next

cmd.CommandText &= " AND (" & String.Join(" OR ", whereParts) & ")"

A more robust, scalable approach is to use a Table-Valued Parameter (SQL Server 2008+) — create a small user-defined table type (one varchar column), change the stored procedure to accept @Suburbs dbo.YourType READONLY, and JOIN Realestate to that table. From VB you build a DataTable of suburbs and pass it as a SqlParameter with SqlDbType.Structured and TypeName = "dbo.YourType".

Troubleshooting notes: trim inputs, normalize casing if needed, use LIKE '%value%' for partial matches, and avoid concatenating raw strings into SQL (risk of injection). Test the generated SQL/parameters in SSMS if results are unexpected. For autocomplete, populate the client widget from a server endpoint that returns matching suburb names (jQuery UI or HTML5 datalist work well).

Recommended Answers

All 3 Replies

Member Avatar for abathurst

Sorry just realised I posted old code the last line is:

and suburb IN (@suburb)
Member Avatar for crishlay

use Like in sql for multiple keyword data find

Member Avatar for abathurst

I've tried LIKE with the same results. Works with one keyword but not multiple

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.