Posted by Dan Guzman on 02/15/06 14:31

If the server-side code path is identical, I suggest you focus on the client
browser settings. My guess is that you have client-side script that doesn't
execute under certain configurations so data isn't posted back as expected.
This causes your server-side script to generate an invalid SQL statement
like 'INSERT INTO MyTable VALUES()' and results in the error you describe.

There are other clues in your post that you aren't following Best Practices
so you might want to revisit the items below.

Rather than ODBC, consider using the OLE DB provider directly. For Windows
Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI"

For SQL Authentication:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User
Id=MyLogin;Password=myPassword1"

Consider using parameterized SQL Statements rather than build SQL statement
strings. This is more secure, provides better performance and eliminates
the need to double-up embedded quotes and format dates. For example:

myCommand.CommandText = "INSERT INTO MyTable VALUES(?)"
Set myIntegerParameter = myCommand.CreateParameter( _
"@MyIntegerParameter", 3, 1)
myCommand.Parameters.Append myIntegerParameter
myIntegerParameter.Value = Request("UserValue")

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Guy Debord" <laugh@life.org> wrote in message
news:tyDIf.45673$Rw6.23215@newsfe7-gui.ntli.net...
> Hello all,
>
> I know that this is a long shot, but I have a problem which someone
> reading this group *may* just be able to shed some light on.
>
> We have a new internal personnel planner/attendance system in place. It
> uses a web interface to allow members of staff to select their site
> location for any week, request leave and record absences.
>
> The server-side scripting is composed of VB/ASP and Javascript which
> ultimately queries & writes to an MS SQL Server 2000 database via an
> ODBC connection between webserver (IIS6.0).
>
> Here is the problem and I *know* that it sounds unlikely/impossible but
> we carried out exhaustive tests.
>
> -> I open a web browser on my local PC (XP Pro SP2)
> -> Login to the web-based planning system
> -> Update information
> -> Submit Changes
>
> At this point I get a script error:
>
> ---error message---
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near ')'.
>
> /staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108
>
> -------------------
>
> Which is fair enough as it probably points to a problem with the SQL
> statement being passed by the .asp page.
>
> ***BUT*** I _only_ get this message when I access the page via a browser
> from certain PCs!
>
> I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.
>
> I have obviously ensured that it isn't any kind of browser caching issue.
>
> I have no issues using scripted-based web pages in general from the PC
> in question.
>
> I have checked the regional settings to ensure that I am using a
> standard character set.
>
> I *can* submit database changes via web browsers from most other PCs on
> our network, without incurring the error message.
>
> Even when I get the error message, the values are written to the db and
> the information is updated.
>
> The other PCs from which I am experiencing the same difficulty are
> servers running Win2000 & Win2003 respectively.
>
> Is there any kind of OS/User Profile setting which would mean that
> scripts running on a remote webserver would fall over when trying to
> perform an SQL write?
>
> Could it be some kind of character encoding issue, which means that the
> parameters that are received by the .asp script and then written via the
> SQL statement are mangled in any way?
>
> I can't see that there is any kind of local setting that would influence
> whether remote scripts would or would not cause an error such as this.
>
> If anyone has any ideas, has encountered anything similar or can shed
> light on a system-specific setting or feature that may cause this,
> please post your thoughts.
>
> Yours Gratefully
>
> Guy

[Back to original message]