incorrect syntax near '4'. - 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 2008 Forums

Transact-SQL (2008)

incorrect syntax near '4'.

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-10-13 : 12:39:29

i get error when i execute a storedprocedure .
values r passed to the stored procedure using parameter

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-13 : 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

62 Posts

Posted - 2012-10-13 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 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

62 Posts

Posted - 2012-10-15 : 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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 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

62 Posts

Posted - 2012-10-15 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 08:15:34

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

svibuk
Yak Posting Veteran

62 Posts

Posted - 2012-10-15 : 08:21:28

ya ofcourse its there

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources