error - incorrect syntax near '4'

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » error - incorrect syntax near '4'

21 posts, Page 1 of 3

1 2 3 »»»

error - incorrect syntax near '4'

Rate Topic

Display Mode

Topic Options

Author

Message

ssurekha2000

Posted Monday, October 15, 2012 12:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:13 PM
Points: 46, Visits: 120

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

Vegard Hagen

Posted Monday, October 15, 2012 2:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 494, Visits: 326

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

Sony Francis @EY

Posted Monday, October 15, 2012 3:24 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:07 AM
Points: 62, Visits: 227

Maybe data type mismatch issues.

Make sure variable type and column types are same.

Post #1372620

ssurekha2000

Posted Monday, October 15, 2012 5:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:13 PM
Points: 46, Visits: 120

not ableto undertstand why i get syntax error near 4

Post #1372652

SQL Dev-938873

Posted Monday, October 15, 2012 6:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:03 AM
Points: 90, Visits: 417

Please post the table structures which are participated in the procedure

GaNeSH

Post #1372681

ssurekha2000

Posted Monday, October 15, 2012 6:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:13 PM
Points: 46, Visits: 120

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

Eugene Elutin

Posted Monday, October 15, 2012 6:40 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 2,556, Visits: 4,398

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1372696

SQL Dev-938873

Posted Monday, October 15, 2012 6:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:03 AM
Points: 90, Visits: 417

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

SQL Dev-938873

Posted Monday, October 15, 2012 6:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:03 AM
Points: 90, Visits: 417

Dats good point Eugene Elutin..

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

GaNeSH

Post #1372708

ssurekha2000

Posted Monday, October 15, 2012 7:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 10:13 PM
Points: 46, Visits: 120

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 »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions