How to Update/Delete multiple tables in SQL Server

Hi all,

Can anyone point out a proper way to perform DML (Insert/Update/Delete) operations on MULTIPLE TABLES in sql 2k with asp?

I have a stored proc that does that from Query Analyser, but when I attemt to call it from asp code I get errors. Actually, insert works, but update presents problems:

The user must know the actual primary key of the record that they want to modify . Also, pk must be sequential identity field, which is not the case in my database. We have meaninful pk, that actually connects tables based on common data.
Same problems with deleting records.

I want the user to simply click in the record, then click on delete button, and be able to update this record in however many tables are on the form view.

Thank you very much for your thoughts

Can you provide an example and some insight into how your data tables are designed and joined?
-Dave

WineIsGood, sure can.

Say I have 3 tables in already existing database: Individual_Demo, Analyses, and Buying_Behaviour.

1. Individual_Demo table has demographics of each specific person, as well as Group# and Individual#. Each individual belongs to a specific research group, and what makes each record unique is cancatination of Group# & Individual#. This field is named AnalysisID, and is Primary Key

2. Analyses table also has this AnalysisID (Group# & Individual# combination), and other stuff about various analyses. Again AnalysisID is PK.

3. Buying_Behaviour has same AnalysisID, and buying behavior stuff.
Like so:
Group#| Ind# | AnalysisID
1 | 1 | 1-1
1 | 2 | 1-2
2 | 1 | 2-1
2 | 2 | 2-2
etc.

I find that asp needs a primary key that is a sequential numeric identity field, automatically incremented by 1. Will call it SeqPK
I created such field just for this purpose, but when I want to update a record, I must state the exact SeqPK I want to update (not very user friendly).

My goal is to present a user with asp page that shows a view of all 3 tables for instance, and let the user update/insert/delete any record they need in multiple tables. So if they want to change something in Ind_Demo and Buying_Behaviour at the same time, they should be able to.

Thanks again, I hope I'm being clear.

Hmm, well, I'm not sure I agree with you on one point: ASP doesn't care what the PK is. It doesn't have to be an incrementing identity column at all.... unless you're using some kind of IDE to build your screens, and that IDE requires it.
On another note, there really is no way to issue one UPDATE statement and have that command update more than one row in one table, unless you use triggers or link tables with foreign keys, but that will only really delete records for you, not update them. In other words, if you select fields from, say, three tables, and show it to a user in one table, you cannot perform one update statement and expect it to update all tables. You're going to have to write it yourself, and place the logic into your code which knows how to propogate into each table, updating what's necessary.
As far as you needing to specify the unique sequential PK each time you want to update, that's true, because it wants to build a statement like:
Update myTable Set myField=myValue Where SeqPK=theIDValue
And there's only one way to do that -- it must know the SeqPK. However, there's nothing stopping you from doing this (so long as it yields one, unique row):
Update myTable Set myField=myValue Where AnalysisID='1-2'
or even
Update myTable Set myField=myValue Where GroupID=1 And IndID=2
Get the idea? If your IDE development tool is forcing you to specify a SeqPK, then trash it and write it yourself. You can easily pass the PK by using the QueryString but refrain from showing it to the user...
Does this help at all or are you now more confused?
-Dave

Dave,

What do you mean by IDE?

You're absolutely right about the update, I'm using stored proc to propogate the updates. That makes sense.

Is QueryString an ASP functioin that grabs the value of the PK from the textbox on the asp form and passes it to sql code? If yes, that's what I need.

Thanks

IDE = Integrated Development Environment. Are you using a tool to build your programs which forces you to use a sequential primary key? Are you using Visual Interdev? Request.QueryString() returns values sent in the URL. It's just a way to pass the PK to another page if you need to. For example, let's say you have a table with rows that display people's names. Of course names are not unique, so you have a PK on that table too, called ID. But, you don't want to show the ID to the user. Once the user selects a name to edit, you need to pass that PK to the edit form, so it knows the row to lookup, etc. You can do it by passing the ID in the querystring of the link:
<a href="""editPerson.asp?ID=" & recordSet.Fields("ID").Value & """">Edit</a>
Now the "edit" link will pass the ID field to the editPerson.asp form, but it's not displayed to the user. You can use this approach to pass all kinds of things to all kinds of forms without showing them to the user.
-Dave

Thanks for your input, Dave.

I am not using IDE, just modifying existing asp code to call specific stored procs for my specific tables.

I am also using triggers in order to perform DML operations on multiple tables.

I will try your suggestion with querystring, thank you.

BTW (unrelated), do you know of any good way to secure asp pages on the IIS server (apart from firewall and such). I mean, the connection string is just sitting there in clear text, and cannot be encrypted. You can send it in SSL, but what if someone gets access to your IIS server's files?

Well, you mentioned a few things at once there... are you worried about your database connection string or the query string in the url? No one can "get access to your asp pages" located on your server through port 80 or the like. The server will execute the page and render the html instead. So, if you're worried that someone will somehow read your global.asa file or some other asp page you use to keep your connection string, I wouldn't be too concerned. If, on the other hand, you're worried that someone will be able to see sensitive ID information passed between pages in the querystring, therefore visible in the url, then you have a point. You can, instead, pass them in hidden input fields within a form post (making them only visible if someone views the source code in the browser), or place them in session variables, although that's a costly solution. If you wanted to secure your client's connection with SSL, you can install an SSL certificate for free using Microsoft's certificate server. Your clients will get a popup claiming that the certification cannot be authenticated/verified, however, and they'll have to press Ok. Why this is done is still a mystery, considering that once you pay to have the certificate authenticated, it's no safer than before. It's just a marketing ploy, if you ask me.
-Dave

Ahhh, insteresting, I never tried M$ free certificate. I presume you mean the one you create with certificate wizard in IIS properties?

So the only thing different between paying $400 to someone like Verisign and doing it this way is the pop-up?

Do I understand correctly that once I have SSL certificate in place (M$ or other), the user will still be able to see sensitive ID information in their browser source code, but this time it will only be authenticated user, so0 it's OK.

Thanks for droping the knowledge

The only thing SSL will do for you is encrypt the data being passed from your server to the client, and back again. Once it hits the client, however, they still get the same HTML you've always given them, and they can still click View Source to see it. They can also still see the URL, including any parameters you send in it. All SSL does for you is package up your transmissions in an encrypted gift box. The browers then unwraps it (and so does your server when it comes back). Only your server and the client know the key to unlock it. That way, if it's intercepted along the way, no one can open it up and read the data.
As for your question on the free MS Certificate, yes, it's the same exact thing but you don't pay to have it authenticated by Verisign, etc. The user gets that annoying popup, for no good reason other than for marketing purposes. Your client can disable that popup by "trusting" the site in IE. It's an option under IE's configuration. IE already comes with some sites trusted in this way -- check them out, you'll be surpised at which companies have paid MS to have this done... it's funny: Tools|Internet Options|Content Tab|Certificates Button.
If you need to pass important parameters between pages, the two common ways are: in the url using the querystring parameter, which can be seen by the user because it's in the url. You see it all the time when you see websites, like this one, and he url looks like this: http://www.whatever.com?id=234&acti...g&etc=&etc=....
The first parameter starts with ? and the rest start with &. Don't ask why... You read them by doing this:
TheID = Request.QueryString("ID")
Anyway, the second is using form elements, like input tags, etc, but making them hidden, like this:
<input type=hidden id="theID" value="123">
Then, when your form is posted, along with all the other form elements you asked the user to enter, this one comes along for the ride too. However, it can be seen by the user if they click View Source in their browser, since it's just HTML. The only other way would be to create a Session variable like this:
Session("TheID") = 123
Then you can read it in the next page like this:
TheID = Session("TheID")
Since this is ASP script code, it never gets turned into HTML and sent to the client's browser, so they're totally unaware that it happened. Three things bad with it: one, session variables use cookies and need to make a round trip to the client, therefore take up bandwidth, secondly they eat memory on the IIS server, lastly they only live for the life of the user's session. The server doesn't know the user is still alive until they click something and interact with the website. As soon as your server gives them a page, it starts counting... once it reaches a certain elapsed time without hearing from the client, it times out the session and clears all those nice session variables pertaining to it. You can extend it, however, just look up how to extend the session timeout value in asp.
So, the choice is yours. If you feel that revealing the primary key ID code is no big deal, then by all means send it in the url or put it in the form with a hidden element. What I do sometimes is verify that the url contains a valid ID code and some hacker didn't just change the url manually to see if it works. I take the ID from the URL and match it to some other number, like say the order number, and if it smells right, then I continue. If that ID number doesn't belong with the order number, for example, then I know someone messed around with the url. It's very unlikely that they could get both numbers right if they tried to guess at it.
Hope that helps,
-Dave

Dave, thanks for all your help!

Lastly: What would you recommend as a good starting guide for ASP with SQL Server 2K and IIS ?

If you're just starting out with ASP, I would not recommend it. I would recommend learning ASP.NET instead. It's free also, and so it Web Matrix -- the IDE editor from Microsoft. It's a different way of programming ASP (and better). I wouldn't want you to take the time to learn ASP only to move to ASP.NET inevitably.
-Dave