stop users registering with same email address

[speedy05]

hi

i have a registration page which works fine so far-im using vb.net and connecting to a mysql database wth odbc.
im just wondering how can i stop someone registering with the same email address more than once.I have a couple of books on asp.net but they only refer how to do this with a stored procedure which my version of mysql doesnt support
Really appreciate any help on this..im a beginner and i cant figure out how to do it without a stored procedure...can it be done without?
heres the part of the code i have so far which inserts the users data:


Sub Register_Click(s As Object, e As EventArgs)

Cmd = New OdbcCommand ("INSERT INTO User (firstname, lastname, address1,address2,email,telno,pwd) VALUES (?, ?, ?,?,?,?,?)" ,conn)
Cmd.Parameters.Add("@firstname",odbctype.varchar,10).value = txtfirstname.text
Cmd.Parameters.Add("@lastname",odbctype.varchar,10).value = txtlastname.text
Cmd.Parameters.Add("@addr1",odbctype.varchar,20).value = txtaddress1.text
Cmd.Parameters.Add("@addr2",odbctype.varchar,20).value = txtaddress2.text
Cmd.Parameters.Add("@email",odbctype.varchar,20).value = txtemail.text

Cmd.Parameters.Add("@telno",odbctype.varchar,20).value = txttelno.text
Cmd.Parameters.Add("@pwd",odbctype.varchar,20).value = txtpassword.text

conn.open()
Cmd.ExecuteNonQuery()
Conn.Close()

Response.Redirect("login.aspx")
End Sub

appreciate all suggestions :)
thanks

[A1ien51]

Do something like
select count(email) as total form TableX where email = yourField

if you get a count then you know that the email is there so give them an error message.

Eric

[speedy05]

Thanks for your reply eric,
ive tried that but i keep getting this error:

Exception Details: System.Data.Odbc.OdbcException: ERROR [07002] SQLBindParameter not used for all parameters

Source Error:

Line 39:
Line 40: conn.open()
Line 41: Cmd.ExecuteNonQuery()
Line 42: if count <> 0 then
Line 43: label1.text = "email already exists"

this is the code ive used:

Sub AddDetails_Click(s As Object, e As EventArgs)

dim count as integer

Cmd = New OdbcCommand ("INSERT INTO User (firstname, lastname, address1,address2,email,telno,pwd) VALUES (?, ?, ?,?,?,?,?)" ,conn)


Cmd.Parameters.Add("@firstname",odbctype.varchar,10).value = txtfirstname.text
Cmd.Parameters.Add("@lastname",odbctype.varchar,10).value = txtlastname.text
Cmd.Parameters.Add("@addr1",odbctype.varchar,20).value = txtaddress1.text
Cmd.Parameters.Add("@addr2",odbctype.varchar,20).value = txtaddress2.text
Cmd.Parameters.Add("@email",odbctype.varchar,20).value = txtemail.text
Cmd.Parameters.Add("@telno",odbctype.varchar,20).value = txttelno.text
Cmd.Parameters.Add("@pwd",odbctype.varchar,20).value = txtpassword.text

cmd = new odbccommand("select count (email) as total from researcher where email = ?",conn)

conn.open()
Cmd.ExecuteNonQuery()

if count <> 0 then
label1.text = "email already exists"
else
Response.Redirect("login.aspx")
Conn.Close()
end if

it doesnt give an initial error when i load the page,the error pops up after i click the button to submit the details to db
any ideas?

[Brandoe85]

Something like:

'assuming txtEmail is the email address they enter
cmd = new odbccommand("select count(email) from researcher where email = '" & txtEmail & "'",conn)
If cmd.ExecuteScalar() <> 0 Then
'This email allready exists
Else
'email doesnt exist do insert
End If


Good luck

[speedy05]

hi
thanks for your reply
i was playing around with that but unfortunately that wouldnt work either.
in the end i set a unique constraint on the email column in my database and created my own error message page to show up if a user did try to enter an email already in the db...this seems to have done the trick
thanks again for your help tho :thumbsup:

[miranda]

Here is how i handle this


'check for existance of userID in database
Function CheckUserName(ByVal userID As String) As DataSet
Dim queryString As String = "SELECT userID FROM users WHERE userID = '" & userID & "'"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As IDbDataAdapter = New OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

'check for existance of users email address in database
Function CheckEmail(ByVal email As String) As DataSet
Dim queryString As String = "SELECT email FROM users WHERE email = '" & email & "'"
Dim dbCommand As IDbCommand = New OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As IDbDataAdapter = New OleDbDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

Sub next_Click(sender As Object, e As EventArgs)
If Page.IsValid Then
Dim userDS As New DataSet
userDS = CheckUserName(UserID.Text)
Dim emailDS As New DataSet
emailDS = CheckEmail(email.Text)
If userDS.Tables(0).Rows.Count = 1 Then
'userid exists so display a message
p1Message.Text = "That UserID Exists. Please Pick Another"
ElseIf emailDS.Tables(0).Rows.Count = 1 Then
'email address exists so display a message
p1Message.Text = "Someone has registered with that Email address."
End If
End If
End Sub

[speedy05]

thanks miranda
i might try incorporate + adapt your idea into my code when i get a chance,see which works better ;)