Front-end input error checking or backend...?

This maybe belongs in the Data-Access Forum, but I'm not sure.

Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.

Are there performance/coding issues involved? Best practices?In ASP.NET use Validator controls and to disallow duplicates make your table UNIQUE INDEX compatible because SQL Server will not allow the creation of UNIQUE INDEX on Columns that already include duplicate values. Include IGNORE_DUP_KEY in your create INDEX statement. Which means you are using both the application and the database. Hope this helps.

Kind regards,
Gift Peddie

Front end website full-text search

I have a front-end search set up that takes the users input and runs a
stored procedure that uses FREETEXTTABLE to get the results. I want it
so that when a user types in a search that has words grouped together
inside quotes (i.e. "sql server") it does an AND instead of an OR. I
know that CONTAINSTABLE can have this kind of logic but I was wondering
if there is a way to handle this without having my script (ASP)
building the search phrase by parsing the user input.
Any ideas? Or has someone done this and have some advice.
Thanks.
But if it does an AND it will not be the same as "sql server" which searches
for the two words occurring adjacent to each other.
Here is a proc which you can set whether you want the entire phase, an OR,
or an AND.
CREATE PROCEDURE SearchSQL (@.stringin varchar(200), @.BooleanType int= NULL)
AS
-- a @.boolean type of null means a phrase based search
-- a @.boolean type of 0 means an OR type search
-- a @.boolean type of 1 means an AND type search
DECLARE @.holdingString VARCHAR(2000)
DECLARE @.whitespace INT
DECLARE @.boolean VARCHAR(10)
--returning a syntax message if no search phrase is passed
IF LEN(@.stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @.boolean=case WHEN @.booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @.booleantype=2 THEN char(34)+' AND ' + char(34)
ELSE ' ' END
DECLARE @.counter INT
DECLARE @.posold int
DECLARE @.posnew int
SET @.holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+char(34)
SELECT @.whitespace=LEN(@.stringin) - LEN(replace(@.stringin,' ',''))
SELECT @.posold=0
SELECT @.posnew=Charindex(' ',@.stringin)
WHILE @.whitespace >=0
BEGIN
IF @.whitespace=0
BEGIN
SELECT
@.holdingString=@.holdingString+SUBSTRING(@.stringin, @.posold+1,LEN(@.stringin)-@.
posold+1)+char(34)+char(39)+',200) AS t ON '
END
ELSE
BEGIN
SELECT @.holdingString = CASE WHEN LEN(SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1))>0 THEN @.holdingString+SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1)+@.boolean ELSE @.holdingstring END
SELECT @.posold=@.posnew, @.posnew=Charindex(' ',@.stringin, @.posold+1)
END
SELECT @.whitespace=@.whitespace-1
END
SELECT @.holdingString = @.holdingString + 't.[KEY]=a.au_id ORDER BY RANK
DESC'
--PRINT @.holdingstring
EXEC(@.holdingstring)
RETURN @.@.rowcount
Usage is:
DECLARE @.returncode int
EXEC @.returncode=SearchSQL 'this is a test'
PRINT @.returncode
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JT" <jtreuting@.gmail.com> wrote in message
news:1112316885.228212.171380@.f14g2000cwb.googlegr oups.com...
> I have a front-end search set up that takes the users input and runs a
> stored procedure that uses FREETEXTTABLE to get the results. I want it
> so that when a user types in a search that has words grouped together
> inside quotes (i.e. "sql server") it does an AND instead of an OR. I
> know that CONTAINSTABLE can have this kind of logic but I was wondering
> if there is a way to handle this without having my script (ASP)
> building the search phrase by parsing the user input.
> Any ideas? Or has someone done this and have some advice.
> Thanks.
>