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;

On 5/7/08 5:05 PM, in article #FU9WYIs...@TK2MSFTNGP03.phx.gbl, "Chris"

[Chris]

Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> schreef in
bericht news:C4479439.3D4B%ten...@dnartreb.noraa...

[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

"Chris" <s...@qsd.dc> wrote in message
news:uF5L0oIs...@TK2MSFTNGP04.phx.gbl...

[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

"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> schreef in

bericht news:88ABDF45-EB78-41EC...@microsoft.com...