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_01C1EAF6.ACDF3C10
Content-Type: text/plain

Hi,

 

Could someone please help me to figure out why this SQL statement:

 

SQLcmd1="Insert Into PROGRESS (StateModificationEnd) Values (#08/08/2002#)
where StateAbbrev='x2' "

 

would give me the following error:

 

Error Type:
Microsoft JET Database Engine (0x80040E14)
Missing semicolon (;) at end of SQL statement.

 

 

My Code:

stateAbbrev=Replace(Request("StateAbbrev"),"'","''")

stateFinal=Replace(Request("txtStateFinal"),"'","''")

 

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

newconn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data
source=e:\web\Test1\Test1.mdb"

 

'** This statement does NOT work****

SQLcmd1="Insert Into PROGRESS (StateModificationEnd) Values (#08/08/2002#)
where StateAbbrev='x2' "

 

'**This Insert statement does NOT work *****

'SQLcmd1="Insert Into PROGRESS (StateFinalDate) Values ('"&stateFinal&"')
Where StateAbbrev='"& stateAbbrev &"'"

 

'**This Insert statement worked!**

'SQLcmd1="Insert Into PROGRESS (StateAbbrev,StateEffectiveDate) Values
('x2',#07/07/2002#)"

 

'**This update statement works!***

'SQLcmd1="Update PROGRESS set stateEffectiveDate=#09/09/2002# where
StateAbbrev='x2' " 

 

newconn.Execute SQLcmd1

newconn.Close

 

 

Thanks a lot!

 

Cindy

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Cindy Huang" <YHuang@c...>
Subject: [access_asp] Help needed in SQL query


: Could someone please help me to figure out why this SQL statement:
:
: SQLcmd1="Insert Into PROGRESS (StateModificationEnd) Values (#08/08/2002#)
: where StateAbbrev='x2' "
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Missing semicolon (;) at end of SQL statement.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

An INSERT INTO clause can not have a WHERE clause because you are inserting
a *new* record

If you want to change records where StartAbbrev = 'x2' then you need to use
an UPDATE statement.

The reason you are getting the error is because the INSERT INTO statement
should finish after the VALUES(...) clause, and Access then thinks that
anything after that is part of a second SQL statement, which in turn
requires a batch delimiter (ie a ;)

Cheers
Ken

Ken:

Thank you so much. It's working now using UPDATE statement. Cindy

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Cindy Huang" <YHuang@c...>
Subject: [access_asp] Help needed in SQL query


: Could someone please help me to figure out why this SQL statement:
:
: SQLcmd1="Insert Into PROGRESS (StateModificationEnd) Values 
(#08/08/2002#)
: where StateAbbrev='x2' "
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Missing semicolon (;) at end of SQL statement.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

An INSERT INTO clause can not have a WHERE clause because you are inserting
a *new* record

If you want to change records where StartAbbrev = 'x2' then you need to use
an UPDATE statement.

The reason you are getting the error is because the INSERT INTO statement
should finish after the VALUES(...) clause, and Access then thinks that
anything after that is part of a second SQL statement, which in turn
requires a batch delimiter (ie a ;)

Cheers
Ken