check and prevent double entry for one field in sql table on insert mode form view

I want to prevent double entry for sql table field Name when user is inserting new data in table. Have a formview in insert mode, and when user type name in text box I need to chek if there is a record with the same name in sql table. For example. John is already inserted in field name, and if other user try to enter John in Name text box he will get message box that name is already in table and when click OK he can change name. How can I do that?

Thanks for help.
Who is Participating?
MoedConnect With a Mentor Commented:
Private function CheckForValue(ValueToCheck as string)as boolean
 Dim Command As New OleDbCommand      
Command.Connection = Cnn
Command.CommandText = sql

Dim icount As Integer

      Sql = "SELECT Count (*) " _
            & "FROM MyTable " _
            & "WHERE MyTable.MyField = " & ValueToCheck & "  "

      icount = Data.DataExecuteScalarCount(Sql, False)

      If icount > 0 Then
         Return True
         Return False
      End If

End function

You might have to tweak the sql.  I am using MS Access and the syntax might be a bit different.  If the count is greater than 0 then you know the value already exists.  Make sure you trim the value before you send it to the function so all leading and trailing spaces are removed.  Trim(MyTextBox.text)
Call this from the MyTextBox.LostFocus event.  I've found the Leave event can be a little strange if you try to clear MyTextBox and place the focus back to it.  

If CheckForValue(MyTextBox.text.tostring) then
MsqBox("Value exists")
mytextbox.text = nothing
end if
If it's false then nothing happens

You can do this in many ways.
The best way is to set the "Name" column in the database as primary key. And then catch the "Primary Key Violation" exception whitch will be thrown if a user tries to insert same name.

Other way is to first check if the name exists before insert.

I call a function in the textbox.Leave event that goes to the database and checks for the value.  If already there it returns true else false.  If true then msgbox to inform the user, clear the field and set focus back to the textbox.  I prefer to catch the error immediately.  This achieves the same result as utter77 just a different way.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Carl TawnSystems and Integration DeveloperCommented:
How are you writing to the database? If its via Stored Procedure then you can do the check there before commiting the data, which gives you the added bonus of applying the logic at the database level in case you need to insert data from elsewhere.
kahvedzicAuthor Commented:
I use form view in insert mode to insert data in table. In formview have a text box that is bound to field in sql table.
@Moed can you give me detailed explanation with code for your solution with function on textbox that I can apply to my formview.
kahvedzicAuthor Commented:
Anyone with some solution for my problem? It\s quite an urgent one.

This line "icount = Data.DataExecuteScalarCount(Sql, False)"

should be:

icount = Data.DataExecuteScalarCount(Sql)

kahvedzicAuthor Commented:
I did not find LostFocus event for textbox anywhere in visual studio 2005.How to call a function?
In the editor window on the left drop down find the name of the your text box.  In the right drop down will be all the available events for this object. You will find the lost focus event.  
kahvedzicAuthor Commented:
on attached picture are events that I have for this text box and there is no where event for Lost Focus. This is web app not windows forms.

Sorry, don't do web development but based on a quick check you would want to work with validation of input.  Check out this link:
Seems pretty straight forward to check the user input.
Sorry I can't be more help.
kahvedzicAuthor Commented:
This is in code behind:
    Sub ServerValidation(ByVal source As Object, ByVal args As ServerValidateEventArgs)
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            Dim mySqlConnection As New Data.SqlClient.SqlConnection(connectionString)
            Dim cmd As New Data.SqlClient.SqlCommand
            cmd.Connection = mySqlConnection
            If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close()
            cmd.CommandText = "SELECT Count (*)FROM Servers WHERE Name = '" & args.Value & " ' "

            Dim icount As Integer
            icount = cmd.ExecuteScalar()

            args.IsValid = (icount = 0)
        Catch ex As Exception
            args.IsValid = False
        End Try
    End Sub

Then add a custom validator on insert template for nametextbox and add server validation for server validate event of validator. Works like a charm, thanks for effort.


All Courses

From novice to tech pro — start learning today.