Question ID_number (Check If Already exists)

[jtoutou]

hello
I have table in sql server with personnel fields like lastname ,firstname id number e.t.c
In my form on id_number textbox leave event i want to check if the id_number already exists.i'am using the following code :

Dim conn As New SqlConnection(My.Settings.MyConnectionString)
        Dim text_check = Me.textbox.Text
        
        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()

        Dim cmd As New SqlCommand("SELECT Lastname,Firstname,ID_number  FROM Personnel WHERE ID_number = '" & Me.Textbox.Text & "' ", conn)
        Dim valid As Boolean = False
        Dim HasRows As Boolean = False

        Dim IDParam As New SqlParameter("@ID_number", Me.textbox.Text)
        
        cmd.Parameters.Add(IDParam)

        
        Dim dataReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Try
            If dataReader.HasRows Then
                While dataReader.Read
                    If text_check = dataReader.Item("ID_number") Then
                        valid = True
                    End If
                End While
            End If
            HasRows = True
            dataReader.Close()

        Catch exO As SqlException
            MessageBox.Show(exO.Message)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()

            End If

            cmd = Nothing
            dataReader = Nothing
            conn.Dispose()
            
        End Try

        If valid = True Then


            Dim msg2 As String
            Dim style2 As MsgBoxStyle
            Dim title2 As String
            Dim result As MsgBoxResult

            msg2 = "Current ID_number Already exist"
            style2 = MsgBoxStyle.OkOnly
            title2 = "Caution"
            result = MsgBox(msg2, style2, title2)
            If result = MsgBoxResult.Ok Then
                Textbox.Text = string.Empty()
               textbox.Focus()

            End If
        End If

where textbox=ID_number text

I use the same code for my log in form and it works properlly ..
in my Personnel form does not work and i do not know why..
i need a guideline...thanx in avance

 

[jtoutou]

One more question plz..
is there any guideline about handling a violation of unique key?

 

[cjard]

You tell me! What do you want to do when a key is violated?

ps; if you get the database to do the key calculation, it won't happen

Make life easier:
1) read the DW2 link in my signature, starting with the section Creating a Simple Data App
2) Use SQLServer, not Access. Using access will make your life much harder than it needs to be

 

[picoflop]

Using access will make your life much harder than it needs to be

Installation is easier of course. Creating tables, queries etc is easier too (at least if one is not very familiar with the management tools for SQL servers). LATER you will have an easier life with SQL server. Though there ARE scenarios where a simple access database makes sense.

BTW: Even Access knows auto incrementing values. So it would make sense to make the id column an "auto number".

 

[cjard]

Access does know about autonumbers, yes.. But you'll really struggle to get the number it just made, returned to your dataset. You know; the thing that you use SELECT scope_identity() for in SQLs. Access = nuisance.

Also, I'd hesitate to say that SQLServer databases are harder to design; both access and the VS IDE can perform that function