Solved

CreateParameter question

Posted on 2004-09-03
29
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Downside of adding characters set in ASP pages 6 41
Call a function within the ASP code 4 32
ASP AND XML 3 41
Pass SQL to JSON. Page is in classic ASP and using Jquery 4 57
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

732 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