DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Primary Key Issue

Last »

•

Join Date: Jun 2003

Posts: 313

Reputation: red_evolve is on a distinguished road

Solved Threads: 0

red_evolve red_evolve is offline

Offline

Posting Whiz

Primary Key Issue

Jun 23rd, 2004

Greetings.
The subject doesn't quite match the forum? :cheesy:
Well, I've to develop a system in VB. It's going to be a system used by some Management people. So, as I was designing the database, I was wondering, how could I assign the type of the primary key as?
The scenario goes like this:-

•

The Management people have the flexibility of adding new records into the database, so, the issue here is about the primary key. I would have a approximately 10 tables. If I use primary keys such as [A001, A002, A003] for table A, [B001, B002, B003] for table B and [C001, C002, C003] for table C, I know it is quite tough or even impossible for people to just key in the first name and last name to be stored in Table A and to have the new primary key be stored automatically following the first name and last name.

I could have asked them to key in the primary key along with the first name and last name, but I know that this is quite not-professional. If I use auto increment primary keys to solve the problem, can I use it for almost all the 10 tables? Will there be any clashes as of primary key is concerned?

Err, I hope you guys understand what my problem is.

•

Join Date: Jun 2003

Posts: 313

Reputation: red_evolve is on a distinguished road

Solved Threads: 0

red_evolve red_evolve is offline

Offline

Posting Whiz

Re: Primary Key Issue

Jun 28th, 2004

Greetings.
Okay, I've got a little idea here.

Let's say my primary key set has this format -> C0001, C0002, C0003, etc.
Is there any split function available where I can split the letter 'C' and integers? Thereafter, in VB, when there is a 'Add new records" event, I would put:-

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim iCount As Integer
'Get the last record in the table
'Use the split function
'Get the last record's PK's integer portion & store it as iCount
:
:
iCount = iCount + 1
'Format the iCount to become a 4 digit format
.Recordset.AddNew
.Recordset("com_code") = "C" & iCount
.Recordset.Update
Dim iCount As Integer
'Get the last record in the table
'Use the split function
'Get the last record's PK's integer portion & store it as iCount
:
:
iCount = iCount + 1
'Format the iCount to become a 4 digit format
.Recordset.AddNew
.Recordset("com_code") = "C" & iCount
.Recordset.Update

Please help.
Thanks.

•

Join Date: Apr 2004

Posts: 321

Reputation: bentkey is an unknown quantity at this point

Solved Threads: 8

bentkey

Offline

Posting Whiz

Re: Primary Key Issue

Jun 28th, 2004

What database are you using? In all my tables in all applications I always assign an automatically generated numerical primary key. Even if I don't figure I'll use it, sometime in the future I might. These keys can be used for relational links and should have nothing to do with your actual data. If you want to search on names, etc, create an index for that column. And unless you intend to have a large database you can use the same generator for all your tables, no one ever sees these columns anyway in most cases.

bentkey MCSE,CCNA

SupportWindow Remote Desktop Software
Bytewiser Data Systems

•

Join Date: Mar 2004

Posts: 634

Reputation: Slade has a spectacular aura about

Solved Threads: 7

Slade

Offline

Practically a Master Poster

Re: Primary Key Issue

Jun 28th, 2004

for most databases you can use a seed and have it increment by however much you want, what database program are you using?

Formerly known as Slade.

•

Join Date: Jun 2003

Posts: 313

Reputation: red_evolve is on a distinguished road

Solved Threads: 0

red_evolve red_evolve is offline

Offline

Posting Whiz

Re: Primary Key Issue

Jun 29th, 2004

Greetings.
I am using Access2000. It is going to be a small database, I guess.
I see. So, it is alright to use an auto increment for all the PK's in each table. I was just afraid that there might be some clashes or confusion.
Anyway, I came out with this function yesterday after posting.

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Private Function formatPK(sCom As String, sTable As String) As String
 
    Dim sNewCom As String       ' The new com_code
    Dim iNewCom As Long
    Dim iLastCom As Integer
    Dim iLen As Integer
    Dim i As Integer
 
    sNewCom = Right(sCom, 4)    ' Get the digit portion of the PK
    iLastCom = Val(sNewCom)     ' Get the integer value of the portion
 
    iNewCom = iLastCom + 1      ' Add 1 to get a new PK digit portion
    sNewCom = iNewCom
 
    iLen = Len(sNewCom)
 
    If iLen < 4 Then
        iLen = 4 - iLen
 
        For i = 1 To iLen
            sNewCom = "0" & sNewCom
        Next i
    End If
 
    If sTable = "competency" Then
        formatPK = "C" & sNewCom
    End If
 
End Function
Private Function formatPK(sCom As String, sTable As String) As String
    
    Dim sNewCom As String       ' The new com_code
    Dim iNewCom As Long
    Dim iLastCom As Integer
    Dim iLen As Integer
    Dim i As Integer
    
    sNewCom = Right(sCom, 4)    ' Get the digit portion of the PK
    iLastCom = Val(sNewCom)     ' Get the integer value of the portion
                        
    iNewCom = iLastCom + 1      ' Add 1 to get a new PK digit portion
    sNewCom = iNewCom
            
    iLen = Len(sNewCom)
            
    If iLen < 4 Then
        iLen = 4 - iLen
                
        For i = 1 To iLen
            sNewCom = "0" & sNewCom
        Next i
    End If
    
    If sTable = "competency" Then
        formatPK = "C" & sNewCom
    End If
    
End Function

•

Join Date: Jun 2004

Posts: 173

Reputation: BinaryMayhem is an unknown quantity at this point

Solved Threads: 9

BinaryMayhem BinaryMayhem is offline

Offline

Unverified User

Re: Primary Key Issue

Jun 29th, 2004

question: why dont you just set the table to autoincrement and scrap the letters. This way you can let MS access do the incrementing internaly and save yourself from writing a function!

•

Join Date: Jun 2003

Posts: 313

Reputation: red_evolve is on a distinguished road

Solved Threads: 0

red_evolve red_evolve is offline

Offline

Posting Whiz

Re: Primary Key Issue

Jul 1st, 2004

Greetings.
Hmph...alright.
Err, one more thing.
Is it possible to connect 2 tables this way?

Help with Code Tags

(Toggle Plain Text)

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

I get an error on the red line but I'm not sure how to correct that.
Please help.

•

Join Date: Jun 2004

Posts: 173

Reputation: BinaryMayhem is an unknown quantity at this point

Solved Threads: 9

BinaryMayhem BinaryMayhem is offline

Offline

Unverified User

Re: Primary Key Issue

Jul 1st, 2004

yes, you can do it that way, or you can use JOIN.

SELECT * FROM comact INNER JOIN competency ON comact.com_code=competency.com_code;

there are lots of difrent types of joins, INNER, OUTER, LEFT,RIGHT and they all do diffrent things.. if your intrested I suggest you look them up. (im to lazzy to describe the diffrences right now! just saw spiderman 2... it was awesome!)

•

Join Date: Jun 2003

Posts: 313

Reputation: red_evolve is on a distinguished road

Solved Threads: 0

red_evolve red_evolve is offline

Offline

Posting Whiz

Re: Primary Key Issue

Jul 1st, 2004

Greetings.
Thanks for the information.
Ok, now that I know it can be done, I still can't figure out why can't I do this:-

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
sComIndex(iIndex) = myRS!comact.com_code
sComIndex(iIndex) = myRS!comact.com_code