[dba-SQLServer]Re: [AccessD] Simple stored proc problem (I hope)

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




More information about the dba-SQLServer mailing list