Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert Record not Working

Posted on 2014-01-31
12
Medium Priority
?
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
Jack Leach earned 1600 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 200 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 200 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 34

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 85
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

604 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