Not sure if this is a place where one can ask a basic SQL question but figured I’d give it a shot. Working with a legacy Intranet system running classic ASP on IIS 5.1 and Microsoft Access, it’s old and refuses to die.
The Intranet serves up pages for tracking, managing & shipping packages to existing customers along with adding new customers. I’d like to update the ASP code to only populate the Country & State dropdown lists so it only show locations of existing customers while still having the ability of showing a complete list when adding new customers.
Not a database or SQL guy by any stretch of the imagination and not sure the best way of doing this without going through several steps and multiple queries.
Added an ‘In-Use’ field to both the Country & State database tables so the ASP code can serve up records for either existing or new customers. The problem I’m having is maintaining the ‘In-Use’ field especially when deleting customers.
When removing a customer say from Hawaii, I want to clear the ‘In-Use’ field from the State table if they are the only customer from that State. I tired a ‘count’ query in the ASP code to return the numbers of customers from Hawaii but couldn’t get it to work. So then setup a query requesting all customers from Hawaii and ran through all records keeping a running tally. If the count comes up zero, meaning there are no other customers from Hawaii, I then run an update query to clear the ‘In-Use’ field from the State table. And this is just for the State table, will need to duplicate all this for Country table as well.
Was wondering if there is a better way of accomplishing this as my solution does not seem very clean or efficient.
For future reference, if you post in the wrong forum, use the Report Post icon under your post to send a message to the mods to ask them to move it for you.
Was wondering if there is a better way of accomplishing this as my solution does not seem very clean or efficient.
You can update "In-Use" flag for all the rows in your States table w/ a single query -- that's the whole set-oriented reason for existence (raison d'être) of SQL as a language.
You have to come up with an UPDATE query based on your table schema and yes, you can use COUNT here too, something as simple as this probably:
Code:
UDPATE State SET [In-Use] = (SELECT COUNT(*) FROM Customers WHERE Customers.StateID = State.ID)
You'll have to hook up the above UPDATE query to be run exactly once after a Customer is added, modified or deleted in your system.
And if it were any other DBMS than Access you could put wqweto's UPDATE-Statement in a Trigger, and let the Database itself manage it.
Upside to wqweto's approach: "In-Use" would NOT be a boolean, but an (unsigned) Integer, so you can use that Integer as a Boolean, a value of 0 (Zero) being False (Not In-Use), everything else being True (Yes, it is In-Use)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Sorry took so long to respond, been sidetracked putting out other fires.
jmcilhinney – Appreciate the heads up when miss-posting things, shouldn’t happen again but now know how to handle it.
wqweto – Thanks for the SQL statement, way better solution then what I was doing, come Monday I’ll give it a shot and see how it goes.
Zvoni – Setting up a trigger would be a great way to get this done, plan is to eventually upgrade everything but for now the system seems to be doing it’s job pretty well.
The only problem I’ve run into was when trying to add code to validate addresses through an http request to an outside source. Not sure what’s going on, guessing has something to do with IIS 5.1 being so old. Keep getting an error message that the Access-Control-Allow-Origin header is missing. Added the header using both a specific URL and an asterisk (*), neither has worked.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad