asked on

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:

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.

mbizup

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

mbizup

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

Gustav Brock

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

ASKER CERTIFIED SOLUTION

Jack Leach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

JimFive

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

if you switch over to using parameterized queries, you shouldn't run into these kind of issues:

https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/A_3626-ASP-Classic-Using-Parameterized-Queries.html

mbizup

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

slegy

ASKER

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!

Scott McDaniel (EE MVE )

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.

slegy

ASKER

I didn't use any brackets. I renamed the offending fields and corrected the handling of the numeric field.

Jack Leach

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