Weird INSERT problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Weird INSERT problem

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-12 : 15:41:48

I have a sp to insert some values to the table which returns @@identity as output. It's working fine but there's an error when I use it within the ASP page: it inserts six rows instead of one. I run it exactly same way in the page as I do in Query Analyzer and there are no loops or something like that. I'm pretty confused about it, I'll be glad if you can help me with this. Here's the code:

procedure : proc_YeniKonu
create procedure proc_YeniKonu
@forum int,@baslik varchar(50),@mesaj text,@yazan int,@tarih varchar(50), @zaman varchar(50),@cevap_eposta int=0,@IP varchar(50),@cikti varchar(3000) OUTPUT
as
set nocount on

declare @identity varchar(3000)

insert into dbo.[KONULAR]
(K_Forum,K_Baslik,K_Yazan,K_Tarih,K_Mesaj,K_CevapEposta,K_YazanIP)
values (@forum,@baslik,@yazan,@tarih+@zaman,@mesaj,@cevap_eposta,@IP)
select @identity=@@identity

--Konu sayýsýný güncelle
update dbo.[FORUMLAR] set F_KonuSayisi=F_KonuSayisi+1 where F_ID=@forum

--Son konu bilgilerini güncelle
update dbo.[FORUMLAR] set F_SonKonuBaslik=@baslik where F_ID=@forum
update dbo.[FORUMLAR] set F_SonKonuID=@identity where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazan=(select U_TakmaAd from dbo.[UYELER] where U_ID=@yazan) where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazanTarih=@tarih where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazanZaman=@zaman where F_ID=@forum

set @cikti=@identity
go

asp page:
strSQL = "declare @mesaj varchar(3000) "
strSQL = strSQL & "exec proc_YeniKonu '1','gene2','dd','1','20020212','2211',DEFAULT,'127.0.0.1',@mesaj OUTPUT "
strSQL = strSQL & " select @mesaj as MESAJ "
set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL,sysDB,adOpenStatic,adLockReadOnly,adCmdText
response.write "output value: "&objRS("MESAJ")
objRS.Close
set objRS = Nothing

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2002-02-12 : 22:25:37

Kensai, g'day, just to help you out with your update statement, you can consolidate this:

quote:

update dbo.[FORUMLAR] set F_KonuSayisi=F_KonuSayisi+1 where F_ID=@forum

--Son konu bilgilerini güncelle
update dbo.[FORUMLAR] set F_SonKonuBaslik=@baslik where F_ID=@forum
update dbo.[FORUMLAR] set F_SonKonuID=@identity where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazan=(select U_TakmaAd from dbo.[UYELER] where U_ID=@yazan) where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazanTarih=@tarih where F_ID=@forum
update dbo.[FORUMLAR] set F_SonYazanZaman=@zaman where F_ID=@forum

With:


update dbo.[FORUMLAR] set 
	F_KonuSayisi=F_KonuSayisi+1,
	F_SonKonuBaslik=@baslik,
	F_SonKonuID=@identity,
	F_SonYazan=(select U_TakmaAd from dbo.[UYELER] where U_ID=@yazan),
	F_SonYazanTarih=@tarih,
	F_SonYazanZaman=@zaman
where F_ID=@forum

I dunno why you're getting 6 inserts. Also you could probably replace GO with RETURN(0), that really doesn't matter coz you're not after any return values, and you are using an OUTPUT param..

Sorry I couldn't help with the multiple inserts, but at least I'm helping you code better.

==================================================
Do not argue with IDIOTS. They will take you down to their level and BEAT you with experience.
Master Fisherman

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-13 : 06:38:56

Thanks a lot for the tip jake.

quote:

I dunno why you're getting 6 inserts. Also you could probably replace GO with RETURN(0), that really doesn't matter coz you're not after any return values, and you are using an OUTPUT param..

Actually, I am. I'm planning to do some username controls in the sp. For example, when username is not found or the password is wrong than the sp will return an error message. And if everything's fine and the insert succeded than it will return @@identity as return message. The @mesaj in asp page and @cikti in sp will be used for this. FYI :)

I didn't started to code this, right now I have to solve this six entries problem.

Edited by - kensai on 02/13/2002 06:42:55

Nazim
A custom title

1408 Posts

Posted - 2002-02-13 : 07:48:08

kensai, are you sure you are not calling the sp multiple times. i would suggest you to have a look at Sql Profiler when the sp is getting executed to check the flow.

Are the Updates running only once. or multiple times?.

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-13 : 13:40:42

Are you absolutley, positively certain that the code you are putting into QA is the same that the ASP page is running? To verify, do a Response.Write strSQL in your asp page, then copy & paste that into QA and run it.

------------------------
GENERAL-ly speaking...

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-13 : 16:41:00

quote:

Are you absolutley, positively certain that the code you are putting into QA is the same that the ASP page is running? To verify, do a Response.Write strSQL in your asp page, then copy & paste that into QA and run it.

I did. I used the writed sql line to the QA and it inserted only one row.

quote:
kensai, are you sure you are not calling the sp multiple times

I think I'm sure. Sp is called in a sub and doesn't have any loops in it. And the sub is called only one time (without any loops either)

quote:
i would suggest you to have a look at Sql Profiler when the sp is getting executed to check the flow.

I did. I used the same line the what response.write strSQL generates. I took a screenshot which will explain it better. Please take a look at it here : http://ocal.net/temp/trace.gif . The first four lines are the ones when I run it using QA. The rest is the ASP page (don't mind proc_forumNav, it's another sp used elsewhere in the same page).

quote:
Are the Updates running only once. or multiple times?.

Once. I used the same code I wrote in my first post here. And there are no loops in the sp.

I'm absolutly clueless about this. It's very weird. I'm hoping that you can help me with this problem or I will just get stuck. I can post the code of the sub too if you want to take a look...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-13 : 17:00:33

WILD GUESS: Do you have any triggers on this table?

------------------------
GENERAL-ly speaking...

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-13 : 17:41:23

Have you tried using the ADO command object to call the SP?

-Chad

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-14 : 06:01:21

quote:
WILD GUESS: Do you have any triggers on this table?

No I don't.

quote:
Have you tried using the ADO command object to call the SP?

No, I don't know much about Command. I always use Recordset to deal with sp's.

Nazim
A custom title

1408 Posts

Posted - 2002-02-14 : 08:03:56

Follow this link it should help you on using ADO's
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11741

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-14 : 16:39:04

quote:

Follow this link it should help you on using ADO's
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11741

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

huh? thanks for the link but I don't have a problem with using ADO?

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-14 : 16:45:46

I think you are supposed to use the command object to call SPs. I'm not sure if that is the cause of your problem, but I would at least try to modify the VBScript to use the command object.

-Chad

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-14 : 16:59:23

I don't know why using a Recordset object would make a difference, but I always use the Command object, with parameters, as has been suggested, to execute stored procedures. It's easy to retrieve output values with them.

Now, a little off topic, but I was looking back at your original post. I understand you will be doing more with the sproc than is show here, but why are @cikti and @identity defined as varchar(3000)? If they are receiving the value of @@IDENTITY then they should be INT datatype. It'll save you some hassles and memory. I'm guessing that you're going to do something extra with @cikti, otherwise having both @cikti and @identity is redundant.

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 02/14/2002 17:00:01

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-14 : 18:53:22

quote:

Now, a little off topic, but I was looking back at your original post. I understand you will be doing more with the sproc than is show here, but why are @cikti and @identity defined as varchar(3000)? If they are receiving the value of @@IDENTITY then they should be INT datatype. It'll save you some hassles and memory. I'm guessing that you're going to do something extra with @cikti, otherwise having both @cikti and @identity is redundant.

You're right. The @cikti will not be used only for getting the @@identity. I'll do some username/password controls in the future and if there are any errors than the @cikti will return a custom error message. But this has to wait right now, the insert problem needs to be solved first.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-14 : 19:11:15

Have you tried the command object idea to see if it made any difference?

------------------------
GENERAL-ly speaking...

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-15 : 06:51:15

I think I've found the source of the problem. It's the output parameter. I created a temporary sp with only this code:

create procedure ptemp
@name varchar(50)
as
set nocount on

insert into dbo.[temp]
(t_name)
values (@name)
go

And I executed it like this both in QA and ASP page :

"exec ptemp 'myname' "

Both of them inserted only one row. After that I changed the code to this:

create procedure ptemp
@name varchar(50),@a int output
as
set nocount on

insert into dbo.[temp]
(t_name)
values (@name)

set @a=@@identity
go

And executed like this:

strSQL = "declare @m varchar(3000) "
strSQL = strSQL&" exec ptemp 'myname',@m output "
strSQL = strSQL&" select @m as identity "

Now it started to insert 6 rows!

I didn't tried Command object. I don't have much time to learn it. Do you have any idea about the reason of this or do you know any solutions to make it run with Recordset?

kensai
Posting Yak Master

172 Posts

Posted - 2002-02-16 : 07:17:34

I tried Command, it works now. Thanks a lot for all help everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources