May 16, 2006 at 3:40 pm
I am trying to insert a record in when updating the table that record does not exist (based on the primaryKey column)
Here is the error that i get when i run the update
The x.Phone is not permitted in this context. Valid expressions are constants, constant expressions, and (in some cases)variables. Column names are not permitted.
Incorrect syntax near keyword 'WHERE'.
Incorrect syntax near keyword 'RETURN'
------------------------------
ALTER PROCEDURE
[dbo].[Users_Update_Phones]
@UserName
nvarchar(256) = null
,@SqldocPhone
varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
EXEC
sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
UPDATE
[Users.Phones]
SET
PhoneTypeCD = x.PhoneTypeCD
,Phone = x.Phone
,Extention = x.Extention
FROM
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH (
Phone
nvarchar(12), PhoneTypeCD nvarchar(3),Extention nvarchar(10),PhonesID INT
)x
IF (x.PhonesID = 0 OR x.PhonesID IS NULL AND x.PhoneType IS NOT NULL)
BEGIN
INSERT INTO [Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
VALUES
(x.Phone, x.PhoneTypeCD, x.Extention, @user-id)
WHERE x.Phone NOT IN (SELECT Phone FROM [Users.Phones])
END
WHERE [UserId] = LOWER(@UserId) AND IsLockedOut = 0 AND [Users.Phones].[PhonesID] = X.PhonesID
EXEC
sp_xml_removedocument @idoc
END
RETURN
===============================================================
Here is my error
-------------------
The x.Phone is not permitted in this context. Valid expressions are constants, constant expressions, and (in some cases)variables. Column names are not permitted.
Incorrect syntax near keyword 'WHERE'.
Incorrect syntax near keyword 'RETURN'
Thanks alot for the help!!!
Erik
Dam again!
May 16, 2006 at 4:43 pm
This is usually done in a pattern of code that looks something like:
if exists (select 1 from sometable where primarykey = values you have available)
BEGIN
update the row here, because it exists
END
else
BEGIN
insert the row here because it doesn't exist
END
May 16, 2006 at 4:50 pm
Thank you for the reply.. sweating over here trying to figure this out..
How do i merge this way into my design that i have now?
with the xml
thanks
erik
Dam again!
May 16, 2006 at 4:59 pm
I have come up with this but still get the same error about the x.Phone can not be used in this context
ALTER PROCEDURE
[dbo].[Users_Update_Phones_2]
@UserName
nvarchar(256) = null
,@SqldocPhone
varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
if exists (select 1 from [Users.Phones] where [Users.Phones].PhonesID = x.PhonesID)
BEGIN
UPDATE [Users.Phones]
SET
PhoneTypeCD = x.PhoneTypeCD
,Phone = x.Phone
,Extention = x.Extention
WHERE [UserId] = LOWER(@UserId) AND IsLockedOut = 0 AND [Users.Phones].[PhonesID] = X.PhonesID
END
else
BEGIN
INSERT INTO [Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
VALUES
(x.Phone, x.PhoneTypeCD, x.Extention, @user-id)
WHERE x.Phone NOT IN (SELECT Phone FROM [Users.Phones])
END
EXEC sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH (
Phone
nvarchar(12), PhoneTypeCD nvarchar(3),Extention nvarchar(10),PhonesID INT
)x
EXEC
sp_xml_removedocument @idoc
END
RETURN
Dam again!
May 16, 2006 at 5:03 pm
It's hard to tell without knowing what the primary key actually is (phone? phoneid?) but here's a rough guess.
IF (x.PhonesID = 0 OR x.PhonesID IS NULL AND x.PhoneType IS NOT NULL)
BEGIN
IF EXISTS (select 1 from users.phones a where a.phone = x.phone)
BEGIN
INSERT INTO [Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
VALUES
(x.Phone, x.PhoneTypeCD, x.Extention, @user-id)
END
ELSE
BEGIN
update users.phones set phonetypecd = x.phonetypecd,
extention = x.extention, userid = @userid
WHERE IsLockedOut = 0 AND [Users.Phones].[Phone] = X.Phone
END
Just a guess, but the placement should be correct.
May 16, 2006 at 5:19 pm
The PrimayKey is PhonesID.....
Here is a link to what the error looks like.. No matter what i do i get the same error.. I need to get this figured out been on it about 3 hours...
any help will be greate
http://www.Afcc1.Com/Examples/Images/sql/OpenXml/SQL_OpenXml.png
Rearranged the code a little..
ALTER PROCEDURE
[dbo].[Users_Update_Phones_2]
@UserName
nvarchar(256) = null
,@SqldocPhone
varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
EXEC
sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
IF EXISTS
(select 1 from users.phones a where a.phone = x.phone)
BEGIN
update
users.phones set phonetypecd = x.phonetypecd,
extention = x.extention, userid = @userid
WHERE
IsLockedOut = 0 AND [Users.Phones].[Phone] = x.Phone
END
ELSE
BEGIN
INSERT INTO
[Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
VALUES
(x.Phone, x.PhoneTypeCD, x.Extention, @user-id)
END
FROM
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH (
Phone
nvarchar(12), PhoneTypeCD nvarchar(3),Extention nvarchar(10),PhonesID INT
)x
EXEC
sp_xml_removedocument @idoc
END
RETURN
Dam again!
May 16, 2006 at 5:40 pm
How about this? I moved the 'END' from the middle of the insert.
ALTER PROCEDURE [dbo].[Users_Update_Phones_2]
@UserName nvarchar(256) = null
,@SqldocPhone
varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
EXEC
sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
IF EXISTS
(select 1 from users.phones a where a.phone = x.phone)
BEGIN
update
users.phones set phonetypecd = x.phonetypecd,
extention = x.extention, userid = @userid
WHERE
IsLockedOut = 0 AND [Users.Phones].[Phone] = x.Phone
END
ELSE
BEGIN
INSERT INTO
[Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
select
(x.Phone, x.PhoneTypeCD, x.Extention, @user-id)
From
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH (Phone nvarchar(12), PhoneTypeCD nvarchar(3),Extention nvarchar(10),PhonesID INT)x
EXEC
sp_xml_removedocument @idoc
END
END
RETURN
May 16, 2006 at 6:01 pm
Updated Link:
http://www.afcc1.com/Examples/Images/sql/OpenXml/SQL_OpenXml_1.png
Updated Code..
Getting a different error now... i have arranged around with no luck
ALTER PROCEDURE
[dbo].[Users_Update_Phones_2]
@UserName
nvarchar(256) = null ,@SqldocPhone varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
EXEC
sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
IF EXISTS
(select 1 from users.phones a where a.phone = x.phone)
BEGIN
update
users.phones set phonetypecd = x.phonetypecd,
phone = x.phone,
extention = x.extention
WHERE
IsLockedOut = 0 AND [Users.Phones].[UserID] = @user-id
END
ELSE
BEGIN
INSERT INTO
[Users.Phones] (Phone, PhoneTypeCD, Extention, UserID)
select
(x.Phone, x.PhoneTypeCD, x.Extention, x.PhoneID)
From
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
END
WITH
(Phone nvarchar(12), PhoneTypeCD nvarchar(3),Extention nvarchar(10),PhonesID INT)x
EXEC
sp_xml_removedocument @idoc
END
RETURN
Dam again!
May 16, 2006 at 6:09 pm
You can't have the 'END' after the OPENXML. It breaks up the insert in an unacceptable manner.
May 16, 2006 at 6:20 pm
i am lost on this one.. Keeps telling me that incorrect syntax near ",".
Dam again!
May 16, 2006 at 6:31 pm
Could you post the table DDL and the XML file (or a sample of it)? I might have some time to play with this tomorrow. It's been a while since I've played with the XML portions of the DBMS, so if someone sees my error and can jump in, please do so.
May 16, 2006 at 7:02 pm
Thank you very much for your help..
This querry works great...
ALTER PROCEDURE
[dbo].[Users_Update_Phones]
@UserName
nvarchar(256) = null,
@SqldocPhone
varchar (4000)
AS
DECLARE
@idoc int
DECLARE
@user-id uniqueidentifier
SELECT
@user-id = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName)= LoweredUserName
IF
(@UserID IS NOT NULL)
BEGIN
EXEC
sp_xml_preparedocument @idoc OUTPUT, @SqldocPhone
INSERT INTO
[Users.Phones] (PhoneTypeCD, Phone, Extention)
SELECT
PhoneTypeCD, Phone, Extention
FROM OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH
(PhonesID int,PhoneTypeCD nvarchar(3), Phone nvarchar(12),Extention nvarchar(10))
WHERE
PhonesID NOT IN (SELECT PhonesID FROM [Users.Phones])
UPDATE
[Users.Phones]
SET
[Users.Phones].[PhoneTypeCD] = x.PhoneTypeCD,
[Users.Phones].[Phone] = x.Phone,
[Users.Phones].[Extention] = x.Extention
FROM
OPENXML
(@idoc,'/ROOT/UsersPhones',1)
WITH
(PhonesID INT,PhoneTypeCD nvarchar(3), Phone nvarchar(12),Extention nvarchar(10)) x
WHERE
[Users.Phones].[PhonesID] = x.PhonesID EXEC sp_xml_removedocument @idoc
END
RETURN
Dam again!
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
