How to verify for duplicates before insertion in a dataset?

  • Thread starter Thread starter Pascal
  • Start date Start date
P

Pascal

Hello
I am trying to create a small application where the user must identify
before he begins. If he has never used the application, he identifies
creating a new user : firtsname + name + date of birth.
I created a form on which I drag and drop the table Eleves from my dataset :
calcul_mentalDataSet from the database calcul_mental.mdb. As mentioned here:
http://plasserre.developpez.com/v6-6.htm in french.... sorry
I'd like to verify that the user does not exist prior to his registration.
I tried without success to compare strings in the textboxes of the form to a
row in the dataset with find and with sql.... Any help will be appreciated
thanks a lot
pascal
 
I tried this but don't work:
Function VerifUserExist() As Boolean
'text from textboxes to verify
Dim strNom As String = NomTextBox.Text
Dim strPrenom As String = PrenomTextBox.Text
Dim dteDdn As String = DdnDateTimePicker.Value.ToString
Dim dv As DataView
dv = New DataView(Me.Calcul_mentalDataSet.Eleves)

Dim x As Integer
'dv.RowFilter = "Nom = '" & strNom & "'"
dv.Sort = "Nom DESC"

x = dv.Find(strNom)

If x > 0 Then
Return True
Else
Return False
End If

End Function
 
Pascal said:
I tried this but don't work:
Function VerifUserExist() As Boolean
'text from textboxes to verify
Dim strNom As String = NomTextBox.Text
Dim strPrenom As String = PrenomTextBox.Text
Dim dteDdn As String = DdnDateTimePicker.Value.ToString
Dim dv As DataView
dv = New DataView(Me.Calcul_mentalDataSet.Eleves)

Dim x As Integer
'dv.RowFilter = "Nom = '" & strNom & "'"
dv.Sort = "Nom DESC"

x = dv.Find(strNom)

If x > 0 Then
Return True
Else
Return False
End If

End Function
Click to expand...


I believe from the docs, Find() uses the Primary Key collumn, so confirm Nom
is the key column. After that, you check if x is greater than 0 for the
found flag. Find returns -1 if not found. Zero is perfectly fine if the
value is in the first row, as the rows are zero based.
 
Hello
thanks for answering
the table is like that
N_Eleves
Nom
Prenom
Ddn
where N_Eleves is the primary key....

Do I have to change the table ?
pascal
 
Pascal said:
Hello
thanks for answering
the table is like that
N_Eleves
Nom
Prenom
Ddn
where N_Eleves is the primary key....

Do I have to change the table ?
pascal
--
http://www.scalpa.info
http://scalpa98.blogspot.com/
http://scalpa-production.blogspot.com/

Family Tree Mike said:
I believe from the docs, Find() uses the Primary Key collumn, so confirm
Nom is the key column. After that, you check if x is greater than 0 for
the found flag. Find returns -1 if not found. Zero is perfectly fine if
the value is in the first row, as the rows are zero based.
Click to expand...


I believe this will test what you want, but you may want to add more
conditions in the filter expression (the first string in the select). I
hard coded test strings as I was using a console app for testing.

Function MyVerify()
Dim rows As DataRow() = dt.Select("Nom='Lewis' and Prenom='Jerry'", "Nom
DESC")
Return rows.Count > 0
End Function
Click to expand...
 
If i understood , this is the new try ( rows.count didn't work because of a
system.array problem I don't understand) so i changed it to Lengh but the
code didn't work..... arrgh It's difficult!: thnks for your help Have a good
weekend

Function VerifUserExist() As Boolean
'I get strings from textboxes
Dim strNom As String = NomTextBox.Text
Dim strPrenom As String = PrenomTextBox.Text
Dim dteDdn As String = DdnDateTimePicker.Value.ToString
Dim x As Integer
Dim dt As DataTable = New DataTable()
dt = Me.Calcul_mentalDataSet.Eleves
Dim rows As DataRow() = dt.Select("Nom='" & strNom & "' and
Prenom='" & strPrenom & "'", "Nom DESC")
x = rows.Length
If x >= 1 Then
Return True
Else
Return False
End If

End Function

--
http://www.scalpa.info
http://scalpa98.blogspot.com/
http://scalpa-production.blogspot.com/

 
DataSet.Table already as a .Select method, so you don't necessarily need to
do the conversion to a DataTable.


Function VerifUserExist(lastName as string , preName as string ) As Boolean

dim rows as DataRow()

dim limitedDataSetSql as string = string.Empty

limitedDataSetSql = "Nom='" & "Cousteau" & "'" '' Or lastName , ha ha
limitedDataSetSql += " AND Prenom='" & preName & "'"
Console.Writeline ( limitedDataSetSql )
rows = Me.Calcul_mentalDataSet.Eleves.Select( limitedDataSetSql )

dim returnValue as boolean = false

If Not (rows is nothing) then
if rows.Length > 0 then
returnValue = true
end if
ENd if


return returnValue

end function

......
If you're doing just a .Length check, then you don't need the overhead of
the .Sort (the second argument).


I used limitedDataSetSql as the variable name because .Select on the
DataSet.Table is very limited.
But it has some useful abilities.

A couple of hints.
Encapsulate your logic. Don't mix the "collect info from the user controls
(text boxes) with the code".
Collect the info, then call the routine above.

Try to wean away from your hungarian notation.

..................

Qu'est-ce que c'est? Cafe du Lait? Ahhh, C'est Vrai.

My favorite commercial from the mid 90's!




Pascal said:
If i understood , this is the new try ( rows.count didn't work because of
a system.array problem I don't understand) so i changed it to Lengh but
the code didn't work..... arrgh It's difficult!: thnks for your help Have
a good weekend

Function VerifUserExist() As Boolean
'I get strings from textboxes
Dim strNom As String = NomTextBox.Text
Dim strPrenom As String = PrenomTextBox.Text
Dim dteDdn As String = DdnDateTimePicker.Value.ToString
Dim x As Integer
Dim dt As DataTable = New DataTable()
dt = Me.Calcul_mentalDataSet.Eleves
Dim rows As DataRow() = dt.Select("Nom='" & strNom & "' and
Prenom='" & strPrenom & "'", "Nom DESC")
x = rows.Length
If x >= 1 Then
Return True
Else
Return False
End If

End Function
Click to expand...
 
the solution from Jens Suessmeyer at microsoft is nice:

Public Function VerifUserExist() As Boolean

'I get strings from textboxes Jens.Suessmeyer
Dim strNom As String = NomTextBox.Text
Dim strPrenom As String = PrenomTextBox.Text
Dim dteDdn As String = DdnDateTimePicker.Value.ToString
Dim x As Integer = 0
Dim dt As New DataTable()
dt = Me.Calcul_mentalDataSet.Eleves
dt.DefaultView.RowFilter = String.Format("Nom='{0}' AND Prenom='{1}'
AND Ddn='{2}'", strNom, strPrenom, dteDdn)
If dt.DefaultView.Count > 1 Then
Return True
Else
Return False
End If

End Function
http://www.scalpa.info/logiciels/mdi/index.htm
 
Back
Top