If someone has any idea about what's wrong with this code please give me 

some advice!

The code has to do with the field password of the table Students in my 

Access Database.



Here's the code:

<%

  Dim conn

  Set conn = Server.CreateObject("ADODB.Connection")

  conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

                                "Data Source=C:\Inetpub\wwwroot\ptixiaki2

\db\dbdistant.mdb;"  &_

								"Persist 

Security Info=False"



strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd") 

& "' WHERE StudentID= " & Session("StudentID") 

 conn.Execute strSQL

 conn.close

  Response.Redirect "UserProfile_after.asp"

  

 %>





Here's the error:

Error Type:

Microsoft JET Database Engine (0x80040E14)

Syntax error in UPDATE statement.

/onlinecampus/UpdatePassword.asp, line 15



Line 15: conn.Execute strSQL



Thanks a lot!!

--tsivas

hey, it's not a good idea to make multiple (different) posts on the same

problem you have!



right after you create the sql string, add the following two lines:



response.write strSQL

response.end



further more, if you're using iis4, add "response.buffer = true" to the very

top of the page!



if you process the page and it doesn't show your sql string, the problem is

that that part of the code is never executed, otherwise copy the sql string

that's printed upon processing the form and post it here.



the problems is to be found within the sql string, so the most logical thing

is to response.write it and see if the result is what you expected.



-Kim





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

> From: tsivas [mailto:tsivas@o...]

> Sent: 10. december 2001 22:01

> To: ASP Databases

> Subject: [asp_databases] Syntax error in UPDATE statement

>

>

> If someone has any idea about what's wrong with this code please give me

> some advice!

> The code has to do with the field password of the table Students in my

> Access Database.

>

> Here's the code:

> <%

>   Dim conn

>   Set conn = Server.CreateObject("ADODB.Connection")

>   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

>                                 "Data Source=C:\Inetpub\wwwroot\ptixiaki2

> \db\dbdistant.mdb;"  &_

> 								"Persist

> Security Info=False"

>

> strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

> & "' WHERE StudentID= " & Session("StudentID")

>  conn.Execute strSQL

>  conn.close

>   Response.Redirect "UserProfile_after.asp"

>

>  %>

>

>

> Here's the error:

> Error Type:

> Microsoft JET Database Engine (0x80040E14)

> Syntax error in UPDATE statement.

> /onlinecampus/UpdatePassword.asp, line 15

>

> Line 15: conn.Execute strSQL

>

> Thanks a lot!!

> --tsivas




> $subst('Email.Unsub').

>

This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C181C7.D6F73FCC

Content-Type: text/plain;

	charset="iso-8859-1"



Also have a look at:

http://www.adopenstatic.com/faq/800a0bb9.asp



Ken's site has a lot of good information and a good FAQ for some of the more

common ADO errors.



regards

David Cameron

nOw.b2b

dcameron@i...



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

From: Kim Iwan Hansen [mailto:kimiwan@k...]

Sent: Tuesday, 11 December 2001 8:11 AM

To: ASP Databases

Subject: [asp_databases] RE: Syntax error in UPDATE statement





hey, it's not a good idea to make multiple (different) posts on the same

problem you have!



right after you create the sql string, add the following two lines:



response.write strSQL

response.end



further more, if you're using iis4, add "response.buffer = true" to the very

top of the page!



if you process the page and it doesn't show your sql string, the problem is

that that part of the code is never executed, otherwise copy the sql string

that's printed upon processing the form and post it here.



the problems is to be found within the sql string, so the most logical thing

is to response.write it and see if the result is what you expected.



-Kim





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

> From: tsivas [mailto:tsivas@o...]

> Sent: 10. december 2001 22:01

> To: ASP Databases

> Subject: [asp_databases] Syntax error in UPDATE statement

>

>

> If someone has any idea about what's wrong with this code please give me

> some advice!

> The code has to do with the field password of the table Students in my

> Access Database.

>

> Here's the code:

> <%

>   Dim conn

>   Set conn = Server.CreateObject("ADODB.Connection")

>   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

>                                 "Data Source=C:\Inetpub\wwwroot\ptixiaki2

> \db\dbdistant.mdb;"  &_

> 								"Persist

> Security Info=False"

>

> strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

> & "' WHERE StudentID= " & Session("StudentID")

>  conn.Execute strSQL

>  conn.close

>   Response.Redirect "UserProfile_after.asp"

>

>  %>

>

>

> Here's the error:

> Error Type:

> Microsoft JET Database Engine (0x80040E14)

> Syntax error in UPDATE statement.

> /onlinecampus/UpdatePassword.asp, line 15

>

> Line 15: conn.Execute strSQL

>

> Thanks a lot!!

> --tsivas




> $subst('Email.Unsub').

>








$subst('Email.Unsub').

First of all thanks a lot for your interesting!

You are right about making different posts on the same problem! Sorry!



About my problem, I processed the page with the 2 lines you suggested and 

it worked :

UPDATE Students SET Password = 'qwerty' WHERE StudentID= 2

But how this result will update my database(Access 2000) automatically or 

in a way that only the administrator is able to view??

Thank you!

--tsivas

ok, here's how it goes



response.end ENDS the execution of the script, so if you add it before you

do the actual insertion of data (objCn.execute(strSQL)), it won't update the

password.



so what you do is to print the sql string, then stop the page and avoid the

error message - the lines that come after the response.end are never

interpreted.



now you can take your sql string and look at the content of it.  is Password

supposed to be a string? is it not too many characters?  is studentid

supposed to be a number?  look at your database and check that the date

you're trying to insert complies with the way the table is set up.



a good tool for checking if the update statement is good is the query

analyzer.  just open access and go to queries > create query in design view

> close > right-click the grey area of the window and select sql view. this

is where you paste in the update statement and run (the red "!").  test it

till it works in query analyzer, then move it to the asp code.



play around with it and see if you can figure it out.  of course, if you

can't, just come back and let us know what you've tried and what happened :)



these couple of routines will save you many posts in the future :)



-Kim



p.s. like david said, ken's www.adopenstatic.com is always a good place to

check before posting.  he's got some of the most commonly asked questions,

plus answers.





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

> From: tsivas [mailto:tsivas@o...]

> Sent: 10. december 2001 22:45

> To: ASP Databases

> Subject: [asp_databases] RE: Syntax error in UPDATE statement

>

>

> First of all thanks a lot for your interesting!

> You are right about making different posts on the same problem! Sorry!

>

> About my problem, I processed the page with the 2 lines you suggested and

> it worked :

> UPDATE Students SET Password = 'qwerty' WHERE StudentID= 2

> But how this result will update my database(Access 2000) automatically or

> in a way that only the administrator is able to view??

> Thank you!

> --tsivas




> $subst('Email.Unsub').

>

I really appreciate the way you're trying to help me. 

But I really don't know if I got the point.. 

do I have to put the response.end at the end of the code? I tried some 

different ways to put it above the execute statement but there's always an 

error: ADODB.Connection error '800a0e78' 

Operation is not allowed when the object is closed. 

/OnLineCampus/UpdatePassword.asp, line 18 

this is line 18: conn.Execute strSQL

--  OR --

Microsoft JET Database Engine error '80040e14' 

Syntax error in string in query expression ''StudentID= 2'. 

/OnLineCampus/UpdatePassword.asp, line 17 

this is line 17: conn.Execute strSQL





I also checked the update statement and like you said I used the query 

analyzer. It seemed to be ok but why I'm still having problem with that??



I also visited adopenstatic.com but it didn't help me this time..

If you can help me please do it one more time...  I am a little bit 

disappointed that I can't manage it by myself :(((



Thank you very much,

tsivas

www.adopenstatic.com/faq/80040e14.asp



Password is a reserved word - you can't use it unescaped in an SQL

statement - see the link above.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "tsivas" <tsivas@o...>

Subject: [asp_databases] Syntax error in UPDATE statement





: If someone has any idea about what's wrong with this code please give me

: some advice!

: The code has to do with the field password of the table Students in my

: Access Database.

:

: Here's the code:

: <%

:   Dim conn

:   Set conn = Server.CreateObject("ADODB.Connection")

:   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

:                                 "Data Source=C:\Inetpub\wwwroot\ptixiaki2

: \db\dbdistant.mdb;"  &_

: "Persist

: Security Info=False"

:

: strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

: & "' WHERE StudentID= " & Session("StudentID")

:  conn.Execute strSQL

:  conn.close

:   Response.Redirect "UserProfile_after.asp"

:

:  %>

:

:

: Here's the error:

: Error Type:

: Microsoft JET Database Engine (0x80040E14)

: Syntax error in UPDATE statement.

: /onlinecampus/UpdatePassword.asp, line 15

:

: Line 15: conn.Execute strSQL



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I don't understand how you could be getting two *completely* different error

messages just because you added a single Response.Write() line.



The original problem you were having "Syntax error in update statement" can

be solved by looking at: www.adopenstatic.com/faq/80040e14.asp



You are getting *different* error messages now - please post the code you

are using that generates the error messages below.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "tsivas" <tsivas@o...>

Subject: [asp_databases] RE: Syntax error in UPDATE statement





: I really appreciate the way you're trying to help me.

: But I really don't know if I got the point..

: do I have to put the response.end at the end of the code? I tried some

: different ways to put it above the execute statement but there's always an

: error: ADODB.Connection error '800a0e78'

: Operation is not allowed when the object is closed.

: /OnLineCampus/UpdatePassword.asp, line 18

: this is line 18: conn.Execute strSQL

: --  OR --

: Microsoft JET Database Engine error '80040e14'

: Syntax error in string in query expression ''StudentID= 2'.

: /OnLineCampus/UpdatePassword.asp, line 17

: this is line 17: conn.Execute strSQL



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

makes sense :)  ..thanks from me too :)



-Kim



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

> From: Ken Schaefer [mailto:ken@a...]

> Sent: 11. december 2001 02:21

> To: ASP Databases

> Subject: [asp_databases] Re: Syntax error in UPDATE statement

>

>

> www.adopenstatic.com/faq/80040e14.asp

>

> Password is a reserved word - you can't use it unescaped in an SQL

> statement - see the link above.

>

> Cheers

> Ken

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "tsivas" <tsivas@o...>

> Subject: [asp_databases] Syntax error in UPDATE statement

>

>

> : If someone has any idea about what's wrong with this code please give me

> : some advice!

> : The code has to do with the field password of the table Students in my

> : Access Database.

> :

> : Here's the code:

> : <%

> :   Dim conn

> :   Set conn = Server.CreateObject("ADODB.Connection")

> :   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

> :                                 "Data

> Source=C:\Inetpub\wwwroot\ptixiaki2

> : \db\dbdistant.mdb;"  &_

> : "Persist

> : Security Info=False"

> :

> : strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

> : & "' WHERE StudentID= " & Session("StudentID")

> :  conn.Execute strSQL

> :  conn.close

> :   Response.Redirect "UserProfile_after.asp"

> :

> :  %>

> :

> :

> : Here's the error:

> : Error Type:

> : Microsoft JET Database Engine (0x80040E14)

> : Syntax error in UPDATE statement.

> : /onlinecampus/UpdatePassword.asp, line 15

> :

> : Line 15: conn.Execute strSQL

>

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>

>




> $subst('Email.Unsub').

>

Can you do a Response.Write strSQL to see what it looks like?

Comment out the conn.Execute



>From: "tsivas" <tsivas@o...>

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

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

>Subject: [asp_databases] Syntax error in UPDATE statement

>Date: Mon, 10 Dec 2001 22:01:03

>

>If someone has any idea about what's wrong with this code please give me

>some advice!

>The code has to do with the field password of the table Students in my

>Access Database.

>

>Here's the code:

><%

>   Dim conn

>   Set conn = Server.CreateObject("ADODB.Connection")

>   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"  &_

>                                 "Data Source=C:\Inetpub\wwwroot\ptixiaki2

>\db\dbdistant.mdb;"  &_

>								"Persist

>Security Info=False"

>

>strSQL = "UPDATE Students SET Password = '" & Request.Form("txtnewpwd")

>& "' WHERE StudentID= " & Session("StudentID")

>  conn.Execute strSQL

>  conn.close

>   Response.Redirect "UserProfile_after.asp"

>

>  %>

>

>

>Here's the error:

>Error Type:

>Microsoft JET Database Engine (0x80040E14)

>Syntax error in UPDATE statement.

>/onlinecampus/UpdatePassword.asp, line 15

>

>Line 15: conn.Execute strSQL

>

>Thanks a lot!!

>--tsivas




>$subst('Email.Unsub').





_________________________________________________________________

Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp