• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1055
  • Last Modified:

CreateParameter question

Hi,

I am using this to send value to sp:

Set pm = oCmd.CreateParameter("@ABC", adVarNumeric, adParamInput, , (value/100))
oCmd.Parameters.Append pm
Which type should I use?
adVarNumeric or adInteger??  Thx
0
mcrmg
Asked:
mcrmg
  • 13
  • 7
  • 7
  • +2
3 Solutions
 
vnvkCommented:
adInteger
0
 
mcrmgAuthor Commented:
so, it will take something like 1.22 or 0.88?thx
0
 
mcrmgAuthor Commented:
Can I pass float to sp?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
joeposter649Commented:
There's a list of datatypes here...
http://devguru.com/Technologies/ado/quickref/command_createparameter.html

I assume you know to use the same type that defined in your sp.
0
 
mcrmgAuthor Commented:
I use this in asp

Set pm = oCmd.CreateParameter("@abc", adDouble, adParamInput, , 0.05)
oCmd.Parameters.Append pm


and data type in sp I use float, is that ok?
because it gives an error "@abc not supplied"

thx
0
 
Anthony PerkinsCommented:
>>and data type in sp I use float, is that ok?
because it gives an error "@abc not supplied"<<
Post your Stored Procedure.
0
 
Anthony PerkinsCommented:
Also, post the relevant ASP code.
0
 
mcrmgAuthor Commented:
If (len(ABC)=0) then
      Set pm = oCmd.CreateParameter("@ABC", adDouble, adParamInput, , NULL)      
else
    Set pm = oCmd.CreateParameter("@ABC", adDouble, adParamInput, , ABC)
end if
oCmd.Parameters.Append pm

If (len(XYZ)=0) then
      Set pm = oCmd.CreateParameter("@XYZ", adDouble, adParamInput, , NULL)      
else
    Set pm = oCmd.CreateParameter("@XYZ", adDouble, adParamInput, , XYZ)
end if
oCmd.Parameters.Append pm


*********************************
ALTER     PROCEDURE dbo.st_sp
@ABC float, @XYZ float

AS

SET NOCOUNT ON

SELECT * FROM Table1
WHERE ( (VALUE >= isNULL(@ABC, VALUE)) AND (VALUE < isNULL(@XYZ, VALUE + 1)) )
0
 
Anthony PerkinsCommented:
I cannot see anything obviously wrong with that code.  Try running it and posting the exact error message and the line where it occurs.
0
 
mcrmgAuthor Commented:
Procedure 'st_sp' expects parameter '@ABC', which was not supplied.

If I check them to adInteger, it works fine.  But since the value in "VALUE" column contains 0.2, 0.5689 ............  Not interger, when I send 0.05, it sends to sp as 0. (I think) So, it always bring back all records.
0
 
joeposter649Commented:
Maybe you need to pass CDbl(0.05) or specify the lenght.

Half way down this link is a list of how SQL Server datatypes map to ADO...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvid/html/msdn_visql.asp 
0
 
mcrmgAuthor Commented:
Strange, I tried adding cdbl() and/or length....does not work.......
0
 
joeposter649Commented:
What did you try for a length?  Try this...
Set pm = oCmd.CreateParameter("@abc", adDouble, adParamInput,8, 0.05)
0
 
mcrmgAuthor Commented:
Hi,

I tried your code:
Set pm = oCmd.CreateParameter("@abc", adDouble, adParamInput,8, 0.05)

It still shows:

Procedure 'st_sp' expects parameter '@abc', which was not supplied.
0
 
Anthony PerkinsCommented:
Try posting all the code including the Connection and Command definition, as well as let us know whether the stored procedure has changed since the last one you posted.
0
 
joeposter649Commented:
You could also try printing out the parameters before you execute the command...
For Each objParameter In oCmd.Parameters
   response.write objParameter.name & ":" & objParameter.value& "<br>"
Next

Or you could try setting oCmd.NamedParameters = true
0
 
mcrmgAuthor Commented:
@joeposter649
If I use adinteger, the parameter can be printed out, but if I use adDouble, not even name of the parameters can be printed out.


@acperkins
I did not change SP: Please have a look at the following:
ALTER     PROCEDURE dbo.st_sp
@ABC float, @XYZ float

AS

SET NOCOUNT ON

SELECT * FROM Table1
WHERE ( (VALUE >= isNULL(@ABC, VALUE)) AND (VALUE < isNULL(@XYZ, VALUE + 1)) )

***********************************
oCmd.ActiveConnection = oConn
oCmd.CommandText = "st_sp"
oCmd.CommandType = 4
***********************************

'Create the oConneciton object
Set oConn = CreateObject("ADODB.Connection")

'Create the oConnection string
oConn.Provider = "SQLOLEDB.1"


oConn.Properties("Data Source").Value = "(local)"
oConn.Properties("Initial Catalog").Value = "my_DB"
oConn.Properties("User ID") = "sa"
oConn.Properties("Password") = "password"

'Open the oConnection
oConn.Open
0
 
joeposter649Commented:
Try setting all the oConn properties and opening it before setting up the oCmd stuff.
0
 
mcrmgAuthor Commented:
I am sorry for the confusion, but yes, I did do it in order.........  Thx
0
 
joeposter649Commented:
Perhaps you should be using adNumeric.
Try doing a oCmd.Parameters.Refresh then write out the parameter types...
For Each objParameter In oCmd.Parameters
   response.write objParameter.name & ":" & objParameter.type & "<br>"
Next
0
 
mcrmgAuthor Commented:
parameter types says "3"  Thx
0
 
joeposter649Commented:
3 is adInteger.  Are you picking up a version of your sp that passes an integer?  Try specifying the schema in your commandtext...
oCmd.CommandText = "dbo.st_sp"
0
 
mcrmgAuthor Commented:
I tried that, still the same.

Set pm = oCmd.CreateParameter("@abc", adDouble, adParamInput,, 0.05)
The parameter says 3, output of parameter is empty

Set pm = oCmd.CreateParameter("@Marginmin", adInteger, adParamInput,, 0.05)
The parameter says 3, output of parameter is 0

thx
0
 
markofsotonCommented:
you need to set the scale and precision of floating point numbers either using decimal or numeric, this scaling should match a scale specified in the column settings


    .Parameters.Append .CreateParameter("@myDecimal", adDecimal, adParamInput, 9, 765.5432321)
    With .Parameters.Item("@myDecimal")
        .Precision = 10
        .NumericScale = 7
    End With
   
    Set prm = .CreateParameter("@myNumeric", adNumeric, adParamInput, 5, 432.6544)
    prm.Precision = 7
    prm.NumericScale = 4
    .Parameters.Append prm
   
    Set prm = Nothing

I can recommend this page http://www.davidpenton.com/testsite/scratch/adodb.command3.asp
0
 
Anthony PerkinsCommented:
>>you need to set the scale and precision of floating point numbers either using decimal or numeric<<
For better or worse, the questioner is not using numeric (decimal) data type but rather float, which does not require setting the Precision and/or NumericScale properties.
0
 
mcrmgAuthor Commented:
>..............data type but rather float.............

Does it mean that sp can take parameter as normal way?  Thx
0
 
Anthony PerkinsCommented:
>>Does it mean that sp can take parameter as normal way? <<
Not following you here.
0
 
mcrmgAuthor Commented:
okay, I have tried different combinations, it looks like the parameter will only works when setting to adInteger,
not adNumeric/adDouble.....did I miss something? Please let me know what info you need in order for you to
direct me.  Thx
0
 
Anthony PerkinsCommented:
>>did I miss something?<<
Please take a look a this code that I have tested successfully with your Stored Procedure (with float parameters):

<%
Const adCmdStoredProc = 4
Const adDouble = 5
Const adParamInput = 1

Dim cn, cmd, rs
Set cn = CreateObject("ADODB.Connection")
cn.Open MY_CONN

Set cmd = CreateObject("ADODB.Command")
With cmd
   Set .ActiveConnection = cn
   .CommandText = "st_sp"
   .CommandType = 4
      .Parameters.Append .CreateParameter("@ABC", adDouble, adParamInput, 0, 12345.678901)
      .Parameters.Append .CreateParameter("@XYZ", adDouble, adParamInput, 0, 10987.654321)
      Set rs = .Execute
End With
Set cmd = Nothing

With rs
      Do While Not .EOF
            ' Show some arbitrary columns to make sure the recordset has values
            Response.write .Fields(0).Value & "|" & .Fields(1).Value & "<br/>"
            .MoveNext
      Loop
      .Close
End With
Set rs = Nothing

cn.Close
Set cn = Nothing

%>

Please compare to your code and amend appropriately.

I trust this will clear the problem up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now