Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL decimal input parameter when doing a select statement problem

Posted on 2010-11-07
15
Medium Priority
?
368 Views
Last Modified: 2012-08-13
I'm trying to create a recordset from within an ASP page which should return some value when I pass in a decimal. I am getting some bizarre error when doing so. I imagine this may have to do with setting the parameter correctly. The parameter is TotalPrice below.

Can you please advise:

set sp_select_product_bundle = Server.CreateObject("ADODB.Command")
      sp_select_product_bundle.ActiveConnection = MY_SQL_STRING
      sp_select_product_bundle.CommandText = "SQL123.sp_bbb_select_product_bundle"
      sp_select_product_bundle.CommandType = 4
      sp_select_product_bundle.CommandTimeout = 0
      sp_select_product_bundle.Prepared = true
      sp_select_product_bundle.Parameters.Append sp_select_product_bundle.CreateParameter("@RETURN_VALUE", 3, 4)
      sp_select_product_bundle.Parameters.Append sp_select_product_bundle.CreateParameter("@TotalPrice", 14, 1,2,sp_select_product_bundle__TotalPrice)
      sp_select_product_bundle.Parameters.Append sp_select_product_bundle.CreateParameter("@DomainPurchased", 11, 1,1,sp_select_product_bundle__DomainPurchased)
      set RS_ProductBundle = sp_select_product_bundle.Execute
0
Comment
Question by:juliandormon
[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
15 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34080972
what error are you getting?

I bet you are using dreamweaver!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081032
     sp_select_product_bundle.Parameters.Append sp_select_product_bundle.CreateParameter("@TotalPrice", 14, 1,2,sp_select_product_bundle__TotalPrice)

Wouldn't 2 be too small a size for something like "total" price?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081035
http://www.w3schools.com/ado/ado_ref_parameter.asp

Size: Sets or returns the maximum size in bytes or characters of a value in a Parameter object

You have allowed a sum total of 2 bytes...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:juliandormon
ID: 34081105
Sorry that was actually set to 8 - but same error
0
 

Author Comment

by:juliandormon
ID: 34081117
Set it back to 8 and still get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081121
If you want a recordset, you need to use Open, not Execute

      set RS_ProductBundle = sp_select_product_bundle.Open
0
 

Author Comment

by:juliandormon
ID: 34081129
Is this relating to decimal? I've got over 100 stored procedures which return recordsets and they all use Execute without a problem.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081130
Next, verify that the SP runs correctly in SSMS

exec SQL123.sp_bbb_select_product_bundle 123.45, 1

(fake values for total and purchased)

If that also works, then try changing this to smallint.  I have trouble sometimes with bit/boolean fields.

Dim purchased
If sp_select_product_bundle__DomainPurchased then purchased = 1 else purchased = 0
      sp_select_product_bundle.Parameters.Append sp_select_product_bundle.CreateParameter("@DomainPurchased", 2, 1,1, purchased)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081136
When running this

exec SQL123.sp_bbb_select_product_bundle 123.45, 1

Check how many resultsets are generated by the SP, including informational messages like
(3) rows affected.  If it generates multiple of these, you need to add SET NOCOUNT ON in the SP.
0
 

Author Comment

by:juliandormon
ID: 34081155
It runs fun from within SSMS.
Do i have to set precision and scale for a select?
0
 

Accepted Solution

by:
juliandormon earned 0 total points
ID: 34081211
I got it to work by switching to a money datatype - thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34081523
>>Do i have to set precision and scale for a select?<<
Whenever you use decimal data type you must set precision and scale.
0
 

Author Comment

by:juliandormon
ID: 35439331
n
0
 

Expert Comment

by:ModCorlEEone
ID: 35465196
juliandormon,
 
I am starting the self-close process on this question because it appears to me that you have answered your own question.
 
ModCorlEEone
Community Support Moderator
0

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.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

715 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