Web Query

[nsr]

Simple query? Update Help Requested.

I've been reading through various threads on this board, and seeing as how I'm new to Access/SQL on the web a lot of this is over my head. My problem might seem simple to someone here. However, I'm hoping that maybe some of you might be kind enough show me what I'm doing wrong.

I have a database on a webserver, and have had no problem setting up templates and queries to return results for site visitors. I am now experimenting with updating and deleting records from the database and have run into a snag.

I am basically allowing a user to select a record via a query, and want to be able to allow them to update the data. I have setup a template which inputs the existing data into an html form. The user would then be allowed to see the existing values and update any that need it, or delete the record altogether.

I already have the delete working, but I have been unsuccessful in getting the update function to work. In short: I need to be able to update multiple fields on a single record.

The database is in MS Access 97 format. (don't ask) And this is pretty much what I have in my .IDC file:

DataSource:testdb
Template:update.htx
SQLStatement:
+UPDATE testdb SET Name as '%Name%' and Status as '%Status%' and DateJoined as '%DateJoined%' and Locality as '%Locality%' and Email as '%Email%' and Notes as '%Notes%' and TrialDateStart as '%TrialDateStart%' and TrialDateEnd as '%TrialDateEnd%'
+WHERE ID eq '%ID%'

I have tried "eq" instead of "as", I've tried with a SELECT statement and without. I have been unable to produce any results, and this is the error message I'm getting:

Error Performing Query

* [State=37000][Error=-3503][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


Anyone have any ideas?

Thanks.

 

[DanR]

I haven't worked with web stuff, so your syntax is a little unfamiliar, but just a couple of suggestions.

• The query builder in Access is invaluable for getting your SQL right. You can build your query visually, check that it does what you want, then copy the code into your program

• You need to use the format 'SET fieldname = value, fieldname2 = value2 ...'

• The + signs seem out of place (or maybe they're specific to your web environment)

• Another question is whether Access is able to correctly interpret the variables that you have in quotes - eg '%Name%' .
  I often edit the SQL as a text string to insert the required values into the statement before executing it. In VBA this could look like:

SQLstring = "UPDATE testdb SET Name = '" & 96Name96 & "', Status = '" & 96Status96 & "', ...

So that the query that Access receives might actually look like:

UPDATE testdb SET Name = 'Bob', Status = 'Current', etc etc

I hope at least some of this is of help to you.

cheers
Dan

 

[nsr]

I tried that and have been unsuccessful. I even tried just updating one field only, and am still getting errors.

I'm honestly at a loss.