Syntax Error in Update Statement 
Author Message
 Syntax Error in Update Statement

I have a simple data adpater and dataset that I populate and can access in
code no problem.
The conection is to a simple access DB.
When I change a value in a row of the dataset - no problem.
When I execute the Update method on the data adapter I get a "Syntax Error
in UPDATE statement" error

I've checked the update comman in the adapter and it looks fine.

What is driving me up the wall (apart from not knowing why I am gettin ghte
error!) is I can't for the life of me find out where or how I can see and
debug the actual SQL statements that ADO is trying to execute.

It tells me that there is a syntax error but I can't see it to see what the
syntax error is .

Somebody please help before I throw all my books at the cat!
Kevin



Fri, 26 Aug 2005 04:53:33 GMT
 Syntax Error in Update Statement

Kevin,

Looks like you have some field names in your SQL statement, which are
reserved words. You would need to warp them into square brackets
([fieldName]). Could you post your SQL statement here?

--
Val Mazur
Microsoft MVP


Quote:
> I have a simple data adpater and dataset that I populate and can access in
> code no problem.
> The conection is to a simple access DB.
> When I change a value in a row of the dataset - no problem.
> When I execute the Update method on the data adapter I get a "Syntax Error
> in UPDATE statement" error

> I've checked the update comman in the adapter and it looks fine.

> What is driving me up the wall (apart from not knowing why I am gettin
ghte
> error!) is I can't for the life of me find out where or how I can see and
> debug the actual SQL statements that ADO is trying to execute.

> It tells me that there is a syntax error but I can't see it to see what
the
> syntax error is .

> Somebody please help before I throw all my books at the cat!
> Kevin



Fri, 26 Aug 2005 08:47:41 GMT
 Syntax Error in Update Statement
Hi Val,

The SQL Statement in the adpatrs update property is as follows...
"UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?, DateSent =
?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ? WHERE
(ID = ?)"

I can't find anyway of seeing what the actical statement is that is being
executed though, i.e. with all the ?'s replaced by apropriate values.

I need to see the actual SQL statement that is being executed and causing
the error....

Kevin.


Quote:
> Kevin,

> Looks like you have some field names in your SQL statement, which are
> reserved words. You would need to warp them into square brackets
> ([fieldName]). Could you post your SQL statement here?

> --
> Val Mazur
> Microsoft MVP



> > I have a simple data adpater and dataset that I populate and can access
in
> > code no problem.
> > The conection is to a simple access DB.
> > When I change a value in a row of the dataset - no problem.
> > When I execute the Update method on the data adapter I get a "Syntax
Error
> > in UPDATE statement" error

> > I've checked the update comman in the adapter and it looks fine.

> > What is driving me up the wall (apart from not knowing why I am gettin
> ghte
> > error!) is I can't for the life of me find out where or how I can see
and
> > debug the actual SQL statements that ADO is trying to execute.

> > It tells me that there is a syntax error but I can't see it to see what
> the
> > syntax error is .

> > Somebody please help before I throw all my books at the cat!
> > Kevin



Fri, 26 Aug 2005 18:55:41 GMT
 Syntax Error in Update Statement
Kevin,

Try to remove all the fields from the UPDATE statement except one, see of it
works, then add fields one-by-one to see which one throws error. I think it
could be Message or ID fields. Then wrap them into square brackets, like

"UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?, DateSent =
 ?, [Message] = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
WHERE ([ID] = ?)"

--
Val Mazur
Microsoft MVP


Quote:
> Hi Val,

> The SQL Statement in the adpatrs update property is as follows...
> "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?, DateSent
=
> ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
WHERE
> (ID = ?)"

> I can't find anyway of seeing what the actical statement is that is being
> executed though, i.e. with all the ?'s replaced by apropriate values.

> I need to see the actual SQL statement that is being executed and causing
> the error....

> Kevin.



> > Kevin,

> > Looks like you have some field names in your SQL statement, which are
> > reserved words. You would need to warp them into square brackets
> > ([fieldName]). Could you post your SQL statement here?

> > --
> > Val Mazur
> > Microsoft MVP



> > > I have a simple data adpater and dataset that I populate and can
access
> in
> > > code no problem.
> > > The conection is to a simple access DB.
> > > When I change a value in a row of the dataset - no problem.
> > > When I execute the Update method on the data adapter I get a "Syntax
> Error
> > > in UPDATE statement" error

> > > I've checked the update comman in the adapter and it looks fine.

> > > What is driving me up the wall (apart from not knowing why I am gettin
> > ghte
> > > error!) is I can't for the life of me find out where or how I can see
> and
> > > debug the actual SQL statements that ADO is trying to execute.

> > > It tells me that there is a syntax error but I can't see it to see
what
> > the
> > > syntax error is .

> > > Somebody please help before I throw all my books at the cat!
> > > Kevin



Fri, 26 Aug 2005 20:37:33 GMT
 Syntax Error in Update Statement
Hi Val,

Nice one mate!

It turned out to be the "Action" column name it didn't like. When I enclosed
it in square brackets, as you suggested i.e. "[Action]" it works fine.

Thanks a lot, I can let me cat out of the cupboard now ;-)

Kevin.

Quote:
> Kevin,

> Try to remove all the fields from the UPDATE statement except one, see of
it
> works, then add fields one-by-one to see which one throws error. I think
it
> could be Message or ID fields. Then wrap them into square brackets, like

> "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?, DateSent
=
>  ?, [Message] = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
> WHERE ([ID] = ?)"

> --
> Val Mazur
> Microsoft MVP



> > Hi Val,

> > The SQL Statement in the adpatrs update property is as follows...
> > "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?,
DateSent
> =
> > ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
> WHERE
> > (ID = ?)"

> > I can't find anyway of seeing what the actical statement is that is
being
> > executed though, i.e. with all the ?'s replaced by apropriate values.

> > I need to see the actual SQL statement that is being executed and
causing
> > the error....

> > Kevin.



> > > Kevin,

> > > Looks like you have some field names in your SQL statement, which are
> > > reserved words. You would need to warp them into square brackets
> > > ([fieldName]). Could you post your SQL statement here?

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > I have a simple data adpater and dataset that I populate and can
> access
> > in
> > > > code no problem.
> > > > The conection is to a simple access DB.
> > > > When I change a value in a row of the dataset - no problem.
> > > > When I execute the Update method on the data adapter I get a "Syntax
> > Error
> > > > in UPDATE statement" error

> > > > I've checked the update comman in the adapter and it looks fine.

> > > > What is driving me up the wall (apart from not knowing why I am
gettin
> > > ghte
> > > > error!) is I can't for the life of me find out where or how I can
see
> > and
> > > > debug the actual SQL statements that ADO is trying to execute.

> > > > It tells me that there is a syntax error but I can't see it to see
> what
> > > the
> > > > syntax error is .

> > > > Somebody please help before I throw all my books at the cat!
> > > > Kevin



Fri, 26 Aug 2005 21:28:23 GMT
 Syntax Error in Update Statement

Quote:

> Hi Val,

> The SQL Statement in the adpatrs update property is as follows...
> "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?, DateSent =
> ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ? WHERE
> (ID = ?)"

> I can't find anyway of seeing what the actical statement is that is being
> executed though, i.e. with all the ?'s replaced by apropriate values.

> I need to see the actual SQL statement that is being executed and causing
> the error....

> Kevin.



> > Kevin,

> > Looks like you have some field names in your SQL statement, which are
> > reserved words. You would need to warp them into square brackets
> > ([fieldName]). Could you post your SQL statement here?

> > --
> > Val Mazur
> > Microsoft MVP



> > > I have a simple data adpater and dataset that I populate and can access
>  in
> > > code no problem.
> > > The conection is to a simple access DB.
> > > When I change a value in a row of the dataset - no problem.
> > > When I execute the Update method on the data adapter I get a "Syntax
>  Error
> > > in UPDATE statement" error

> > > I've checked the update comman in the adapter and it looks fine.

> > > What is driving me up the wall (apart from not knowing why I am gettin
>  ghte
> > > error!) is I can't for the life of me find out where or how I can see
>  and
> > > debug the actual SQL statements that ADO is trying to execute.

> > > It tells me that there is a syntax error but I can't see it to see what
>  the
> > > syntax error is .

> > > Somebody please help before I throw all my books at the cat!
> > > Kevin

Kevin

In your code where you build your SQL string place a break point and
run your program.   When the program stops at your break point you
either use the immediate window to view the string where your SQL code
is
e.g.
? strSQL

or you can look at your code and move the cursor over variable with
your code in it and all should be revelled.

Another possibility to add to Val's answer is that you are not placing
quotes around string values so the SQL for the column Attachment = ?,
looks like ..., Attachment = A text file, ... instead of ....,
Attachment = "A text file", ...

Hope this helps

Duncan



Fri, 26 Aug 2005 23:11:02 GMT
 Syntax Error in Update Statement
Hi Duncan,

Thaks for the info, but I'm not building the SQL statement, ADO.NET is and
thats where I have the probelm. You can see the basic statement with place
holders that it generates in the proprties but you can't see anywhere the
actual statement that is being executed.....

the [] round the action column fixed it, although I would still like to find
out how you can see the actual SQL being executed....



Quote:
> > Hi Val,

> > The SQL Statement in the adpatrs update property is as follows...
> > "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?,
DateSent =
> > ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
WHERE
> > (ID = ?)"

> > I can't find anyway of seeing what the actical statement is that is
being
> > executed though, i.e. with all the ?'s replaced by apropriate values.

> > I need to see the actual SQL statement that is being executed and
causing
> > the error....

> > Kevin.



> > > Kevin,

> > > Looks like you have some field names in your SQL statement, which are
> > > reserved words. You would need to warp them into square brackets
> > > ([fieldName]). Could you post your SQL statement here?

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > I have a simple data adpater and dataset that I populate and can
access
> >  in
> > > > code no problem.
> > > > The conection is to a simple access DB.
> > > > When I change a value in a row of the dataset - no problem.
> > > > When I execute the Update method on the data adapter I get a "Syntax
> >  Error
> > > > in UPDATE statement" error

> > > > I've checked the update comman in the adapter and it looks fine.

> > > > What is driving me up the wall (apart from not knowing why I am
gettin
> >  ghte
> > > > error!) is I can't for the life of me find out where or how I can
see
> >  and
> > > > debug the actual SQL statements that ADO is trying to execute.

> > > > It tells me that there is a syntax error but I can't see it to see
what
> >  the
> > > > syntax error is .

> > > > Somebody please help before I throw all my books at the cat!
> > > > Kevin

> Kevin

> In your code where you build your SQL string place a break point and
> run your program.   When the program stops at your break point you
> either use the immediate window to view the string where your SQL code
> is
> e.g.
> ? strSQL

> or you can look at your code and move the cursor over variable with
> your code in it and all should be revelled.

> Another possibility to add to Val's answer is that you are not placing
> quotes around string values so the SQL for the column Attachment = ?,
> looks like ..., Attachment = A text file, ... instead of ....,
> Attachment = "A text file", ...

> Hope this helps

> Duncan



Fri, 26 Aug 2005 23:41:13 GMT
 Syntax Error in Update Statement
Kevin,

Is it SQL Server? If yes, then you could use SQL Profiler, which comes with
SQL Server to see how it executes your statement

--
Val Mazur
Microsoft MVP


Quote:
> Hi Duncan,

> Thaks for the info, but I'm not building the SQL statement, ADO.NET is and
> thats where I have the probelm. You can see the basic statement with place
> holders that it generates in the proprties but you can't see anywhere the
> actual statement that is being executed.....

> the [] round the action column fixed it, although I would still like to
find
> out how you can see the actual SQL being executed....





> > > Hi Val,

> > > The SQL Statement in the adpatrs update property is as follows...
> > > "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?,
> DateSent =
> > > ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject = ?
> WHERE
> > > (ID = ?)"

> > > I can't find anyway of seeing what the actical statement is that is
> being
> > > executed though, i.e. with all the ?'s replaced by apropriate values.

> > > I need to see the actual SQL statement that is being executed and
> causing
> > > the error....

> > > Kevin.



> > > > Kevin,

> > > > Looks like you have some field names in your SQL statement, which
are
> > > > reserved words. You would need to warp them into square brackets
> > > > ([fieldName]). Could you post your SQL statement here?

> > > > --
> > > > Val Mazur
> > > > Microsoft MVP



> > > > > I have a simple data adpater and dataset that I populate and can
> access
> > >  in
> > > > > code no problem.
> > > > > The conection is to a simple access DB.
> > > > > When I change a value in a row of the dataset - no problem.
> > > > > When I execute the Update method on the data adapter I get a
"Syntax
> > >  Error
> > > > > in UPDATE statement" error

> > > > > I've checked the update comman in the adapter and it looks fine.

> > > > > What is driving me up the wall (apart from not knowing why I am
> gettin
> > >  ghte
> > > > > error!) is I can't for the life of me find out where or how I can
> see
> > >  and
> > > > > debug the actual SQL statements that ADO is trying to execute.

> > > > > It tells me that there is a syntax error but I can't see it to see
> what
> > >  the
> > > > > syntax error is .

> > > > > Somebody please help before I throw all my books at the cat!
> > > > > Kevin

> > Kevin

> > In your code where you build your SQL string place a break point and
> > run your program.   When the program stops at your break point you
> > either use the immediate window to view the string where your SQL code
> > is
> > e.g.
> > ? strSQL

> > or you can look at your code and move the cursor over variable with
> > your code in it and all should be revelled.

> > Another possibility to add to Val's answer is that you are not placing
> > quotes around string values so the SQL for the column Attachment = ?,
> > looks like ..., Attachment = A text file, ... instead of ....,
> > Attachment = "A text file", ...

> > Hope this helps

> > Duncan



Sat, 27 Aug 2005 01:03:04 GMT
 Syntax Error in Update Statement
Hi Val,

Unfortunately Not. Its access 2002/200 format.


Quote:
> Kevin,

> Is it SQL Server? If yes, then you could use SQL Profiler, which comes
with
> SQL Server to see how it executes your statement

> --
> Val Mazur
> Microsoft MVP



> > Hi Duncan,

> > Thaks for the info, but I'm not building the SQL statement, ADO.NET is
and
> > thats where I have the probelm. You can see the basic statement with
place
> > holders that it generates in the proprties but you can't see anywhere
the
> > actual statement that is being executed.....

> > the [] round the action column fixed it, although I would still like to
> find
> > out how you can see the actual SQL being executed....





> > > > Hi Val,

> > > > The SQL Statement in the adpatrs update property is as follows...
> > > > "UPDATE WorkToDo SET Action = ?, Attachment = ?, CreditsLeft = ?,
> > DateSent =
> > > > ?, Message = ?, MessageFormat = ?, ReturnEmailAddress = ?, Subject =
?
> > WHERE
> > > > (ID = ?)"

> > > > I can't find anyway of seeing what the actical statement is that is
> > being
> > > > executed though, i.e. with all the ?'s replaced by apropriate
values.

> > > > I need to see the actual SQL statement that is being executed and
> > causing
> > > > the error....

> > > > Kevin.



> > > > > Kevin,

> > > > > Looks like you have some field names in your SQL statement, which
> are
> > > > > reserved words. You would need to warp them into square brackets
> > > > > ([fieldName]). Could you post your SQL statement here?

> > > > > --
> > > > > Val Mazur
> > > > > Microsoft MVP



> > > > > > I have a simple data adpater and dataset that I populate and can
> > access
> > > >  in
> > > > > > code no problem.
> > > > > > The conection is to a simple access DB.
> > > > > > When I change a value in a row of the dataset - no problem.
> > > > > > When I execute the Update method on the data adapter I get a
> "Syntax
> > > >  Error
> > > > > > in UPDATE statement" error

> > > > > > I've checked the update comman in the adapter and it looks fine.

> > > > > > What is driving me up the wall (apart from not knowing why I am
> > gettin
> > > >  ghte
> > > > > > error!) is I can't for the life of me find out where or how I
can
> > see
> > > >  and
> > > > > > debug the actual SQL statements that ADO is trying to execute.

> > > > > > It tells me that there is a syntax error but I can't see it to
see
> > what
> > > >  the
> > > > > > syntax error is .

> > > > > > Somebody please help before I throw all my books at the cat!
> > > > > > Kevin

> > > Kevin

> > > In your code where you build your SQL string place a break point and
> > > run your program.   When the program stops at your break point you
> > > either use the immediate window to view the string where your SQL code
> > > is
> > > e.g.
> > > ? strSQL

> > > or you can look at your code and move the cursor over variable with
> > > your code in it and all should be revelled.

> > > Another possibility to add to Val's answer is that you are not placing
> > > quotes around string values so the SQL for the column Attachment = ?,
> > > looks like ..., Attachment = A text file, ... instead of ....,
> > > Attachment = "A text file", ...

> > > Hope this helps

> > > Duncan



Sat, 27 Aug 2005 01:46:26 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. (0x80040E14) Syntax error in UPDATE statement.

2. Syntax error in UPDATE statement while using a Memo field

3. (0x80040E14) Syntax error in UPDATE statement.

4. Syntax error in UPDATE statement

5. HE: Syntax Error in UPDATE statement

6. Syntax error in UPDATE statement

7. Why "Syntax error in UPDATE statement" error?

8. -2147217900 Error: Syntax error in INSERT INTO statement

9. NEWBIE: easy syntax question with UPDATE statement

10. Help with SQL update statement syntax

11. SQL Update Statement Syntax Help

12. Need help with Syntax on Update or Insert SQL Statement


 
Powered by phpBB® Forum Software