check if the phone number exist in ms table

Question

how i can know if the phone number exist in ms database or not ?

I'm using this code but i have error , i think i need to get all phone number as array and search in array if the number exist or not .

Dim number As String
    con.Open()
    Dim sql As String = "select cphone from cust "
    Dim cmd As New OleDbCommand(sql, con)
    Dim dr As OleDbDataReader
    dr = cmd.ExecuteReader
    While dr.Read
        number = dr(5)
    End While
    con.Close()

    If TextBox5.Text = number Then
        MsgBox("exist")
    Else
        MsgBox("NOT-exist")
    End If

but i think there is easy way to do this , any help please

enter image description here

score 1 · Answer 1 · edited May 23 '17 at 10:24

Try this:

Dim ReturnValue as object
Dim sql As String = "select 1 from cust where cphone = @cphone"
using (Con as OleDbConnection = New OleDbConnection(ConnectionString)) 
    using (cmd As OleDbCommand = New OleDbCommand(sql, Con))
        cmd.Parameters.Add(@cphone).Value = TextBox5.Text
        Con.Open()
        ReturnValue = cmd.ExecureScalar()
        Con.Close()
        If ReturnValue Is Nothing Then
            MsgBox("exist")
        Else
            MsgBox("NOT-exist")
        End If
    end using
end using

Explanations:

Using blocks provides a convenient syntax that ensures the correct use of IDisposable objects.
Parametized queries ensure sql injection safety
Using ExecuteScalar returns the first value that returns from a query. in this case, if the text in TextBox5 doesn't match any cphone in the database, Nothing will return, this is why I've used the ReturnValue object

Note: Code written directly here, I might have made some syntax mistakes, but this is the general direction you should take your code to.

i have error in this : using (con as new OleDbConnection(ConnectionString) using (cmd As New OleDbCommand(sql, con)) — Rabeea Qabaha, May 13 '15 at 13:04
I've written this code directly here as an example. Usually I do add a note about it, somehow I forgot to do it. Anyway, there was a missing `)` in the first using statement that I've fixed now. — Zohar Peled, May 13 '15 at 13:08
See my edited answer. It's been a while since my VB.Net days, I'm mostly working in c# now, so I might have missed a thing or 2. — Zohar Peled, May 13 '15 at 14:56

Rohit Vipin Mathews · Answer 2 · 2015-05-13T11:12:23.487

0

You can do checking in Query

Dim sql As String = "select cphone from cust where cphone = " + TextBox5.Text

Instead of the number you can even return a BIT or TOP 1 You can use ExecuteScalar which would improve performance as well.

edited May 13 '15 at 11:12

answered May 13 '15 at 11:07

Rohit Vipin Mathews

11,629
15
57
112

2

2 comments in the price of one: **First**, use parameters instead of string concatenation. **Second**, replace `number` with `TextBox5.Text`. – Zohar Peled May 13 '15 at 11:11
1

Sure. Those are 2 great and valid points. Feel free to add a parameter version. I'm not sure how to do it in VB. – Rohit Vipin Mathews May 13 '15 at 11:13

check if the phone number exist in ms table

2 Answers2