Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Can't Update Access Database Field

I've tried everything I can think of but can't find the solution. I have a VBScript page that receives information back from PayPal. I update  a field in a database with the returned payment status code, then retrieve that record and insert a new record in another database. It is very straightforward. All fields update except the last two - last name and first name. When I finally narrowed down the problem yesterday, I manually added a new last name and a new first name field to the table being inserted. Voila! The insert worked. This morning I updated the local query that creates the table to include the two new fields. I uploaded to the web, ran the test and the query failed. I recreated the local query again, removing the sort from the last name field - same results. I can only conclude that the way the table is initially created is the problem, but I can't see it.

Local query creating the table:
SELECT [tblILCA Members].[Member ID] AS memberID, IIf([District Name]="Connecticut/Rhode Island","Connecticut-Rhode Island",IIf([District Name]="US@Large","US Large",[District Name])) AS districtName, [tblILCA Members].[Fleet#] AS fleet, [First] & " " & IIf(Not IsNull([MI]),[MI] & " ") & [Last] AS name, [tblILCA Members].Suffix, [tblILCA Members].Address1, [tblILCA Members].State, [tblILCA Members].Country, IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![State]) And IsNull([tblILCA Members]![Zip]),[tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![State]),[tblILCA Members]![Zip] & " " & [tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![Zip]),[tblILCA Members]![State] & " " & [tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![State]),[tblILCA Members]![City] & " " & [tblILCA Members]![Country] & " " & [tblILCA Members]![Zip],IIf([tblILCA Members]![Country]="US",[tblILCA Members]![City] & ", " & [tblILCA Members]![State] & " " & [tblILCA Members]![Zip],[tblILCA Members]![City] & ", " & [tblILCA Members]![State] & " " & [tblILCA Members]![Zip]))))) AS location, [tblILCA Member Types].[Member Type] AS memType, "" AS boat, "" AS boatName, [tblILCA Members].Last AS [last], [tblILCA Members].First AS [first], [tblILCA Members].[Home Email] AS [password] INTO ilcaMembers
FROM (([tblILCA Member History] INNER JOIN [tblILCA Members] ON [tblILCA Member History].[Member ID] = [tblILCA Members].[Member ID]) INNER JOIN [tblILCA Member Types] ON [tblILCA Member History].MType = [tblILCA Member Types].[Member Code]) INNER JOIN [tblILCA Districts] ON [tblILCA Members].[District Code] = [tblILCA Districts].[District Code]
WHERE ((([tblILCA Member Types].[Member Type])="Skipper" Or ([tblILCA Member Types].[Member Type])="Associate" Or ([tblILCA Member Types].[Member Type])="Crew") AND (([tblILCA Member History].Year)=Year(Date()))) OR ((([tblILCA Member Types].[Member Type])="Life") AND (([tblILCA Member History].Year)="9999"))
ORDER BY IIf([District Name]="Connecticut/Rhode Island","Connecticut-Rhode Island",IIf([District Name]="US@Large","US Large",[District Name])), [tblILCA Members].[Fleet#], [tblILCA Members].Last;

Open in new window


Web query inserting records into this table:
sql = "INSERT INTO ilcaMembers2 (memberID, districtName, fleet, name, suffix, address1, state, country, location, memType, boat, boatName, last, first) VALUES ('" & memberID & "', '" & districtName & "', '" & fleetNo & "', '" & memName & "', '" & suffix & "', '" & address & "', '" & stateProv & "', '" & country & "', '" & loc & "', '" & memType & "', '" & boatNo & "', '" & boatName & "', '" & memLast & "', '" & memFirst &"')"

Open in new window


If I remove the last and first name fields, the query runs correctly. What am I missing?
0
slegy
Asked:
slegy
  • 5
  • 4
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
do you get any errors returned?

if you hard code values into the sql query for first and last names, will they get inserted?

also, I only see code for ilcaMembers, and in your sql statement, you reference ilcaMembers2
0
 
slegyAuthor Commented:
There are no error messages. Apparently, an error is detected and the code just doesn't execute.  The creating query produces the table ilcaMembers, but because it is a live table, I copied the structure to ilcaMembers2. I have to test online because that is the only way I can get the PayPal IPN. I have logged the values of memLast and memFirst and they are correct. I will try hard-coded values.
0
 
slegyAuthor Commented:
I'm at my wits end. I created a brand new query from scratch to create the ilcaMembers table. I should note that up to yesterday I was directly updating that table, so the results for both the original table and the copied table structure are the same. In the new query I rearranged the order of the fields. Then I executed a query to update only the Last field with 'Faus'. It didn't execute. Two textfile.WriteLine statements immediately preceding did They reflect the correct first and last names. Both the Last and First fields are 24 characters long, so it isn't a size issue.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I've run into issues with Access where field order could be problematic, not sure if that is the case here. Why are you not just updating the table directly?
0
 
slegyAuthor Commented:
Because the ilcaMembers table is a live table listing current paid members. I don't want to corrupt it with test data. I thot order might be an issue too. That is why I changed it in the new query. Yesterday I tried doing the update with two queries. That didn't work. Any suggestions as to what else to try?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
I would run a test to see if you can at least update the main table, use a first name of 'aaaaa' so you can easily find it and remove it. By testing this, you'll be able to determine if its the query or the structure of the 2nd table.
0
 
slegyAuthor Commented:
I lost two days on something that should have taken a half hour at most. I started back this afternoon adding one field at a time until it again failed. You won't believe this. I decided to give up on the sql query and added the Dreamweaver code to insert a record. Worked the first time!  Don't know what to do about closing this.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
very weird, glad you got it working anyways :)

Accept your answer for 0 points and that'll begin the process of closing out the question
0
 
slegyAuthor Commented:
Wouldn't let me reduce the points. Thank you for your support and help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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