• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Insert Record not Working

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
slegy
Asked:
slegy
  • 4
  • 2
  • 2
  • +4
3 Solutions
 
mbizupCommented:
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
 
mbizupCommented:
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
 
Gustav BrockCIOCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jack LeachProprietorCommented:
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
 
JimFiveCommented:
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
 
mbizupCommented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
mbizupCommented:
<< 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
 
slegyAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
slegyAuthor Commented:
I didn't use any brackets. I renamed the offending fields and corrected the handling of the numeric field.
0
 
Jack LeachProprietorCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now