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 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
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
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
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?
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?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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...
|
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...
| |