Adding records in vb.net and Checking if records exist using elseif

Question

I'm new to vb.net.. so sorry in advance. can anyone help me what's wrong with my elseif line of code.

    Dim con As SqlConnection = New SqlConnection("Data Source=PC11-PC\kim;Initial Catalog=ordering;User ID=sa;Password=123")
    Dim cmd1 As SqlCommand = New SqlCommand("Select * from Customer", con)

    Dim first1 As String
    Dim second2 As String
    first1 = "FirstName"
    second2 = "LastName"

    con.Open()
        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MsgBox("Please fill-up all fields!", MsgBoxStyle.Exclamation, "Add New Customer!")
        'this will supposedly display error message for "User Already Exist"
        ' ElseIf textbox1.text = first1 and textbox2.text = second2 Then
        '   MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
        Else
            Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text + "')", con)
            cmd.ExecuteNonQuery()
            MsgBox("Records Successfully Added!", MsgBoxStyle.Information, "Add New Customer!")
            TextBox1.Text = ""
            TextBox2.Text = ""
            con.Close()

        End If

Actually it works. but when I input an existing record it still adds the record on the database instead of displaying a error message "USER ALREADY EXIST!" — Sam Teng Wong, Dec 18 '13 at 04:25
Your elseif line of code is just checking if the textbox values are equal to "FirstName" and "LastName" you are not checking if they exist in the database. If you are trying to do that, you can either run a select query against the DB and see if you get a result OR if you have a unique constraint on the table, just try to insert and handle the exception when there is a constraint error. — pstrjds, Dec 18 '13 at 04:26
well sir, I actually assigned a variable to hold the value of FirstName and LastName there which is named first1 and second2. after that I response.write it to see if it displays the records and it displays. I am wondering why it won't work on elseif condition. can you help me construct a condition for elseif sir? — Sam Teng Wong, Dec 18 '13 at 04:30

Karl Anderson · Accepted Answer · 2013-12-18T04:45:01.583

You need to actually check to see if the user already exists by executing the SELECT * FROM Customer query, but you need to add the WHERE clause, like this:

If TextBox1.Text = "" Or TextBox2.Text = "" Then
    MsgBox("Please fill-up all fields!", MsgBoxStyle.Exclamation, "Add New Customer!")
Else
    Dim theQuery As String = "SELECT * FROM Customer WHERE FirstName=@FirstName AND LastName=@LastName"
    Dim cmd1 As SqlCommand = New SqlCommand(theQuery, con)
    cmd1.Parameters.AddWithValue("@FirstName", TextBox1.Text)
    cmd1.Parameters.AddWithValue("@LastName", TextBox2.Text)

    Using reader As SqlDataReader = cmd1.ExecuteReader()
        If reader.HasRows Then
            ' User already exists
            MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
        Else
            ' User does not exist, add them
            Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text + "')", con)
            cmd.ExecuteNonQuery()
            MsgBox("Records Successfully Added!", MsgBoxStyle.Information, "Add New Customer!")
            TextBox1.Text = ""
            TextBox2.Text = ""
        End If
    End Using    

    con.Close()
End If

Note: I added the usage of a parameterized query in the SELECT * query. You should prefer parameterized queries to in-line SQL because it will protect your code from SQL Injection attacks. Never trust the data typed in by the user.

thanks sir carl.. you're a big help and your code is easy to understand compared to other that I searched in google. =) — Sam Teng Wong, Dec 18 '13 at 04:44
@user3113490 if this answers you question please mark it as so, then others searching for a similar result know you have an answer that works. — OSKM, Dec 18 '13 at 07:23
@user3113490 - sure no problem, glad it helped you. Feel free to accept the answer if you wish and up-vote the answer when you can. :-) — Karl Anderson, Dec 18 '13 at 12:18

Adding records in vb.net and Checking if records exist using elseif

1 Answers1

Linked