sql problem with updating data from a gridview

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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;
 
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
 
Hi, sorry the error is:
Incorrect syntax near the keyword 'INNER'
Click to expand...

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?
 
Thanks, it works now

Aaron Bertrand said:
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?
Click to expand...
 
Back
Top