How to use subselect in ASP ? Example included

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to use subselect in ASP ? Example included

Author

Topic

one1234
Starting Member

6 Posts

Posted - 2006-01-12 : 03:37:49

I am working with asp + sql + Access.

I am trying to update a client's prices which are to be set equal to another clients existing prices.
That is, the client with client_no = 10025 is to have same prices as client with client_no = 4900.
The condition is the value of rc (field name) are to be equal each other.

The table called prices looks like this with its 3 columns (client_no, price and rc):
client_no price rc
10025 33 103
10025 17 154
10025 92 130
4900 54 103
4900 97 154
4900 88 130
- and more clients are included in the table

Now I want client 10025 to have the price 54 where rc = 103 which is the price of client 4900, price is to be 97 where rc = 154 and so on.

I have tried to work with this statement but it gives me no result:

sql="update prices set A.price = B.price from (select price from prices where client_no = 10025 ) A ,(select price from prices where client_no = 4900 ) B where A.rc=B.rc"

Also:

sql="update prices set price = (select price from prices where client_no = 4900 ) B where rc=B.rc and client_no = 10025 "

Any suggestions ?
Best regards

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 03:58:52

Try this
update prices set price = a.price
from
(select * from prices where client_no =4900 ) a
where a.rc = prices.rc
and prices.client_no = 10025

one1234
Starting Member

6 Posts

Posted - 2006-01-12 : 04:59:53

Thanks for your swift reply.

This does not work. I can update a single row 'manually' by specifying the single rc.

... I do not get it. Any comments ?

best regards

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-12 : 05:16:57

query

quote:

update prices set price = a.price
from
(select * from prices where client_no =4900 ) a
where a.rc = prices.rc
and prices.client_no = 10025

will update the client_no 10025 with the proces of client_no 4900 which are having same rcs..
btw what error are you getting in this?

one1234
Starting Member

6 Posts

Posted - 2006-01-12 : 07:57:32

My total asp + sql looks like this:

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("database_1.mdb")

sql="update prices set price = a.price from (select * from prices where client_no =4900 ) a where a.rc = prices.rc and prices.client_no = 10025"

DataConn.execute(sql)

Response.Expires=-1000
DataConn.Close
set DataConn = Nothing
set rstContacts = Nothing
%>

Nothing happens. I do not know if this is a problem because I use subselect ? Does though not make sense. I can do everything else ie delete, insert, update etc but not when I use the line above including the subselect.

For testing purposes I tried to run this which worked fine:
sql=" update prices set price = 7000 where client_no = 4900 and rc = 103"

FYI: I get not err message since I activate the asp page via Flash. I just activate the asp page and checks the result in the access db.

one1234
Starting Member

6 Posts

Posted - 2006-01-12 : 08:34:56

Found that this works......but that is obviously not the way to do it:

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("database_1.mdb")

sql1 = " select price, rc from prices where client_no = 4900"

set rstContacts= DataConn.execute(sql1)

If rstContacts.EOF Then
Response.Write "success=False"
Else
Response.Write "success=true&results="
Do While Not rstContacts.EOF

'All columns
For i = 0 to rstContacts.Fields.Count - 1
sql = " update prices set price = " & rstContacts.Fields(i) & " where client_no = 10025 "
DataConn.execute(sql)
Next

rstContacts.MoveNext
Loop

End If

Response.Expires=-1000
DataConn.Close
set DataConn = Nothing
set rstContacts = Nothing
%>

I have though not include the criteria re rc.
If I do that, it does not work ie:

For i = 0 to rstContacts.Fields.Count - 1
sql = " update prices set price = " & rstContacts.Fields(i) & " where client_no = 10025 and rc =" & rstContacts.Fields(i + 1)
DataConn.execute(sql)
i=i+1
Next

does this help you ?

one1234
Starting Member

6 Posts

Posted - 2006-01-13 : 05:46:50

I have got an error message trying to run the statement in Access:
'Operation must use an updateable query.'

All parts of the statement works fine when run separately. But together I receive this err msg.

The table is not read only etc. and I can update using even more simple sql string.

Any suggestions to what can be wrong here ?
I am out of ideas.....

one1234
Starting Member

6 Posts

Posted - 2006-01-16 : 04:14:38

SOLUTION:

Work around...

I first retrieved data related to subselect.
Then I went through all records using EOF and for each record I updated my table using DataConn.execute(sql)

Thanks for input.

Best regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources