Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 15th, 2006, 02:12 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Coding many to many relationship table

Hi guys,

I have three tables in MsAccess 2000:-
- Movie table (movieID - autonumber, title- text)
- cast table (castId -autonumber, actor - text, description - memo)
- movie-cast table (movieId, castId)

I have a form with controls (textboxes, and a combbox) for capturing the above information. The main challenge is how to code an INSERT statement that can associate one movie to more than 2 actors.

Eg Movie title = Coming to America
Actors 1 - EddieMurphy
            2 - Arsenio Hall

Remember the INSERT code is getting it's values from the controls on the form.

Any form of sample code will be much appreciated.



Reply With Quote
  #2 (permalink)  
Old June 15th, 2006, 03:58 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

You would need to have one INSERT statement for each movie/actor combination.
If Coming to America were movie ID 10:
Code:
INSERT INTO MOVIE_CAST ( MOVIE_ID, CAST_ID ) VALUES ( 10, 1);
INSERT INTO MOVIE_CAST ( MOVIE_ID, CAST_ID ) VALUES ( 10, 2);
Reply With Quote
  #3 (permalink)  
Old June 16th, 2006, 02:27 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi BrianWren,

I agree with you about using individual INSERT statements, however, the challenge that I have is how to establish the second record in the cast table. I am using the SELECT @@Identity to generate the last MovieId entered in the movie table but the problem now lies with establishing the two entries from the cast table so that they correspond with this one movie. Remember, I am using a combobox to select/insert actors into the cast table. Is there a way of determining the sequence of selected items in a combobox? I believe that is the only way I can be able to use individual INSERT statements to enter these entries in the bridge table. Or is there another way out? Any suggestions will be most welcome.
Reply With Quote
  #4 (permalink)  
Old June 16th, 2006, 03:11 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

I hate to admit it, but I've looked and I've looked, and I cannot find anything that explains the meaning of the “@@” . . . How is that used, and what does it do?

I don't exactly follow your quandary. The entries in the CAST table are completely independent of anything in any other tables; It is just a list of actors (with possible ancillary data like age, religion, nationality, etc.). The entries in the MOVIES table are just a list of movies (with ancillary data like copyright date, running time, etc.). The MOVIE_CAST table has [u]only</u> 2 columns: one that holds an ID from the MOVIES table, and one that holds an ID from the CAST table.

I am not clear on your interface, but if the idea is that the user enters a movie into one control, and a list of actors into a second control, then the operation would be something like this:

    1. Find whether the movie exists in the MOVIE table.
       a) If so, get the ID
       b) If not, add it to that table then get the ID

    2. For each entry in the Actors control:
       a) Find whether the actor exists in the CAST table.
          i. If so, get the ID
          i. not, add it to that table then get the ID
       b) Find whether this specific combo of CAST_ID and MOVIE_ID already
          exists in the MOVIE_CAST table.
          i. If so, nothing to do; Get the next actor
          i. If not, build the INSERT statement, and issue it,
             then get the next actor

Or am I missing something in the setup?
Reply With Quote
  #5 (permalink)  
Old June 19th, 2006, 01:59 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

BrianWren,

Thanks for having taken your time to help me out with this 'tricky' situation. Maybe I forgot to mention that am relatively new to VB and am learning on my own. Am using the web resources and friends like you to help me learn. Am determined to learn and this is my first application that am trying out.

Back to my dilema, you have analysed my problem very well and your suggestions make so much sense but since am still new to VB, am NOT sure how I will implement it. Will you mind writing a sample code that I can use to find whether the movie or actor exists in the MOVIE/CAST table.
       a) If so, get the ID
       b) If not, add it to that table then get the ID?

I know how to search a database but NOT how to get the search results to be used in another query/code.

Just for information, @@identity is a stored procedure that gives u the last inserted record in a table.

Thanks for your patience.


Reply With Quote
  #6 (permalink)  
Old June 20th, 2006, 01:29 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

You would do that by trying to open a recordset with the details that would point to the record that you are after, with the ID field as one of the fileds in the SELECT statment. If no records result, then you know the record does not exist. If there [u]is</u> a record, it will be the ID you are after.
So let's say we’re looking for Jennifer Aniston (whom I would very much like to find...).
Code:
Function CastID(ByVal FirstName As String, ByVal LastName As String) As Long

    Dim r As ADODB.Recordset
    r.open "SELECT ID                                   " & _
           "FROM   CAST                                 " & _
           "WHERE  LCase(FNAME) = """ & FirstName & """ " & _
           "  AND  LCase(LNAME) = """ & LastName & """  "

    If r.EOF And r.BOF Then
        CastID = -1
    Else
        CastID = r!ID
    End If

End Function
Then you would call this function:
Code:
    lngCastID = CastID("Jennifer", "Aniston")
    If lngCastID = -1 then
        RunSql "INSERT INTO CAST (        FNAME,       LNAME  ) " & _
               "VALUES           ( ""Jennifer"", ""Aniston"" ")
        ' Now the records exists.  Get the ID.
        lngCastID = CastID("Jennifer", "Aniston")
    End If
    And so on. If you are using a connection (named, say "c"), you can substitute in place of the RunSql the following c.Execute "INSERT . . . "

In your case you would use the values of the input objects of your form in plkace of the hard-coded strings I've used, but the ideas in the above should get you up and running.
Reply With Quote
  #7 (permalink)  
Old June 28th, 2006, 10:42 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am new to programming and I have been trying with no success. How can I substitute your code with the controls on my form?

I need this code to work desperately.

Thanks

Reply With Quote
  #8 (permalink)  
Old July 28th, 2006, 03:52 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Brian,

Am still pursuing this thread. I have tried to get a solution using your code sample but seems not to work for me. Can you elaborate how it works?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
One To One RelationShip prasanta2expert SQL Language 0 November 7th, 2006 06:30 AM
tables relationship through coding itHighway Classic ASP Basics 2 May 3rd, 2005 06:39 PM
Many To Many Relationship samersult BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 1 January 26th, 2005 07:09 AM
Copying Table Relationship Diagram mike123abc SQL Server 2000 1 February 19th, 2004 09:16 AM
Add Records to Table with 1 to Many Relationship twsinc Access VBA 8 October 5th, 2003 05:35 AM



All times are GMT -4. The time now is 06:21 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.