1

I have an Oracle 10g database with a field defined as NUMBER. I am converting a classic ASP (vbscript) from inline sql to parameterized. I cannot get the ADO.PARAMETER to map to a NUMBER. All I get is "Parameter object is improperly defined. Inconsistent or incomplete information was provided." I've tried decimal, numeric, with precision and scale, int, bigint, varchar, sizes and everything combination I can think of.

Dim param
Set param = Server.CreateObject("ADODB.Parameter")
param.Type = ? ' adDecimal
param.Precision = ? ' 38
param.NumericScale = ? ' 0
param.size = ?
.Value = MYNUMBER

Can someone tell me the type, precision, scale, size etc that I need?

Marc Johnston
  • 1,276
  • 1
  • 7
  • 16
  • Best resource I ever found for this sort of thing is [Data Type Mapping](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx) and its never steered me wrong. Looking at Oracle it suggests `number` should map to the ADO constant `adNumeric` *(131)*, so I would suggest using that. – user692942 Apr 04 '17 at 18:16
  • I've been through the URL numerous times. adNumeric is not working, unless there is a decimal, precision or size that I have not been able to get right. – Marc Johnston Apr 04 '17 at 18:19
  • I have adDecimal mapping to NUMBER(15,4) with a precision and scale set accordingly working. I also have varchar parameters working. This Oracle NUMBER is not. – Marc Johnston Apr 04 '17 at 18:21
  • You don't need to define all that, you are over complicating the parameter definitions, have you looked at the other questions that give examples of implementing parameterised SQL in Classic ASP? I've written a few myself. I'd recommend using the `CreateParameter()` method of the `ADODB.Command` object to build your paramter definitions. – user692942 Apr 04 '17 at 18:22

1 Answers1

1

I would consider using the CreateParameter() method of the ADODB.Command object to build the parameters correctly. It's likely not the data type you are passing or the precision etc. that is the problem, rather it will be missing properties of the Parameter object not being set (such as Direction for example) or the Named Constants are not defined.

With that in mind for this particular example I looked up the expected data type Named Constant for Oracle's NUMBER data type and according to Carl Prothman - Data Type Mapping (an excellent resource for mapping ADO constants to various data sources) you should be using adNumeric which is 131. Armed with that information I would build the parameter like so;

<%
Dim cmd: Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  ...
  'Define parameters
  Call .Parameter.Append(.CreateParameter("par1", adNumeric, adParamInput, 9))
  ...
  'Set Parameter values
  .Parameter("par1").Value = MYNUMBER
  ...
End With
%>

... - denotes assumed code for building the ADODB.Command object.

Useful Links

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
  • My problem went away for some unknown reason. But based on my experience, Lankymart does have the best answer for this ... so I will accept it. :) Thanks – Marc Johnston Apr 06 '17 at 16:51