Syntax error in UPDATE statement- asp/jscript

[Robert Mark Bram]

Hi All!

I have the following two methods in an asp/jscript page - my problem is that
without the update statement there is no error, but with the update
statement I get the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/polyprint/dataEntry.asp, line 158

I fail to understand why there is a syntax error - I am pretty sure all of
the parameters to the Open method are ok..

Any advice would be most welcome!

Rob
:)

function modifyNewsletter()
{
var _newsletter = getParameter ("edition");
var _language = getParameter ("language");

var sql = "SELECT * " +
"FROM newsletterText " +
"WHERE (((newsletterText.pageName)='" + _newsletter + "') " +
"AND ((newsletterText.language)='" + _language + "')) " +
"order by newsletterText.paragraph asc";

var newsletterText = getRecordSetForModifyingRecords (sql);

while (!newsletterText.EOF)
{
Response.Write (newsletterText ("text") + " --- " +
newsletterText ("style") + "<br>");

newsletterText ("text") = "changed...";

// problem statement
newsletterText.Update;

Response.Write (newsletterText ("text") + " --- " +
newsletterText ("style") + "<br>");


newsletterText.MoveNext();
} // end while


// Gets a recordset for modifying records.
// Uses the adLockOptimistic lock and accepts an sql statement.
function getRecordSetForModifyingRecords (sql)
{
var rs = Server.CreateObject ("ADODB.Recordset");
rs.Open (sql, // Source
polyprintConnection, // ActiveConnection
adOpenForwardOnly, // CursorType
adLockOptimistic); // LockType
return rs;
} // end getRecordSetForModifyingRecords function

[Stuart Palmer]

Try response.writing your variable sql to the screen and see what the lines
says, try and post that as a query directly into access and see if it gives
you anything.

It could be something to do with the way you are breaking up the sql
statement (like missing a space accidently) so put it all in one line for
the moment might help and then when you are done, then you can break it up.

I don't know about JS with serverside stuff, but in vbscript you should put
something like

sql = "SELECT * "sql = sql +
sql = sql + "FROM newsletterText "sql = sql +
sql = sql + "WHERE (((newsletterText.pageName)='" + _newsletter + "') "sql
= sql +
sql = sql + "AND ((newsletterText.language)='" + _language + "')) "sql =
sql +
sql = sql + "order by newsletterText.paragraph asc"

Not saying any of these things are the prob, but may help trouble shoot the
issues you are seeing.

Good luck

Stu



"Robert Mark Bram" <relaxedrob@removethis.optushome.com.au> wrote in message
news:3f9518d0$0$9554$afc38c87@news.optusnet.com.au ...

> Hi All!
>
> I have the following two methods in an asp/jscript page - my problem is

that

> without the update statement there is no error, but with the update
> statement I get the following error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE

statement.

> /polyprint/dataEntry.asp, line 158
>
> I fail to understand why there is a syntax error - I am pretty sure all of
> the parameters to the Open method are ok..
>
> Any advice would be most welcome!
>
> Rob
> :)
>
> function modifyNewsletter()
> {
> var _newsletter = getParameter ("edition");
> var _language = getParameter ("language");
>
> var sql = "SELECT * " +
> "FROM newsletterText " +
> "WHERE (((newsletterText.pageName)='" + _newsletter + "') "

+

> "AND ((newsletterText.language)='" + _language + "')) " +
> "order by newsletterText.paragraph asc";
>
> var newsletterText = getRecordSetForModifyingRecords (sql);
>
> while (!newsletterText.EOF)
> {
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
> newsletterText ("text") = "changed...";
>
> // problem statement
> newsletterText.Update;
>
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
>
> newsletterText.MoveNext();
> } // end while
>
>
> // Gets a recordset for modifying records.
> // Uses the adLockOptimistic lock and accepts an sql statement.
> function getRecordSetForModifyingRecords (sql)
> {
> var rs = Server.CreateObject ("ADODB.Recordset");
> rs.Open (sql, // Source
> polyprintConnection, // ActiveConnection
> adOpenForwardOnly, // CursorType
> adLockOptimistic); // LockType
> return rs;
> } // end getRecordSetForModifyingRecords function
>
>

[Ray at]

Where's line 158? And what does Response.write(sql) show you for the SQL
query just before that line?

Ray at work

"Robert Mark Bram" <relaxedrob@removethis.optushome.com.au> wrote in message
news:3f9518d0$0$9554$afc38c87@news.optusnet.com.au ...

> Hi All!
>
> I have the following two methods in an asp/jscript page - my problem is

that

> without the update statement there is no error, but with the update
> statement I get the following error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE

statement.

> /polyprint/dataEntry.asp, line 158
>
> I fail to understand why there is a syntax error - I am pretty sure all of
> the parameters to the Open method are ok..
>
> Any advice would be most welcome!
>
> Rob
> :)
>
> function modifyNewsletter()
> {
> var _newsletter = getParameter ("edition");
> var _language = getParameter ("language");
>
> var sql = "SELECT * " +
> "FROM newsletterText " +
> "WHERE (((newsletterText.pageName)='" + _newsletter + "') "

+

> "AND ((newsletterText.language)='" + _language + "')) " +
> "order by newsletterText.paragraph asc";
>
> var newsletterText = getRecordSetForModifyingRecords (sql);
>
> while (!newsletterText.EOF)
> {
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
> newsletterText ("text") = "changed...";
>
> // problem statement
> newsletterText.Update;
>
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
>
> newsletterText.MoveNext();
> } // end while
>
>
> // Gets a recordset for modifying records.
> // Uses the adLockOptimistic lock and accepts an sql statement.
> function getRecordSetForModifyingRecords (sql)
> {
> var rs = Server.CreateObject ("ADODB.Recordset");
> rs.Open (sql, // Source
> polyprintConnection, // ActiveConnection
> adOpenForwardOnly, // CursorType
> adLockOptimistic); // LockType
> return rs;
> } // end getRecordSetForModifyingRecords function
>
>

[Mike Florio]

Looks like you're opening the recordset as ForwardOnly - which is obviously
not updateable !


"Robert Mark Bram" <relaxedrob@removethis.optushome.com.au> wrote in message
news:3f9518d0$0$9554$afc38c87@news.optusnet.com.au ...

> Hi All!
>
> I have the following two methods in an asp/jscript page - my problem is

that

> without the update statement there is no error, but with the update
> statement I get the following error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE

statement.

> /polyprint/dataEntry.asp, line 158
>
> I fail to understand why there is a syntax error - I am pretty sure all of
> the parameters to the Open method are ok..
>
> Any advice would be most welcome!
>
> Rob
> :)
>
> function modifyNewsletter()
> {
> var _newsletter = getParameter ("edition");
> var _language = getParameter ("language");
>
> var sql = "SELECT * " +
> "FROM newsletterText " +
> "WHERE (((newsletterText.pageName)='" + _newsletter + "') "

+

> "AND ((newsletterText.language)='" + _language + "')) " +
> "order by newsletterText.paragraph asc";
>
> var newsletterText = getRecordSetForModifyingRecords (sql);
>
> while (!newsletterText.EOF)
> {
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
> newsletterText ("text") = "changed...";
>
> // problem statement
> newsletterText.Update;
>
> Response.Write (newsletterText ("text") + " --- " +
> newsletterText ("style") + "<br>");
>
>
> newsletterText.MoveNext();
> } // end while
>
>
> // Gets a recordset for modifying records.
> // Uses the adLockOptimistic lock and accepts an sql statement.
> function getRecordSetForModifyingRecords (sql)
> {
> var rs = Server.CreateObject ("ADODB.Recordset");
> rs.Open (sql, // Source
> polyprintConnection, // ActiveConnection
> adOpenForwardOnly, // CursorType
> adLockOptimistic); // LockType
> return rs;
> } // end getRecordSetForModifyingRecords function
>
>