Confused about stored procedure error using arguments 1

[rtgordon]

ADODB.Command error '800a0bb9'

The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.

/development/gadams/irsctest/incident_dtl.asp, line 158

This is my stored procedure

CREATE PROCEDURE mysp_update_status

	@incident_num		integer,
	@usr_id			char(18),
	@sts_dtl_msg		char(100),
	@lst_upd_date		smalldatetime,
	@lst_upd_time		char(5)

AS

DECLARE @incident_ocr_num integer
SELECT @incident_ocr_num = (MAX(incident_ocr_num) + 1) FROM [irsc_sts] WHERE incident_num=@incident_num

IF @incident_ocr_num > 0
BEGIN
	INSERT INTO irsc_sts VALUES (@incident_num, @incident_ocr_num, @usr_id, @sts_dtl_msg, @lst_upd_date, @lst_upd_time)

	RETURN 0
END
ELSE
	RETURN 1

This is the ASP (ADO) that populates it:

IncidentNum = CInt(IncidentNum)
StatusMessage = "Incident viewed by " & UserId & "."
StatusDate = FormatDateTime(Date, 2)
StatusTime = FormatDateTime(Time, 4)
StatusTime = CStr(StatusTime)

Set cmd = Server.CreateObject("ADODB.Command")

With cmd
  .ActiveConnection = objConn
  .CommandText = "mysp_update_status"
  .CommandType = adCmdStoredProc

  .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
  .Parameters.Append .CreateParameter ("@incident_num", adInteger, IncidentNum)
  .Parameters.Append .CreateParameter ("@usr_id", adChar, adParamInput, 18, UserId)
  .Parameters.Append .CreateParameter ("@status_dtl_msg", adChar, adParamInput, 100, StatusMessage)
  .Parameters.Append .CreateParameter ("@lst_upd_date", adDBTimeStamp, adParamInput, 8, StatusDate)
  .Parameters.Append .CreateParameter ("@lst_upd_time", adChar, adParamInput, 5, StatusTime)

  '  the first two empty fields are recordset options the name and open parameters
  .Execute , , adExecuteNoRecords
  ReturnValue = .Parameters("RETURN_VALUE")
End With

The database formats match up. I have just been sitting here staring at it too long. I imagine it is easy, but I can't figure it out.

Thanks,
gordon

 

[rtgordon]

sheesh... typical. you look at something until you can't see straight, and when you ask about it, it rears its head at you.

I was missing an adParamInput on the first parameter.

gordon

 

[tlbroadbent]

Change the adDBTimeStamp to adTimeStamp. According to the ADO/SQL data type cross reference adDBTimeStamp corresponds to the SQL DateTime data type and adTimeStamp corresponds to the smalldatetime type. See the following reference for more info.

http://msdn.microsoft.com/library/en-us/adosql/adoprg02_294j.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[rtgordon]

Any idea about the numeric representation of adTimeStamp? I don't have it in my adovbs file. It worked fine in my last stored proc, but if it is recommended/better I will change it.

Thanks,
gordon

 

[tlbroadbent]

It is interesting the that adTimeStamp is listed in the cross reference but not enumerated in ADOVBS.INC. There are three other datetime types enumerated.

Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135

You might be able to use adDBDate instead of adDBTimeStamp. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[rtgordon]

I haven't noticed anything negative with using adDBTimeStamp. It seems fine. Is there anything that I should know about why not to use it? My SP appears to be doing what it is intended to do at this point.

Thanks for the replies.

gordon

 

[tlbroadbent]

I don't know of any reason not to use it. There is some discussion in other news groups of problems and the cross-reference mapping that indicates adDateTime matches smalldatetime but that mapping may, in fact, be in error.

I hadn't noticed your posting that you solved the problem so I was still going under the assumption that something wasn't working. I just need to pay more attention. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[rtgordon]

it has been a long grueling week, and it is late friday afternoon... ;-)

You really have been a big help. The archives are full of informative posts and your name pops up quite a bit.

Thanks,
gordon