Insert Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert Query

Author

Topic

jonnod123
Starting Member

6 Posts

Posted - 2008-04-28 : 10:18:54

Hi All,

I've been going around in circles and was wondering if anyone could help with this insert query. (I'm using SQL Server 2005).

I have two tables aspnet_Users and aspnet_Draws. They have a 1 to many relationship, with UserID being the PK in aspnet_Users and the FK in aspnet_Draws. Table structure is:

aspnet_Draws
ID (PK)
UserId
DrawDate
NumberOfEntries

aspnet_Users
UserId (PK)
UserName

I want to insert a row into aspnet_Draws. UserID needs to be taken from aspnet_Users where UserName is a particular value, e.g. NameXXX

DrawDate and NumberOfEntries do not allow nulls and also need to be updated, though these values will be taken from a webForm and not from aspnet_Users. ID is automatically incremented.

Any help or advice would be greatly appreciated.

Cheers,
Jon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 10:26:17

You need to write front end application code to get DrawDate,NumberOfEntries & UserName from webform.Then call a stored procedure from application passing these values.It will be of form:-

CREATE PROC SaveWebValues
@NumberOfEntries int,--from form
@DrawDate datetime = GETDATE()--date value from form
@UserName = SUSER_SNAME()--name of user who is logged in
AS
INSERT INTO aspnet_Draws 
(UserId,
DrawDate,
NumberOfEntries)
SELECT 
UserId,
@DrawDate,
@NumberOfEntries
FROM aspnet_Users
WHERE UserName=@UserName
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 10:28:43

[code]CREATE PROCEDURE dbo.spTest
(
@UserName VARCHAR(100)
)
AS

SET NOCOUNT ON

UPDATE d
SET d.DrawDate = GETDATE(),
d.NumberOfEntries = d.NumberOfEntries + 1
FROM aspnet_Draws AS d
INNER JOIN aspnet_Users AS u ON u.UserID = d.UserID
WHERE u.UserName = @UserName

IF @@ROWCOUNT > 0
RETURN

IF NOT EXISTS (SELECT * FROM aspnet_Users WHERE UserName = @UserName)
INSERT aspnet_Users
(
UserName
)
VALUES (
@UserName
)

INSERT aspnet_Draws
(
UserID,
DrawDate,
NumberOfEntries
)
SELECT UserID,
GETDATE(),
1
FROM aspnet_Users
WHERE UserName = @UserName[/code]

E 12°55'05.25"
N 56°04'39.16"

jonnod123
Starting Member

6 Posts

Posted - 2008-04-29 : 05:44:02

Hi Guys and thanks for your help.

I'm getting a syntax error now. My stored procedure is:

CREATE PROCEDURE [dbo].[aspnet_Draws_CreateDraws]
-- Add the parameters for the stored procedure here
@NumberOfEntries int, --from form
@DrawDate datetime, --date value from form
@UserName nvarchar(256), --name of user from form
AS
INSERT INTO aspnet_Draws
(UserId,
DrawDate,
NumberOfEntries)
SELECT
UserId,
@DrawDate,
@NumberOfEntries
FROM aspnet_Users
INNER JOIN aspnet_Users ON aspnet_Draws.UserId = aspnet_Users.UserId
--INNER JOIN aspnet_Users ON aspnet_Users.UserId = aspnet_Draws.UserId
WHERE aspnet_Users.UserName = @UserName
GO

And the error message is:

Msg 156, Level 15, State 1, Procedure aspnet_Draws_CreateDraws, Line 11
Incorrect syntax near the keyword 'AS'.

Thanks in advance for any help.
Jon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 05:49:18

Change the INNER JOIN table to "aspnet_Draws".
The code you posted is not the same I suggested.

E 12°55'05.25"
N 56°04'39.16"

jonnod123
Starting Member

6 Posts

Posted - 2008-04-29 : 06:39:18

Hi Peso, Thanks for taking the time to look at this.

I'm happy to use your code and it does compile. However, the values DrawDate and NumberOfEntries cannot be created dynamically, they need to be taken as user input from the webform. Also, the SP is not used to update existing records in aspnet_Draws, each input should be inserted as a new record in the table.

This is my first attempt at a stored procedure, so sorry if this wasn't clear.

Thanks,
Jon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 06:43:02

quote:
Originally posted by jonnod123

Hi Peso, Thanks for taking the time to look at this.

I'm happy to use your code and it does compile. However, the values DrawDate and NumberOfEntries cannot be created dynamically, they need to be taken as user input from the webform. Also, the SP is not used to update existing records in aspnet_Draws, each input should be inserted as a new record in the table.

This is my first attempt at a stored procedure, so sorry if this wasn't clear.

Thanks,
Jon

thats what you are doing by passing the value in parameters. You jsut want your application to grab these values from webform and apss them onto stored procedure

jonnod123
Starting Member

6 Posts

Posted - 2008-04-29 : 06:57:07

I realise that Visakh, and it's what I've been trying to do (see post 3). But I'm getting a syntax error.

jonnod123
Starting Member

6 Posts

Posted - 2008-04-29 : 06:58:58

Post 4 sorry!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 07:07:01

quote:
Originally posted by jonnod123

I realise that Visakh, and it's what I've been trying to do (see post 3). But I'm getting a syntax error.

there was an unwanted ,. remove it and try

CREATE PROCEDURE [dbo].[aspnet_Draws_CreateDraws]
-- Add the parameters for the stored procedure here
@NumberOfEntries int, --from form
@DrawDate datetime, --date value from form
@UserName nvarchar(256), --name of user from form
AS
INSERT INTO aspnet_Draws 
(UserId,
DrawDate,
NumberOfEntries)
SELECT 
UserId,
@DrawDate,
@NumberOfEntries
FROM aspnet_Users
INNER JOIN aspnet_Users ON aspnet_Draws.UserId = aspnet_Users.UserId 
--INNER JOIN aspnet_Users ON aspnet_Users.UserId = aspnet_Draws.UserId
WHERE aspnet_Users.UserName = @UserName
GO

Also why are you self joining to aspnet_Users?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 07:52:14

See 04/29/2008 : 05:49:18

E 12°55'05.25"
N 56°04'39.16"

jonnod123
Starting Member

6 Posts

Posted - 2008-04-29 : 08:57:08

Hi Guys,

Got it working! Thanks for all the help. The working SP is:

CREATE PROCEDURE [dbo].[aspnet_Draws_CreateDraws]
-- Add the parameters for the stored procedure here
@NumberOfEntries int, --from form
@DrawDate datetime, --date value from form
@UserName nvarchar(256) --name of user from form
AS
INSERT INTO aspnet_Draws
(UserId,
DrawDate,
NumberOfEntries)
SELECT
UserId,
@DrawDate,
@NumberOfEntries
FROM aspnet_Users
WHERE aspnet_Users.UserName = @UserName

You were right Visakh, didn't need the inner join at all. Couldn't see the wood for the trees!

Thanks again,
Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources