October 15, 2012 at 12:06 am
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
October 15, 2012 at 2:45 am
So, what are you asking about here?
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 15, 2012 at 3:24 am
Maybe data type mismatch issues.
Make sure variable type and column types are same.
October 15, 2012 at 5:05 am
not ableto undertstand why i get syntax error near 4
October 15, 2012 at 6:20 am
Please post the table structures which are participated in the procedure
GaNeSH
October 15, 2012 at 6:32 am
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]
October 15, 2012 at 6:40 am
Is it SQL error or application error? Your SP looks fine.
Any triggers on tables involved?
October 15, 2012 at 6:55 am
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
October 15, 2012 at 6:57 am
Dats good point Eugene Elutin..
check is there any update/insert triggers on trans table are created or not
GaNeSH
October 15, 2012 at 7:11 am
no i have not created anytriggers
and i modified the sp as given by u ,still the same error
October 15, 2012 at 8:06 am
ssurekha2000 (10/15/2012)
no i have not created anytriggers
Can you please post exact error message you have from your application including error source (call-stack would be even better)
October 15, 2012 at 11:41 am
Then, may be problem with data
have u updated with my procedure.....(missing columns included in insert for trans table)
do one thing....
post rate value which u get from another table in the first statement... that row data and
post the row of data which u r trying to update in trans table if data exists
i guess.. this problem with the data
GaNeSH
October 16, 2012 at 2:48 am
ya i updated the stored procedure as given by u
i even tried by hardcoding the values in sp without passing the parameters still i get the error as below
System.Data.SqlClient.Sqlexception }: {"Incorrect syntax near '4'."}
error code :-2146232060
source : ".Net SqlClient Data Provider"
stack trace : " at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
below is thhe sp
ALTER PROCEDURE [dbo].[usp_updaterate]
--@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 =36 and pid =47)--@pcid and pid=@ppid)
BEGIN
UPDATE [trans]
--set rate=@Rate
--WHERE cid =@pcid and pid=@ppid
set rate =6
where cid=36 and pid=47
END
Else
BEGIN
insert into [trans] (tid,cid,pid,qty,rate,[month],[year],amount,billno,frmdt,todt)
--select @ptid,@pcid,@ppid,@pqty,@Rate,0,0,00,'00','1/1/1900','1/1/1900'
select 215,36,47,0,6,0,0,00,'00','1/1/1900','1/1/1900'
END
END
if i execute it frm analyser as below i get it executed properly
exec usp_updaterate
October 16, 2012 at 4:41 am
For some reason I think that you are not executing the proc you think you do.
Try simple test:
Change proc to something like:
ALTER PROCEDURE [dbo].[usp_updaterate]
AS
BEGIN
RETURN;
END
Then execute it from application. If it still gives you the error, then check your connection string...
Or check it before hand 🙂
October 17, 2012 at 3:06 am
checked the sp with jst return stmt
also checked it with simple select stmt without parameters
checkedd connection string
but still when executed frm application gives the same error
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply