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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Moe DeShongCommented:
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.
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.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Moe DeShongCommented:
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?
Moe DeShongCommented:
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.

Moe DeShongCommented:
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.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.