| Author |
 Topic  |
|
|
svibuk
Yak Posting Veteran
50 Posts |
 Posted - 10/13/2012 : 12:39:29
|
i get error when i execute a storedprocedure .
values r passed to the stored procedure using parameter
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/13/2012 : 13:28:40
|
| If you post the code for the stored proc, people on the forum would be able to tell you what needs to be changed. |
 |
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 10/13/2012 : 21:52:51
|
CREATE PROCEDURE usp_update
@pcid int
,@ppid int
,@ptid int
,@pqty int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Rate int
SET @Rate = (select prate from M_PRDT where PID=@ppid)
IF EXISTS (SELECT cid FROM trans WHERE cid =@pcid and pid=@ppid)
BEGIN
UPDATE trans
set pid=@ppid,rate =@Rate
WHERE cid =@pcid and pid=@ppid
END
Else
BEGIN
insert into trans (tid,cid,pid,qty,rate,frmdt,todt)
select @ptid,@pcid,@ppid,@pqty,@Rate,'1/1/1900','1/1/1900'
END
END
when i execute this thr query analyser it gets ecexuted without anny error
i get the above error when executing from the application only
passing sames values in application as well as in query analyser eecution |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/14/2012 : 10:32:45
|
That would mean that somehow the values coming into the stored procedure from the application are not the same as what you are using when you run it from SSMS. However, if that were the case, I would have expected a different kind of message rather than a syntax error as you are seeing.
What are the values you are passing from the application? And what kind of application is it? C#/.Net, Java, or something else?
Also, are you running any other queries as part of the application? Is it possible that the error message is coming from a different part of the application when it calls another stored procedure or query? |
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 10/15/2012 : 01:23:41
|
cmd3.Parameters.AddWithValue("@pcid", SqlDbType.Int).Value = CInt(lblid.Text)
cmd3.Parameters.AddWithValue("@ppid", SqlDbType.Int).Value = CHK_PRDT.CheckedItems.Item(i)("pid")
cmd3.Parameters.AddWithValue("@ptid", CInt(maxid1))
cmd3.Parameters.AddWithValue("@pqty", CInt("0"))
values tht i get on debugging in order of parameters are "36" , 47,210,0
47 i get when debugged are ? CHK_PRDT.CheckedItems.Item(i)("pid")
47D {Decimal}
Decimal: 47D
& when executing thr' analyser its exec usp_updaterate 36,47,210,0
using vb.net |
Edited by - svibuk on 10/15/2012 01:24:32 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/15/2012 : 07:14:25
|
I have never seen AddWithValue function used that way - but I suppose it could work. The signature of the function definitely does not seem to suggest that: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx
In any case for debugging, try this and see if that works as expected:cmd3.Parameters.AddWithValue("@pcid", 36)
cmd3.Parameters.AddWithValue("@ppid", 47)
cmd3.Parameters.AddWithValue("@ptid", 210)
cmd3.Parameters.AddWithValue("@pqty", 0) |
|
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 10/15/2012 : 07:30:02
|
used the same as given above but i get same error
but executed using query analyser
exec usp_updaterate 36,47,210,0
Command(s) completed successfully |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
|
|
svibuk
Yak Posting Veteran
50 Posts |
Posted - 10/15/2012 : 08:21:28
|
| ya ofcourse its there |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/15/2012 : 08:30:39
|
I don't see anything wrong in the code fragments you posted, so it may be someplace else in the code.
One possible approach to debugging is to start with a very simple test stored proc that does not take any parameters and see if you can make that work from VB.Net. Then add one parameter and see if that works etc.
Another approach would be to copy working code from an MSDN sample, get that to run and make incremental changes until it matches your code. There is a working sample code here: http://msdn.microsoft.com/en-us/library/dw70f090.aspx |
|
|
| |
Topic |
|
incorrect syntax near '4'.
