Stored Procedures - Basic Insert?

I'll have to study this stuff.

Below, good idea. I'll use that one. Thanks.
> Response.Redirect to a new page, whether it's the original form page ready
> for another insert, or some kind of "here's what you added" page that
> summarizes the new record, or anywhere at all really. But Response.Redirect
> will not include the POST data from the form.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:er20df$p5h$1@forums.macromedia.com...
> Lee wrote:
>> By redundant do you mean the begin and end?
>
> It just occurred to me that you didn't see the insert and update. What I
> am thinking here, I want one to INSERT and one to UPDATE. the insert will
> be a brand new record while the update would update a current record.
>
> I just realized that what I did would not work because it needs an ID to
> update a record I only want it to update a record that is already in the
> data.
>
> The INSERT, however, doesn't matter because it's a new record.

You could have a procedure that does both. There's no special benefit to
having one that does both over having two separate ones. If you wanted,
though:

CREATE PROCEDURE [dbo].[HandleUpdate]
@RecID INT=NULL OUTPUT,
@ValA VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;

IF @RecID IS NULL
BEGIN
INSERT INTO dbo.TableA(ColA)
VALUES(@ValA)
SET @RecID=SCOPE_IDENTITY()
END
ELSE
BEGIN
UPDATE dbo.TableA
SET ColA = @ValA
WHERE RecID = @RecID
END
END

In DW, to do an insert, either leave out the RecID parameter's value
entirely, or set the variable to the keyword Empty or Null first.

myCommand.Parameters.Append myCommand.CreateParameter("@RecID",3,3,4,RecID)
The first 3 indicates you're passing an integer; the second 3 indicates it's
an input/output parameter (1 for input, 2 for output, 3 for both); and 4 is
the size of an integer in bytes. If RecID is an integer, the procedure will
update. If you set RecID = Empty, then the procedure will insert. After an
insert, you can get the ID of the new record after the .Execute() line.

newID = myCommand.Parameters("@RecID").Value

It sometimes comes in handy for a redirect to a detail page or something.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:er204a$osj$1@forums.macromedia.com...
> By redundant do you mean the begin and end?

Yes, but don't worry about it.

> Another question, I created an insert page and another page that accepts
> this. First, could I have had the SP on the page with the form?

Yes. DW will add the command, but it will fire on page load. To change
this, you'll need to manually add an If block to the page. If you use a
normal submit button,
If Request.Form("submit") <> "" Then
the command code
End If

If you use an image,
If Request.Form("submit.x") <> "" Then
the command code
End If

> Also, when I fill out the form and hit submit, it goes to this second page
> where the SP is located, if I hit refresh, the SP is fired again and items
> are added again. Is there a standard for thwarting this?

Response.Redirect to a new page, whether it's the original form page ready
for another insert, or some kind of "here's what you added" page that
summarizes the new record, or anywhere at all really. But Response.Redirect
will not include the POST data from the form.

Lee wrote:
> By redundant do you mean the begin and end?

It just occurred to me that you didn't see the insert and update. What I
am thinking here, I want one to INSERT and one to UPDATE. the insert
will be a brand new record while the update would update a current record.

I just realized that what I did would not work because it needs an ID to
update a record I only want it to update a record that is already in the
data.

The INSERT, however, doesn't matter because it's a new record.

By redundant do you mean the begin and end?

Another question, I created an insert page and another page that accepts
this. First, could I have had the SP on the page with the form?

Also, when I fill out the form and hit submit, it goes to this second
page where the SP is located, if I hit refresh, the SP is fired again
and items are added again. Is there a standard for thwarting this?

As far as the book is concerned, I haven't seen one that covers this
angle of Dreamweaver/SP. We've had this conversation before but
basically, there are standard issues that most Web Developers/Designers
run into when they start working with databases. My needs are upper
level but my skill are mid-level. Getting SP to work was a huge thing
for me. Now, I just need to find some source that can step me through
this new skill set.

"...At vessel prow I stand which bears me forward, forward over the
star-lit sea..."

The Book, I understand, perhaps someone else is listening. The other
area that I would find interesting is a mid-level book on security from
a Dreamweaver angle.

I've always liked those "...For Dummies" books.

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:er042v$hhf$1@forums.macromedia.com...
> New question. I'm wonder now how it would look to update one and insert
> the other?

What you have works, but you've gone a bit redundant on me. :)
Inside a stored procedure, you can do just about anything you want, so if
you want to update one and insert the other, it's not problem at all. A
sample procedure might be:

CREATE PROCEDURE [dbo].[AddShippingAddress]
@OneA VARCHAR(50),
@TwoA VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;

UPDATE INTO dbo.aXOne(OneA)
VALUES (@OneA)

UPDATE dbo.aXTwo
SET TwoA=@TwoA
WHERE TwoA=@OneA
END
GO

As for the book - I imagine someone else has beat me to the punch. ;)

New question. I'm wonder now how it would look to update one and insert
the other?

CREATE PROCEDURE [dbo].[AddShippingAddress]
@OneA VARCHAR(50), @TwoA VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;

UPDATE INTO dbo.aXOne(OneA)
VALUES (@OneA)

END
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.aXTwo(TwoA)
VALUES(@TwoA)
END
GO

Lee wrote:
> This actually passed the validation
>
>
> CREATE PROCEDURE [dbo].[AddShippingAddress]
> @OneA VARCHAR(50), @TwoA VARCHAR(50)
> AS
> BEGIN
> SET NOCOUNT ON;
>
> INSERT INTO dbo.aXOne(OneA)
> VALUES (@OneA)
> END
> BEGIN
> SET NOCOUNT ON;
>
> INSERT INTO dbo.aXTwo(TwoA)
> VALUES(@TwoA)
> END
> GO


THIS ACTUALLY WORKED!!! hip hip...

Thanks for the help

This actually passed the validation


CREATE PROCEDURE [dbo].[AddShippingAddress]
@OneA VARCHAR(50), @TwoA VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.aXOne(OneA)
VALUES (@OneA)
END
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.aXTwo(TwoA)
VALUES(@TwoA)
END
GO

As far as errors on the multiple, it's syntax errors. I just basically
don't know how to format the procedure so insert into two.