InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:30:03 GMT20http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxIs usp_update the actual sp that you're editing? On your original post your vb is calling usp_updaterate. Nevermind I should check for more pages on a post.Step through your code and check the values of your variables. Also might think about prefacing the variable name with "var" so that it's easier to read and you avoid using a reserved name by accident.Wed, 17 Oct 2012 14:27:53 GMTWant a cool Sighttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxcan you post the code where you set your CommandText in application?It is an application issue actually, not SQL one.Somewhere, instead of stored proc name you use variable with value of "4"Wed, 17 Oct 2012 13:57:46 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxyour variable in app must be geting overriden by some other value assignment. check the variable that you are using to call the proc.Wed, 17 Oct 2012 11:34:45 GMTkoustav_1982http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxthere is no 4 in the SPi even checked with select * from table1i am not sure where do i get it from but i found this on debugging?cmdupdate.CommandText"4"?cmdupdate{System.Data.SqlClient.SqlCommand} CommandText: "4" CommandTimeout: 30 CommandType: Text {1} Connection: {System.Data.SqlClient.SqlConnection} Container: Nothing DesignTimeVisible: True Notification: Nothing NotificationAutoEnlist: True Parameters: {System.Data.SqlClient.SqlParameterCollection} Site: Nothing Transaction: Nothing UpdatedRowSource: Both {3}[b]cmdupdate is the sqlcommandcmdupdate.CommandText = "4"CommandType.StoredProcedure = StoredProcedure {4}[/b]i get this on debugging the applicationWed, 17 Oct 2012 10:23:14 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspx[quote][b]ssurekha2000 (10/17/2012)[/b][hr]checked the sp with jst return stmtalso checked it with simple select stmt without parameterscheckedd connection string but still when executed frm application gives the same error[/quote]Have you checked connection string? Looks like you are not connecting to the server/database where you have made and making changes as proc with only RETURN has no character "4" inside of its body ;-)Wed, 17 Oct 2012 04:10:40 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxTurn on the SQL Server Profiler and then run your app. You will get to see the exact statement that is throwing error. It will help debugging the issue. Wed, 17 Oct 2012 03:44:41 GMTkoustav_1982http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxchecked the sp with jst return stmtalso checked it with simple select stmt without parameterscheckedd connection string but still when executed frm application gives the same errorWed, 17 Oct 2012 03:06:43 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxFor some reason I think that you are not executing the proc you think you do. Try simple test:Change proc to something like:[code="sql"]ALTER PROCEDURE [dbo].[usp_updaterate]ASBEGIN RETURN;END[/code]Then execute it from application. If it still gives you the error, then check your connection string...Or check it before hand :-)Tue, 16 Oct 2012 04:41:10 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxya i updated the stored procedure as given by ui even tried by hardcoding the values in sp without passing the parameters still i get the error as below[b]System.Data.SqlClient.Sqlexception }:[/b] {"Incorrect syntax near '4'."}[b]error code :[/b]-2146232060[b]source :[/b] ".Net SqlClient Data Provider"[b]stack trace :[/b] " 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 spALTER PROCEDURE [dbo].[usp_updaterate]--@pcid int--,@ppid int--,@ptid int--,@pqty intASBEGINSET 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 ENDElse 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' ENDENDif i execute it frm analyser as below i get it executed properlyexec usp_updaterateTue, 16 Oct 2012 02:48:11 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxThen, may be problem with datahave u updated with my procedure.....(missing columns included in insert for trans table)do one thing.... post [b]rate [/b]value which u get from another table in the first statement... that row data andpost the row of data which u r trying to update in trans table if data existsi guess.. this problem with the dataMon, 15 Oct 2012 11:41:08 GMTSQL Dev-938873http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspx[quote][b]ssurekha2000 (10/15/2012)[/b][hr]no i have not created anytriggers[/quote]Can you please post exact error message you have from your application including error source (call-stack would be even better)Mon, 15 Oct 2012 08:06:46 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxno i have not created anytriggersand i modified the sp as given by u ,still the same errorMon, 15 Oct 2012 07:10:59 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxDats good point Eugene Elutin..check is there any update/insert triggers on trans table are created or notMon, 15 Oct 2012 06:57:55 GMTSQL Dev-938873http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxalter PROCEDURE usp_update@pcid int,@ppid int,@ptid int,@pqty intASBEGINSET NOCOUNT ON;DECLARE @Rate intSET @Rate = (select prate from M_PRDT where PID=@ppid)IF EXISTS (SELECT cid FROM trans WHERE cid =@pcid and pid=@ppid)BEGINUPDATE transset pid=@ppid,rate =@RateWHERE cid =@pcid and pid=@ppidENDElseBEGINinsert into trans (tid,cid,pid,qty,rate,[b]month,year,amount,billno,[/b]frmdt,todt)select @ptid,@pcid,@ppid,@pqty,@Rate,[b]1,2012,0,'12092012'[/b],'1/1/1900','1/1/1900'ENDENDthere 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[b]UPDATE transset rate =@RateWHERE cid =@pcid and pid=@ppid[/b]Mon, 15 Oct 2012 06:55:30 GMTSQL Dev-938873http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxIs it SQL error or application error? Your SP looks fine.Any triggers on tables involved?Mon, 15 Oct 2012 06:40:34 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxCREATE 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]Mon, 15 Oct 2012 06:32:11 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxPlease post the table structures which are participated in the procedureMon, 15 Oct 2012 06:20:57 GMTSQL Dev-938873http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxnot ableto undertstand why i get syntax error near 4Mon, 15 Oct 2012 05:05:48 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxMaybe data type mismatch issues.Make sure variable type and column types are same.Mon, 15 Oct 2012 03:24:38 GMTSony Francis @EYhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxSo, what are you asking about here?Mon, 15 Oct 2012 02:45:09 GMTVegard Hagenhttp://www.sqlservercentral.com/Forums/Topic1372565-392-1.aspxCREATE PROCEDURE usp_update @pcid int,@ppid int,@ptid int,@pqty intASBEGINSET NOCOUNT ON;DECLARE @Rate intSET @Rate = (select prate from M_PRDT where PID=@ppid)IF EXISTS (SELECT cid FROM trans WHERE cid =@pcid and pid=@ppid)BEGINUPDATE trans set pid=@ppid,rate =@Rate WHERE cid =@pcid and pid=@ppidENDElseBEGINinsert into trans (tid,cid,pid,qty,rate,frmdt,todt)select @ptid,@pcid,@ppid,@pqty,@Rate,'1/1/1900','1/1/1900'ENDENDwhen i execute this thr query analyser it gets ecexuted without anny errori get the above error when executing from the application onlypassing sames values in application as well as in query analyser eecutioncmd3.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,047 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,0using vb.netMon, 15 Oct 2012 00:06:51 GMTssurekha2000