Solved

CreateParameter question

Posted on 2004-09-03
29
989 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

862 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

25 Experts available now in Live!

Get 1:1 Help Now