Updating recordsets in VB6


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Updating recordsets in VB6

  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Updating recordsets in VB6

    Another question for you...

    I am creating a program that, as a part of it, will display & allow a user to update, the scores for each game in an NFL season. I have 32 tables, 1 for each NFL team, following the naming convention of "tbl_Sch_Buf" or "tbl_Sch_GB"

    When a user inputs the score for GB into a text box, and clicks on "update scores", I want to write that score to the GB table. Here is where I am getting hung up..

    I have a Data Environment with 41 command objects, 32 of which are the tables for each team. The name of the Data Environment is, aptly, DataEnv1. When trying to write to the table, this is what I have been doing...

    strUpdate = "Update DataEnv1.rstbl" & strHomeAbbr & " SET Score = " & txtScoreHome.Text
    DataEnv1.Connection1.Execute (strUpdate)

    When I do this, I get an error stating that VB cannot find the database "DataEnv1.mdb"

    I am really stuck on this and cannot move forward with this part of the program until I can figure out where I am going wrong. Much appreciated if anyone can help me out...

    Scott

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Hi, Scott: In most cases, you'd create separate Commands for querying, inserting, updating and deleting from each table. I don't know of any way to get the name of the underlying table from a Command object. You should be able to do something like this:

    strUpdate = "UPDATE tbl_Sch_" & strHomeAbbr & " SET Score = " & txtScoreHome.Text
    DataEnv1.Connection1.Execute strUpdate

    UPDATE updates an existing record, so you'll probably need to add a WHERE clause to select a specific one.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    May 2006
    Posts
    18
    Hi Scott,
    It appears that you were qualifying the table name with "DataEnv1", which was not good. Also, if I remember right, you are going to need to specify a "Where" clause on your SQL statement.
    jedicoder
    Software for eCommerce and Innovation
    Affordable eCommerce Web Designer
    Website Designer Search Directory

  4. #4
    Join Date
    Mar 2005
    Location
    UK, London
    Posts
    150
    How many records do you have per table, if you have more then one you need to use the WHERE clause to point to the specific record that needs to be updated in the database.

    On a side note, in database development, you wouldnt have seperate tables for things that can be grouped together into one table. What I mean is, it would be better to have a single table with the team names store in the table with a unique id for each team, then you could refer to each team by its unique id. Im not suggesting you change it, but its something to consider in future developments when working with databases.

Similar Threads

  1. updating xml file from VB6
    By smithg22 in forum VB Classic
    Replies: 9
    Last Post: 04-12-2005, 06:23 PM
  2. Adding or Updating Oracle DB via VB6
    By Bert R. in forum VB Classic
    Replies: 0
    Last Post: 03-19-2002, 08:32 AM
  3. Problem updating database w/two tables in VB6
    By Jade29 in forum VB Classic
    Replies: 3
    Last Post: 01-12-2002, 10:12 PM
  4. updating recordsets returned from stored procedures
    By Jennifer in forum VB Classic
    Replies: 4
    Last Post: 11-19-2001, 03:16 AM
  5. updating paged recordsets
    By venkatesh in forum ASP.NET
    Replies: 0
    Last Post: 01-17-2001, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center