SQL statement error

• Hind Badawi

  Grasshopper

  Points: 23

  More actions

  March 14, 2006 at 2:11 am

  #167494

  Hi

  I have a table Articles that contains:

  ArticleID,ArticleDate,ArticleTitle,ArticleAuthor,Flag(the data in Flag is integer)

  In my VB formload sub,I want to select all the articles that have Flag=1 order by Articledate

  I write the following statement in a view called V2

  SELECT    Article.ArticleDate, Article.ArticleID,Article.ArticleTitle, Article.ArticleLanguage, Source.ArticleSource,

                       Article.Flag

  FROM         Article INNER JOIN

                        Source ON Article.SourceID = Source.SourceID

  WHERE     (Article.Flag = 1)

  ORDER BY Article.ArticleDate

  I write in my VB code

  Rs1.open"Select * from [v2]",SICA, adOpenKeyset, adLockOptimistic

  When I click "save" on my VB form,I want to set the Flag of the article to 3 in order that the form can't reselect this article

  the next time it loads

  I write:Rs1!Flag=3

  but it didn't work(a message appears telling me that the view cannot be updatable)

  Can you help me out?

  thanks

   

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  March 14, 2006 at 4:13 am

  #626449

  When the user clicks save, why not run an UPDATE query instead?

  UPDATE article SET Flag = 3 where ArticleID = [ArticleID from form]

  ---

• Hind Badawi

  Grasshopper

  Points: 23

  More actions

  March 14, 2006 at 4:52 am

  #626453

  HiI tried ur statement and it didn't workWell,I can't update the field Flag because it is present in the where ClauseSELECT Article.ArticleDate, Article.ArticleID,Article.ArticleTitle, Article.ArticleLanguage, Source.ArticleSource, Article.FlagFROM Article INNER JOIN Source ON Article.SourceID = Source.SourceIDWHERE (Article.Flag = 1)ORDER BY Article.ArticleDateWhat can I do?

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  March 14, 2006 at 4:58 am

  #626454

  I do not understand. This command will work if you execute it directly on the connection that you have created, regardless of your WHERE clause. Whether it gives you the desired results is another matter.

  ---