p2p.wrox.com: Archives - asp

asp_databases thread: Microsoft JET Database Engine error '80040e14' Syntax error in UPDATE statement.

Message #1 by "jong wan chin" <jongwc@s...> on Wed, 18 Jul 2001 09:45:56

dear all,

i try to update the record in my database.  but get the error message:

"Microsoft JET Database Engine error '80040e14'  Syntax error in UPDATE 

statement. "



the problem line is: cnn.execute strSQL



can anyone tell me what's wrong with my code?



======================== my code==================

For i=1 to lngTotal

  rst.MoveFirst

  do while NOT rst.EOF



  UpdateLevel=request.Form("UpdateLEVEL")(i) 

   'UpdateLevel is number

  skillID=request.Form("skillID")(i) 

   'skillID is string

 

  strSQL = "UPDATE EMPLOYEE_SKILL" & _ 

           "SET EMPLOYEE_SKILL.ACH_LEVEL = ' " & UpdateLevel & " ' " & _ 

           "WHERE EMPLOYEE_SKILL.SKILL_ID=' " & skillID& " ' " 



  Response.Write(strSQL) 



   cnn.execute strSQL

   rst.MoveNext 

  loop 

  Next

==================================================================

what i get from Response.Write(strSQL) is 



UPDATE EMPLOYEE_SKILLSET EMPLOYEE_SKILL.ACH_LEVEL = ' 1 ' WHERE 

EMPLOYEE_SKILL.SKILL_ID=' S0000001 ' 



that means my sql does contain data but why it still got syntax error?



please help.



thanks.



regards,

jong

Message #2 by Steve Carter <Steve.Carter@t...> on Wed, 18 Jul 2001 09:51:49 +0100

If EMPLOYEE_SKILL.ACH_LEVEL is numeric then you need to leave out the '

marks around its value.



> -----Original Message-----

> From: jong wan chin [mailto:jongwc@s...]

> Sent: 18 July 2001 10:46

> To: ASP Databases

> Subject: [asp_databases] Microsoft JET Database Engine error

> '80040e14'

> Syntax error in UPDATE statement.

>

>

> dear all,

> i try to update the record in my database.  but get the error 

message:

> "Microsoft JET Database Engine error '80040e14'  Syntax error

> in UPDATE

> statement. "

>

> the problem line is: cnn.execute strSQL

>

> can anyone tell me what's wrong with my code?

>

> 

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

 my code=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> For i=3D1 to lngTotal

>   rst.MoveFirst

>   do while NOT rst.EOF

>

>   UpdateLevel=3Drequest.Form("UpdateLEVEL")(i)

>    'UpdateLevel is number

>   skillID=3Drequest.Form("skillID")(i)

>    'skillID is string

> 

>   strSQL =3D "UPDATE EMPLOYEE_SKILL" & _

>            "SET EMPLOYEE_SKILL.ACH_LEVEL =3D ' " & UpdateLevel

> & " ' " & _

>            "WHERE EMPLOYEE_SKILL.SKILL_ID=3D' " & skillID& " ' "

>

>   Response.Write(strSQL)

>

>    cnn.execute strSQL

>    rst.MoveNext

>   loop

>   Next

> 

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> what i get from Response.Write(strSQL) is

>

> UPDATE EMPLOYEE_SKILLSET EMPLOYEE_SKILL.ACH_LEVEL =3D ' 1 ' WHERE

> EMPLOYEE_SKILL.SKILL_ID=3D' S0000001 '

>

> that means my sql does contain data but why it still got syntax 

error?

>

> please help.

>

> thanks.

>

> regards,

> jong

>

Message #3 by "Drew, Ron" <RDrew@B...> on Wed, 18 Jul 2001 08:16:53 -0400

Problem line is just the execute statement..we have to look back at the 

sql

statement above.

I think your problem is that there is NO space between SKILL and SET 

(spaces

in general)

Your statement is::::

strSQL =3D "UPDATE EMPLOYEE_SKILL" & _

           "SET EMPLOYEE_SKILL.ACH_LEVEL =3D ' " & UpdateLevel & " ' " 

& _

           "WHERE EMPLOYEE_SKILL.SKILL_ID=3D' " & skillID& " ' "

try:::

strSQL =3D "UPDATE EMPLOYEE_SKILL " & _

           "SET EMPLOYEE_SKILL.ACH_LEVEL =3D '" & UpdateLevel & "' " & 

_

           "WHERE EMPLOYEE_SKILL.SKILL_ID=3D '" & skillID& "' "









-----Original Message-----

From: jong wan chin [mailto:jongwc@s...]

Sent: Wednesday, July 18, 2001 5:46 AM

To: ASP Databases

Subject: [asp_databases] Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.





dear all,

i try to update the record in my database.  but get the error message:

"Microsoft JET Database Engine error '80040e14'  Syntax error in UPDATE 



statement. "



the problem line is: cnn.execute strSQL



can anyone tell me what's wrong with my code?



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

 my code=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

For i=3D1 to lngTotal

  rst.MoveFirst

  do while NOT rst.EOF



  UpdateLevel=3Drequest.Form("UpdateLEVEL")(i)

   'UpdateLevel is number

  skillID=3Drequest.Form("skillID")(i)

   'skillID is string



  strSQL =3D "UPDATE EMPLOYEE_SKILL" & _

           "SET EMPLOYEE_SKILL.ACH_LEVEL =3D ' " & UpdateLevel & " ' " 

& _

           "WHERE EMPLOYEE_SKILL.SKILL_ID=3D' " & skillID& " ' "



  Response.Write(strSQL)



   cnn.execute strSQL

   rst.MoveNext

  loop

  Next

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

what i get from Response.Write(strSQL) is



UPDATE EMPLOYEE_SKILLSET EMPLOYEE_SKILL.ACH_LEVEL =3D ' 1 ' WHERE

EMPLOYEE_SKILL.SKILL_ID=3D' S0000001 '



that means my sql does contain data but why it still got syntax error?



please help.



thanks.



regards,

jong

Message #4 by "jong wan chin" <jongwc@s...> on Thu, 19 Jul 2001 08:45:31 +0800

dear Drew and Steve,

thanks a lot.  my problem solve already.  :)



regards,

jong



----- Original Message -----

From: "Drew, Ron" <RDrew@B...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 18, 2001 8:16 PM

Subject: [asp_databases] RE: Microsoft JET Database Engine error '80040e14 '

Syntax error in UPDATE statement.





> Problem line is just the execute statement..we have to look back at the 

> sql

> statement above.

> I think your problem is that there is NO space between SKILL and SET 

> (spaces

> in general)

> Your statement is::::

> strSQL =3D "UPDATE EMPLOYEE_SKILL" & _

>            "SET EMPLOYEE_SKILL.ACH_LEVEL =3D ' " & UpdateLevel & " ' " 

> & _

>            "WHERE EMPLOYEE_SKILL.SKILL_ID=3D' " & skillID& " ' "

> try:::

> strSQL =3D "UPDATE EMPLOYEE_SKILL " & _

>            "SET EMPLOYEE_SKILL.ACH_LEVEL =3D '" & UpdateLevel & "' " & 

> _

>            "WHERE EMPLOYEE_SKILL.SKILL_ID=3D '" & skillID& "' "

>

>

>

>

> -----Original Message-----

> From: jong wan chin [mailto:jongwc@s...]

> Sent: Wednesday, July 18, 2001 5:46 AM

> To: ASP Databases

> Subject: [asp_databases] Microsoft JET Database Engine error '80040e14'

> Syntax error in UPDATE statement.

>

>

> dear all,

> i try to update the record in my database.  but get the error message:

> "Microsoft JET Database Engine error '80040e14'  Syntax error in UPDATE 

>

> statement. "

>

> the problem line is: cnn.execute strSQL

>

> can anyone tell me what's wrong with my code?

>

> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

>  my code=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> For i=3D1 to lngTotal

>   rst.MoveFirst

>   do while NOT rst.EOF

>

>   UpdateLevel=3Drequest.Form("UpdateLEVEL")(i)

>    'UpdateLevel is number

>   skillID=3Drequest.Form("skillID")(i)

>    'skillID is string

> 

>   strSQL =3D "UPDATE EMPLOYEE_SKILL" & _

>            "SET EMPLOYEE_SKILL.ACH_LEVEL =3D ' " & UpdateLevel & " ' " 

> & _

>            "WHERE EMPLOYEE_SKILL.SKILL_ID=3D' " & skillID& " ' "

>

>   Response.Write(strSQL)

>

>    cnn.execute strSQL

>    rst.MoveNext

>   loop

>   Next

> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

> what i get from Response.Write(strSQL) is

>

> UPDATE EMPLOYEE_SKILLSET EMPLOYEE_SKILL.ACH_LEVEL =3D ' 1 ' WHERE

> EMPLOYEE_SKILL.SKILL_ID=3D' S0000001 '

>

> that means my sql does contain data but why it still got syntax error?

>

> please help.

>

> thanks.

>

> regards,

> jong

>

>

Return to Index

P2P Archives