Checking duplicates

[Elbob]

Hi

I have a form that allows the user to enter a phone number straight into an SQL Server 2005 database.

How can i check that this phone number already exists in this database, to make sure i can't add it again?

I've been reading about performing a select statement before the insert statement, but i cant get this to work.

Any help appreciated

 

[JuggaloBrotha]

You'll need a select query similar to this:

"SELECT PhoneNumberField FROM TheTable WHERE PhoneNumberField LIKE '%" & PhoneFieldOnForm & "%';"

All you really need to do is fill in the:
PhoneNumberField
TheTable
PhoneFieldOnForm

 

[Elbob]

Hi. This is the code I've got so far. Obviously it doesnt work. Keep getting an "incorrect syntax near )" error on
exist = cmd.ExecuteScalar().

Imports System.Data
Imports System.Data.SqlClient

Public Class AddUser

    Private dataCon = New SqlClient.SqlConnection("server=""; database=; User Id=;Password=")
    Private cmd As SqlCommand

    Private Sub AddUser_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmd = New SqlCommand
        cmd.Connection = dataCon
        cmd.CommandText = "INSERT INTO User (TelNo) VALUES (@TelNo)"
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
       
        Dim exist As Integer
        If exist > 0 Then
            MsgBox("Aready exists")
        Else
            cmd.Parameters.AddWithValue("@TelNo", txtTelNo.Text)
           
            dataCon.Open()
            cmd.CommandText = "SELECT TelNo FROM User WHERE TelNo LIKE '%" & txtTelNo.Text & "')"

            exist = cmd.ExecuteScalar()
            cmd.ExecuteNonQuery()
            'Close connection
            dataCon.Close()
            MsgBox("New User Added!")
            Me.Close()
            ' Exit Sub
        End If
      
        Exit Sub
    End Sub
End Class

Am i heading in the right direction with this?

 

[JuggaloBrotha]

You have a random parenthesis in your query, this is how it should be:

cmd.CommandText = "SELECT TelNo FROM User WHERE TelNo LIKE '%" & txtTelNo.Text & "';"

 

[Elbob]

LOL.

As if i missed that

Nothing gets added to the database tho.

Do i need some sort of if statement to handle it?

 Dim str As String
        str = "SELECT TelNo FROM User WHERE TelNo LIKE '%" & txtTelNo.Text & "';"
        cmd.CommandText = str

        If str > 0 Then

            str = "INSERT INTO User (TelNo) VALUES (@TelNo)"
            cmd.CommandText = str
        Else
            MsgBox("Error!!!")
        End If

This is what ive got so far. But i get a string to int conversion error

 

[JuggaloBrotha]

You still need to execute the select query to know if the phone number exists, in which case if there's 0 rows returned then you run the insert query.

You're getting the string to integer error on this line: If str > 0 Then
because you're comparing the select statement string to an integer, I'm not even going to guess why you're trying to do that.

 

[Elbob]

I thought the if str > 0 would say

If no results are found do insert, otherwise dont.

If there another way of doing this that will work?

 

[JuggaloBrotha]

You don't check the string to see if any rows were returned from the query because str will always be "SELECT TelNo FROM User WHERE TelNo LIKE '%" & txtTelNo.Text & "';"

You need to use your database command for that:

Imports System.Data
Imports System.Data.SqlClient

Public Class AddUser

    Private dataCon As New SqlClient.SqlConnection("server=""; database=; User Id=;Password=")
    Private cmd As SqlCommand
    Private SelectString As String = "SELECT TelNo FROM User WHERE TelNo LIKE '%" & txtTelNo.Text & "%';"
    Private InsertString As String = "INSERT INTO User (TelNo) VALUES (@TelNo);"

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        cmd = New SqlCommand(SelectString, dataCon)
        Try
            dataCon.Open()
            If cmd.ExecuteNonQuery() = 0I Then
                'Doesn't exist, so add it
                cmd = New SqlCommand(InsertString, dataCon)
                cmd.Parameters.AddWithValue("@TelNo", txtTelNo.Text)
                cmd.ExecuteNonQuery()   
                Messagebox.Show("New User Added!")
            End If
        Catch ex As Exception
            Messagebox.Show("Problem adding new user:" & Environment.NewLine & ex.Message)
        Finally
            If dataCon.State <> ConnectionState.Closed Then dataCon.Close()
        End Try
        Me.Close()
    End Sub
End Class

Keep in mind that the code may not be perfect, I typed in up in Notepad and I'm not able to run it right now, but it should get the point across

 

[Elbob]

Thanks for that. Only problem is that i'm getting a "reference to a non-shared member requires an object reference" error on the

Private Const SelectString As String = "SELECT No FROM User WHERENo LIKE '%" & No.Text & "')"

 

[Elbob]

I've managed to get this working now.

Thankyou so much for your help JuggaloBrotha.

Your a STAR!!!

 

[JuggaloBrotha]

I've edited my previous post, SelectString and InsertString aren't constants anymore

 

[cjard]

You'll need a select query similar to this:

"SELECT PhoneNumberField FROM TheTable WHERE PhoneNumberField LIKE '%" & PhoneFieldOnForm & "%';"

Please don't advise newbies to use string concatenation to build SQLs; they look to you as a mentor because youre answering their question, so you have a responsiblity to point them in the best direction and building SQLs via string concat has been an unacceptable way of developing software for probably 20 years now.

Further, there should be no need to put wildcard operators at the start and end of a variable; doing so will make any indexes on the field unusable and cause a massively slower search time than is needed

Finally, the user is asserting that phone number is to be unique, and possibly searchable thereby being the perfect candidate for a Unique Index, that will also prevent duplicate entry.
Far from advising Elbob to run a select at all, you should have advised him/her to create a unique index or primary key on the field in question.

 

[JuggaloBrotha]

Please don't advise newbies to use string concatenation to build SQLs; they look to you as a mentor because youre answering their question, so you have a responsiblity to point them in the best direction and building SQLs via string concat has been an unacceptable way of developing software for probably 20 years now.

Further, there should be no need to put wildcard operators at the start and end of a variable; doing so will make any indexes on the field unusable and cause a massively slower search time than is needed

Finally, the user is asserting that phone number is to be unique, and possibly searchable thereby being the perfect candidate for a Unique Index, that will also prevent duplicate entry.
Far from advising Elbob to run a select at all, you should have advised him/her to create a unique index or primary key on the field in question.

Yes, I get the slap on the hand. I've gotta stop doing vba in access 2003/2007 and checking the .Net forums at the same time.