Solved

CreateParameter question

Posted on 2004-09-03
29
976 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
Comment Utility
adInteger
0
 

Author Comment

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

Author Comment

by:mcrmg
Comment Utility
Can I pass float to sp?
0
 
LVL 15

Assisted Solution

by:joeposter649
joeposter649 earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Also, post the relevant ASP code.
0
 

Author Comment

by:mcrmg
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Strange, I tried adding cdbl() and/or length....does not work.......
0
 
LVL 15

Expert Comment

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

Author Comment

by:mcrmg
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
Try setting all the oConn properties and opening it before setting up the oCmd stuff.
0
 

Author Comment

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

Expert Comment

by:joeposter649
Comment Utility
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
Comment Utility
parameter types says "3"  Thx
0
 
LVL 15

Expert Comment

by:joeposter649
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
>..............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
Comment Utility
>>Does it mean that sp can take parameter as normal way? <<
Not following you here.
0
 

Author Comment

by:mcrmg
Comment Utility
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
Comment Utility
>>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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now