update sql with a figure gained from a sum

[andyfresh]

Hi,

This sql is placed within an asp page however im having trouble with the sql itsself. Basically I would like to divide the newsell by the base and then subtract 1 and multiple by 100.

The code so far?

SQL = "UPDATE Temp_lwlees SET MarkupRate =(" & Request("newsell") & "/" & Request("base") & " - 1 *100) WHERE " &_
"(PID='" & Request("prodid") & "')"

Can anyone help. I think its quite simple im probably missing brackets or something

Regards

Andy

 

[jbenson001]

What is the problem? Syntax error, or are your results not as expected? Some sample data if that is the case.

jim

 

[andyfresh]

Currently nothing happens. The next update sql statement runs fine however its like the figure isnt generated.


If Request("switch") = "u" then



SQL = "UPDATE Temp_lwlees SET MarkupRate =(" & Request("newsell") & "/" & Request("base") & "-1 *100) WHERE " &_
"(PID='" & Request("prodid") & "')"
SQL = "UPDATE Temp_lwlees SET SellingPrice =" & Request("newsell") & " WHERE (PID='" & Request("prodid") & "')"


Set objConn = Server.CreateObject ("adodb.connection")
objConn.ConnectionTimeout = 300
objConn.CommandTimeout = 300
objConn.open strConn
objConn.Execute SQL





End If

 

[jbenson001]

Before you try to execute the statemtents, do response writes to show the values you are being passed, as well as the sql statments in the SQL variable.

 

[ESquared]

And probably do the math locally instead of on the server. For one thing, the syntax you're using means x - 100, not (x - 1) * 100...

 

[RobertT687]

In the example code provided, you are assigning the variable SQL the string beginning "UPDATE Temp_lwlees SET MarkupRate..."
then immediately assigning SQL a new string value beginning "UPDATE Temp_lwlees SET SellingPrice..."
When your code reaches the objConn.Execute SQL it is the latest value in SQL getting run.
You must run the objConn.Execute SQL after each assignment of a value to SQL.

 

[ESquared]

or do SQL = SQL & " moreSQLSTuff" '<-- note the space as first char of string

 

[andyfresh]

Thank you. I reorganisaed the code so the sql ran after each statement and also changed the way it *100 and now it works. The code now looks like:

If Request("switch") = "u" then



SQL = "UPDATE Temp_lwlees SET MarkupRate =((" & Request("newsell") & "/" & Request("base") & "-1)*100) WHERE " &_
"(PID='" & Request("prodid") & "')"


Set objConn = Server.CreateObject ("adodb.connection")
objConn.ConnectionTimeout = 300
objConn.CommandTimeout = 300
objConn.open strConn
objConn.Execute SQL

SQL = "UPDATE Temp_lwlees SET SellingPrice =" & Request("newsell") & " WHERE (PID='" & Request("prodid") & "')"
Set objConn = Server.CreateObject ("adodb.connection")
objConn.ConnectionTimeout = 300
objConn.CommandTimeout = 300
objConn.open strConn
objConn.Execute SQL



End If



Thanks again

 

[RobertT687]

You could simplify further as:
If Request("switch") = "u" then
Set objConn = Server.CreateObject ("adodb.connection")
objConn.ConnectionTimeout = 300
objConn.CommandTimeout = 300
objConn.open strConn


SQL = "UPDATE Temp_lwlees SET MarkupRate =((" & Request("newsell") & "/" & Request("base") & "-1)*100) WHERE " &_
"(PID='" & Request("prodid") & "')"

objConn.Execute SQL

SQL = "UPDATE Temp_lwlees SET SellingPrice =" & Request("newsell") & " WHERE (PID='" & Request("prodid") & "')"

objConn.Execute SQL

End If

You also need to close the connection at some point.

 

[ESquared]

why do two executions when they can both be done in one? Less overhead to get the same job done.