JET Database errors

[Rich Palarea]

I've noticed that since moving a number of sites that use the DIW from
shared hosting to my dedicated Windwos 2003 server, I get the following
error when trying to update records using the DIW:

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

I don't think the issue resides in the DIW-generated update.asp file, since
I just used the DIW to create a brand new application and tested the
update.asp file. Got the same error.

I'm wondering if it has something to do with the way that my Windows 2003
server is setup. When I do a Windows Update on the server, I get the "all
clear" and no updates are available.

Are there JET database components that reside on the server that need to be
updated? Am I on the right track?

Thanks,
Rich

 

[Kevin Spencer]

The first thing I would check for is a syntax error in the UPDATE statement.
In other words, rather than not thinking that it is the ASP page that
FrontPage created, find out. Note that a syntax error can occcur in a
DIW-Generated ASP page when certainl form fields are not filled in, and no
client-side validation is performed. The values from the form are
concatenated into a SQL string, which, if they are empty, may result in a
syntax error. Example:

UPDATE myTable SET someNumericColumn = someValue WHERE someTextColumn =
'someOtherValue'

if "someValue" is empty, the resulting SQL reads:

UPDATE myTable SET someNumericColumn = WHERE someTextColumn =
'someOtherValue'

In the case of text, the single quotes delimiting it will prevent this:

UPDATE myTable SET someNumericColumn = someValue WHERE someTextColumn = ''

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Rich Palarea]

Kevin:

Thanks for the help.

I checked the sql of the page and it appears to be correct. The WHERE clause
specifies ID, which is my primary key for the database record. All records
in the dB have a value, including the one I'm trying to edit (all attempts
to edit any records in any databases on my server give the same error).

Since all of these sites worked before I migrated them, I thought it was
something on the server. Also, other pages with queries are working fine
(so its not a global.asa or dsn issue).

Since my other sites are now having issues, could it be server-based. I have
a copy of JET Database update 4.0, but I'm not going to install it until I
can figure out what version is on the server and what the issue is. This is
a production server...!

Thanks for any other help you can offer.
Rich

 

[Kevin Spencer]

Hi Rich,

\You wouldn't be getting an error message from the Jet Database engine if
there was anything wrong with the Connection. Therefore, the problem lies
either with your code, or with the version of MDAC (Microsoft Data Access
Components) on the server. You can find the latest MDAC on the MS web site
at http://www.microsoft.com/data.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Rich Palarea]

I just checked one other site on that server and the queries are working
(even update.asp). The difference that I found in the code was that if I
used a column name ID for the primary key versus using my own name (I have
one site that uses JobID) it seems to fail. When the column name is set to
ID, the DIW generates sql language in the initial query that sets the
default for ID to 0. When I use my own column value, it sets the defautl to
="

Does that provide a clue...?

 

[Kevin Spencer]

Hi Rich,

Unfortunately, that doesn't provide ME with a clue, as I don't use the
FrontPage database components (I'm an ASP.Net programmer by trade). However,
if you can modify the code so that it works, well, there you are!

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.