473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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
Jun 27 '08 #1
5 1132
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 #F**************@TK2MSFTNGP03.phx.gbl, "Chris"
<ss*@qsd.dcwrote:
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

Jun 27 '08 #2
Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraaschreef in
bericht news:C4*******************@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 #F**************@TK2MSFTNGP03.phx.gbl,
"Chris"
<ss*@qsd.dcwrote:
>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


Jun 27 '08 #3
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" <ss*@qsd.dcwrote in message
news:uF**************@TK2MSFTNGP04.phx.gbl...
Hi, sorry the error is:

Incorrect syntax near the keyword 'INNER'
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraaschreef in
bericht news:C4*******************@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 #F**************@TK2MSFTNGP03.phx.gbl,
"Chris"
<ss*@qsd.dcwrote:
>>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


Jun 27 '08 #4
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?

Jun 27 '08 #5
Thanks, it works now

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraaschreef in
bericht news:88**********************************@microsof t.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?

Jun 27 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Michael | last post by:
Hi Everyone, I've been having a problem with the Gridview control. I have posted a few messages relating to the issues, but I have a general question. Does the Dataview control have to be bound to...
1
by: =?Utf-8?B?Q2hyaXM=?= | last post by:
Hi, I have a gridview which I added a <asp:CommandField EditText="E" CancelText="C" UpdateText="U" ButtonType="Link" ShowEditButton="True" /> my gridview looks like this <asp:GridView...
1
by: ist | last post by:
Hi, I am trying to create a Session-Wide, Disconnected, Updatable DataTable (or DataSet) object and use it through an ASP.NET GridView. The scenario is this: 1. When the page is first loaded,...
4
by: =?Utf-8?B?QmFyYmFyYSBBbGRlcnRvbg==?= | last post by:
I setup a simple gridview as a utility just to do some updates, nothing fancy just wanted easy UI to make updates. When I select ‘Edit’, I get the fields I want to edit. I edit them and click...
2
by: foocc | last post by:
Hi. I have a problem with gridview whereby when i want to update 1 row, other row changes as well. For example, i change the quantity in row 1 from 1 to 2, but when i click on update, other row's...
2
by: DC | last post by:
Hi, I am using a GridView to present data in a DataTable, which I store only in ViewState and when the user hits the "OK" button the rows in the DataTable will be used to execute transactions. ...
5
by: cmrchs | last post by:
Hi, I'm trying out Databinding to a Data Acces Layer using a ObjectDataSource-control The Update works fine but the Delete-method doesn't. when debugging I see that my productID-parameter is...
0
by: steve | last post by:
I have been fighting with trying to update a GridView for a while. I don't want to use the "built-in" way to do it because I am using business layer methods for updating and deleteing and I don't...
0
by: BizWeb | last post by:
Hi, i am new to ASP.NET. I have write a very simple code to try the updating of the GridView but it is not updating the data. Below is the simple code that i have use. <%@ Page Language="vb"...
1
by: idan | last post by:
Here's my data: (Table: EmployeePlan) EmpID Plan Status abc123 PlanA A abc123 PlanB D abc123 PlanC A xyz789 PlanA D xyz789 PlanB D xyz789 PlanC...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.