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').

Message #4 by "tsivas" <tsivas@o...> on Mon, 10 Dec 2001 22:44:50

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').
>

Message #6 by "tsivas" <tsivas@o...> on Tue, 11 Dec 2001 00:57:01

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

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~