Stored Procedures - Basic Insert?

Report · Feb 14, 2007

Trying to do a basic INSERT Store Procedure that isn't working.

Created the SP in the database and went to Dreamweaver.
-----------------My Stored Procedure-----------------
CREATE PROCEDURE dbo.spAddShippingAddress (@OneA varchar(50))
AS
INSERT INTO aXOne (OneA)
VALUES (@OneA)
GO

-----------------My Stored Procedure-----------------

aXOne is the Table and OneA is the Column

So which do I use in Dreamweaver, "Stored Procedure" or "INSERT"?

Also, since this is an insert, I have a form page that submits to the
page with the Command on it.

Nothing seems to work. Any ideas welcome. Thanks

Report · Feb 14, 2007

OK, I'm on a roll now.

I got the above to work and am now trying to insert two form items into
into two separate tables.

CREATE PROCEDURE dbo.spAddShippingAddress (@OneA varchar(50), @TwoA
varchar(50))
AS
INSERT INTO (aXOne (OneA), aXTwo (TwoA))
VALUES (@OneA, @TwoA)
GO

I get a syntax error.

Report · Feb 14, 2007

Procedure is close. It will work as-is, but get in the habit of adding
certain commands to every procedure; these will prevent issues in certain
situations. Also, do not use "sp" as a prefix - that's reserved for system
procedures and changes the way SQL Server looks up the procedure name. The
"web-friendly" version:

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

INSERT INTO dbo.aXOne(OneA)
VALUES (@OneA)
END
GO

As for the rest of it:
- Use the "stored procedure" option in Dreamweaver. The "insert" option is
what you would use if you were executing the actual insert query without the
procedure as a wrapper (keep the procedure - it's just nicer for everyone in
the end).
- There's nothing wrong with the form submitting to the page with the
command on it. Make sure you've got the parameters set up properly (form
method="post", then Request.Form; method="get" then Request.QueryString) and
double-check your data types.

By "doesn't work", please be specific - is there an error, or does the page
execute normally while not inserting the record or inserting a blank record,
or...

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:eqvt5u$a0l$1@forums.macromedia.com...
> Trying to do a basic INSERT Store Procedure that isn't working.
>
> Created the SP in the database and went to Dreamweaver.
> -----------------My Stored Procedure-----------------
> CREATE PROCEDURE dbo.spAddShippingAddress (@OneA varchar(50))
> AS
> INSERT INTO aXOne (OneA)
> VALUES (@OneA)
> GO
>
> -----------------My Stored Procedure-----------------
>
> aXOne is the Table and OneA is the Column
>
> So which do I use in Dreamweaver, "Stored Procedure" or "INSERT"?
>
> Also, since this is an insert, I have a form page that submits to the page
> with the Command on it.
>
> Nothing seems to work. Any ideas welcome. Thanks

Report · Feb 14, 2007

I will adapt what you sent to what I have.

Thanks

However, as you have probably noticed, I am now trying to insert into
two separate tables. Any ideas on that?

Thanks once again Lionstone

This is really quite painful and I wish there was a book. You sure you
don't want to write one?

Report · Feb 14, 2007

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.

Report · Feb 14, 2007

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

Report · Feb 14, 2007

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

Report · Feb 14, 2007

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

Report · Feb 15, 2007

"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. ;)

Report · Feb 15, 2007

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.

Report · Feb 15, 2007

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.

Report · Feb 15, 2007

"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.

Report · Feb 15, 2007

"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.

Report · Feb 15, 2007

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.