Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

TSQL decimal input parameter when doing a select statement problem

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
juliandormon
Asked:
juliandormon
1 Solution
 
sammySeltzerCommented:
what error are you getting?

I bet you are using dreamweaver!
0
 
cyberkiwiCommented:
     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
 
cyberkiwiCommented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
juliandormonAuthor Commented:
Sorry that was actually set to 8 - but same error
0
 
juliandormonAuthor Commented:
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
 
cyberkiwiCommented:
If you want a recordset, you need to use Open, not Execute

      set RS_ProductBundle = sp_select_product_bundle.Open
0
 
juliandormonAuthor Commented:
Is this relating to decimal? I've got over 100 stored procedures which return recordsets and they all use Execute without a problem.
0
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
juliandormonAuthor Commented:
It runs fun from within SSMS.
Do i have to set precision and scale for a select?
0
 
juliandormonAuthor Commented:
I got it to work by switching to a money datatype - thanks!
0
 
Anthony PerkinsCommented:
>>Do i have to set precision and scale for a select?<<
Whenever you use decimal data type you must set precision and scale.
0
 
juliandormonAuthor Commented:
n
0
 
ModCorlEEoneCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now