sql problem with updating data from a gridview

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
Wed May 07 16:15:37 CDT 2008

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.1200@TK2MSFTNGP03.phx.gbl, "Chris"
<ssd@qsd.dc> wrote:

> 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
Wed May 07 16:34:59 CDT 2008

Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schreef in
bericht news:C4479439.3D4B%ten.xoc@dnartreb.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.1200@TK2MSFTNGP03.phx.gbl,
> "Chris"
> <ssd@qsd.dc> wrote:
>
>> 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
Wed May 07 18:22:38 CDT 2008

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.dc> wrote in message
news:uF5L0oIsIHA.2188@TK2MSFTNGP04.phx.gbl...
> Hi, sorry the error is:
>
> Incorrect syntax near the keyword 'INNER'
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schreef in
> bericht news:C4479439.3D4B%ten.xoc@dnartreb.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.1200@TK2MSFTNGP03.phx.gbl,
>> "Chris"
>> <ssd@qsd.dc> wrote:
>>
>>> 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
Wed May 07 18:31:23 CDT 2008

> 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
Thu May 08 16:15:35 CDT 2008

Thanks, it works now

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> schreef in
bericht news:88ABDF45-EB78-41EC-9558-8EC3A019F450@microsoft.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?