Solved

Insert Record not Working

Posted on 2014-01-31
12
558 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Accepted Solution

by:
Jack Leach earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
Comment Utility
<< 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now