Prev: Random HTTP/1.1 403 Forbidden
Next: Using RTE but nothing is submitted in the form?
From: GTN170777 on 21 Feb 2008 07:45
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
From: Bob Barrows [MVP] on 21 Feb 2008 09:12
GTN170777 wrote:
> 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

<snip - totally irrelevant>

> <%
>
> 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

<snip>

> 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***************************************************
**



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


From: GTN170777 on 21 Feb 2008 09:55
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

"Bob Barrows [MVP]" wrote:

> GTN170777 wrote:
> > 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
>
> <snip - totally irrelevant>
>
> > <%
> >
> > 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
>
> <snip>
>
> > 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***************************************************
> **
>
>
>
> --
> 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.
>
>
>
From: Bob Barrows [MVP] on 21 Feb 2008 10:24
GTN170777 wrote:
> 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



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


From: GTN170777 on 21 Feb 2008 10:56
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!!)


"Bob Barrows [MVP]" wrote:

> GTN170777 wrote:
> > 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
>
>
>
> --
> 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.
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Random HTTP/1.1 403 Forbidden
Next: Using RTE but nothing is submitted in the form?