Solved

CreateParameter question

Posted on 2004-09-03
29
998 Views
Last Modified: 2012-05-05
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
Comment
Question by:mcrmg
  • 13
  • 7
  • 7
  • +2
29 Comments
 
LVL 7

Expert Comment

by:vnvk
ID: 11972140
adInteger
0
 

Author Comment

by:mcrmg
ID: 11972444
so, it will take something like 1.22 or 0.88?thx
0
 

Author Comment

by:mcrmg
ID: 11973243
Can I pass float to sp?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 15

Assisted Solution

by:joeposter649
joeposter649 earned 200 total points
ID: 11973692
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
 

Author Comment

by:mcrmg
ID: 11973783
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11974146
>>and data type in sp I use float, is that ok?
because it gives an error "@abc not supplied"<<
Post your Stored Procedure.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11974152
Also, post the relevant ASP code.
0
 

Author Comment

by:mcrmg
ID: 11974205
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11974331
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
 

Author Comment

by:mcrmg
ID: 11974378
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
 
LVL 15

Assisted Solution

by:joeposter649
joeposter649 earned 200 total points
ID: 11974554
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
 

Author Comment

by:mcrmg
ID: 11975430
Strange, I tried adding cdbl() and/or length....does not work.......
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11976079
What did you try for a length?  Try this...
Set pm = oCmd.CreateParameter("@abc", adDouble, adParamInput,8, 0.05)
0
 

Author Comment

by:mcrmg
ID: 11978408
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11980190
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
 
LVL 15

Expert Comment

by:joeposter649
ID: 11981579
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
 

Author Comment

by:mcrmg
ID: 11982573
@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
 
LVL 15

Expert Comment

by:joeposter649
ID: 11983409
Try setting all the oConn properties and opening it before setting up the oCmd stuff.
0
 

Author Comment

by:mcrmg
ID: 11984569
I am sorry for the confusion, but yes, I did do it in order.........  Thx
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11985442
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
 

Author Comment

by:mcrmg
ID: 11986326
parameter types says "3"  Thx
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 11986623
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
 

Author Comment

by:mcrmg
ID: 11989568
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
 

Expert Comment

by:markofsoton
ID: 11997646
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11998421
>>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
 

Author Comment

by:mcrmg
ID: 11998541
>..............data type but rather float.............

Does it mean that sp can take parameter as normal way?  Thx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11998792
>>Does it mean that sp can take parameter as normal way? <<
Not following you here.
0
 

Author Comment

by:mcrmg
ID: 11998834
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 11999202
>>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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to hide hopscotch tour popup page reload? 2 80
File download script VBSCRIPT fail on download PPT files 2 52
FileUp - Classic ASP 5 17
Save ms data to server side. 19 44
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question