Iterations from data

Question

0.00/5 (No votes)

See more:

I am writing a program to keep track of our video's, cd's, and books and have a table set up for artists/actors and need to check when someone adds a new video to see if the artist/actor is already in the database or not. If it is I then keep the same ActorID and assign it to the video, but if it is new the actor/artist is added. I need to keep the same ID if found and assign the new one if not. Here is what I have so far:

VB

Private Sub CheckArtist()
        Dim Count As Integer
        Dim StopCount As Integer
        StopCount = (ds.Tables("Video").Rows.Count) - 1
        Name1 = ActorNm1Text.Text
        Name2 = ActorNm2Text.Text
        Name3 = ActorNm3Text.Text
        Name4 = ActorNm4Text.Text
        ActorID1 = 0
        ActorID2 = 0


        For i = 1 To 4

            For Count = 0 To StopCount
                'if is exists use the same id

                If Name(i) = ds.Tables("Video").Rows(Count).Item(1) Then
                    'This is where I need the help! How do I assign the interation to the actor id?
                    ActorID(i) = Count
                    Exit Sub


                End If

            Next Count
        Next i

        'Otherwise add a new record
        AddNewActor()

    End Sub

I am a newb and any help would be greatly appreciated!

Posted 31-Jan-12 13:16pm

James M. White

Add a Solution

2 solutions

Solution 1

This is another place where you should let the DB do it's job. Run SQL like 'select artistID where artistname = 'blah' and if you get no value back, you need to create a new one. I typically do a 'getArtistId' method, for example, that looks it up, inserts if it is not there, and returns an id either way.

Posted 31-Jan-12 16:35pm

Christian Graus

Comments

James M. White 1-Feb-12 12:26pm

Thanks Christian, I have that and am looking to assign the ID from the actor table to the video table or add a new actor and assign that ID to the video table for four different actors per video.

Christian Graus 1-Feb-12 13:43pm

OK, then you need a joining table that links actor ids to video ids, so you can assign an arbitrary number between the two.

James M. White 1-Feb-12 15:53pm

I have the relation set up, but how do I check for the existence and if not there then assign the new ID? I have two arrays set up one for the actorID (int) and another for the actor names (string) and want to check the text input to see if it exists and assign that ID number to the video table if not add a new record to the actor table and assign that ID to the video table.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

James M. White · Accepted Answer · 2012-02-01T12:22:00

This is what I came up with and it works, if someone can improve on my methods I would appreciate it!

VB

Private Sub CheckArtist()
       Dim ActorCount As Integer
       Dim StopCount As Integer
       Dim i As Integer = 0
       Dim StopActor As Integer
       StopCount = ds.Tables("Actor").Rows.Count
       StopActor = ActorName.Length
       ActorName(0) = ActorNm1Text.Text.ToString
       ActorName(1) = ActorNm2Text.Text.ToString
       ActorName(2) = ActorNm3Text.Text.ToString
       ActorName(3) = ActorNm4Text.Text.ToString

       While i < StopActor

           For ActorCount = 0 To StopCount
               'if is exists use the same id

               If ActorName(i) = ds.Tables("Actor").Rows(ActorCount).Item(1) Then
                   'This is where I need the help! How do I assign the interation to the actor id?
                   ActorID(i) = ActorCount
                   i = i + 1
                   Continue While
               Else
                   ActorID(i) = MaxActorRows + 1

               End If

           Next ActorCount

           If ActorID(i) > MaxActorRows Then

               ActorID(i) = MaxActorRows
               'Commit the changes to the database
               'Command builder
               Dim cbActor As New OleDb.OleDbCommandBuilder(daActor)
               'New data rows for data set
               Dim dsNewActorRow As DataRow
               'Where to add the new rows
               dsNewActorRow = ds.Tables("Actor").NewRow()
               'Assing the input to the new row
               dsNewActorRow.Item("actor_name") = ActorName(i)
               'Add the row to the database
               ds.Tables("Actor").Rows.Add(dsNewActorRow)
               daActor.Update(ds, "Actor")
               'Update the rows
               MaxActorRows += 1

           End If
           i = i + 1

       End While

   End Sub