943,492 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > Primary Key Issue
Ad:
You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page
Permalink
Jul 2nd, 2004
0

Re: Primary Key Issue

Greetings.

Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly

listCG.Clear
Do While Not myRS.EOF
    listCG.AddItem myRS!com_name
    sComIndex(iIndex) = myRS!comact.com_code
    iIndex = iIndex + 1
    myRS.MoveNext
Loop

Err, I just wanna store all the joined records' com_code into sComIndex for further population later.
Usually, in a DBMS, we would do it this way:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. SELECT comact.com_code
  2. FROM comact, competency 
  3. WHERE comact.com_code=competency.com_code
I thought it would be the same. Is it because of this: myRS!comact.com_code ? I deem it must be syntactically wrong but do not know what's the correct way.
Reputation Points: 53
Solved Threads: 1
Posting Whiz
red_evolve is offline Offline
313 posts
since Jun 2003
Permalink
Jul 13th, 2004
1

Re: Primary Key Issue

Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly

listCG.Clear
Do While Not myRS.EOF
    listCG.AddItem myRS!com_name
    sComIndex(iIndex) = myRS!comact.com_code

'
'Are you always going to have less than 10 records?  If not you'll go out of 'the index bounds.  Also refer to fields using :
'
'myRS.fields("com_code").value
'
'what you're using will not work as the fields are part of the fields collection
'not properties of the recordset.


    iIndex = iIndex + 1
    myRS.MoveNext
Loop

Also rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.

Like this:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1.     listCG.AddItem myRS!com_name
  2.     listCG.ItemData(listCG.newIndex)=myRS.fields("com_code").value
Reputation Points: 16
Solved Threads: 1
Posting Whiz in Training
mnemtsas is offline Offline
200 posts
since Jul 2004
Permalink
Jul 13th, 2004
0

Re: Primary Key Issue

Greetings.
Hey, thanks for the advice.
Quote ...
Also rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.
What you meant by the above is to advise me to use an invisible list box rather than arrays? If I get you correctly here, I'd say that it's a good idea. I've never thought of that. Thanks!
Reputation Points: 53
Solved Threads: 1
Posting Whiz
red_evolve is offline Offline
313 posts
since Jun 2003
Permalink
Jul 13th, 2004
0

Re: Primary Key Issue

Greetings.
Ummm...one more question on Primary Keys -> set to auto increment.
Let's say I have 1,2,3,4,5 in a table already.
When I delete a record, umm, let's say record with PK = 3, is it possible to code for the records below PK=3 be shifted up + the PK value be modified to -1 each?
Reputation Points: 53
Solved Threads: 1
Posting Whiz
red_evolve is offline Offline
313 posts
since Jun 2003
Permalink
Jul 13th, 2004
0

Re: Primary Key Issue

I know what you're asking but I'm not sure you can because it's a unique identifier for that very record, it won't work like that. If there is a solution to this however please post as I'm interested
Reputation Points: 115
Solved Threads: 7
Practically a Master Poster
Slade is offline Offline
633 posts
since Mar 2004
Permalink
Jul 14th, 2004
0

Re: Primary Key Issue

The only way to do this is to NOT use autonumbering.

You could then write a function to do the renumbering, but why bother?

Your function would also have to take into account renumbering any foreign keys linking to this table from other tables..... in essence, nightmare, and completly unnessesary.
Reputation Points: 10
Solved Threads: 1
Light Poster
RobUK is offline Offline
32 posts
since Jul 2004
Permalink
Jul 14th, 2004
0

Re: Primary Key Issue

In Access 2000 definitely no. Can I suggest if you want to use primary keys for something human useful (and judging by your questions you do) that you keep track of your own numbers. That way you have full control.

It's not hard, use the MAX() SQL operator to get the highest ID to pop the next record in. Perhaps keep a second table with the ID's of deleted records and check this table for unused ID's before using the MAX() operator to put one on top of the pile.
Reputation Points: 16
Solved Threads: 1
Posting Whiz in Training
mnemtsas is offline Offline
200 posts
since Jul 2004
Permalink
Jul 14th, 2004
0

Re: Primary Key Issue

Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.

Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.

1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].
Reputation Points: 53
Solved Threads: 1
Posting Whiz
red_evolve is offline Offline
313 posts
since Jun 2003
Permalink
Jul 14th, 2004
0

Re: Primary Key Issue

Quote originally posted by red_evolve ...
Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.

Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.

1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].
I think you've got the idea, that's pretty much exactly what I was thinking of.
Reputation Points: 16
Solved Threads: 1
Posting Whiz in Training
mnemtsas is offline Offline
200 posts
since Jul 2004
Permalink
Jul 15th, 2004
0

Re: Primary Key Issue

Greetings.
Alright, alright! Thanks a lot for the confirmation.
I'll try to work that out, in fact I deem there'll be no problem with coding that out.
One more question that I have to ask here, imagine I have a lookup table in of the columns in a table in Access.
As such in VB, I'd have a combo box. If I do not wish to add the items manually, say
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. cboRace.AddItem "Malay"
  2. cboRace.AddItem "Chinese"
  3. cboRace.AddItem "Indian"
  4. cboRace.AddItem "Others"
Can I import the items from the access lookup table? If so, could you please give me some clue?
Reputation Points: 53
Solved Threads: 1
Posting Whiz
red_evolve is offline Offline
313 posts
since Jun 2003

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Adding controls into a ListView
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: DVD Collection Program





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC