maggiemel
Posts: 75
|
| Posted: 01/27/2004, 9:44 AM |
|
I have one record form that I would like visitors to be able to use to enter information in one of either two tables which have identical fields. My idea is to include a checkbox that the visitor checks if he wants the data entered in the second table (unchecked, by default, enters data in the first table).
My question: is it better to alter the SQL statement in a BeforeBuildInsert event, BeforeInsert event, or to use the Custom Insert property to determine whether the checkbox is checked and then re-point the SQL command to the second table? There seem to be a bunch of different ways of accomplishing it, but I'm unsure if there's one preferred method.
If it makes any difference, I'm using ASP w/MS SQL.
Thanks, all!
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
Edd
Posts: 547
|
| Posted: 01/27/2004, 1:29 PM |
|
Melissa,
I had a similar issur a few weeks ago and solved it with a custom insert to a Stored Procedure. Easily the best method.
Good Luck
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
BlinkyBill
Posts: 86
|
| Posted: 01/27/2004, 2:12 PM |
|
Quote Edd:
Melissa,
I had a similar issur a few weeks ago and solved it with a custom insert to a Stored Procedure. Easily the best method.
Edd,
Have you worked out how to get the OUTPUT and RETURN parameters out the custom insert using a proc? The after_execute events don't give you access to the cmd object, or I can't worked how to reference the cmd object from the Sub?
I would dearly love to be able to get at the parameters as it would mean I could convert all interacations with the database to procs.
|
|
|
|
Edd
Posts: 547
|
| Posted: 01/27/2004, 4:33 PM |
|
BlinkyBill,
I never had the occassion to use OUPUT Parameter within CCS so I cannot comment (I found SQL2000 Functions to be better for these situations). As for the Return values CCS seemed to handle any issues I had.
What has Support had to say about it? I would love to know as I will be where you are shortly.
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
BlinkyBill
Posts: 86
|
| Posted: 01/27/2004, 11:50 PM |
|
Quote Edd:
BlinkyBill,
I never had the occassion to use OUPUT Parameter within CCS so I cannot comment (I found SQL2000 Functions to be better for these situations). As for the Return values CCS seemed to handle any issues I had.
What has Support had to say about it? I would love to know as I will be where you are shortly.
A while ago they said "soon". Peter R. can we get access to OUTPUT param.
More to the point
Can we access the parent objects of the event caller ?
So on the after_execute event I would like to be able to get access to the variable and objects of the calling function or sub ?
|
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/28/2004, 4:41 PM |
|
Edd, thanks for the suggestion -- it seems like this is the best way to go.
One thing, though. When I converted my Insert to a Custom Insert (using a Stored Procedure), I started having trouble with one of the fields from the input form. It's a listbox and the values are somehow not being passed to the procedure. So the procedure spits back an error telling me nulls aren't allowed for that column and the insert fails.
I've checked the SP to make sure the insert statement is correct and that the variable is spelled correctly, etc., and I can't figure out what's happening. When I show the value of the variable in the Before Show event on my form, I get a value returned. Somehow, that value is not making it up to my procedure.
Duh?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/29/2004, 5:28 PM |
|
Melissa,
I tested this "concept" with custom code, and this code in "BeforeExecuteInsert" worked for me:
if articles.CheckBox1.Value = True then articles.Command.SQL = Replace(articles.Command.SQL, "INSERT INTO articles", "INSERT INTO articles2")
My table names were "articles" and "articles2", while the checkbox name was "CheckBox1".
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
maggiemel
Posts: 75
|
| Posted: 01/29/2004, 8:36 PM |
|
Ooh, that's very elegant! I've already implemented the stored procedure version, which works well (although I discovered when I did this I needed to change the parameter settings to expressions rather than just passing them the way they're set by default (URL?).
Anyway, I've got lots more to add so I'll be back asking something else again soon.
Thanks, as always, for your assistance.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
Robert O'Donnell
|
| Posted: 03/16/2004, 3:00 PM |
|
Any way yet to retrieve the value from an output parameter of a stored procedure in the After Execute Insert or After Insert Custom Code? I use VB.NET custom code. Does anyone have a simple example they could post?
Also - I have a stored procedure that returns a Return Value instead of an output parameter. I wanted to use this return value in a custom code block, but all the stored procedures execute NonQuery which only returns the # of rows affected in VB.NET. If I can figure out the output parameter problem, I can duplicate this stored procedure and modify to use output param instead (I cannot change it since it is used by other applications.)
|
|
|
|
|
