SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 incorrect syntax near '4'.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svibuk
Yak Posting Veteran

62 Posts

Posted - 10/13/2012 :  12:39:29  Show Profile  Reply with Quote
i get error when i execute a storedprocedure .
values r passed to the stored procedure using parameter

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/13/2012 :  13:28:40  Show Profile  Reply with Quote
If you post the code for the stored proc, people on the forum would be able to tell you what needs to be changed.
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 10/13/2012 :  21:52:51  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  10:32:45  Show Profile  Reply with Quote
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?
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 10/15/2012 :  01:23:41  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  07:14:25  Show Profile  Reply with Quote
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)
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 10/15/2012 :  07:30:02  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  08:15:34  Show Profile  Reply with Quote
Do you have a statement somewhere in your code that reads:
cmd3.CommandType = CommandType.StoredProcedure
If not, you need that.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtype.aspx

Edited by - sunitabeck on 10/15/2012 08:16:04
Go to Top of Page

svibuk
Yak Posting Veteran

62 Posts

Posted - 10/15/2012 :  08:21:28  Show Profile  Reply with Quote
ya ofcourse its there
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  08:30:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000