sql problem with updating data from a gridview

[Chris]

Hi,

i defined a gridview and a sqldatasource with following commands: (two
tables 'tableA' and 'tableB' are linked with field 'heure')

SelectCommand="SELECT tableA.mday, tableB.range FROM tableB INNER JOIN
tableA ON tableB.heure = tableA.heure"

UpdateCommand="UPDATE tableB INNER JOIN tableA ON tableB.heure =
tableA.heure SET tableA.mday = @mday
WHERE tableB.range = @range"

the select command is ok.
i tested the update command with MS Access and it works.
With asp.net/sql server, it gives an error: Incorrect syntax near the
keyword 'FROM'.

Could anybody tell me the right syntax?

Thanks
Chris

 

[Aaron Bertrand [SQL Server MVP]]

I don't even see the keyword "FROM" in the UpdateCommand text. Are you sure
that is where the error is coming from?

Anyway, I think the syntax you want is the proprietary UPDATE FROM syntax
(which I don't think will work in Access):


UPDATE A
SET A.mday = @mday
FROM tableA A
INNER JOIN tableB B
ON A.heure = B.heure
WHERE B.heure = @range;

 

[Chris]

Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'

 

[Peter Bromberg [C# MVP]]

I think you might need to resort to something like this:


UPDATE tableB SET tableA.mday = @mday
WHERE tableB.range = @range
AND
tableA.heure= tableB.heure

--Peter

 

[Aaron Bertrand [SQL Server MVP]]

Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'

Well, it's valid SQL Server syntax. Here is a simple repro you can try in
Management Studio:


USE tempdb;
GO

CREATE TABLE tableA
(
mday INT,
heure INT
);

CREATE TABLE tableB
(
heure INT
);
GO

SET NOCOUNT ON;

INSERT tableA SELECT 1,5;
INSERT tableA SELECT 2,5;
INSERT tableA SELECT 3,4;

INSERT tableB SELECT 5;
INSERT tableB SELECT 6;
GO

SELECT * FROM tableA;
/*
1 5
2 5
3 4
*/
GO

DECLARE @mday INT, @range INT;

SELECT @mday = 42, @range = 5;

UPDATE A
SET A.mday = @mday
FROM tableA A
INNER JOIN tableB B
ON A.heure = B.heure
WHERE B.heure = @range;

SELECT * FROM tableA;
GO
/*
42 5
42 5
3 4
*/

DROP TABLE tableA, tableB;
GO



Maybe .NET is doing you a "favor" by lexing against a different set of
syntax? Have you considered using stored procedures for data modifications,
instead of ad hoc SQL?

 

[Chris]

Thanks, it works now

Well, it's valid SQL Server syntax. Here is a simple repro you can try in
Management Studio:


USE tempdb;
GO

CREATE TABLE tableA
(
mday INT,
heure INT
);

CREATE TABLE tableB
(
heure INT
);
GO

SET NOCOUNT ON;

INSERT tableA SELECT 1,5;
INSERT tableA SELECT 2,5;
INSERT tableA SELECT 3,4;

INSERT tableB SELECT 5;
INSERT tableB SELECT 6;
GO

SELECT * FROM tableA;
/*
1 5
2 5
3 4
*/
GO

DECLARE @mday INT, @range INT;

SELECT @mday = 42, @range = 5;

UPDATE A
SET A.mday = @mday
FROM tableA A
INNER JOIN tableB B
ON A.heure = B.heure
WHERE B.heure = @range;

SELECT * FROM tableA;
GO
/*
42 5
42 5
3 4
*/

DROP TABLE tableA, tableB;
GO



Maybe .NET is doing you a "favor" by lexing against a different set of
syntax? Have you considered using stored procedures for data
modifications, instead of ad hoc SQL?