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

G

GTN170777

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),
@reguser10active CHAR(1),
@multiuseractive CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS
UPDATE dbo.JBAccountType
SET JBATPrice = @7dayrate
WHERE JBATSiteID = @siteid and JBATValue = '7'
UPDATE dbo.JBAccountType
SET JBATPrice = @14dayrate
WHERE JBATSiteID = @siteid2 and JBATValue = '14'
UPDATE dbo.JBAccountType
SET JBATPrice = @21dayrate
WHERE JBATSiteID = @siteid3 and JBATValue = '21'
UPDATE dbo.JBAccountType
SET JBATPrice = @28dayrate
WHERE JBATSiteID = @siteid4 and JBATValue = '28'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguserrate, JBATActive = @reguseractive
WHERE JBATSiteID = @siteid5 and JBATValue = 'reguser'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguser5rate, JBATActive = @reguser5active
WHERE JBATSiteID = @siteid6 and JBATValue = 'reguser5'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguser10rate, JBATActive = @reguser10active
WHERE JBATSiteID = @siteid7 and JBATValue = 'reguser10'
UPDATE dbo.JBAccountType
SET JBATPrice = @multiuserrate, JBATActive = @multiuseractive
WHERE JBATSiteID = @siteid8 and JBATValue = 'multiuser'
GO

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


<%

Dim updatepricingCommand__siteid
updatepricingCommand__siteid = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid =
Session("SITEID")

Dim updatepricingCommand__siteid2
updatepricingCommand__siteid2 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid2 =
Session("SITEID")

Dim updatepricingCommand__siteid3
updatepricingCommand__siteid3 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid3 =
Session("SITEID")

Dim updatepricingCommand__siteid4
updatepricingCommand__siteid4 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid4 =
Session("SITEID")

Dim updatepricingCommand__siteid5
updatepricingCommand__siteid5 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid5 =
Session("SITEID")

Dim updatepricingCommand__siteid6
updatepricingCommand__siteid6 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid6 =
Session("SITEID")

Dim updatepricingCommand__siteid7
updatepricingCommand__siteid7 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid7 =
Session("SITEID")

Dim updatepricingCommand__siteid8
updatepricingCommand__siteid8 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid8 =
Session("SITEID")

Dim updatepricingCommand__7dayrate
updatepricingCommand__7dayrate = "0"
if(Request.QueryString("day7") <> "") then updatepricingCommand__7dayrate =
Request.QueryString("day7")

Dim updatepricingCommand__14dayrate
updatepricingCommand__14dayrate = "0"
if(Request.QueryString("day14") <> "") then updatepricingCommand__14dayrate
= Request.QueryString("day14")

Dim updatepricingCommand__21dayrate
updatepricingCommand__21dayrate = "0"
if(Request.QueryString("day21") <> "") then updatepricingCommand__21dayrate
= Request.QueryString("day21")

Dim updatepricingCommand__28dayrate
updatepricingCommand__28dayrate = "0"
if(Request.QueryString("day28") <> "") then updatepricingCommand__28dayrate
= Request.QueryString("day28")

Dim updatepricingCommand__reguseractive
updatepricingCommand__reguseractive = "n"
if(Request.QueryString("ru") <> "") then updatepricingCommand__reguseractive
= Request.QueryString("ru")

Dim updatepricingCommand__reguser5active
updatepricingCommand__reguser5active = "n"
if(Request.QueryString("ru5") <> "") then
updatepricingCommand__reguser5active = Request.QueryString("ru5")

Dim updatepricingCommand__reguser10active
updatepricingCommand__reguser10active = "n"
if(Request.QueryString("ru10") <> "") then
updatepricingCommand__reguser10active = Request.QueryString("ru10")

Dim updatepricingCommand__multiuseractive
updatepricingCommand__multiuseractive = "n"
if(Request.QueryString("mu") <> "") then
updatepricingCommand__multiuseractive = Request.QueryString("mu")

Dim updatepricingCommand__reguserrate
updatepricingCommand__reguserrate = "0"
if(Request.QueryString("rusercharge") <> "") then
updatepricingCommand__reguserrate = Request.QueryString("rusercharge")

Dim updatepricingCommand__reguser5rate
updatepricingCommand__reguser5rate = "0"
if(Request.QueryString("ruser5charge") <> "") then
updatepricingCommand__reguser5rate = Request.QueryString("ruser5charge")

Dim updatepricingCommand__reguser10rate
updatepricingCommand__reguser10rate = "0"
if(Request.QueryString("ruser10charge") <> "") then
updatepricingCommand__reguser10rate = Request.QueryString("ruser10charge")

Dim updatepricingCommand__multiuserrate
updatepricingCommand__multiuserrate = "0"
if(Request.QueryString("musercharge") <> "") then
updatepricingCommand__multiuserrate = Request.QueryString("musercharge")

%>
<%

set updatepricingcommand = Server.CreateObject("ADODB.Command")
updatepricingcommand.ActiveConnection = MM_recruta2_STRING
updatepricingcommand.CommandText = "dbo.updatepricing"
updatepricingcommand.CommandType = 4
updatepricingcommand.CommandTimeout = 0
updatepricingcommand.Prepared = true
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@RETURN_VALUE", 3, 4)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid", 3,
1,5,updatepricingcommand__siteid)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid2", 3,
1,5,updatepricingcommand__siteid2)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid3", 3,
1,5,updatepricingcommand__siteid3)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid4", 3,
1,5,updatepricingcommand__siteid4)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid5", 3,
1,5,updatepricingcommand__siteid5)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid6", 3,
1,5,updatepricingcommand__siteid6)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid7", 3,
1,5,updatepricingcommand__siteid7)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@siteid8", 3,
1,5,updatepricingcommand__siteid8)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14,
1,7,2,updatepricingcommand__7dayrate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@14dayrate", 14,
1,7,2,updatepricingcommand__14dayrate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@21dayrate", 14,
1,7,2,updatepricingcommand__21dayrate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@28dayrate", 14,
1,7,2,updatepricingcommand__28dayrate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguseractive", 129,
1,1,updatepricingcommand__reguseractive)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguser5active", 129,
1,1,updatepricingcommand__reguser5active)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguser10active", 129,
1,1,updatepricingcommand__reguser10active)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@multiuseractive", 129,
1,1,updatepricingcommand__multiuseractive)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguserrate", 14,
1,7,2,updatepricingcommand__reguserrate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguser5rate", 14,
1,7,2,updatepricingcommand__reguser5rate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@reguser10rate", 14,
1,7,2,updatepricingcommand__reguser10rate)
updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@multiuserrate", 14,
1,7,2,updatepricingcommand__multiuserrate)
updatepricingcommand.Execute()

%>

I get the following error --


Microsoft OLE DB Provider for SQL Server error '80004005'
The precision is invalid.
/admin/afterreviewupdatepricing.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
 
B

Bob Barrows [MVP]

GTN170777 said:
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),
@reguser10active CHAR(1),
@multiuseractive CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS

Nothing to do with your problem but you should get into the habit of
starting all procedures with

SET NOCOUNT ON

<%

set updatepricingcommand = Server.CreateObject("ADODB.Command")
updatepricingcommand.ActiveConnection = MM_recruta2_STRING

Very bad practice. Always instantiate and open an explicit Connection
object, which you then use in your subsequent code. NEVER assign a
string to any object's (Command or Recordset) ActiveConnection property.
Doing so defeat's ADO's Session pooling causing more connections to be
opened to your database than is necessary. It also leaves you without
any controlv over when or if the connection object used gets destroyed.

set conn = createobject("adodb.connection")
conn.open MM_recruta2_STRING
SET updatepricingcommand.ActiveConnection = conn

updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14,
1,7,2,updatepricingcommand__7dayrate)

You are going to entirely too much trouble to run this procedure but I
will get to that later.
This statement seems to be entirely incorrect. The thing with
decimal/numeric parameters is that their NumericScale and Precision
properties have to be set BEFORE assigning a value to them. Like this:

With updatepricingcommand
Set parm = .CreateParameter("@7dayrate", 14,1)
parm.Precision = 7
parm.NumericScale = 2
..Parameters.Appen parm
parm.value = updatepricingcommand__7dayrate
End With


If you wish to persist in using explicit Command objects, then you would
probably benefit from using my Stored Procedure Code Generater (a slight
misnomer) which can be found at
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

However, since you have no output parameters, and it does not appear
that you are interested in the Return parameter's value, you might wish
to consider this:
***********copy/paste***************************************************
**
2. The technique I use most often is the
"procedure-as-connection-method" technique. With ADO 2.5 and higher,
stored procedures can be called as if they were native methods of the
connection object, like this:
conn.MyProcedure parmval1,...,parmvalN
This completely avoids the need to worry about delimiters, literal or
otherwise. Plus it turns out that this technique also causes the
procedure to be executed in a very efficient manner on the SQL Server
box.

You can also use this technique if your procedure returns a recordset:
set rs=server.createobject("adodb.recordset")
'optionally, set the cursor location and type properties
conn.MyProcedure parmval1,...,parmvalN, rs
***********copy/paste***************************************************
**
 
G

GTN170777

Hi Bob,

Thanks for the responce, are you saying that each line like-

"updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14,
1,7,2,updatepricingcommand__7dayrate)"

needs to change to -

updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14, 1)
parm.Precision = 7
parm.NumericScale = 2
...Parameters.Appen parm
parm.value = updatepricingcommand__7dayrate


Is that correct?

thanks
 
B

Bob Barrows [MVP]

GTN170777 said:
Hi Bob,

Thanks for the responce, are you saying that each line like-

"updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14,
1,7,2,updatepricingcommand__7dayrate)"

needs to change to -

updatepricingcommand.Parameters.Append
updatepricingcommand.CreateParameter("@7dayrate", 14, 1)
parm.Precision = 7
parm.NumericScale = 2
..Parameters.Appen parm
parm.value = updatepricingcommand__7dayrate


Is that correct?

Err ... oh! I was about to say "Yes" but, the answer is No. What you
have will not work because "parm" is undefined. You have to assign the
parameter object returned from CreateParameter to the parm variable
instead of passing it to the Append method. Then set the properties.
Then append it to Parameters. Look again at my example:

With updatepricingcommand
Set parm = .CreateParameter("@7dayrate", 14,1)
parm.Precision = 7
parm.NumericScale = 2
..Parameters.Append parm
parm.value = updatepricingcommand__7dayrate
End With

If you use my code generator tool, the correct code that will be
generated (of course, the insanely long variable names won't be used ...
<wink>).

Again ... you are going to entirely too much trouble. Try this:

<%

Dim updatepricingCommand__siteid
updatepricingCommand__siteid = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid2
updatepricingCommand__siteid2 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid2 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid3
updatepricingCommand__siteid3 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid3 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid4
updatepricingCommand__siteid4 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid4 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid5
updatepricingCommand__siteid5 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid5 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid6
updatepricingCommand__siteid6 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid6 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid7
updatepricingCommand__siteid7 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid7 =
CLng(Session("SITEID"))

Dim updatepricingCommand__siteid8
updatepricingCommand__siteid8 = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid8 =
CLng(Session("SITEID"))

'I don't understand - why 8 variables all containing the same value??
'Doesn't this seem ridiculous to you? I must be missing something...


Dim updatepricingCommand__7dayrate
updatepricingCommand__7dayrate = "0"
if(Request.QueryString("day7") <> "") then
updatepricingCommand__7dayrate =
CSng(Request.QueryString("day7")))

Dim updatepricingCommand__14dayrate
updatepricingCommand__14dayrate = "0"
if(Request.QueryString("day14") <> "") then
updatepricingCommand__14dayrate
= CSng(Request.QueryString("day14"))

Dim updatepricingCommand__21dayrate
updatepricingCommand__21dayrate = "0"
if(Request.QueryString("day21") <> "") then
updatepricingCommand__21dayrate
= CSng(Request.QueryString("day21"))

Dim updatepricingCommand__28dayrate
updatepricingCommand__28dayrate = "0"
if(Request.QueryString("day28") <> "") then
updatepricingCommand__28dayrate
= CSng(Request.QueryString("day28"))

Dim updatepricingCommand__reguseractive
updatepricingCommand__reguseractive = "n"
if(Request.QueryString("ru") <> "") then
updatepricingCommand__reguseractive
= Request.QueryString("ru")

Dim updatepricingCommand__reguser5active
updatepricingCommand__reguser5active = "n"
if(Request.QueryString("ru5") <> "") then
updatepricingCommand__reguser5active = Request.QueryString("ru5")

Dim updatepricingCommand__reguser10active
updatepricingCommand__reguser10active = "n"
if(Request.QueryString("ru10") <> "") then
updatepricingCommand__reguser10active = Request.QueryString("ru10")

Dim updatepricingCommand__multiuseractive
updatepricingCommand__multiuseractive = "n"
if(Request.QueryString("mu") <> "") then
updatepricingCommand__multiuseractive = Request.QueryString("mu")

Dim updatepricingCommand__reguserrate
updatepricingCommand__reguserrate = "0"
if(Request.QueryString("rusercharge") <> "") then
updatepricingCommand__reguserrate =
CSng(Request.QueryString("rusercharge"))

Dim updatepricingCommand__reguser5rate
updatepricingCommand__reguser5rate = "0"
if(Request.QueryString("ruser5charge") <> "") then
updatepricingCommand__reguser5rate =
CSng(Request.QueryString("ruser5charge"))

Dim updatepricingCommand__reguser10rate
updatepricingCommand__reguser10rate = "0"
if(Request.QueryString("ruser10charge") <> "") then
updatepricingCommand__reguser10rate =
CSng(Request.QueryString("ruser10charge"))

Dim updatepricingCommand__multiuserrate
updatepricingCommand__multiuserrate = "0"
if(Request.QueryString("musercharge") <> "") then
updatepricingCommand__multiuserrate =
CSng(Request.QueryString("musercharge"))

Dim conn
set conn = createobject("adodb.connection")
conn.open MM_recruta2_STRING

conn.updatepricing updatepricingCommand__siteid, _
updatepricingCommand__siteid2, _
updatepricingCommand__siteid3, _
updatepricingCommand__siteid4, _
updatepricingCommand__siteid5, _
updatepricingCommand__siteid6, _
updatepricingCommand__siteid7, _
updatepricingCommand__siteid8, _
updatepricingCommand__7dayrate, _
updatepricingCommand__14dayrate, _
updatepricingCommand__21dayrate, _
updatepricingCommand__28dayrate, _
updatepricingCommand__reguseractive, _
updatepricingCommand__reguser5active, _
updatepricingCommand__reguser10active, _
updatepricingCommand__multiuseractive, _
updatepricingCommand__reguserrate, _
updatepricingCommand__reguser5rate, _
updatepricingCommand__reguser10rate, _
updatepricingCommand__multiuserrate
 
G

GTN170777

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),
@reguser10active CHAR(1),
@multiuseractive CHAR(1),
@reguserrate DECIMAL(7,2),
@reguser5rate DECIMAL(7,2),
@reguser10rate DECIMAL(7,2),
@multiuserrate DECIMAL(7,2)
AS
SET NOCOUNT ON
UPDATE dbo.JBAccountType
SET JBATPrice = @7dayrate
WHERE JBATSiteID = @siteid and JBATValue = '7'
UPDATE dbo.JBAccountType
SET JBATPrice = @14dayrate
WHERE JBATSiteID = @siteid and JBATValue = '14'
UPDATE dbo.JBAccountType
SET JBATPrice = @21dayrate
WHERE JBATSiteID = @siteid and JBATValue = '21'
UPDATE dbo.JBAccountType
SET JBATPrice = @28dayrate
WHERE JBATSiteID = @siteid and JBATValue = '28'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguserrate, JBATActive = @reguseractive
WHERE JBATSiteID = @siteid and JBATValue = 'reguser'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguser5rate, JBATActive = @reguser5active
WHERE JBATSiteID = @siteid and JBATValue = 'reguser5'
UPDATE dbo.JBAccountType
SET JBATPrice = @reguser10rate, JBATActive = @reguser10active
WHERE JBATSiteID = @siteid and JBATValue = 'reguser10'
UPDATE dbo.JBAccountType
SET JBATPrice = @multiuserrate, JBATActive = @multiuseractive
WHERE JBATSiteID = @siteid and JBATValue = 'multiuser'

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

<%

Dim updatepricingCommand__siteid
updatepricingCommand__siteid = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid =
CLng(Session("SITEID"))

Dim updatepricingCommand__7dayrate
updatepricingCommand__7dayrate = "0"
if(Request.QueryString("day7") <> "") then
updatepricingCommand__7dayrate =
CSng(Request.QueryString("day7")))

Dim updatepricingCommand__14dayrate
updatepricingCommand__14dayrate = "0"
if(Request.QueryString("day14") <> "") then
updatepricingCommand__14dayrate
= CSng(Request.QueryString("day14"))

Dim updatepricingCommand__21dayrate
updatepricingCommand__21dayrate = "0"
if(Request.QueryString("day21") <> "") then
updatepricingCommand__21dayrate
= CSng(Request.QueryString("day21"))

Dim updatepricingCommand__28dayrate
updatepricingCommand__28dayrate = "0"
if(Request.QueryString("day28") <> "") then
updatepricingCommand__28dayrate
= CSng(Request.QueryString("day28"))

Dim updatepricingCommand__reguseractive
updatepricingCommand__reguseractive = "n"
if(Request.QueryString("ru") <> "") then
updatepricingCommand__reguseractive
= Request.QueryString("ru")

Dim updatepricingCommand__reguser5active
updatepricingCommand__reguser5active = "n"
if(Request.QueryString("ru5") <> "") then
updatepricingCommand__reguser5active = Request.QueryString("ru5")

Dim updatepricingCommand__reguser10active
updatepricingCommand__reguser10active = "n"
if(Request.QueryString("ru10") <> "") then
updatepricingCommand__reguser10active = Request.QueryString("ru10")

Dim updatepricingCommand__multiuseractive
updatepricingCommand__multiuseractive = "n"
if(Request.QueryString("mu") <> "") then
updatepricingCommand__multiuseractive = Request.QueryString("mu")

Dim updatepricingCommand__reguserrate
updatepricingCommand__reguserrate = "0"
if(Request.QueryString("rusercharge") <> "") then
updatepricingCommand__reguserrate =
CSng(Request.QueryString("rusercharge"))

Dim updatepricingCommand__reguser5rate
updatepricingCommand__reguser5rate = "0"
if(Request.QueryString("ruser5charge") <> "") then
updatepricingCommand__reguser5rate =
CSng(Request.QueryString("ruser5charge"))

Dim updatepricingCommand__reguser10rate
updatepricingCommand__reguser10rate = "0"
if(Request.QueryString("ruser10charge") <> "") then
updatepricingCommand__reguser10rate =
CSng(Request.QueryString("ruser10charge"))

Dim updatepricingCommand__multiuserrate
updatepricingCommand__multiuserrate = "0"
if(Request.QueryString("musercharge") <> "") then
updatepricingCommand__multiuserrate =
CSng(Request.QueryString("musercharge"))

Dim conn
set conn = createobject("adodb.connection")
conn.open MM_recruta2_STRING
conn.updatepricing updatepricingCommand__siteid, _
updatepricingCommand__7dayrate, _
updatepricingCommand__14dayrate, _
updatepricingCommand__21dayrate, _
updatepricingCommand__28dayrate, _
updatepricingCommand__reguseractive, _
updatepricingCommand__reguser5active, _
updatepricingCommand__reguser10active, _
updatepricingCommand__multiuseractive, _
updatepricingCommand__reguserrate, _
updatepricingCommand__reguser5rate, _
updatepricingCommand__reguser10rate, _
updatepricingCommand__multiuserrate
updatepricingcommand.Execute()

Should there be anything after the "updatepricingCommand__7dayrate, _" to
"updatepricingCommand__multiuserrate" lines?
because I can't see the --

""> With updatepricingcommand
Set parm = .CreateParameter("@7dayrate", 14,1)
parm.Precision = 7
parm.NumericScale = 2
..Parameters.Append parm
parm.value = updatepricingcommand__7dayrate
End With""

code??

Thanks again Bob, (this is probably really simple!!)
 
B

Bob Barrows [MVP]

GTN170777 said:
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),
@reguser10active CHAR(1),
@multiuseractive 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("adodb.connection")
conn.open MM_recruta2_STRING
conn.updatepricing updatepricingCommand__siteid, _
updatepricingCommand__7dayrate, _
updatepricingCommand__14dayrate, _
updatepricingCommand__21dayrate, _
updatepricingCommand__28dayrate, _
updatepricingCommand__reguseractive, _
updatepricingCommand__reguser5active, _
updatepricingCommand__reguser10active, _
updatepricingCommand__multiuseractive, _
updatepricingCommand__reguserrate, _
updatepricingCommand__reguser5rate, _
updatepricingCommand__reguser10rate, _
updatepricingCommand__multiuserrate
****
updatepricingcommand.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 "updatepricingCommand__7dayrate,
_" to "updatepricingCommand__multiuserrate" lines?
because I can't see the --

""> With updatepricingcommand

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.
 
G

GTN170777

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

<%

Dim updatepricingCommand__siteid
updatepricingCommand__siteid = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid =
CLng(Session("SITEID"))

Dim updatepricingCommand__7dayrate
updatepricingCommand__7dayrate = "0"
if(Request.QueryString("day7") <> "") then
updatepricingCommand__7dayrate = CSng(Request.QueryString("day7"))

Dim updatepricingCommand__14dayrate
updatepricingCommand__14dayrate = "0"
if(Request.QueryString("day14") <> "") then
updatepricingCommand__14dayrate = CSng(Request.QueryString("day14"))

Dim updatepricingCommand__21dayrate
updatepricingCommand__21dayrate = "0"
if(Request.QueryString("day21") <> "") then
updatepricingCommand__21dayrate = CSng(Request.QueryString("day21"))

Dim updatepricingCommand__28dayrate
updatepricingCommand__28dayrate = "0"
if(Request.QueryString("day28") <> "") then
updatepricingCommand__28dayrate = CSng(Request.QueryString("day28"))

Dim updatepricingCommand__reguseractive
updatepricingCommand__reguseractive = "n"
if(Request.QueryString("ru") <> "") then
updatepricingCommand__reguseractive = Request.QueryString("ru")

Dim updatepricingCommand__reguser5active
updatepricingCommand__reguser5active = "n"
if(Request.QueryString("ru5") <> "") then
updatepricingCommand__reguser5active = Request.QueryString("ru5")

Dim updatepricingCommand__reguser10active
updatepricingCommand__reguser10active = "n"
if(Request.QueryString("ru10") <> "") then
updatepricingCommand__reguser10active = Request.QueryString("ru10")

Dim updatepricingCommand__multiuseractive
updatepricingCommand__multiuseractive = "n"
if(Request.QueryString("mu") <> "") then
updatepricingCommand__multiuseractive = Request.QueryString("mu")

Dim updatepricingCommand__reguserrate
updatepricingCommand__reguserrate = "0"
if(Request.QueryString("rusercharge") <> "") then
updatepricingCommand__reguserrate = CSng(Request.QueryString("rusercharge"))

Dim updatepricingCommand__reguser5rate
updatepricingCommand__reguser5rate = "0"
if(Request.QueryString("ruser5charge") <> "") then
updatepricingCommand__reguser5rate = CSng(Request.QueryString("ruser5charge"))

Dim updatepricingCommand__reguser10rate
updatepricingCommand__reguser10rate = "0"
if(Request.QueryString("ruser10charge") <> "") then
updatepricingCommand__reguser10rate =
CSng(Request.QueryString("ruser10charge"))

Dim updatepricingCommand__multiuserrate
updatepricingCommand__multiuserrate = "0"
if(Request.QueryString("musercharge") <> "") then
updatepricingCommand__multiuserrate = CSng(Request.QueryString("musercharge"))

Dim conn
set conn = createobject("adodb.connection")
conn.open MM_recruta2_STRING
conn.updatepricing updatepricingCommand__siteid, _
updatepricingCommand__7dayrate, _
updatepricingCommand__14dayrate, _
updatepricingCommand__21dayrate, _
updatepricingCommand__28dayrate, _
updatepricingCommand__reguseractive, _
updatepricingCommand__reguser5active, _
updatepricingCommand__reguser10active, _
updatepricingCommand__multiuseractive, _
updatepricingCommand__reguserrate, _
updatepricingCommand__reguser5rate, _
updatepricingCommand__reguser10rate, _
updatepricingCommand__multiuserrate
%>

The Errors being the %> at the end and there was third ) after
"CSng(Request.QueryString("day7"))" which i removed. Also the top bit which
reads -

<%

Dim updatepricingCommand__siteid
updatepricingCommand__siteid = "0"
if(Session("SITEID") <> "") then updatepricingCommand__siteid =
CLng(Session("SITEID"))

should this read -

CSng(Session("SITEID")) instead of CLng(Session("SITEID")) ??

Now i get the following error --

"Microsoft VBScript compilation error '800a03f6'

Expected 'End'

/admin/afterreviewupdatepricing.asp, line 84 "

Sorry and thanks G
 
G

GTN170777

IGNORE that last comment Bob,

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

Cheers my friend
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top