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

[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