error - incorrect syntax near '4'

  • 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

  • So, what are you asking about here?


    Kind regards,

    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...)

    It is better to light a candle than to curse the darkness. (Chinese proverb)
  • Maybe data type mismatch issues.

    Make sure variable type and column types are same.

  • not ableto undertstand why i get syntax error near 4

  • Please post the table structures which are participated in the procedure

    GaNeSH

  • 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]

  • 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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

  • Dats good point Eugene Elutin..

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

    GaNeSH

  • no i have not created anytriggers

    and i modified the sp as given by u ,still the same error

  • 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)

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

    How to post your question to get the best and quick help[/url]

  • 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

  • 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

  • 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 🙂

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

    How to post your question to get the best and quick help[/url]

  • 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