•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Visual Basic 4 / 5 / 6 section within the Software Development category of DaniWeb, a massive community of 375,169 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,256 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Visual Basic 4 / 5 / 6 advertiser:
Views: 6149 | Replies: 22
 |
•
•
Join Date: Jun 2003
Location: Malaysia
Posts: 313
Reputation: 
Rep Power: 6
Solved Threads: 0
Posting Whiz
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:-
Err, I hope you guys understand what my problem is.
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
Location: Malaysia
Posts: 313
Reputation:
Rep Power: 6
Solved Threads: 0
Posting Whiz
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:-
Please help.
Thanks.
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:-
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.UpdatePlease help.
Thanks.
•
•
Join Date: Apr 2004
Posts: 321
Reputation:
Rep Power: 5
Solved Threads: 8
Posting Whiz
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.
•
•
Join Date: Mar 2004
Location: Brisbane
Posts: 632
Reputation:
Rep Power: 7
Solved Threads: 6
Practically a Master Poster
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
Location: Malaysia
Posts: 313
Reputation:
Rep Power: 6
Solved Threads: 0
Posting Whiz
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.
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.
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:
Rep Power: 0
Solved Threads: 9
Unverified User
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
Location: Malaysia
Posts: 313
Reputation:
Rep Power: 6
Solved Threads: 0
Posting Whiz
Greetings.
Hmph...alright.
Err, one more thing.
Is it possible to connect 2 tables this way?
I get an error on the red line but I'm not sure how to correct that.
Please help.
Hmph...alright.
Err, one more thing.
Is it possible to connect 2 tables this way?
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
LoopI 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:
Rep Power: 0
Solved Threads: 9
Unverified User
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!)
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
Location: Malaysia
Posts: 313
Reputation:
Rep Power: 6
Solved Threads: 0
Posting Whiz
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:-
The error pointed to this line of code.
I'm a big spidey fan! Can only watch it during the weekend, work work work
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:-
sComIndex(iIndex) = myRS!comact.com_code
•
•
•
•
just saw spiderman 2... it was awesome!
I'm a big spidey fan! Can only watch it during the weekend, work work work |
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Visual Basic 4 / 5 / 6 Marketplace
Linear Mode
- Need HELP IN returning an auto incriment primary key to zero (PHP)
- Need Help Accessing a Primary key MS Access in SQL to print out informatio. (MS Access and FileMaker Pro)
- HELP! Need someone that Knows SQL to tell Me How to Access a Primary Key (C)
- Need to make program access a data base primary key number entered by user (C)
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: i need a code for first come first serve simulation using visual basic
- Next Thread: DVD Collection Program
