|
 |
asp_web_howto thread: Return Error Command Object
Message #1 by "Joe" <joe@k...> on Fri, 18 Oct 2002 12:04:42
|
|
Hi
I'm using the command object to add a new record to a database and return
a value from the newly inserted record.
What I'm trying to achieve is to first check whether a related record
exists, if yes, insert the new record and return the value, if no, return
an error message to the user. Could someone have a quick look over the
snip of code and stored procedure, and let me know where i'm going wrong?
SP:
ALTER PROCEDURE PrAddPiecePartNumber
(
@txtPiecePartDesc varchar(1000),
@txtDrawingNumber int,
@txtRequester varchar(55),
@txtPiecePartNumberDate smalldatetime,
@NextPiecePartNumber int OUTPUT
)
AS
SET NOCOUNT ON
IF NOT EXISTS (SELECT DrawingNumber FROM tblDrawingNumber WHERE
DrawingNumber = @txtDrawingNumber)
RETURN 55555
ELSE
SET @NextPiecePartNumber = (SELECT (MAX(PiecePartNumber) + 1) AS
NextPiecePartNumber FROM tblPiecePartNumber)
INSERT INTO tblPiecePartNumber
(
PiecePartNumber,
PiecePartDesc,
DrawingNumber,
Requester,
PiecePartNumberDate
)
VALUES
(
@NextPiecePartNumber,
@txtPiecePartDesc,
@txtDrawingNumber,
@txtRequester,
@txtPiecePartNumberDate
)
SELECT @NextPiecePartNumber
RETURN @@ERROR
ASP:
Set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = conntemp
.CommandText = "PrAddPiecePartNumber"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@txtPiecePartDesc", 202,
adParamInput, 1000, txtPiecePartDesc)
.Parameters.Append .CreateParameter("@mltDrawingNumber", 3,
adParamInput, 4, mltDrawingNumber)
.Parameters.Append .CreateParameter("@txtRequester", 202,
adParamInput, 55, txtRequester)
.Parameters.Append .CreateParameter("@txtPiecePartNumberDate",
135, adParamInput, 4, txtPiecePartNumberDate)
.Parameters.Append .CreateParameter("@NextPiecePartNumber",
adInteger, adParamOutput, , 0)
.Parameters.Append .CreateParameter("@RETURN_VALUE", adVarInteger,
adParamReturnValue, 0, lngRetVal)
.Execute
NextPiecePartNumber = .Parameters("@NextPiecePartNumber")
ReturnValue = .Parameters("@RETURN_VALUE")
End With
If ReturnValue <> 0 Then
Response.Write MyError(ReturnValue)
Else
Blah Blah
TIA
Joe
Message #2 by "Imar Spaanjaars" <Imar@S...> on Fri, 18 Oct 2002 13:11:47 +0200 (CEST)
|
|
It would really help if you'd desribe what goes wrong / what error you are
getting.
Cheers,
Imar
> Hi
>
> I'm using the command object to add a new record to a database and
> return a value from the newly inserted record.
>
> What I'm trying to achieve is to first check whether a related record
> exists, if yes, insert the new record and return the value, if no,
> return an error message to the user. Could someone have a quick look
> over the snip of code and stored procedure, and let me know where i'm
> going wrong?
>
> SP:
>
> ALTER PROCEDURE PrAddPiecePartNumber
> (
> @txtPiecePartDesc varchar(1000),
> @txtDrawingNumber int,
> @txtRequester varchar(55),
> @txtPiecePartNumberDate smalldatetime,
> @NextPiecePartNumber int OUTPUT
> )
> AS
>
> SET NOCOUNT ON
>
> IF NOT EXISTS (SELECT DrawingNumber FROM tblDrawingNumber WHERE
> DrawingNumber = @txtDrawingNumber)
>
> RETURN 55555
>
> ELSE
>
> SET @NextPiecePartNumber = (SELECT (MAX(PiecePartNumber) + 1) AS
> NextPiecePartNumber FROM tblPiecePartNumber)
>
> INSERT INTO tblPiecePartNumber
> (
> PiecePartNumber,
> PiecePartDesc,
> DrawingNumber,
> Requester,
> PiecePartNumberDate
> )
>
> VALUES
>
> (
> @NextPiecePartNumber,
> @txtPiecePartDesc,
> @txtDrawingNumber,
> @txtRequester,
> @txtPiecePartNumberDate
> )
>
> SELECT @NextPiecePartNumber
>
> RETURN @@ERROR
>
> ASP:
>
>
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> With objCmd
> .ActiveConnection = conntemp
> .CommandText = "PrAddPiecePartNumber"
> .CommandType = adCmdStoredProc
>
> .Parameters.Append .CreateParameter("@txtPiecePartDesc", 202,
> adParamInput, 1000, txtPiecePartDesc)
> .Parameters.Append .CreateParameter("@mltDrawingNumber", 3,
> adParamInput, 4, mltDrawingNumber)
> .Parameters.Append .CreateParameter("@txtRequester", 202,
> adParamInput, 55, txtRequester)
> .Parameters.Append .CreateParameter("@txtPiecePartNumberDate",
> 135, adParamInput, 4, txtPiecePartNumberDate)
>
> .Parameters.Append .CreateParameter("@NextPiecePartNumber",
> adInteger, adParamOutput, , 0)
> .Parameters.Append .CreateParameter("@RETURN_VALUE", adVarInteger,
> adParamReturnValue, 0, lngRetVal)
>
>
> .Execute
>
> NextPiecePartNumber = .Parameters("@NextPiecePartNumber")
> ReturnValue = .Parameters("@RETURN_VALUE")
>
> End With
>
> If ReturnValue <> 0 Then
>
> Response.Write MyError(ReturnValue)
>
> Else
>
> Blah Blah
>
> TIA
>
> Joe
Message #3 by "Joe" <joe@k...> on Fri, 18 Oct 2002 12:28:43
|
|
Hi Imar
Error message is:
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
on line:
.Parameters.Append .CreateParameter("@RETURN_VALUE", adVarInteger,
adParamReturnValue, 0, lngRetVal)
Cheers
Joe
Message #4 by "Imar Spaanjaars" <Imar@S...> on Fri, 18 Oct 2002 14:00:20 +0200 (CEST)
|
|
Makes sense, if you ask me. adVarInteger does not exist. This should be
adInteger (with a value of 3)
Right now, you're passing an empty variable which will most likely default
to 0 which means adEmpty.
I bet you don't have <%Option Explicit%> to the top of your page??
Cheers,
Imar
> Hi Imar
>
> Error message is:
>
> Parameter object is improperly defined. Inconsistent or incomplete
> information was provided.
>
> on line:
>
> .Parameters.Append .CreateParameter("@RETURN_VALUE", adVarInteger,
> adParamReturnValue, 0, lngRetVal)
>
> Cheers
>
> Joe
>
Message #5 by "Joe Ingle" <joe@k...> on Fri, 18 Oct 2002 13:53:53 +0100
|
|
Cheers Imar
Working fine now.
Joe
-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: Friday, October 18, 2002 1:00 PM
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Return Error Command Object
Makes sense, if you ask me. adVarInteger does not exist. This should be
adInteger (with a value of 3)
Right now, you're passing an empty variable which will most likely
default
to 0 which means adEmpty.
I bet you don't have <%Option Explicit%> to the top of your page??
Cheers,
Imar
> Hi Imar
>
> Error message is:
>
> Parameter object is improperly defined. Inconsistent or incomplete
> information was provided.
>
> on line:
>
> .Parameters.Append .CreateParameter("@RETURN_VALUE", adVarInteger,
> adParamReturnValue, 0, lngRetVal)
>
> Cheers
>
> Joe
>
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=3Dnosim/theprogramm
e
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=3Dnosim/theprogramm
e
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=3Dnosim/theprogramm
e
r-20
|
|
|
View Cart
