Solved

Insert Record not Working

Posted on 2014-01-31
12
563 Views
Last Modified: 2014-01-31
I'm resurrecting a fairly recent question. I thought I'd solved the problem, but not so, and I'm at my wits end.

We have an Access database on our website. As a part of our membership processing, I need to update several tables. It is all working successfully except one. It is a table that was created locally and uploaded. I need to update this table when a member successfully renews and signs up. I spent two days on this a couple of weeks ago and now all day today. I have absolutely determined that I can update all fields except the last three.

Here is the table:
Table layout
Here is some of the data I've logged:
1/30/2014 5:02:30 PM
Member ID:  SMIMA000
District Name:  Unaffiliated
Fleet#:  0
Member Name:  Marion Smith
Address:  13 East Ct
State:  CO
Loc:  Durango, CO 81147
Type:  Skipper
Boat#:  
Boat Name:  
Last:  Smith
First:  Marion
Email:  marionsmith1234@msn.com
Token:  cd6482ca%2Dfe8d%2D4c93%2Da3d2%1982
The following query works:
                  
sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName) VALUES ('" & memberID & "', '" & districtName &"', '" & fleetNo &"', '" & memName &"', '" & suffix &"', '" & address &"', '" & stateProv &"', '" & country &"', '" & loc &"', '" & loginEmail &"', '" & memType &"', '" & fbToken &"', '" & ggToken &"', '" & liToken &"', '" & yhToken &"', '" & boatNo &"', '" & boatName &"');"

Open in new window

This does not:
sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName, last, first, password) VALUES ('" & memberID & "', '" & districtName &"', '" & fleetNo &"', '" & memName &"', '" & suffix &"', '" & address &"', '" & stateProv &"', '" & country &"', '" & loc &"', '" & loginEmail &"', '" & memType &"', '" & fbToken &"', '" & ggToken &"', '" & liToken &"', '" & yhToken &"', '" & boatNo &"', '" & boatName &"', '" & memberLast &"', '" & memberFirst &"', '" & email &"');"

Open in new window


Has anyone run into this problem or have any thoughts as to what could be wrong? I've looked carefully at all three fields and cannot see a problem. They are not required or indexed and zero length is allowed (tho the fields are never empty). Any thoughts greatly appreciated.
0
Comment
Question by:slegy
  • 4
  • 2
  • 2
  • +4
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39824087
Try this (placing suqre brackets around those three field names)

sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName, [last], [first], [password]) VALUES ('" & memberID & "', '" & districtName &"', '" & fleetNo &"', '" & memName &"', '" & suffix &"', '" & address &"', '" & stateProv &"', '" & country &"', '" & loc &"', '" & loginEmail &"', '" & memType &"', '" & fbToken &"', '" & ggToken &"', '" & liToken &"', '" & yhToken &"', '" & boatNo &"', '" & boatName &"', '" & memberLast &"', '" & memberFirst &"', '" & email &"');"

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39824096
Also try adding some spaces to separate &'s from " in your syntax:

sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName, [last], [first], [password]) VALUES ('" & memberID & "', '" & districtName & "', '" & fleetNo & "', '" & memName & "', '" & suffix & "', '" & address & "', '" & stateProv & "', '" & country & "', '" & loc & "', '" & loginEmail & "', '" & memType & "', '" & fbToken & "', '" & ggToken & "', '" & liToken & "', '" & yhToken & "', '" & boatNo & "', '" & boatName & "', '" & memberLast & "', '" & memberFirst & "', '" & email & "');"

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39824103
Try with some brackets:
sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName, [last], [first], password) VALUES ('" & memberID & "', '" & districtName &"', '" & fleetNo &"', '" & memName &"', '" & suffix &"', '" & address &"', '" & stateProv &"', '" & country &"', '" & loc &"', '" & loginEmail &"', '" & memType &"', '" & fbToken &"', '" & ggToken &"', '" & liToken &"', '" & yhToken &"', '" & boatNo &"', '" & boatName &"', '" & memberLast &"', '" & memberFirst &"', '" & email &"');"

Open in new window

/gustav
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 4

Accepted Solution

by:
Jack Leach earned 400 total points
ID: 39824106
You have a few reserved words in your field names... one of them in particular - "name" - is the worst reserved word that can be used and will cause any number of strange errors.

While enclosing them in square brackets helps - to a small degree - it is entirely not recommended to use them in the first place.

One naming convention that I've adopted is to use two-part names for field names.  An object name of two parts will get rid of any reserved word errors.

Much like you use MemberID as a field name, so should you be using MemberName as well.  Do yourself a favor and look up a list of reserved words to get familiar with them.  There's an abundance of them on the web, but as mentioned, dual part naming convention will effectively take care of that particular problem.

Cheers,
-jack
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 50 total points
ID: 39824109
Further to mbizup,
Last and First are reserved words in Access as they are functions.  Putting square brackets around them should cause them to be parsed as fields.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 50 total points
ID: 39824110
Also, if Fleet is numeric, drop the surrounding single quotes.  I'm surprised that works in the first query you posted:

sql = "INSERT INTO ilcaMembers (memberID, districtName, fleet, name, suffix, address1, state, country, location, loginEmail, memType, fbToken, ggToken, liToken, yhToken, boat, boatName, [last], [first], [password]) VALUES ('" & memberID & "', '" & districtName & "', " & fleetNo & ", '" & memName & "', '" & suffix & "', '" & address & "', '" & stateProv & "', '" & country & "', '" & loc & "', '" & loginEmail & "', '" & memType & "', '" & fbToken & "', '" & ggToken & "', '" & liToken & "', '" & yhToken & "', '" & boatNo & "', '" & boatName & "', '" & memberLast & "', '" & memberFirst & "', '" & email & "');"

Open in new window

0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39824290
if you switch over to using parameterized queries, you shouldn't run into these kind of issues:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/A_3626-ASP-Classic-Using-Parameterized-Queries.html
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39824472
<< if you switch over to using parameterized queries, you shouldn't run into these kind of issues >>

In part...

Using parameterized queries eliminates the need for delimiters for specific datatypes (eg: single quotes for text data).

But it doesn't resolve issues arising from using reserved words as field names -- which ideally should be corrected/avoided as described in Jack Leach's comment, or handled with square brackets if renaming fields is not an option.
0
 

Author Closing Comment

by:slegy
ID: 39825583
Well, I feel extremely foolish. I've done very little of this, so the idea of reserved words never occurred to me, although, given as long as I bashed my head against the wall, it should have dawned on me. Thank you for your help. Everything is working great!
0
 
LVL 84
ID: 39825588
While enclosing them in square brackets helps - to a small degree - it is entirely not recommended to use them in the first place.
While I agree that NOT using them is the preferred course of action, enclosing them in square brackets resolves the matter fully, at least in terms of the query engine correctly parsing the field names.
0
 

Author Comment

by:slegy
ID: 39825593
I didn't use any brackets. I renamed the offending fields and corrected the handling of the numeric field.
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39825634
While I agree that NOT using them is the preferred course of action, enclosing them in square brackets resolves the matter fully, at least in terms of the query engine correctly parsing the field names.

Agreed.  I should have been more clear in that there's other areas besides the query engine where reserved words can cause problems.

Cheers,
-jack
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
How can I Flash a mandatory field in Access Form? 13 46
Turn off MS Access Default=0 for Numerics 6 25
backup programme - VBA 3 24
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question