The precision is invalid. -- Stored procedure,..

[=?Utf-8?B?R1ROMTcwNzc3?=]

help, I'm trying to access a stored procedure The stored procedure works fine
when run from the server) the code is below -

CREATE PROCEDURE updatepricing
@siteid INT,
@siteid2 INT,
@siteid3 INT,
@siteid4 INT,
@siteid5 INT,
@siteid6 INT,
@siteid7 INT,
@siteid8 INT,
@7dayrate DECIMAL(7,2),
@14dayrate DECIMAL(7,2),
@21dayrate DECIMAL(7,2),
@28dayrate DECIMAL(7,2),
@reguseractive CHAR(1),
@reguser5active CHAR(1),
@reguser10activ e CHAR(1),
@multiuseractiv e CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @7dayrate
WHERE JBATSiteID = @siteid and JBATValue = '7'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @14dayrate
WHERE JBATSiteID = @siteid2 and JBATValue = '14'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @21dayrate
WHERE JBATSiteID = @siteid3 and JBATValue = '21'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @28dayrate
WHERE JBATSiteID = @siteid4 and JBATValue = '28'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @reguserrate, JBATActive = @reguseractive
WHERE JBATSiteID = @siteid5 and JBATValue = 'reguser'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @reguser5rate, JBATActive = @reguser5active
WHERE JBATSiteID = @siteid6 and JBATValue = 'reguser5'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @reguser10rate, JBATActive = @reguser10activ e
WHERE JBATSiteID = @siteid7 and JBATValue = 'reguser10'
UPDATE dbo.JBAccountTy pe
SET JBATPrice = @multiuserrate, JBATActive = @multiuseractiv e
WHERE JBATSiteID = @siteid8 and JBATValue = 'multiuser'
GO

However when i try and run this from an ASP page with the following code --


<%

Dim updatepricingCo mmand__siteid
updatepricingCo mmand__siteid = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid =
Session("SITEID ")

Dim updatepricingCo mmand__siteid2
updatepricingCo mmand__siteid2 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid2 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid3
updatepricingCo mmand__siteid3 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid3 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid4
updatepricingCo mmand__siteid4 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid4 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid5
updatepricingCo mmand__siteid5 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid5 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid6
updatepricingCo mmand__siteid6 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid6 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid7
updatepricingCo mmand__siteid7 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid7 =
Session("SITEID ")

Dim updatepricingCo mmand__siteid8
updatepricingCo mmand__siteid8 = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid8 =
Session("SITEID ")

Dim updatepricingCo mmand__7dayrate
updatepricingCo mmand__7dayrate = "0"
if(Request.Quer yString("day7") <"") then updatepricingCo mmand__7dayrate =
Request.QuerySt ring("day7")

Dim updatepricingCo mmand__14dayrat e
updatepricingCo mmand__14dayrat e = "0"
if(Request.Quer yString("day14" ) <"") then updatepricingCo mmand__14dayrat e
= Request.QuerySt ring("day14")

Dim updatepricingCo mmand__21dayrat e
updatepricingCo mmand__21dayrat e = "0"
if(Request.Quer yString("day21" ) <"") then updatepricingCo mmand__21dayrat e
= Request.QuerySt ring("day21")

Dim updatepricingCo mmand__28dayrat e
updatepricingCo mmand__28dayrat e = "0"
if(Request.Quer yString("day28" ) <"") then updatepricingCo mmand__28dayrat e
= Request.QuerySt ring("day28")

Dim updatepricingCo mmand__regusera ctive
updatepricingCo mmand__regusera ctive = "n"
if(Request.Quer yString("ru") <"") then updatepricingCo mmand__regusera ctive
= Request.QuerySt ring("ru")

Dim updatepricingCo mmand__reguser5 active
updatepricingCo mmand__reguser5 active = "n"
if(Request.Quer yString("ru5") <"") then
updatepricingCo mmand__reguser5 active = Request.QuerySt ring("ru5")

Dim updatepricingCo mmand__reguser1 0active
updatepricingCo mmand__reguser1 0active = "n"
if(Request.Quer yString("ru10") <"") then
updatepricingCo mmand__reguser1 0active = Request.QuerySt ring("ru10")

Dim updatepricingCo mmand__multiuse ractive
updatepricingCo mmand__multiuse ractive = "n"
if(Request.Quer yString("mu") <"") then
updatepricingCo mmand__multiuse ractive = Request.QuerySt ring("mu")

Dim updatepricingCo mmand__reguserr ate
updatepricingCo mmand__reguserr ate = "0"
if(Request.Quer yString("ruserc harge") <"") then
updatepricingCo mmand__reguserr ate = Request.QuerySt ring("ruserchar ge")

Dim updatepricingCo mmand__reguser5 rate
updatepricingCo mmand__reguser5 rate = "0"
if(Request.Quer yString("ruser5 charge") <"") then
updatepricingCo mmand__reguser5 rate = Request.QuerySt ring("ruser5cha rge")

Dim updatepricingCo mmand__reguser1 0rate
updatepricingCo mmand__reguser1 0rate = "0"
if(Request.Quer yString("ruser1 0charge") <"") then
updatepricingCo mmand__reguser1 0rate = Request.QuerySt ring("ruser10ch arge")

Dim updatepricingCo mmand__multiuse rrate
updatepricingCo mmand__multiuse rrate = "0"
if(Request.Quer yString("muserc harge") <"") then
updatepricingCo mmand__multiuse rrate = Request.QuerySt ring("muserchar ge")

%>
<%

set updatepricingco mmand = Server.CreateOb ject("ADODB.Com mand")
updatepricingco mmand.ActiveCon nection = MM_recruta2_STR ING
updatepricingco mmand.CommandTe xt = "dbo.updatepric ing"
updatepricingco mmand.CommandTy pe = 4
updatepricingco mmand.CommandTi meout = 0
updatepricingco mmand.Prepared = true
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@RETURN _VALUE", 3, 4)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid ", 3,
1,5,updateprici ngcommand__site id)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 2", 3,
1,5,updateprici ngcommand__site id2)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 3", 3,
1,5,updateprici ngcommand__site id3)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 4", 3,
1,5,updateprici ngcommand__site id4)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 5", 3,
1,5,updateprici ngcommand__site id5)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 6", 3,
1,5,updateprici ngcommand__site id6)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 7", 3,
1,5,updateprici ngcommand__site id7)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@siteid 8", 3,
1,5,updateprici ngcommand__site id8)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@7dayra te", 14,
1,7,2,updatepri cingcommand__7d ayrate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@14dayr ate", 14,
1,7,2,updatepri cingcommand__14 dayrate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@21dayr ate", 14,
1,7,2,updatepri cingcommand__21 dayrate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@28dayr ate", 14,
1,7,2,updatepri cingcommand__28 dayrate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse ractive", 129,
1,1,updateprici ngcommand__regu seractive)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse r5active", 129,
1,1,updateprici ngcommand__regu ser5active)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse r10active", 129,
1,1,updateprici ngcommand__regu ser10active)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@multiu seractive", 129,
1,1,updateprici ngcommand__mult iuseractive)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse rrate", 14,
1,7,2,updatepri cingcommand__re guserrate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse r5rate", 14,
1,7,2,updatepri cingcommand__re guser5rate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@reguse r10rate", 14,
1,7,2,updatepri cingcommand__re guser10rate)
updatepricingco mmand.Parameter s.Append
updatepricingco mmand.CreatePar ameter("@multiu serrate", 14,
1,7,2,updatepri cingcommand__mu ltiuserrate)
updatepricingco mmand.Execute()

%>

I get the following error --


Microsoft OLE DB Provider for SQL Server error '80004005'
The precision is invalid.
/admin/afterreviewupda tepricing.asp, line 115


I think this has something to do with the decimal values??? but an really
stuck...

Appreciate any help you can give..

Thanks

[Bob Barrows [MVP]]

Re: The precision is invalid. -- Stored procedure,..

GTN170777 wrote:

You're right, I've deleted the variables @siteid2 - @siteid8, so now
my SP reads -
>
CREATE PROCEDURE updatepricing
@siteid INT,
@7dayrate DECIMAL(7,2),
@14dayrate DECIMAL(7,2),
@21dayrate DECIMAL(7,2),
@28dayrate DECIMAL(7,2),
@reguseractive CHAR(1),
@reguser5active CHAR(1),
@reguser10activ e CHAR(1),
@multiuseractiv e CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS
>
I'm still a little confused though -- (i've uploaded the following
code)
>
Dim conn
set conn = createobject("a dodb.connection ")
conn.open MM_recruta2_STR ING
conn.updatepric ing updatepricingCo mmand__siteid, _
updatepricingCo mmand__7dayrate , _
updatepricingCo mmand__14dayrat e, _
updatepricingCo mmand__21dayrat e, _
updatepricingCo mmand__28dayrat e, _
updatepricingCo mmand__regusera ctive, _
updatepricingCo mmand__reguser5 active, _
updatepricingCo mmand__reguser1 0active, _
updatepricingCo mmand__multiuse ractive, _
updatepricingCo mmand__reguserr ate, _
updatepricingCo mmand__reguser5 rate, _
updatepricingCo mmand__reguser1 0rate, _
updatepricingCo mmand__multiuse rrate

****

updatepricingco mmand.Execute()

***
Why did you put that there? That did not appear in my code sample did
it? The procedure has already been executed at this point ... without an
explicit command object.

>
Should there be anything after the "updatepricingC ommand__7dayrat e,
_" to "updatepricingC ommand__multius errate" lines?
because I can't see the --
>
""With updatepricingco mmand

>Set parm = .CreateParamete r("@7dayrate" , 14,1)
>parm.Precisi on = 7
>parm.NumericSc ale = 2
>..Parameters.A ppend parm
>parm.value = updatepricingco mmand__7dayrate
>End With""

>

That part is only needed if you wish to continue using an explicit
command object. I was suggesting an alternative technique that did not
require all those CreateParameter calls.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[=?Utf-8?B?R1ROMTcwNzc3?=]

Re: The precision is invalid. -- Stored procedure,..

IGNORE that last comment Bob,

Again you are a star, thank you so much - I think i've got it working...

Cheers my friend

"Bob Barrows [MVP]" wrote:

GTN170777 wrote:

You're right, I've deleted the variables @siteid2 - @siteid8, so now
my SP reads -

CREATE PROCEDURE updatepricing
@siteid INT,
@7dayrate DECIMAL(7,2),
@14dayrate DECIMAL(7,2),
@21dayrate DECIMAL(7,2),
@28dayrate DECIMAL(7,2),
@reguseractive CHAR(1),
@reguser5active CHAR(1),
@reguser10activ e CHAR(1),
@multiuseractiv e CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS

I'm still a little confused though -- (i've uploaded the following
code)

Dim conn
set conn = createobject("a dodb.connection ")
conn.open MM_recruta2_STR ING
conn.updatepric ing updatepricingCo mmand__siteid, _
updatepricingCo mmand__7dayrate , _
updatepricingCo mmand__14dayrat e, _
updatepricingCo mmand__21dayrat e, _
updatepricingCo mmand__28dayrat e, _
updatepricingCo mmand__regusera ctive, _
updatepricingCo mmand__reguser5 active, _
updatepricingCo mmand__reguser1 0active, _
updatepricingCo mmand__multiuse ractive, _
updatepricingCo mmand__reguserr ate, _
updatepricingCo mmand__reguser5 rate, _
updatepricingCo mmand__reguser1 0rate, _
updatepricingCo mmand__multiuse rrate

>
****

updatepricingco mmand.Execute()

***
Why did you put that there? That did not appear in my code sample did
it? The procedure has already been executed at this point ... without an
explicit command object.
>

Should there be anything after the "updatepricingC ommand__7dayrat e,
_" to "updatepricingC ommand__multius errate" lines?
because I can't see the --

""With updatepricingco mmand

Set parm = .CreateParamete r("@7dayrate" , 14,1)
parm.Precision = 7
parm.NumericSca le = 2
..Parameters.Ap pend parm
parm.value = updatepricingco mmand__7dayrate
End With""

>
That part is only needed if you wish to continue using an explicit
command object. I was suggesting an alternative technique that did not
require all those CreateParameter calls.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>
>

[=?Utf-8?B?R1ROMTcwNzc3?=]

Re: The precision is invalid. -- Stored procedure,..

Thanks Bob, done that, it through in a few errors that i tried to fix, hence
code now looks like -

<%

Dim updatepricingCo mmand__siteid
updatepricingCo mmand__siteid = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid =
CLng(Session("S ITEID"))

Dim updatepricingCo mmand__7dayrate
updatepricingCo mmand__7dayrate = "0"
if(Request.Quer yString("day7") <"") then
updatepricingCo mmand__7dayrate = CSng(Request.Qu eryString("day7 "))

Dim updatepricingCo mmand__14dayrat e
updatepricingCo mmand__14dayrat e = "0"
if(Request.Quer yString("day14" ) <"") then
updatepricingCo mmand__14dayrat e = CSng(Request.Qu eryString("day1 4"))

Dim updatepricingCo mmand__21dayrat e
updatepricingCo mmand__21dayrat e = "0"
if(Request.Quer yString("day21" ) <"") then
updatepricingCo mmand__21dayrat e = CSng(Request.Qu eryString("day2 1"))

Dim updatepricingCo mmand__28dayrat e
updatepricingCo mmand__28dayrat e = "0"
if(Request.Quer yString("day28" ) <"") then
updatepricingCo mmand__28dayrat e = CSng(Request.Qu eryString("day2 8"))

Dim updatepricingCo mmand__regusera ctive
updatepricingCo mmand__regusera ctive = "n"
if(Request.Quer yString("ru") <"") then
updatepricingCo mmand__regusera ctive = Request.QuerySt ring("ru")

Dim updatepricingCo mmand__reguser5 active
updatepricingCo mmand__reguser5 active = "n"
if(Request.Quer yString("ru5") <"") then
updatepricingCo mmand__reguser5 active = Request.QuerySt ring("ru5")

Dim updatepricingCo mmand__reguser1 0active
updatepricingCo mmand__reguser1 0active = "n"
if(Request.Quer yString("ru10") <"") then
updatepricingCo mmand__reguser1 0active = Request.QuerySt ring("ru10")

Dim updatepricingCo mmand__multiuse ractive
updatepricingCo mmand__multiuse ractive = "n"
if(Request.Quer yString("mu") <"") then
updatepricingCo mmand__multiuse ractive = Request.QuerySt ring("mu")

Dim updatepricingCo mmand__reguserr ate
updatepricingCo mmand__reguserr ate = "0"
if(Request.Quer yString("ruserc harge") <"") then
updatepricingCo mmand__reguserr ate = CSng(Request.Qu eryString("ruse rcharge"))

Dim updatepricingCo mmand__reguser5 rate
updatepricingCo mmand__reguser5 rate = "0"
if(Request.Quer yString("ruser5 charge") <"") then
updatepricingCo mmand__reguser5 rate = CSng(Request.Qu eryString("ruse r5charge"))

Dim updatepricingCo mmand__reguser1 0rate
updatepricingCo mmand__reguser1 0rate = "0"
if(Request.Quer yString("ruser1 0charge") <"") then
updatepricingCo mmand__reguser1 0rate =
CSng(Request.Qu eryString("ruse r10charge"))

Dim updatepricingCo mmand__multiuse rrate
updatepricingCo mmand__multiuse rrate = "0"
if(Request.Quer yString("muserc harge") <"") then
updatepricingCo mmand__multiuse rrate = CSng(Request.Qu eryString("muse rcharge"))

Dim conn
set conn = createobject("a dodb.connection ")
conn.open MM_recruta2_STR ING
conn.updatepric ing updatepricingCo mmand__siteid, _
updatepricingCo mmand__7dayrate , _
updatepricingCo mmand__14dayrat e, _
updatepricingCo mmand__21dayrat e, _
updatepricingCo mmand__28dayrat e, _
updatepricingCo mmand__regusera ctive, _
updatepricingCo mmand__reguser5 active, _
updatepricingCo mmand__reguser1 0active, _
updatepricingCo mmand__multiuse ractive, _
updatepricingCo mmand__reguserr ate, _
updatepricingCo mmand__reguser5 rate, _
updatepricingCo mmand__reguser1 0rate, _
updatepricingCo mmand__multiuse rrate
%>

The Errors being the %at the end and there was third ) after
"CSng(Request.Q ueryString("day 7"))" which i removed. Also the top bit which
reads -

<%

Dim updatepricingCo mmand__siteid
updatepricingCo mmand__siteid = "0"
if(Session("SIT EID") <"") then updatepricingCo mmand__siteid =
CLng(Session("S ITEID"))

should this read -

CSng(Session("S ITEID")) instead of CLng(Session("S ITEID")) ??

Now i get the following error --

"Microsoft VBScript compilation error '800a03f6'

Expected 'End'

/admin/afterreviewupda tepricing.asp, line 84 "

Sorry and thanks G


"Bob Barrows [MVP]" wrote:

GTN170777 wrote:

You're right, I've deleted the variables @siteid2 - @siteid8, so now
my SP reads -

CREATE PROCEDURE updatepricing
@siteid INT,
@7dayrate DECIMAL(7,2),
@14dayrate DECIMAL(7,2),
@21dayrate DECIMAL(7,2),
@28dayrate DECIMAL(7,2),
@reguseractive CHAR(1),
@reguser5active CHAR(1),
@reguser10activ e CHAR(1),
@multiuseractiv e CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS

I'm still a little confused though -- (i've uploaded the following
code)

Dim conn
set conn = createobject("a dodb.connection ")
conn.open MM_recruta2_STR ING
conn.updatepric ing updatepricingCo mmand__siteid, _
updatepricingCo mmand__7dayrate , _
updatepricingCo mmand__14dayrat e, _
updatepricingCo mmand__21dayrat e, _
updatepricingCo mmand__28dayrat e, _
updatepricingCo mmand__regusera ctive, _
updatepricingCo mmand__reguser5 active, _
updatepricingCo mmand__reguser1 0active, _
updatepricingCo mmand__multiuse ractive, _
updatepricingCo mmand__reguserr ate, _
updatepricingCo mmand__reguser5 rate, _
updatepricingCo mmand__reguser1 0rate, _
updatepricingCo mmand__multiuse rrate

>
****

updatepricingco mmand.Execute()

***
Why did you put that there? That did not appear in my code sample did
it? The procedure has already been executed at this point ... without an
explicit command object.
>

Should there be anything after the "updatepricingC ommand__7dayrat e,
_" to "updatepricingC ommand__multius errate" lines?
because I can't see the --

""With updatepricingco mmand

Set parm = .CreateParamete r("@7dayrate" , 14,1)
parm.Precision = 7
parm.NumericSca le = 2
..Parameters.Ap pend parm
parm.value = updatepricingco mmand__7dayrate
End With""

>
That part is only needed if you wish to continue using an explicit
command object. I was suggesting an alternative technique that did not
require all those CreateParameter calls.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
>
>
>