How To Validate Duplicate Id Number In Client Server?

[OBA]

Hi To All.... This Is My First Try To Create A Program Which Compose A Server And 2 Client...i Use Access Database As My Back End And Vb6 As My Front End...

Problem
1. I Create A Code In Which A User Can Generate Autonumber
But The Problem Is If Two Clients Input Together It Would
Both Save The Data To The Server With A Duplicate And
Same Id Number...

This Is My Code Which I Generate Auto Number.....

Dim Rs As New Adodb.recordset
Set Rs=cn.execute(" select Max(fieldname) From Tblname)
If Isnull(rs(0))=t rue Then
Idnum=1
Else
Idnum=rs(0)+1
Endif

Please Help Me..... How Can I Validate Idnum Before It Save To Server?....
tnx...

[werks]

HI this is my code in Validating the username of the Librarian


[CODE=vb]
Dim ExistUserName AS Boolean
Private Sub cmdSave_Click()
...
Call VerifyUserName
If ExistUserName = True Then
MsgBox "Username exist please choose aanother", 48, "Username Existing"
Exit Sub
End If
...
End Sub


Private Sub VerifyUserName( )
Dim rsUserList As New ADODB.Recordset
'ASSUMING THAT THE USERNAME IS NOT EXISTING
ExistUserName = False
'OPEN THE STUDENT TABLE
rsUserList.Open "SELECT * FROM tblUser WHERE Username='" & txtName(7).Text & "'", CoNN, 3, 3
'CHECK IF THE ID NUMBER IS ALREADY EXIST
If rsUserList.Reco rdCount > 0 Then
ExistUserName = True
End If

rsUserList.Clos e
Set rsUserList = Nothing

End Sub

[/CODE]

Hope this help..

[mafaisal]

Hello Werks

I think this is not good
if we edit some thing then save then duplication error is coming using this code
Better way is use Primary key and Unique key constarint for duplication
Also this code
use verifyusename as function return type boolean, then we can avoid extra var for

Faisal

HI this is my code in Validating the username of the Librarian


[CODE=vb 6]
Dim ExistUserName AS Boolean
Private Sub cmdSave_Click()
...
Call VerifyUserName
If ExistUserName = True Then
MsgBox "Username exist please choose aanother", 48, "Username Existing"
Exit Sub
End If
...
End Sub


Private Sub VerifyUserName( )
Dim rsUserList As New ADODB.Recordset
'ASSUMING THAT THE USERNAME IS NOT EXISTING
ExistUserName = False
'OPEN THE STUDENT TABLE
rsUserList.Open "SELECT * FROM tblUser WHERE Username='" & txtName(7).Text & "'", CoNN, 3, 3
'CHECK IF THE ID NUMBER IS ALREADY EXIST
If rsUserList.Reco rdCount > 0 Then
ExistUserName = True
End If

rsUserList.Clos e
Set rsUserList = Nothing

End Sub

[/CODE]

Hope this help..

[debasisdas]

1.Make the auto number field your primary key. It won't accept duplicate value.
or
2.You can check for existence of the record using count and then insert the record if that does not exist already in database.