Expected end of statement

i got this kind of error type:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/wanhe/services/update_succeed.asp, line 80, column 129
sqlStrDB = "update applicant set Name='"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"',HighEdu='"&hEducation&"',EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

this is my coding:
<%
dim cnStrDB
dim rcSetDB
dim sqlStrDB

'create connection and recordset objects
Set cnStrDB = Server.CreateObject("ADODB.Connection")
Set rcSetDB = Server.CreateObject("ADODB.Recordset")

' connection string
dim pathDB
dim providerDB

' change this path to your database path
pathDB = "C:\Inetpub\wwwroot\wanhe\services\oApplication.mdb "
providerDB = "Microsoft.Jet.OLEDB.4.0"

' defining database connection (connectionstring.asp)
cnStrDB.ConnectionString = pathDB
cnStrDB.Provider = providerDB
cnStrDB.Mode = 3

cnStrDB.open
dim username
Dim coNameCN
Dim coNameEN
Dim coAddress
Dim coPostal
dim gender
dim age
dim maritalstatus
dim hEducation
dim engScore
dim toeflScore
dim ieltsScore
dim fysmScore
dim ncetScore
dim appSchool
dim appMajor
Dim coTel
Dim userID
Dim coMobile
dim coEmail
dim coComment

username = request.form("userName")
coNameCN = request.form("txtCoNameCN")
coNameEN= request.form("txtCoNameEN")
coAddress= request.form("txtCoAddress")
coPostal= request.form("txtPostal")
coTel= request.form("Tel")
coMobile= request.form("Mobile")
coEmail= request.form("Email")
coComment= request.form("Comment")
gender= request.form("Gender")
age= request.form("Age")
maritalstatus= request.form("Age")
hEducation= request.form("highEdu")
'response.write hEducation
'response.end
engScore= request.form("EngScore")
toeflScore= request.form("ToeflScore")
ieltsScore= request.form("IeltsScore")
fysmScore= request.form("FysmScore")
ncetScore= request.form("NcetScore")
appSchool= request.form("AppSchool")
appMajor= request.form("AppMajor")

userID=Request.cookies("ID")

sqlStrDB = "update applicant set Name= '"&username&"', Gender= '"&gender&"', Age='"&age&"',Marriage='"&maritalstatus&"', HighEdu='"&hEducation&"', EngScore='"&engScore&"',ToeflScore='"&toeflScore&"',IeltsScore='"&ieltsScore&"',FysmScore='"&fysmScore&"',NcetScore='"&ncetScore&"',Tel='"&coTel&"',Mobile='"&coMobile&"',Email='"&coEmail&"',AppSchool='"&appSchool&"',AppMajor='"&appMajor&"',Comment='"&coComment&"',companyNameCN='"&coNameCN&"',companyNameEN='"&coNameEN&"',CoAddress='"&coAddress&"',CoPostal='"&coPostal&"' where ID = "&userID&""

rcSetDB.ActiveConnection = cnStrDB
rcSetDB.cursorType = 3 'for add record into database
rcSetDB.cursorLocation = 3
rcSetDB.open(sqlStrDB)

%>

i know it's somehting wrong with
HighEdu='"&hEducation&"
but i cant find where is wrong

anyone can helps me??
thanks a lot
:()Maybe that "" at the end should have been ";"?

You should really use prepared statements with bind variables. Not only do they improve performance and remove a security loophole, they also make it easier to write your SQL:

sqlStrDB = "update applicant set Name=?, Gender=?, Age=?,Marriage=?, HighEdu=?, ... , CoPostal=? where ID = ?;"|||same error message|||your first line:
sqlStrDB = "Your statement" _ (add the "_" at the end)

your second line:
& "Your statement" (add "&" at the first)

try to manipulate it if error happens again. I think it should work because you have to separate your string to another lines by these characters.

regards.

chris|||the problem is the variable name
i change to another name can liao

by the way thanks for ur help

Execution/compilation

What is the difference beteween execution och compilation in SQL Server?
I suspect that SELECT-statements aren't compiled while CREATE
TABLE-statements are? Is that correct?Have I understood you right if Query execution is the same as Query
compilation?
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F4760AF.C87F25CD@.toomuchspamalready.nl...
> When you submit a SELECT statement, SQL-Server will go through different
> stages before it returns the resultset.
> Parsing: first the statement will be analysed to see if it is
> syntactically correct, and if all object names can be found in the
> schema.
> Query optimizing: during this stage, SQL-Server will create execution
> plans that will give the correct result. There are usually many
> different query plans that give the same result, but one query plan
> might be much more efficient than the other. It is also possible, that
> the query plan of an earlier compilation is still in cache. In that
> case, this fase is skipped.
> Query executing: the fastest query plan will be executed. This is where
> the actual rows are read from disk (or buffer cache) and all 'real'
> processing is done.
> The steps above are all relevant for DML (such as SELECT, UPDATE, etc.)
>
> When it comes to DDL (such as CREATE TABLE statements), it is not
> possible to optimize the query. The statements are straightforward and
> can be executed immediately after parsing.
> Gert-Jan
>
> Stefan wrote:
> >
> > What is the difference beteween execution och compilation in SQL Server?
> >
> > I suspect that SELECT-statements aren't compiled while CREATE
> > TABLE-statements are? Is that correct?

Execution plan compilation performance troubles

Hello all,
Could anybody give me hints or links on what is affecting stored procedure
execution plan compilation time ?
I execute this statement on 2 different machines:
EXEC MyProc WITH RECOMPILE
On M1 I have 20s for compilation of the execution plan and 1s for proc
execution
on M2 I have 50s for compilation of the execution plan and 1s for proc
execution
MyProc is quite big (3MB text file) and does a lot of insert.
M1 is P4 2.6GHZ XPSP2 SQL Server Developper
M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
Are there some parameters affecting execution plan compilation time ?
Both machines are idle and devoted to performance tests...
ThanksFourmi,
Are the databases exactly the same? Same indexes, statistics current and
up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
exec plans generated the same?
HTH
Jerry
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks|||Jerry,
The databases are exactly the same (in fact I transfered the mdf and log to
copy the DB). They have the same structure, indexes, triggers, constraints
etc. To be sure I updated the statistics on both servers.
Also the SQL server configuration is exactly the same as shown by sp_configu
re
I cleaned the Proccache with DBCC FREEPROCCACHE.
The exec plans are the same.
And this factor 2 in time for sme operations !
When I generated the plan on M1 it took 2'44'' and on M2 4'48''
Additional infos
The proc is only one big transaction with a lot of inserts.
M2 is StandardEdition and not EE.
M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
without raid on another machine.
M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
In fact all our computers running WindowsServer2003 seems to have this
problem regardless of 2003 SP1 or not.
"Jerry Spivey" wrote:

> Fourmi,
> Are the databases exactly the same? Same indexes, statistics current and
> up-to-date for both, comparable fragmentation, buffer cache cleared? Are t
he
> exec plans generated the same?
> HTH
> Jerry
> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
>
>|||You will never get good compile performance on a 3MB procedure. You really
should think about breaking it up into several smaller sp's that can be more
easily compiled and are less likely to recompile.
Andrew J. Kelly SQL MVP
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:C7AA619B-BDD5-4F22-9FAA-96D37778EC2A@.microsoft.com...[vbcol=seagreen]
> Jerry,
> The databases are exactly the same (in fact I transfered the mdf and log
> to
> copy the DB). They have the same structure, indexes, triggers, constraints
> etc. To be sure I updated the statistics on both servers.
> Also the SQL server configuration is exactly the same as shown by
> sp_configure
> I cleaned the Proccache with DBCC FREEPROCCACHE.
> The exec plans are the same.
> And this factor 2 in time for sme operations !
> When I generated the plan on M1 it took 2'44'' and on M2 4'48''
>
> Additional infos
> The proc is only one big transaction with a lot of inserts.
> M2 is StandardEdition and not EE.
> M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
> without raid on another machine.
> M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
> In fact all our computers running WindowsServer2003 seems to have this
> problem regardless of 2003 SP1 or not.
>
> "Jerry Spivey" wrote:
>|||I would suggest that you modify the SQL server setting to only use 1 CPU on
the Processor tab of the server properties. Strange things sometimes happens
on multiproc boxes. If this helps you - have a look at "option (maxdop)"
/Fredrik
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks

Execution plan compilation performance troubles

Hello all,
Could anybody give me hints or links on what is affecting stored procedure
execution plan compilation time ?
I execute this statement on 2 different machines:
EXEC MyProc WITH RECOMPILE
On M1 I have 20s for compilation of the execution plan and 1s for proc
execution
on M2 I have 50s for compilation of the execution plan and 1s for proc
execution
MyProc is quite big (3MB text file) and does a lot of insert.
M1 is P4 2.6GHZ XPSP2 SQL Server Developper
M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
Are there some parameters affecting execution plan compilation time ?
Both machines are idle and devoted to performance tests...
Thanks
Fourmi,
Are the databases exactly the same? Same indexes, statistics current and
up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
exec plans generated the same?
HTH
Jerry
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks
|||Jerry,
The databases are exactly the same (in fact I transfered the mdf and log to
copy the DB). They have the same structure, indexes, triggers, constraints
etc. To be sure I updated the statistics on both servers.
Also the SQL server configuration is exactly the same as shown by sp_configure
I cleaned the Proccache with DBCC FREEPROCCACHE.
The exec plans are the same.
And this factor 2 in time for sme operations !
When I generated the plan on M1 it took 2'44'' and on M2 4'48''
Additional infos
The proc is only one big transaction with a lot of inserts.
M2 is StandardEdition and not EE.
M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
without raid on another machine.
M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
In fact all our computers running WindowsServer2003 seems to have this
problem regardless of 2003 SP1 or not.
"Jerry Spivey" wrote:

> Fourmi,
> Are the databases exactly the same? Same indexes, statistics current and
> up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
> exec plans generated the same?
> HTH
> Jerry
> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
>
>
|||You will never get good compile performance on a 3MB procedure. You really
should think about breaking it up into several smaller sp's that can be more
easily compiled and are less likely to recompile.
Andrew J. Kelly SQL MVP
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:C7AA619B-BDD5-4F22-9FAA-96D37778EC2A@.microsoft.com...[vbcol=seagreen]
> Jerry,
> The databases are exactly the same (in fact I transfered the mdf and log
> to
> copy the DB). They have the same structure, indexes, triggers, constraints
> etc. To be sure I updated the statistics on both servers.
> Also the SQL server configuration is exactly the same as shown by
> sp_configure
> I cleaned the Proccache with DBCC FREEPROCCACHE.
> The exec plans are the same.
> And this factor 2 in time for sme operations !
> When I generated the plan on M1 it took 2'44'' and on M2 4'48''
>
> Additional infos
> The proc is only one big transaction with a lot of inserts.
> M2 is StandardEdition and not EE.
> M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
> without raid on another machine.
> M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
> In fact all our computers running WindowsServer2003 seems to have this
> problem regardless of 2003 SP1 or not.
>
> "Jerry Spivey" wrote:
|||I would suggest that you modify the SQL server setting to only use 1 CPU on
the Processor tab of the server properties. Strange things sometimes happens
on multiproc boxes. If this helps you - have a look at "option (maxdop)"
/Fredrik
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks

Execution plan compilation performance troubles

Hello all,
Could anybody give me hints or links on what is affecting stored procedure
execution plan compilation time ?
I execute this statement on 2 different machines:
EXEC MyProc WITH RECOMPILE
On M1 I have 20s for compilation of the execution plan and 1s for proc
execution
on M2 I have 50s for compilation of the execution plan and 1s for proc
execution
MyProc is quite big (3MB text file) and does a lot of insert.
M1 is P4 2.6GHZ XPSP2 SQL Server Developper
M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
Are there some parameters affecting execution plan compilation time ?
Both machines are idle and devoted to performance tests...
ThanksFourmi,
Are the databases exactly the same? Same indexes, statistics current and
up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
exec plans generated the same?
HTH
Jerry
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks|||Jerry,
The databases are exactly the same (in fact I transfered the mdf and log to
copy the DB). They have the same structure, indexes, triggers, constraints
etc. To be sure I updated the statistics on both servers.
Also the SQL server configuration is exactly the same as shown by sp_configure
I cleaned the Proccache with DBCC FREEPROCCACHE.
The exec plans are the same.
And this factor 2 in time for sme operations !
When I generated the plan on M1 it took 2'44'' and on M2 4'48''
Additional infos
The proc is only one big transaction with a lot of inserts.
M2 is StandardEdition and not EE.
M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
without raid on another machine.
M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
In fact all our computers running WindowsServer2003 seems to have this
problem regardless of 2003 SP1 or not.
"Jerry Spivey" wrote:
> Fourmi,
> Are the databases exactly the same? Same indexes, statistics current and
> up-to-date for both, comparable fragmentation, buffer cache cleared? Are the
> exec plans generated the same?
> HTH
> Jerry
> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> > Hello all,
> >
> > Could anybody give me hints or links on what is affecting stored procedure
> > execution plan compilation time ?
> >
> > I execute this statement on 2 different machines:
> > EXEC MyProc WITH RECOMPILE
> >
> > On M1 I have 20s for compilation of the execution plan and 1s for proc
> > execution
> > on M2 I have 50s for compilation of the execution plan and 1s for proc
> > execution
> >
> > MyProc is quite big (3MB text file) and does a lot of insert.
> >
> > M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> > M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> >
> > Are there some parameters affecting execution plan compilation time ?
> >
> > Both machines are idle and devoted to performance tests...
> >
> > Thanks
>
>|||You will never get good compile performance on a 3MB procedure. You really
should think about breaking it up into several smaller sp's that can be more
easily compiled and are less likely to recompile.
--
Andrew J. Kelly SQL MVP
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:C7AA619B-BDD5-4F22-9FAA-96D37778EC2A@.microsoft.com...
> Jerry,
> The databases are exactly the same (in fact I transfered the mdf and log
> to
> copy the DB). They have the same structure, indexes, triggers, constraints
> etc. To be sure I updated the statistics on both servers.
> Also the SQL server configuration is exactly the same as shown by
> sp_configure
> I cleaned the Proccache with DBCC FREEPROCCACHE.
> The exec plans are the same.
> And this factor 2 in time for sme operations !
> When I generated the plan on M1 it took 2'44'' and on M2 4'48''
>
> Additional infos
> The proc is only one big transaction with a lot of inserts.
> M2 is StandardEdition and not EE.
> M2 is scsi dual disks in raid 1 but same problem with only one scsi disk
> without raid on another machine.
> M2 is 4GBMem. M1 is 1GBmem (and I am disappointed...)
> In fact all our computers running WindowsServer2003 seems to have this
> problem regardless of 2003 SP1 or not.
>
> "Jerry Spivey" wrote:
>> Fourmi,
>> Are the databases exactly the same? Same indexes, statistics current and
>> up-to-date for both, comparable fragmentation, buffer cache cleared? Are
>> the
>> exec plans generated the same?
>> HTH
>> Jerry
>> "fourmi" <fourmi@.discussions.microsoft.com> wrote in message
>> news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
>> > Hello all,
>> >
>> > Could anybody give me hints or links on what is affecting stored
>> > procedure
>> > execution plan compilation time ?
>> >
>> > I execute this statement on 2 different machines:
>> > EXEC MyProc WITH RECOMPILE
>> >
>> > On M1 I have 20s for compilation of the execution plan and 1s for proc
>> > execution
>> > on M2 I have 50s for compilation of the execution plan and 1s for proc
>> > execution
>> >
>> > MyProc is quite big (3MB text file) and does a lot of insert.
>> >
>> > M1 is P4 2.6GHZ XPSP2 SQL Server Developper
>> > M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
>> >
>> > Are there some parameters affecting execution plan compilation time ?
>> >
>> > Both machines are idle and devoted to performance tests...
>> >
>> > Thanks
>>|||I would suggest that you modify the SQL server setting to only use 1 CPU on
the Processor tab of the server properties. Strange things sometimes happens
on multiproc boxes. If this helps you - have a look at "option (maxdop)"
/Fredrik
"fourmi" <fourmi@.discussions.microsoft.com> wrote in message
news:F52A5C06-AD1A-403C-884E-EA109B9B62A7@.microsoft.com...
> Hello all,
> Could anybody give me hints or links on what is affecting stored procedure
> execution plan compilation time ?
> I execute this statement on 2 different machines:
> EXEC MyProc WITH RECOMPILE
> On M1 I have 20s for compilation of the execution plan and 1s for proc
> execution
> on M2 I have 50s for compilation of the execution plan and 1s for proc
> execution
> MyProc is quite big (3MB text file) and does a lot of insert.
> M1 is P4 2.6GHZ XPSP2 SQL Server Developper
> M2 is a Bi Xeon 3.0GHz WS2003 SQL Server Enterprise !
> Are there some parameters affecting execution plan compilation time ?
> Both machines are idle and devoted to performance tests...
> Thanks