juliandormon
asked on
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.A ctiveConne ction = MY_SQL_STRING
sp_select_product_bundle.C ommandText = "SQL123.sp_bbb_select_prod uct_bundle "
sp_select_product_bundle.C ommandType = 4
sp_select_product_bundle.C ommandTime out = 0
sp_select_product_bundle.P repared = true
sp_select_product_bundle.P arameters. Append sp_select_product_bundle.C reateParam eter("@RET URN_VALUE" , 3, 4)
sp_select_product_bundle.P arameters. Append sp_select_product_bundle.C reateParam eter("@Tot alPrice", 14, 1,2,sp_select_product_bund le__TotalP rice)
sp_select_product_bundle.P arameters. Append sp_select_product_bundle.C reateParam eter("@Dom ainPurchas ed", 11, 1,1,sp_select_product_bund le__Domain Purchased)
set RS_ProductBundle = sp_select_product_bundle.E xecute
Can you please advise:
set sp_select_product_bundle = Server.CreateObject("ADODB
sp_select_product_bundle.A
sp_select_product_bundle.C
sp_select_product_bundle.C
sp_select_product_bundle.C
sp_select_product_bundle.P
sp_select_product_bundle.P
sp_select_product_bundle.P
sp_select_product_bundle.P
set RS_ProductBundle = sp_select_product_bundle.E
sp_select_product_bundle.P arameters. Append sp_select_product_bundle.C reateParam eter("@Tot alPrice", 14, 1,2,sp_select_product_bund le__TotalP rice)
Wouldn't 2 be too small a size for something like "total" price?
Wouldn't 2 be too small a size for something like "total" price?
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...
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...
ASKER
Sorry that was actually set to 8 - but same error
ASKER
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.
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.
If you want a recordset, you need to use Open, not Execute
set RS_ProductBundle = sp_select_product_bundle.O pen
set RS_ProductBundle = sp_select_product_bundle.O
ASKER
Is this relating to decimal? I've got over 100 stored procedures which return recordsets and they all use Execute without a problem.
Next, verify that the SP runs correctly in SSMS
exec SQL123.sp_bbb_select_produ ct_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__ DomainPurc hased then purchased = 1 else purchased = 0
sp_select_product_bundle.P arameters. Append sp_select_product_bundle.C reateParam eter("@Dom ainPurchas ed", 2, 1,1, purchased)
exec SQL123.sp_bbb_select_produ
(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__
sp_select_product_bundle.P
When running this
exec SQL123.sp_bbb_select_produ ct_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.
exec SQL123.sp_bbb_select_produ
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.
ASKER
It runs fun from within SSMS.
Do i have to set precision and scale for a select?
Do i have to set precision and scale for a select?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Do i have to set precision and scale for a select?<<
Whenever you use decimal data type you must set precision and scale.
Whenever you use decimal data type you must set precision and scale.
ASKER
n
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
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
I bet you are using dreamweaver!