ADO error

[tmcneil]

Hi All,

I've got this line in a call to an oracle stored procedure that I just can't seem to fix.

set param = comm.CreateParameter("TakeID",				adInteger, adParamInput, ,	    TakeID)

It used to be ok until I changed the values being sent to it. it used to be no higher than 59999, now it's over 70000. I know that this is the problem, however, here is the error:

Error Type:
Provider (0x8002000A)
Out of present range.
/mbtapacis/include/message.asp, line 199

I've tried chnaging the adInteger to an adBigInt, which makes perfect sense.

set param = comm.CreateParameter("TakeID",				adBigInt, adParamInput, ,	    TakeID)

This is the result...

Error Type:
ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
/mbtapacis/include/message.asp, line 200

Not sure if this is progress or not.....

So, I basically need to find something that can handle numbers around 70000 range.

Thanks,
Todd

 

[PHV]

But, adInteger means Long, so 70000 is far away from overflow.
I'd check the SP to be sure it doesn't expect a Smallint ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tmcneil]

The oracle stored procedure was originally set for a VARCHAR2. yes, it does not make sense, but it had always worked that way. It stored the VARCHAR2 into a Numeric field in a table. It's been since I increased the number from 59999 to 70000, that I am having problems. Still Out of present range errors. it doesn't make any sense......

Todd

 

[monksnake]

You may have to specify the size since you are putting it into a VARCHAR2 field.

set param = comm.CreateParameter("TakeID",                adBigInt, adParamInput, [/!]5[/!],        TakeID)

<.

 

[PHV]

A VARCHAR2 ?
So maximum value is Hex FFFF (dec 65536) ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[monksnake]

That's assuming a length of 1 though, right PHV??

VARCHAR2(1)

<.

 

[PHV]

I don't know Oracle, so I'm not sure why a SP uses such parameter type for a numeric column ...

 

[tmcneil]

Well, I'm changing the SP to handle a NUMBER instead of a VARCHAR2. That would be the first step. I tested with a value lower than 60000 and it works. Now, to get the 70000 range solved....

I'm still trying to use AdInteger, but it keeps saying that it is "Out of present range."

AdBigInt gives me, "ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided."

So, it's the same examples that are in the 1st post.

So, that is where I am at.

 

[tmcneil]

So, here is where I am right now...

I have been referencing this page,

http://www.w3schools.com/ado/met_comm_createparameter.asp

to determine which data type I would need to use. For the 50000, adInteger, works beautifully. For the 70000, I use adBigInt. Well, this is the CreateParameter call:

set param = comm.CreateParameter("TakeID",				adBigInt, adParamInput, ,	    TakeID)

This is the error:

ADODB.Parameters (0x800A0E7C)
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
/mbtapacis/include/message.asp, line 200

Based on the above link, it does not tell me enough info. on how to fix the error. So, does anyone have any ideas or have come across this problem.

Thanks,
Todd

 

[tmcneil]

monksnake,

What does this mean:

[/!]5[/!]

I know it's supposed to be define the adBigInt, but I get a syntax error when I use it as written.

-Todd

 

[monksnake]

That was me mistyping the highlighter with the TGML, it should be just 5.


[!]5[/!]

<.

 

[tmcneil]

Well, according to this link that I just found this morning:

http://www.w3schools.com/ado/ado_datatypes.asp

The adBigInt does not work for Oracle. This leaves me with adNumeric and adInteger. The adInteger data type should work, but I'm not getting it to work as I'm getting this error when using values @ 70000. I'm casting the incoming data to a CLng to make sure it's right. Grrr.... frustration setting in.

Error #5: ADO Error #-2147217887 Description ODBC driver does not support the requested properties. Source Microsoft OLE DB Provider for ODBC Drivers

T-

 

[monksnake]

I see, nice find tmcneil, looks like you should get that to work casting the data as CLng.

<.

 

[tmcneil]

Monksnake,

This is the call from the asp page to the include file:

slot_num = CLng(Replace(audioFile, ".wav", ""))
            
Call StoreMsg(msgType, slot_num, playtype, audiotype, Priority, message, audioFile, Duration)

As you can see, I've casted the data in with a CLng.

This is the call to the oracle stored procedure:

Sub StoreMsg(msgType, TakeID, AudioCtrl, PlayCtrl, Priority, Message, PreRecordedWavFile, Duration)

	Dim comm		    
	Set cn = GetDBConnection()
	Set comm = Server.CreateObject("ADODB.Command")
	comm.ActiveConnection = cn

	comm.CommandText = "{CALL Msg_Pkg.StoreMsg(?,?,?,?,?,?,?,?,?,?) }"
	comm.CommandType = adCmdText

    set param = comm.CreateParameter("msgType",		        adVarChar, adParamInput, 10,	msgType)
	comm.Parameters.Append param
	If msgType = "AMBALERT" Then
	    set param = comm.CreateParameter("TakeID",			adInteger, adParamInput, ,	    TakeID)
	Else
	    set param = comm.CreateParameter("TakeID",			adInteger, adParamInput, ,      TakeID)
	End If
	comm.Parameters.Append param
	set param = comm.CreateParameter("AudioCtrl",			adInteger, adParamInput, ,		AudioCtrl)
	comm.Parameters.Append param
	set param = comm.CreateParameter("PlayCtrl",			adInteger, adParamInput, ,		PlayCtrl)
	comm.Parameters.Append param
	set param = comm.CreateParameter("Priority",			adInteger, adParamInput, ,		Priority)
	comm.Parameters.Append param
	set param = comm.CreateParameter("Message",				adVarChar, adParamInput, 512,	Message)
	comm.Parameters.Append param
	set param = comm.CreateParameter("PreRecordedWavFile",	adVarChar, adParamInput, 1000,	PreRecordedWavFile)
	comm.Parameters.Append param
	set param = comm.CreateParameter("Duration",			adInteger, adParamInput, ,		Duration)
	comm.Parameters.Append param
	set param = comm.CreateParameter("ErrorMsg",            adVarChar, adParamOutput, 512,  ErrorMsg)
	comm.Parameters.Append param
	set param = comm.CreateParameter("ErrorCode",			adInteger, adParamOutput, ,		ErrorCode)
	comm.Parameters.Append param
	set param = Nothing

	ON ERROR RESUME NEXT
    comm.Execute()
	If GetDBError(cn) = 0 THEN
  	  InDuration  = comm.Parameters("InDuration").Value
  	  ErrorMsg  = comm.Parameters("ErrorMsg").Value
  	  ErrorCode = comm.Parameters("ErrorCode").Value
	  status = 0
	Else
	  status = -1
	end if
    Set comm = Nothing

End Sub

I'm getting this error:

Error #5: ADO Error #-2147217887 Description ODBC driver does not support the requested properties. Source Microsoft OLE DB Provider for ODBC Drivers

This is the oracle SP:

PROCEDURE StoreMsg(InAnnType	     IN ANN_SCHEDULE.TYPE%TYPE,
                   InTakeID		     IN NUMBER,
				   InPlayCtrl		 IN NUMBER,
				   InAudioCtrl		 IN NUMBER,
				   InPriority		 IN NUMBER,
				   InMessage		 IN VARCHAR2,
				   InPreRecWavFile	 IN VARCHAR2,
				   InWavFileDuration IN NUMBER,
				   ErrorMsg          OUT VARCHAR2, 
                   ErrorCode         OUT NUMBER);

So, I guess I'm saying the CLng does not give me what I thought I needed. Maybe it's something else....

T-

 

[PHV]

Anyway, no discrepancy here ?
CreateParameter("Duration",
vs
Parameters("InDuration").Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tmcneil]

Definitely helps when another set of eyes can spot problems with someone else's code. Thanks, PHV.

I just out the Duration line, however, that's not the problem.

T-

 

[PHV]

What are the Oracle numeric data types ?
Doesn't it support the standard INTEGER type ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tmcneil]

A link to Oracle data types.

http://www.ss64.com/orasyntax/datatypes.html

 

[PHV]

Have you tried PLS_INTEGER instead of NUMBER in your SP ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tmcneil]

I've tried PLS_INTEGER, but it still doesn't work for me. Well, I decided to use adNumeric of which I have to figure out how to add the precision and numeric scale.

If msgType = "ADHOC" Then
    set param = comm.CreateParameter("TakeID",			adInteger, adParamInput, ,	    TakeID)
Else
    set param = comm.CreateParameter("TakeID",			adNumeric, adParamInput, 10, 	TakeID)
    comm.CreateParameter("TakeID").Precision = 8
    comm.CreateParameter("TakeID").NumericScale = 2
End If    
comm.Parameters.Append param

I have not been able to get this to work yet. The "If" block is fine, it's the "Else" block in defining the adNumeric. Any ideas.

T-