Updating a timestamp field using a stored procedure

G

Guest

I am running SQL Server 2000 and VB .Net. I would like to update a timestamp field (called customer_timestamp) I have in my Customer table using a stored procedure. I am calling the stored procedure and using the values from a datatable to update my Customer table. I have defined a variable in my stored procedure called @@Now that I am setting to the value of GETDATE(). I am then using @@Now in my values clause to set the value of my customer_timestamp field, but when I execute the stored procedure I get an error: Procedure 'SpInsertDetail' expects parameter '@customer_timestamp', which is not supplied. I don't understand why it is not accepting @@Now as the value

Here the store procedure code
CREATE PROCEDURE dbo.spInsertDetail (@customer_lname varchar(25)
@customer_fname varchar(20), @customer_mi char(4), @customer_suffix char(4)
@customer_companyName varchar(50), @customer_attn varchar(25)
@customer_street1 varchar(50), @customer_street2 varchar(25)
@customer_city varchar(20), @customer_state char(2), @customer_zip char(9)
@customer_country varchar(25), @customer_phone char(10), @customer_ext char(5)
@customer_phoneAlt char(10), @customer_phoneAltExt char(5), @customer_homePhone char(10)
@customer_fax char(10), @customer_cell char(10), @customer_emailAddress varchar(40)
@customer_notes ntext, @customer_commissionPercentage int, @customer_timestamp smalldatetime,
@customer_id int output

AS SET NOCOUNT OFF

DECLARE @ErrorCode in
DECLARE @@Now datetim
SET @@Now = GETDATE(

BEGIN TRA
Insert into tblCustome
(customer_lname, customer_fname, customer_mi, customer_suffix
customer_companyName, customer_attn, customer_street1, customer_street2
customer_city, customer_state, customer_zip, customer_country, customer_phone
customer_ext, customer_phoneAlt, customer_phoneAltExt, customer_homePhone
customer_fax, customer_cell, customer_emailAddress, customer_notes
customer_commissionPercentage, customer_timestamp
Values(@customer_lname, @customer_fname, @customer_mi
@customer_suffix, @customer_companyName, @customer_attn, @customer_street1
@customer_street2, @customer_city, @customer_state, @customer_zip
@customer_country, @customer_phone, @customer_ext, @customer_phoneAlt
@customer_phoneAltExt, @customer_homePhone, @customer_fax, @customer_cell
@customer_emailAddress, @customer_notes, @customer_commissionPercentage, @@Now
SET @ErrorCode=@@ERRO
If (@ErrorCode <> 0
goto Erro

selec
@customer_id = @@Identity

/* check for error*
IF (@@ERROR <> 0
GOTO Erro

GOTO O

Error
/* Error Condition, so rollback*
ROLLBACK TRANSACTIO
RETURN -
GOTO Finally

Ok
/*Everything is ok, so commit*
COMMIT TRANSACTIO
RETURN
Finally

G
 
W

William Ryan eMVP

Hi Gary:
--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
Gary said:
I am running SQL Server 2000 and VB .Net. I would like to update a
Click to expand...
timestamp field (called customer_timestamp) I have in my Customer table
using a stored procedure. I am calling the stored procedure and using the
values from a datatable to update my Customer table. I have defined a
variable in my stored procedure called @@Now that I am setting to the value
of GETDATE(). I am then using @@Now in my values clause to set the value of
my customer_timestamp field, but when I execute the stored procedure I get
an error: Procedure 'SpInsertDetail' expects parameter
'@customer_timestamp', which is not supplied. I don't understand why it is
not accepting @@Now as the value.

If you aren't passing in the value @customer_timestamp or using it as a
return value (I don't see the local variable declaration for the command
object, but based on the declaration below I'm assuming it's not the case)
or output param, don't use it in the signature. If you are going to use it,
you'll need to either supply a default parameter for it in the signature or
you'll need to add it to the Command's parameters collection and give it a
value http://www.knowdotnet.com/articles/defaultparameters.html However, if
you aren't doing anything with it and you are just using @@Now to set it
anyway, there's no need to have it in the signature. This will reduce the
code client side (although it appears you aren't supplying the value so
that's not probably the case) as well as server side - granted it's only one
variable each place but why use it if you don't need to. Anyway, you can
make it it @customer TIMESTAMP and set the value here or you can send
something in from the paramaters collection. Easiest things is probably
just taknig it out of the signature though.
HTH,

Bill
Here the store procedure code:
CREATE PROCEDURE dbo.spInsertDetail (@customer_lname varchar(25),
@customer_fname varchar(20), @customer_mi char(4), @customer_suffix char(4),
@customer_companyName varchar(50), @customer_attn varchar(25),
@customer_street1 varchar(50), @customer_street2 varchar(25),
@customer_city varchar(20), @customer_state char(2), @customer_zip char(9),
@customer_country varchar(25), @customer_phone char(10), @customer_ext char(5),
@customer_phoneAlt char(10), @customer_phoneAltExt char(5), @customer_homePhone char(10),
@customer_fax char(10), @customer_cell char(10), @customer_emailAddress varchar(40),
@customer_notes ntext, @customer_commissionPercentage int,
Click to expand...
@customer_timestamp smalldatetime,
 
G

Guest

Hi Bill,

Not sure if I understand. I am just using @@Now to set the value of customer_timestamp. If I remove it from the signature, not sure how to use @@Now to set the value. Tried removing customer_timestamp from the signature, but received an error in my code that is calling the stored procedure and the record was not inserted:

CREATE PROCEDURE dbo.spInsertDetail (@customer_lname varchar(25),
@customer_fname varchar(20), @customer_mi char(4), @customer_suffix char(4),
@customer_companyName varchar(50), @customer_attn varchar(25),
@customer_street1 varchar(50), @customer_street2 varchar(25),
@customer_city varchar(20), @customer_state char(2), @customer_zip char(9),
@customer_country varchar(25), @customer_phone char(10), @customer_ext char(5),
@customer_phoneAlt char(10), @customer_phoneAltExt char(5), @customer_homePhone char(10),
@customer_fax char(10), @customer_cell char(10), @customer_emailAddress varchar(40),
@customer_notes ntext, @customer_commissionPercentage int,
@customer_id int output)


AS SET NOCOUNT OFF;

DECLARE @ErrorCode int
DECLARE @@Now datetime
SET @@Now = GETDATE()

BEGIN TRAN
Insert into tblCustomer
(customer_lname, customer_fname, customer_mi, customer_suffix,
customer_companyName, customer_attn, customer_street1, customer_street2,
customer_city, customer_state, customer_zip, customer_country, customer_phone,
customer_ext, customer_phoneAlt, customer_phoneAltExt, customer_homePhone,
customer_fax, customer_cell, customer_emailAddress, customer_notes,
customer_commissionPercentage, customer_timestamp)
Values(@customer_lname, @customer_fname, @customer_mi,
@customer_suffix, @customer_companyName, @customer_attn, @customer_street1,
@customer_street2, @customer_city, @customer_state, @customer_zip,
@customer_country, @customer_phone, @customer_ext, @customer_phoneAlt,
@customer_phoneAltExt, @customer_homePhone, @customer_fax, @customer_cell,
@customer_emailAddress, @customer_notes, @customer_commissionPercentage, @@Now)
SET @ErrorCode=@@ERROR
If (@ErrorCode <> 0)
goto Error

select
@customer_id = @@Identity

/* check for error*/
IF (@@ERROR <> 0)
GOTO Error

GOTO Ok

Error:
/* Error Condition, so rollback*/
ROLLBACK TRANSACTION
RETURN -1
GOTO Finally

Ok:
/*Everything is ok, so commit*/
COMMIT TRANSACTION
RETURN 0
Finally:
GO
 
W

William Ryan eMVP

@customer_timestamp isn't an output paramater or return value is it?
Unless I'm missing something, you aren't setting the @customer_timestamp
variable to @@now. When I say the signature, I'm talking about the
parameter not the field. The issue is that in your command on the client,
you add a bunch of parameters. One of them is @customer_timestamp or needs
to be. Or more particularly, you should leave it out. The Parameters
collection needs to match the proc signature for all the values its
expecting. So it's expecting @customer_timestamp and either it's not
included in the command's parameters collection (most likely ) or its
expecting a value that's not being supplied. From the usage I don't see
anything referencing @customer_timestamp the Variable -- @@now is being used
for customer_timestamp the field which needs to be used but the variable
isn't. If you take out the variable, it doesn't work?

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Gary said:
Hi Bill,

Not sure if I understand. I am just using @@Now to set the value of
Click to expand...
customer_timestamp. If I remove it from the signature, not sure how to use
@@Now to set the value. Tried removing customer_timestamp from the
signature, but received an error in my code that is calling the stored
procedure and the record was not inserted:
 
Top