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]

find answer

the code use properly...
the problem is that for the id_number i use masked text box and i did not trim the data form spaces..

 

[InertiaM]

You shouldnt be writing queries like this:-

"SELECT Lastname,Firstname,ID_number  FROM Personnel WHERE ID_number = '" & Me.Textbox.Text & "' "

Read cjard's excellent post about Parameterised queries

 

[jtoutou]

THANX FOR YOUR REPLY ...THE STATEMENT WAS GOOD...I FOUND ANOTHER PROBLEM IN MY TABLE.
there are spaces stored as data in the id_number field that's why

ID_check = dataReader.Item("ID_number")

never much data..
the only i can think now (for the spaces) is that i use maskedtext box with particular length

 

[InertiaM]

You are adding a parameter to the command, and then not using it.

It should be :-

        Dim cmd As New SqlCommand("SELECT Lastname,Firstname,ID_number  FROM Personnel WHERE ID_number = @ID_number", 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)

To get around the spaces problem, use something like :-

        Dim cmd As New SqlCommand("SELECT Lastname,Firstname,ID_number  FROM Personnel WHERE TRIM (ID_number) = @ID_number", conn)

and

        Dim IDParam As New SqlParameter("@ID_number", Me.textbox.Text.trim)

 

[jtoutou]

it works fine....thank you for your knowledge..

 

[jtoutou]

Please i need to ask something else
the above code as i said works fine
so... when the id numer exists a msgbox is being displayed...

Dim msg3 As String
                        Dim style3 As MsgBoxStyle
                        Dim title3 As String
                        Dim result As MsgBoxResult

                        msg3 = "the Id_number Already exists in department:" & dataReader.Item("id_department") & vbCrLf & dataReader.Item("Lastname") & " " & dataReader.Item("Firstname") & " ID NUMBER :" & dataReader.Item("id_number")
                        style3 = MsgBoxStyle.OkOnly
                        title3 = "Wrong Id NUMBER"
                        result = MsgBox(msg3, style3, title3)

the id deprtment comes from a combo box and in the table is a number.
how the

dataReader.Item("id_department")

will give me the display member
thanx...

 

[InertiaM]

1. id_department isnt in your query, so it wont be returning it.

2. Turn Option Strict on and also Option Explict on (if it is off)

3. Investigate Messagebox (instead of Msgbox), String.Format and Environment.NewLine (instead of vbCrLf) - then you can lose the reference to Microsoft.VisualBasic

 

[jtoutou]

I am very Sory i forgot to tell you that....
the id_department is alreaydy in my statement...
i will study the other two parameters you wrote to me and i will msg you again
thnak you very much

 

[jtoutou]

WHY do i hve to Turn Option Strict on and also Option Explict on (if it is off) when the default value is on
and also i get error messages
Error 1 Option Strict On prohibits operands of type Object for operator '&'.
and also
dataReader.Item("id_number) = ID_number.text i get
Error 1 Option Strict On disallows operands of type Object for operator '='. Use the 'Is' operator to test for object identity.

 

[picoflop]

WHY do i hve to Turn Option Strict on and also Option Explict on (if it is off)

avoids unclean coding like:
dataReader.Item("ASMA") = ID_number.text
because "item" is of type object and "text" is of type string. If you THINK that "item" returns a string, you should explicitely tell VB that it IS. Also you might want to check IF "item" is not DBNull.

 

[cjard]

WHY do i hve to Turn Option Strict on and also Option Explict on (if it is off) when the default value is on

Stops mistakes caused by sloppy coding

and also i get error messages

Evidence of sloppy coding

Error 1 Option Strict On prohibits operands of type Object for operator '&'.

If you have an Object that is actually a String, either call DirectCast(theobject, String), or call theobject.ToString()

dataReader.Item("id_number) = ID_number.text i get
Error 1 Option Strict On disallows operands of type Object for operator '='. Use the 'Is' operator to test for object identity.

I need to see the full line of code before I can properly comment on this. I don't work in VB, but it looks like youre doing a reference comparison when you want to do an .Equals or vice versa


-

As to your original question.. checking whether a record exists first? DOn't bother. Just insert it anyway and if it throws an error, handle it. You shouldnt be having the client calculate an ID value at all; get the database to do it

 

[jtoutou]

At first thank you very for your reply.
My first question was about to check if an id_number exists after leaving event of a textbox.
my code is the following which with

Option Explicit On
Option Strict On

does not work
note that i am a new in vb (i am just a pilot)
code follows

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,id_Department 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).trim = Directcast(dataReader.Item("id_number"),string) 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 msg As String
            Dim title As String
            Dim result As MsgBoxResult

		msg=”......”
title=”.....”
            Messagebox.show(msg,title)
           textbox.focus
        End If

plz point me my mistakes so i can learn form them..
thank you

 

[picoflop]

Your SQLCommand does not have the parameter @id_number, but later you use Parameters.Add
If id_number is a numeric value in your db, surrounding it with single quotes would be an error. If id_number is not a number either rename the field or make it numeric

Your SQL statements selects ALL records with matching id_number -> should be only one, or not? And later you check if the selected record has a matching id-number. What for? If id_number would not match, the record would not have been selected, would it?

Is id_number in your database a unique key? If not ... why not? If yes, simply do as already suggested and insert the record without checking if id_number is valid. If the value already exists in the db, you will get an exception anyway that you can handle.

Is there a reason why id_number (for a newly created record) has to be entered by the user? You would avoid much hassle (as it was already stated) if you would define that field as something that your SQL server increases automatically (depends on the SQL server you use) when a new record is created.

Just a hint (has nothing to do with your problem): Never pass any user input directly into a SQL statement! This can easily lead to security issues (SQL injection). Better check the entered value first, before you continue to use it in your app. In your case (the entered text value is a number), you could simply convert the string to int and back to string.

 

[jtoutou]

Your SQL statements selects ALL records with matching id_number -> should be only one, or not? And later you check if the selected record has a matching id-number. What for? If id_number would not match, the record would not have been selected, would it?

1.the only for that is that i want to return exactly the name whose the id belongs to.

2.Your are very right about the "unique key"...(I think that i like making my life difficult)...I've already made the id_number unique after you told me to.

3. as about the safety ..i think its to early for me ...i have to learn first of all the basics and believe me i will take your advise seriously

thank you for your knowledge