I am implementing a registration process on my site in vb.net. I have written the sql that INSERT's the user details into my database including a random string as the Verification code. An email is then sent to the user with a verification link. The link has a query that is the verification code. From the link, the user is directed to a page that has an on_load event that checks if the code from the query string is the same in the database. Every time i test it the user is directed back to the Default page, meaning that the query string verification code was not the same as that in the database. Please can someone tell me what is wrong with my code. I suspect there is a few glaring errors but I am quite new to sql and so not sure why its not working.
Code to add register input details to database and to email them the verification link (after database is updated with random string:
Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Collections.Generic
Partial Class Account_Register
Inherits System.Web.UI.Page
Protected Sub RegisterWizard_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles RegisterWizard.FinishButtonClick
'ADD NEW USER ACCOUNT DETAILS TO USER DATABASE --------------------------------------------------
Dim oPath As String = Server.MapPath("~/app_data/databaseX.mdb")
Dim oReader As System.Data.OleDb.OleDbDataReader = Nothing
Dim oConnection As System.Data.OleDb.OleDbConnection = Nothing
If Not agreeTerms.Checked Then
agreeTerms.ForeColor = Drawing.Color.Red
Return
ElseIf Page.IsValid Then
Dim Letters As New List(Of Integer)
'add ASCII codes for numbers
For i As Integer = 48 To 56
Letters.Add(i)
Next
'lowercase letters
For i As Integer = 97 To 122
Letters.Add(i)
Next
'uppercase letters
For i As Integer = 65 To 90
Letters.Add(i)
Next
'select 8 random integers from number of items in Letters
'then convert those random integers to characters and
'add each to a string and display in Textbox
Dim Rnd As New Random
Dim SB As New System.Text.StringBuilder
Dim Temp As Integer
For count As Integer = 1 To 8
Temp = Rnd.Next(0, Letters.Count)
SB.Append(Chr(Letters(Temp)))
Next
Dim oUserId As Integer = 0
Dim oName As String = txtName.Text
Dim oUserName As String = txtUsername.Text
Dim oPassword As String = txtpsswrd.Text
Dim oActiveAcc As String = "Yes"
Dim oVerCode As String = SB.ToString
Dim oVerUser As String = "No"
Dim ologged As String = "No"
Dim oOrg As String = txtorganiz.Text
Dim oTel As String = txttelephone.Text
Dim oEmail As String = txtEmail.Text
Dim oTown As String = Txttown.Text
Dim oRegStart As String = Date.Today
Dim oSubscribedUser As String = "No"
oConnection = New System.Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Jet OLEDB:Database Password=xxxxxxxxx", oPath))
oConnection.Open()
Dim ipAddress As String = Request.ServerVariables("REMOTE_ADDR")
Dim cmd As New SqlCommand
Dim oCommandSession As New System.Data.OleDb.OleDbCommand("INSERT INTO Users ([Name], Username, [Password], ActiveAccount, VerificationCode, VerifiedUser, LoggedIn, Organisation, Telephone, email, Town, RegistryStart, SubscribedUser)" & _
"VALUES (oName, oUserName, oPassword, oActiveAcc, oVerCode, oVerUser, ologged, oOrg, oTel, oEmail, oTown, oRegStart, oSubscribedUser)", oConnection)
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oName", oName))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oUserName", oUserName))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oPassword", oPassword))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oActiveAcc", oActiveAcc))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oVerCode", oVerCode))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oVerUSer", oVerUser))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@ologged", ologged))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oOrg", oOrg))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oTel", oTel))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oEmail", oEmail))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oTown", oTown))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oRegStart", oRegStart))
oCommandSession.Parameters.Add(New System.Data.OleDb.OleDbParameter("@oSubscribedUser", oSubscribedUser))
oCommandSession.ExecuteNonQuery()
If Not oConnection Is Nothing Then
If oConnection.State = Data.ConnectionState.Open Then
oConnection.Close()
End If
End If
'SEND REGISTERATION CONFIRMATION EMAIL TO USER ------------------------------------
Dim oReciever As String = txtEmail.Text
Dim mm As New MailMessage("[email protected]", oReciever)
mm.Subject = "Registeration complete : Shipping Resources.net"
mm.Body = "<table align='center' width='70%' cellpadding='10' style='text-align: center; border: 3px solid #3366cc; background: #4576ea; color: #333'>" & _
"<tr><td colspan='2' style='background: #3366cc'><img src='http://www.shippingresources.net/Imgs/TitleBanner.png' width='100%' /></td></tr>" & _
"<tr><td colspan='2' style='text-align: left; text-indent: 50px'>Hello " & txtName.Text & ",</td></tr>" & _
"<tr><td colspan='2' style='font-size: 28px; padding: 10px auto 10px auto'><b>You're nearly there. You now just need to click the below link to verify your account</b></td></tr>" & _
"<tr><td colspan='2'><a href='http://www.shippingresources.net/Account/VerifyAccount.aspx?id=oVerCode'>Verify account.</a></td></tr>" & _
"<tr><td colspan='2' style='height: 80px'></td></tr>" & _
"<tr><td colspan='2' style='background: #3366cc'>© Shippingresources.net 2013 <img src='http://www.shippingresources.net/Imgs/logosmall.png' style='position: relative; top: 8px' /></td></tr>" & _
"</table>"
mm.IsBodyHtml = True
Dim smtp As New SmtpClient()
smtp.Host = "mail.shippingresources.net"
smtp.EnableSsl = False
Dim NetworkCred As New System.Net.NetworkCredential()
NetworkCred.UserName = "[email protected]"
NetworkCred.Password = "xxxxxxxxxx"
smtp.UseDefaultCredentials = True
smtp.Credentials = NetworkCred
smtp.Send(mm)
Response.Redirect("~/Default.aspx")
End If
End Sub
End Class
The code behind page VerifyAccount.aspx to check if te random code (oVerCode), is the same as in the database and update the database column "VerifiedUser" as "Yes":
Imports System.Data.SqlClient
Imports System.Collections.Generic
Partial Class VerAccount
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Dim VerifyAccount As String = Request.QueryString("id")
Dim oPath As String = Server.MapPath("app_data/databaseX.mdb")
Dim oValid As Boolean = False
Dim oReader As System.Data.OleDb.OleDbDataReader = Nothing
Dim oConnection As System.Data.OleDb.OleDbConnection = Nothing
Try
oConnection = New System.Data.OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Jet OLEDB:Database Password=xxxxxxxx", oPath))
oConnection.Open()
Dim ipAddress As String = Request.ServerVariables("REMOTE_ADDR")
Dim cmd As New SqlCommand
Dim oParams As New List(Of System.Data.OleDb.OleDbParameter)
oParams.Add(New System.Data.OleDb.OleDbParameter("@VerificationCode", VerifyAccount))
Dim oCommand As New System.Data.OleDb.OleDbCommand( _
"SELECT VerificationCode FROM Users " & _
"WHERE VerificationCode = ?", _
oConnection)
oCommand.Parameters.AddWithValue("?", VerifyAccount)
oReader = oCommand.ExecuteReader()
If oReader.Read() Then
oValid = True
Dim oVerCode = oReader.GetString(oReader.GetOrdinal("VerificationCode"))
End If
Catch ex As Exception
Finally
If Not oReader Is Nothing Then
If Not oReader.IsClosed Then
oReader.Close()
End If
oReader = Nothing
End If
End Try
If oValid Then
Dim oUserVerified As String = "Yes"
Dim oCommandSession As New System.Data.OleDb.OleDbCommand( _
"UPDATE Users SET VerifiedUser = ? " & _
"WHERE VerificationCode = ?", _
oConnection)
oCommandSession.Parameters.AddWithValue("?", oUserVerified)
oCommandSession.Parameters.AddWithValue("?", VerifyAccount)
oCommandSession.ExecuteNonQuery()
Response.Redirect("~/Account/RegistrationComplete.aspx")
Else
Response.Redirect("~/Account/VerificationFailed.aspx")
End If
If Not oConnection Is Nothing Then
If oConnection.State = Data.ConnectionState.Open Then
oConnection.Close()
End If
End If
End Sub
End Class
Many thanks for any kind help :)
Thank both. there were two issues, firstly i am very embarrassed to say the path to my
database was wrong. Also as you both suggested the email link should have been:
VerifyAccount.aspx?id=" & oVerCode & "'
instead of a literal string.
Many thanks