Francisco H Tapia
my.lists at verizon.net
Tue Apr 8 17:49:14 CDT 2003
One notable diffrence is that your @psnID_1 does not have a value for the amount of text you need declared try modifying it to include up to 10 characters for example: ALTER PROCEDURE dbo.update_tbl_psn_1(@psnID_1 [nvarchar] (10), @person_name_2 [nvarchar](100)) AS UPDATE dbo.tbl_psn SET person_name = @person_name_2 WHERE (psnID = @psnID_1) GO -Francisco http://rcm.netfirms.com On Tuesday, April 08, 2003 3:35 PM [GMT-8], Mark Hayes <markH at bitgen.co.uk> wrote: : Thanks Francisco : : The stored proc is.... : : CREATE PROCEDURE dbo.update_tbl_psn_1(@psnID_1 [nvarchar], : @person_name_2 [nvarchar](100)) : AS UPDATE dbo.tbl_psn : SET person_name = @person_name_2 : WHERE (psnID = @psnID_1) : GO : : in query analyser if I execute : : SELECT * FROM tbl_psn WHERE psnID='555' : : I get the record for the person whose ID is "555" : : however, still in QA, if I execute the SP like this : : exec update_tbl_psn_1 '555','Updated Name' : : then its the record with an ID of "5" thats updated. I have tried this : from QA and Access and get the same result. : : If I execute the SP from access by just double clicking on it, I get : prompted for the parameters (psnID and person_name), if I enter "555" : as the psnID record "5" gets updated. : : I have even had the same result working with a separate sql server : installation and a different data set / table. This is really : confusing me as I have written SP's before (a couple of years) ago : that work just fine. : : Really scratching my head on this one : : Cheers : : Mark : : -----Original Message----- : From: accessd-bounces at databaseadvisors.com : [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Francisco H : Tapia : Sent: 08 April 2003 22:57 : To: accessd at databaseadvisors.com; dba-SQLServer at databaseadvisors.com : Subject: [dba-SQLServer]Re: [AccessD] Simple stored proc problem (I : hope) : : : Mark, : as this is getting more SQL Server related than simply Access ADP's : I'm also posting to the dba-SqlServer list (to subscribe follow this : link: : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver) : : Now on to the problem... How are you passing the parameters over to : SQL server? could you post that info? also, do you have QA (Query : Analyzer) if so I would suggest trying out what is happening there... : I was going to ask why is the psnID declared as varchar, but you did : mentioned you were testing so I am assuming that your planning on : using alphanumeric psnID's. : : in QA you would execute the following : Select * FROM tbl_psn Where psnID = '11' : : what are your results? : : : -Francisco : http://rcm.netfirms.com : : On Tuesday, April 08, 2003 2:03 PM [GMT-8], : Mark Hayes <markH at bitgen.co.uk> wrote: : :: Just had another go and it’s the same for characters... If I enter :: "AAA" then recod "A" gets updated... :: :: If I do the same update from code using a simple update sql string :: and a command object then its ok. :: :: ???? :: :: Mark :: :: -----Original Message----- :: From: accessd-bounces at databaseadvisors.com :: [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Hayes :: Sent: 08 April 2003 21:47 :: To: accessd at databaseadvisors.com :: Subject: [AccessD] Simple stored proc problem (I hope) :: :: :: Hello again... :: :: I am using XP data project and have a problem with a stored proc. :: :: I am attempting to update a person record by passing a psnID and a :: new name (just playing) :: :: The problem is that although I may pass the psnID as "11" the record :: with the ID "1" is changed. If I am trying to update the psnID "55" :: then "5" gets updated. :: :: The sproc is v.simple and I really can't see whats going wrong :: :: UPDATE tbl_psn :: SET person_name=@person_name_2 :: WHERE (psnID=@psnID_1) :: :: The psnID field is nvarchar 10 :: :: This XP stuffs getting confusing :o( :: :: Any help much appreciated :: :: Mark :: : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com : : _______________________________________________ : AccessD mailing list : AccessD at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/accessd : Website: http://www.databaseadvisors.com : : --- : Incoming mail is certified Virus Free. : Checked by AVG anti-virus system (http://www.grisoft.com). : Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 : : : --- : Outgoing mail is certified Virus Free. : Checked by AVG anti-virus system (http://www.grisoft.com). : Version: 6.0.470 / Virus Database: 268 - Release Date: 08/04/2003 : : : _______________________________________________ : AccessD mailing list : AccessD at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/accessd : Website: http://www.databaseadvisors.com