How to check values in a Sql DataBase

bex · December 30th, 2008, 08:42 AM

Hi there i have a textbox controll in a web form a next btn and a label controll, on btn click event i want to open the connection to the db look for textbox value then if record exists lbl.text="record exsist, try onother"
else
Response.ridirect=Default.aspx

I need help whith the searching of the db and i am using VB

thanks for looking

gbianchi · December 30th, 2008, 08:53 AM

Hello. You need help in building the sql??? You will have to provide more info them, i

dparsons · December 30th, 2008, 09:30 AM

Yes it is not entirely clear as to what you are asking. Are you asking how to connect to the database so that you can preform the search or are you asking how to search a database?

-Doug

bex · December 30th, 2008, 10:00 AM

I am asking how to search the database

dparsons · December 30th, 2008, 10:13 AM

First, what RDBMS are you using?

The next obvious question, then, is how do you want to search the database? Are you searching a single table or do you want to query against multiple tables? In the same vein are you searching a single column or multiple columns?

Are you looking for an exact match in the database or for rows of data that are similar to whatever was entered into the textbox? For example if someone types 'mo' into the textbox rows of data will be returned where the strin 'mo' is found as well as rows where 'mom', 'moth', and so forth will be returned as well.

Are you wanting to do a phonetic ("sounds like") search where rows of data are returned if they contain words that sound like those placed in the textbox?

-Doug

bex · December 30th, 2008, 01:22 PM

I am using MS SQL 2005 and i need to search 1 tabe and multiple columns

so the idea is to search the database for whatever is entered into the textbox and tell me if any thing was found in the db like you sed 'mom', 'moth',

i am not wanting to do a phonetic

thanks

dparsons · December 30th, 2008, 01:36 PM

If you want a fast and dirty solution, something like the following would work*:

csharp Code:

string SearchText = textbox1.Text;
 string Sql = @"SELECT * from MyTable where Column1 like '%" +  
                     SearchText + "%' OR Column2 like '%" + 
                     SearchText + "%' OR Column3 like '%" +
                     SearchText + "%'";

Personally I would use a Full Text Index when trying to search a database since it allows for flexibility if and when I would need to search more columns/tables as well as various other things. The dynamic sql approach I showed above is effective only for the most trivial of database searches.

Information about Full Text Indexs can be found at the following links:

http://www.wherecanibuyit.co.uk/ASP/...xt-search.html

http://www.simple-talk.com/sql/learn...uage-features/

hth
-Doug.

##EDIT##
It is probably worthwhile for me to point out the use of the wildcard character %. By wrapping your search critera with % % your search will look for the existence of the string ANYWHERE within a given word. So a search for 'po' would return pop, empower, and expo. More information about SQL Server wildcard characters can be found here:

http://doc.ddart.net/mssql/sql70/la-lz_2.htm
##/EDIT##

*I am using dynamic sql here for demonstration only. You should move this to a sproc or to some other form of dynamic sql that is better protected (e.g. parametrized) from SQL Injection

Lee Dumond · December 30th, 2008, 02:50 PM

Quote:

Originally Posted by bex

Hi there i have a textbox controll in a web form a next btn and a label controll, on btn click event i want to open the connection to the db look for textbox value then if record exists lbl.text="record exsist, try onother"

I just put a quick example of how to do this on my blog, using server-side validation with a CustomValidator:

http://leedumond.com/blog/how-to-val...om-a-database/

The example shows a straight equality check, but obviously you can do more complex comparisons as well, including regular expressions.

The example is C# but should not be difficult to convert. If you need a VB version, please let me know.

bex · December 30th, 2008, 02:56 PM

i do need the VB version please

Lee Dumond · December 30th, 2008, 03:26 PM

Quote:

Originally Posted by bex

i do need the VB version please

Code:

Protected Sub custval_txtTeam_ServerValidate(ByVal source As Object, _
       ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) _
       Handles custval_txtTeam.ServerValidate

        args.IsValid = True

        For Each drv As DataRowView In SqlDataSourceTeams.Select(DataSourceSelectArguments.Empty)
            If drv("TeamName").ToString() = args.Value Then
                args.IsValid = False
                Exit For
            End If
        Next

        If args.IsValid Then
            lblResult.Text = "Congratulations! That name is not taken."
        End If

    End Sub