Inserts twice

[berkshirea]

hi guys i have this code and it inserts into my database. the thing about this is sometimes it inserts the words into the field 2 or more times. sometimes it just inserts the words once(which it should be). do you have any ideas about this guys? i've searched the net but didn't find any good lead. btw, my sqlserver is version 2k-8.00.760

CREATE PROCEDURE countsSearch
(@searchKeywords varchar(50))AS

BEGIN

DECLARE @countCountries int

SELECT @countCountries= Count(Countries.id)
FROM Countries
WHERE
Countries.Details LIKE '%' + @searchKeywords + '%'

INSERT INTO keywordsTable (keywordSearch)
SELECT @searchKeywords WHERE (@countCountries) > 0

RETURN
END

 

[hmckillop]

Only things I can think of are

- can you provide the DB using SQL profiler to see when your application runs - this will check is the application code calling the procedure twice.
- you should also be able to check the table keywordstable to see if any triggers exist

The code in itself is fine (asides from the syntax being incorrect for "SELECT @searchKeywords WHERE (@countCountries) > 0",



"I'm living so far beyond my income that we may almost be said to be living apart

 

[berkshirea]

hi hmckillop, thanks for your reply.

i can not use the sql profiler as i don't have admin access rights to it. it's hosted somewhere.

i don't think there are triggers to the table keywordstable.

 

[markros]

Replace your code with

if exists (select 1 from Countries where Details like '%' + @searchKeywords + '%')
   INSERT INTO keywordsTable (keywordSearch)
   SELECT @searchKeywords

 

[gmmastros]

asides from the syntax being incorrect for "SELECT @searchKeywords WHERE (@countCountries) > 0"

That syntax works just fine.

For example:

Declare @Keywords Table(KeywordSearch VarChar(1000))

Insert Into @Keywords(KeywordSearch)
Select 'Apple' where 1=1

Insert Into @Keywords(KeywordSearch)
Select 'Banana' Where 1=0

Select * From @Keywords

berkshirea

If I understand correctly, the issue is that you are getting duplicate rows KeywordsTable. Is this right? Why are you checking the Countries table then?

Seems to me like there should be duplicates in the keywords table. Once for each time the code is run.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[berkshirea]

hi gmmastros, i am using 'Countries' table to search and check if there's a record found in it and if thre's > 0 record found it will insert that word into the 'keywordsTable'.

yes, you are right that there should be duplicates in the keywordsTable. what i am concerned about is that it creates consecutive duplicates.

sample:

id keyword
1 banana
2 apple
3 cherry
4 orange
5 orange
6 apple
7 orange
8 melon
9 blackberry
10 orange
11 orange
12 orange

on this sample,
-in ids 4 & 5 are two consecutive duplicates. i want the id=4 to stay and don't want id=5

-in ids 10, 11, 12: i want id=10 and don't want ids 11,12

im stuck, i don't know why i am getting 2 or more duplicates when inserting into my database. but there are times that it doesn't insert duplicates. i've check page codes etc and still getting duplicates.

guys, my apologies if i was not so clear the first time.

 

[markros]

Are you calling this from ASP.NET? Are you using VB.NET?

If yes, check

http://forums.asp.net/p/1449962/3307087.aspx#3307087

 

[berkshirea]

yes markros, it's asp.net vb.net. thanks a lot guys, i will look into that.

 

[checkai]

I have heard of ASP.Net calls that will do this. I could bet that is your issue if utilizing it via web page. Count up the post backs!