[RESOLVED] Help needed on web application login!

[eakin]

Hey guys
I am fairly new so would be grateful if anyone could help me out...

i am creating a web application using asp.net with vb.net code which connects to an sql database.

I am trying to configure some code for my login section which basically figures out if a username already exists and then outputs 'username already exists' in a label.

At present it works for the first row in the database
i.e if my database consists of

username <-- this is the colum name
user
user1
user2

then if I try and register a new username called 'user' then everything works and the label is updated with the 'username already exists' label.
but if I try to pick a new username of 'user1' or 'user2' then it doesn't work and it creates a duplicate entry into the database.

I beleive this is because I am not looping through all the usernames although I am not sure how to do this, can someone please submit the modifed code or instruct me on how to do this loop?

my current code is :-

strSQL = "SELECT * FROM users"
objCmd.CommandText = strSQL
objReader = objCmd.ExecuteReader()
If objReader.Read() Then
If objReader.Item("username") = userTextbox.Text Then
levelTextbox.Text = "username already exists"
objReader.Close()
Else
objReader.Close()
strSQL1 = "INSERT INTO users VALUES ( '" + username + "' , ***other fields contianed here (not important)***)"
objCmd.CommandText = strSQL1
objCmd.ExecuteNonQuery()

End If
End If

[d2005]

im a newbie to so im not too sure,
i think you might need a where

i.e. select user name from users where username.text box = username

then output already exsists

[mendhak]

Hey guys
I am fairly new so would be grateful if anyone could help me out...

i am creating a web application using asp.net with vb.net code which connects to an sql database.

I am trying to configure some code for my login section which basically figures out if a username already exists and then outputs 'username already exists' in a label.

At present it works for the first row in the database
i.e if my database consists of

username <-- this is the colum name
user
user1
user2

then if I try and register a new username called 'user' then everything works and the label is updated with the 'username already exists' label.
but if I try to pick a new username of 'user1' or 'user2' then it doesn't work and it creates a duplicate entry into the database.

I beleive this is because I am not looping through all the usernames although I am not sure how to do this, can someone please submit the modifed code or instruct me on how to do this loop?

my current code is :-

strSQL = "SELECT * FROM users"
objCmd.CommandText = strSQL
objReader = objCmd.ExecuteReader()
If objReader.Read() Then
If objReader.Item("username") = userTextbox.Text Then
levelTextbox.Text = "username already exists"
objReader.Close()
Else
objReader.Close()
strSQL1 = "INSERT INTO users VALUES ( '" + username + "' , ***other fields contianed here (not important)***)"
objCmd.CommandText = strSQL1
objCmd.ExecuteNonQuery()

End If
End If

Avoid posting your question in more than one place. You'll end up with 10 different, half-answers.

[eakin]

Yea mendhak I put it in the wrong section earleir by mistake, sorry

d2005, I'm not trying to select the username form the databse, I am trying to check all the usernames in the table to amke sure it doens't exist in which case I can write the username to the table as it will be unique or otherwise give a 'username already exists' output.

any ideas guys?

[d2005]

yeah
change ure select statement
strSQL = "SELECT * FROM users"

put a where clause in it where textbox text = username
that will check for all the users with that name

i dont think u even need the object readers and stuff then u see

if you select username from users where username = ' " & txtbox.text & " '
then return your error

thats just how i would go about doin it

[aconybeare]

I would say you're best off creating a stored procedure to do this for you

CREATE PROCEDURE up_InsertName
(
  @username varchar(25), 
  @password varchar(25)
)
As
If EXISTS(SELECT 'True' FROM MyTable WHERE username = @username)
BEGIN
  --This means it exists, return it To ASP And tell us
  Return (0)
End
Else
BEGIN
  --This means the record isn't In there already, let's go ahead And add it
  INSERT into MyTable(username, userpassword) VALUES(@username, @userpassword)
End
  Return (1)

In ASP.NET

Private Sub SaveNewUser()
        ' Open the connection
        If myCon.State = ConnectionState.Closed Then myCon.Open()
 
        Dim myCmd As New SqlCommand("up_InsertName", myCon)
        myCmd.CommandType = CommandType.StoredProcedure
 
        ' Create a SqlParameter object to hold the output parameter value
        Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
 
        ' IMPORTANT - must set Direction as ReturnValue
        retValParam.Direction = ParameterDirection.ReturnValue
 
        ' Add the parameters to the Command's Parameters collection
        myCmd.Parameters.Add("@username", Username.Text.Trim.ToLower)
        myCmd.Parameters.Add("@password", NewPassword.Text)
        ' Finally, add the output parameter
        myCmd.Parameters.Add(retValParam)
 
        Dim dr As SqlDataReader
        ' Call the SProc...
 
        Try
            dr = myCmd.ExecuteReader()
            ' Now you can grab the output parameter's value...
            Dim retVal As Integer = Convert.ToInt32(retValParam.Value)
            If retVal = 1 Then
                'SaveNewUser = "OK"
                Response.Redirect("sendtonextpage.aspx", False)
            Else
                'SaveNewUser = "Error"
                lblUserName.Text = "Your chosen username already exists please select another"
            End If
        Catch ex As Exception
            ' Record any exceptions and exit
            ' message is a hidden literal control
            message.Visible = True
            message.Text = "The following exception occurred: SavePerson<br />" + ex.Message.ToString
        End Try
 
        dr.Close()
        If myCon.State = ConnectionState.Open Then myCon.Close()
    End Sub
 
    Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        ' Do validation here
 
        ' Call Save the new user
        SaveNewUser()
 
    End Sub

This is a good article which I used when learning this stuff - 4GuysFromRolla

Hope this helps, any questions let me know

Cheers Al

[eakin]

thanks
problem resolved

[aconybeare]

Excellent! You need to mark the thread as resolved

At the top of this page click the item "Thread Tools" choose "Mark Thread Resolved"

Cheers Al

Oops I see you've already done it, please accept my apologies