Click here to Skip to main content
13,556,712 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
CurrentDb().Execute "UPDATE W1 SET " & _
            "W1.[ Prg Name] = '" & RS.Fields("[Card Pgm Name]") & _
            "', W1.[Prg  Amt] = " & RS.Fields("[Trans Amt]") & ", " & _
            "W1.[Prg  Count] = " & RS.Fields("[Trans Count]") & _
            " WHERE [Level Name] = '" & RS.Fields("[Program Name]") & "'" & _
            " AND [Period] = '" & cmdPeriod.Value & "'" & _
            " AND [Tran Post Date] = #" & RS.Fields("[Posted Date]") & "#"


What I have tried:

Tried to complie with updating " "
Posted 1-Jan-18 22:20pm
Updated 3-Jan-18 10:45am
v2
Comments
Richard-MacCutchan 2-Jan-18 5:47am
   
It looks like you have quote/double quote characters in the wrong places.
User-10673509 2-Jan-18 17:36pm
   
if you put a break point on the execute statement and paste the "UPDATE W1..." in the immediate window what does it look like? perhaps that will help you see a missing quote or if the [Prg Name] field has a quote in the name, that will also cause issues.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

OK, wow. Two words: "Paremeterized Queries"

Using string concatenation will result in the death of your database. Don't believe me? Google for "SQL Injection Attack" and start reading to find out why what you're doing is so bad.

Next, Google for "VB.NET SQL parameterized queries" to find out what to do about it. This will also fix your UPDATE query string problem, making you're code much more readable and far easier to see mistakes and debug.

Oh, and don't put spaces in your database column names, unless, of course, you like typing the square brackets around every single field name in every single query you write.

You might also want to look into what a Primary Key is and using autogenerated Id columns in your tables.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01-2016 | 2.8.180515.1 | Last Updated 3 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100