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]]

Re: sql problem with updating data from a gridview

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 #FU9WYIsIHA.120 0@TK2MSFTNGP03. phx.gbl, "Chris"
<ssd@qsd.dcwrot e:

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
>
>

[Chris]

Re: sql problem with updating data from a gridview

Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraaschreef in
bericht news:C4479439.3 D4B%ten.xoc@dna rtreb.noraa...

>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 #FU9WYIsIHA.120 0@TK2MSFTNGP03. phx.gbl,
"Chris"
<ssd@qsd.dcwrot e:
>

>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
>>
>>

>

[Peter Bromberg [C# MVP]]

Re: sql problem with updating data from a gridview

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" <ssd@qsd.dcwrot e in message
news:uF5L0oIsIH A.2188@TK2MSFTN GP04.phx.gbl...

Hi, sorry the error is:
>
Incorrect syntax near the keyword 'INNER'
>
>
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraaschreef in
bericht news:C4479439.3 D4B%ten.xoc@dna rtreb.noraa...

>>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 #FU9WYIsIHA.120 0@TK2MSFTNGP03. phx.gbl,
>"Chris"
><ssd@qsd.dcwro te:
>>

>>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.heur e 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]]

Re: sql problem with updating data from a gridview

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]

Re: sql problem with updating data from a gridview

Thanks, it works now

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraaschreef in
bericht news:88ABDF45-EB78-41EC-9558-8EC3A019F450@mi crosoft.com...

>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?