[RESOLVED] Help needed on web application login!

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

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

Quote:

---

Originally Posted by 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

---

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

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?

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

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

Code:

---

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

VB Code:

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

thanks
problem resolved

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 :bigyello: