See if an email exists

Question

I am able to create a registration but I'm not sure on how to check if an email already exists in the database. Would I have to search the database first or is there a line of code I can use?

MyConn = New OleDbConnection
MyConn.ConnectionString = connString 
MyConn.Open() 

Dim cmd1 As OleDbCommand = New OleDbCommand(str1, MyConn)
str1 = "insert into UserData ([First Name], [Last Name], [Email], [Address], [Phone Number], [Username], [Password]) values (?, ?, ?, ?, ?, ?, ?)"

cmd1.Parameters.Add(New OleDbParameter("First Name", CType(TxtFirstNameReg.Text, String))) 
cmd1.Parameters.Add(New OleDbParameter("Last Name", CType(TxtLastNameReg.Text, String))) 
cmd1.Parameters.Add(New OleDbParameter("Email", CType(TxtEmailReg.Text, String))) 

cmd1.Parameters.Add(New OleDbParameter("Address", CType(TxtAddress.Text, String))) 

cmd1.Parameters.Add(New OleDbParameter("Phone Number", CType(TxtPhoneNumReg.Text, String))) 
cmd1.Parameters.Add(New OleDbParameter("Username", CType(TxtUsernameReg.Text, String))) 

cmd1.Parameters.Add(New OleDbParameter("Password", CType(TxtPasswordReg.Text, String))) 

Try 

    cmd1.ExecuteNonQuery() 
    cmd1.Dispose() 

    MsgBox("Your account has been created") 

    Dim objOutlook As Object 
    Dim objOutlookMsg As Object 
    objOutlook = CreateObject("Outlook.Application") 
    objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
        .To = TxtEmailReg.Text
        .Subject = "Registration Confirmation"
        .Body = "Thank you for registering with Hotel Booking System, your login details for this account are as follows" & Environment.NewLine & Environment.NewLine & " Username: " & TxtUsernameReg.Text & Environment.NewLine & " Password: " & TxtPasswordReg.Text 
        .Send() 
    End With
    objOutlookMsg = Nothing 
    objOutlook = Nothing 


Catch ex As Exception
    'MsgBox(ex.Message) 
End Try
MyConn.Close()

score 0 · Accepted Answer · answered May 15 '17 at 09:05

I would check the database first to see if the email address exists:

Using con As New OleDbConnection(connString ),
      cmd As New OleDbCommand("SELECT * FROM [UserData] WHERE [Email] = ?", con)

 cmd.Parameters.Add(New OleDbParameter("@Email", OleDbType.VarChar)).Value = TxtEmailReg.Text

  con.Open()

  If cmd.ExecuteReader().HasRows Then
     'Record exists
  End If

End Using

Note that I have implemented Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

I would also specify the data type when passing parameters. In your case it probably doesn't matter but I would still consider using the OleDbParameter Constructor (String, OleDbType) to add your parameters. At this point it would be worth noting that when using OleDbParameter it's the order that's important not the name. You already have the order right but wanted to point this out for future reference.

You also don't need to use CType in the way you are. The .Text property is already type String so using CType doesn't actually do anything in this case.

Note that ExecuteNonQuery tells you how many rows have been affected. It would be worth checking for this.

When dealing with Outlook, consider using the Microsoft.Office.Interop.Outlook namespace to use the objects directly. You will have to download these but it's simple enough to do. Just have a Google. I would also look at implementing some code to help with the releasing of these objects. This code is based on Siddharth Rout answer:

Private Sub ReleaseObject(ByVal obj As Object)
    Try
        Dim intRel As Integer = 0
        Do
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Loop While intRel > 0
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

I've put together a bit of sample code which will hopefully give you a good start:

Dim addressExists As Boolean = False
Dim userAdded As Boolean = False

Using con As New OleDbConnection(connString)
    Using cmd As New OleDbCommand("SELECT * FROM [UserData] WHERE [Email] = ?", con)

        cmd.Parameters.Add(New OleDbParameter("@Email", OleDbType.VarChar)).Value = TxtEmailReg.Text

        con.Open()

        If cmd.ExecuteReader().HasRows Then
            addressExists = True
        End If

    End Using

    If addressExists Then
        MessageBox.Show("Email address already exists.", "Email Exists", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Else
        Using cmd As New OleDbCommand("INSERT INTO [UserData] ([First Name], [Last Name], [Email], [Address], [Phone Number], [Username], [Password]) VALUES(?, ?, ?, ?, ?, ?, ?)", con)

            cmd.Parameters.Add(New OleDbParameter("@FirstName", OleDbType.VarChar)).Value = TxtFirstNameReg.Text
            cmd.Parameters.Add(New OleDbParameter("@LastName", OleDbType.VarChar)).Value = TxtLastNameReg.Text
            cmd.Parameters.Add(New OleDbParameter("@Email", OleDbType.VarChar)).Value = TxtEmailReg.Text
            cmd.Parameters.Add(New OleDbParameter("@Address", OleDbType.VarChar)).Value = TxtAddress.Text
            cmd.Parameters.Add(New OleDbParameter("@PhoneNumber", OleDbType.VarChar)).Value = TxtPhoneNumReg.Text
            cmd.Parameters.Add(New OleDbParameter("@Username", OleDbType.VarChar)).Value = TxtUsernameReg.Text
            cmd.Parameters.Add(New OleDbParameter("@Password", OleDbType.VarChar)).Value = TxtPasswordReg.Text

            If cmd.ExecuteNonQuery() = 1 Then
                userAdded = True
            End If
        End Using
    End If
End Using

If userAdded Then

    MessageBox.Show("Your account has been created", "Account Created", MessageBoxButtons.OK, MessageBoxIcon.Information)

    Dim oApp As New Outlook.Application
    Dim oMessage As Outlook.MailItem = DirectCast(oApp.CreateItem(Outlook.OlItemType.olMailItem), Outlook.MailItem)
    With oMessage
        .To = TxtEmailReg.Text
        .Subject = "Registration Confirmation"
        .Body = .Body = "Thank you for registering with Hotel Booking System, your login details for this account are as follows" & Environment.NewLine & Environment.NewLine & " Username: " & TxtUsernameReg.Text & Environment.NewLine & " Password: " & TxtPasswordReg.Text
        .Send()
    End With

    ReleaseObject(oMessage)
    ReleaseObject(oApp)

End If

^{It's outside the scope of this question but I would also look at encrypting passwords. Storing them as plain text is bad practice. Have a look at the SO question; Best way to store password in database, which may give you some ideas on how best to do this.}

I hope this gives you something to go on.

See if an email exists

1 Answers1