Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 9881

Page 1
2
3
Next

You are currently viewing page 1 of this multi-page discussion thread

Jun 23rd, 2004

Primary Key Issue

Expand Post »

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:-

Quote ...

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.

Similar Threads

Need HELP IN returning an auto incriment primary key to zero in PHP
Need Help Accessing a Primary key MS Access in SQL to print out informatio. in MS Access and FileMaker Pro
HELP! Need someone that Knows SQL to tell Me How to Access a Primary Key in C
Need to make program access a data base primary key number entered by user in C

red_evolve

Reputation Points: 53

Solved Threads: 1

Posting Whiz

Offline

313 posts
since Jun 2003

Permalink

Jun 28th, 2004

Re: Primary Key Issue

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:-

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.

red_evolve

Reputation Points: 53

Solved Threads: 1

Posting Whiz

Offline

313 posts
since Jun 2003

Permalink

Jun 28th, 2004

Re: Primary Key Issue

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

Reputation Points: 24

Solved Threads: 8

Posting Whiz

Offline

321 posts
since Apr 2004

Permalink

Jun 28th, 2004

Re: Primary Key Issue

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

Slade

Reputation Points: 115

Solved Threads: 7

Practically a Master Poster

Offline

633 posts
since Mar 2004

Permalink

Jun 29th, 2004

Re: Primary Key Issue

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.

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

red_evolve

Reputation Points: 53

Solved Threads: 1

Posting Whiz

Offline

313 posts
since Jun 2003

Permalink

Jun 29th, 2004

Re: Primary Key Issue

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!

BinaryMayhem

Reputation Points: 15

Solved Threads: 10

Unverified User

Offline

173 posts
since Jun 2004

Permalink

Jul 1st, 2004

Re: Primary Key Issue

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

(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.

red_evolve

Reputation Points: 53

Solved Threads: 1

Posting Whiz

Offline

313 posts
since Jun 2003

Permalink

Jul 1st, 2004

Re: Primary Key Issue

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!)

BinaryMayhem

Reputation Points: 15

Solved Threads: 10

Unverified User

Offline

173 posts
since Jun 2004

Permalink

Jul 1st, 2004

Re: Primary Key Issue

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:-

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

The error pointed to this line of code.

Quote ...

just saw spiderman 2... it was awesome!

I'm a big spidey fan! Can only watch it during the weekend, work work work

red_evolve

Reputation Points: 53

Solved Threads: 1

Posting Whiz

Offline

313 posts
since Jun 2003

Permalink

Jul 2nd, 2004

Re: Primary Key Issue

what is sComIndex(iIndex) (IE: variable type) a little more code might help.

BinaryMayhem

Reputation Points: 15

Solved Threads: 10

Unverified User

Offline

173 posts
since Jun 2004

Page 1
2
3
Next

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.

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

DaniWeb Message

Cancel Changes

User Name
Password