Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

error - incorrect syntax near '4' Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 12:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:28 AM
Points: 75, Visits: 197
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


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
Post #1372565
Posted Monday, October 15, 2012 2:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
So, what are you asking about here?



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1372598
Posted Monday, October 15, 2012 3:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 4:01 AM
Points: 63, Visits: 277
Maybe data type mismatch issues.

Make sure variable type and column types are same.
Post #1372620
Posted Monday, October 15, 2012 5:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:28 AM
Points: 75, Visits: 197
not ableto undertstand why i get syntax error near 4
Post #1372652
Posted Monday, October 15, 2012 6:20 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 23, 2013 9:32 AM
Points: 90, Visits: 418
Please post the table structures which are participated in the procedure




GaNeSH
Post #1372681
Posted Monday, October 15, 2012 6:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:28 AM
Points: 75, Visits: 197
CREATE TABLE [dbo].[M_PRDT](
[PID] [numeric](18, 0) NOT NULL,
[PNAME] [varchar](50) NOT NULL,
[PRATE] [float] NOT NULL,
[SCHEME] [bit] NOT NULL,
[REMARKS] [varchar](50) NOT NULL,

CONSTRAINT [PK_M_PRDT] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





CREATE TABLE [dbo].[TRANS](
[tid] [numeric](18, 0) NOT NULL,
[cid] [numeric](18, 0) NOT NULL,
[pid] [numeric](18, 0) NOT NULL,
[qty] [numeric](18, 0) NOT NULL,
[rate] [float] NOT NULL,
[month] [int] NOT NULL,
[Year] [varchar](50) NOT NULL,
[AMOUNT] [numeric](15, 0) NOT NULL,
[Billno] [varchar](50) NOT NULL,
[frmdt] [smalldatetime] NOT NULL,
[todt] [smalldatetime] NOT NULL,
CONSTRAINT [PK_TRANS] PRIMARY KEY CLUSTERED
(
[tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Post #1372689
Posted Monday, October 15, 2012 6:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Is it SQL error or application error? Your SP looks fine.
Any triggers on tables involved?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1372696
Posted Monday, October 15, 2012 6:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 23, 2013 9:32 AM
Points: 90, Visits: 418
alter 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,month,year,amount,billno,frmdt,todt)
select @ptid,@pcid,@ppid,@pqty,@Rate,1,2012,0,'12092012','1/1/1900','1/1/1900'
END
END

there might be an error which i have highlighted in bold in the script above.
Or might be data error in the update statement.

i dont have data ,., so i couldn't figure out the error.
I found the error in the insert statement.
there are not null columns in trans table. But u r not inserting anything.

Let me know, if u still have issues.
and also in update statement,,, there is unnecessary column update pid=@ppid.
because, u have the same condition in the where clause. so there is no change in the pid value, u can remove that from update statment
UPDATE trans
set rate =@Rate
WHERE cid =@pcid and pid=@ppid




GaNeSH
Post #1372707
Posted Monday, October 15, 2012 6:57 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 23, 2013 9:32 AM
Points: 90, Visits: 418
Dats good point Eugene Elutin..

check is there any update/insert triggers on trans table are created or not




GaNeSH
Post #1372708
Posted Monday, October 15, 2012 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:28 AM
Points: 75, Visits: 197
no i have not created anytriggers

and i modified the sp as given by u ,still the same error
Post #1372717
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse