Solved

How to check username availability?

Posted on 2008-06-17
6
820 Views
Last Modified: 2011-10-03
Hi Guys,
I'm using ASP.net + VB.Net + Access. Please help me to uderstand how to check username availability? Thanks. I have the following code:

Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click

        Dim con As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\App_data\GLC.mdb"))

        Dim SqlCommand As New Data.OleDb.OleDbCommand

        Dim SqlCommandPro As New Data.OleDb.OleDbCommand

        SqlCommand.CommandText = "INSERT INTO Students ( Username, [Password], SchoolID, LastName, FirstName, Email ) Values ('" + UserName.Text.Replace("'", "_") + "','" + Password.Text.Replace("'", "_") + "','" + DropDownListSchool.SelectedValue + "','" + LastName.Text.Replace("'", "_") + "','" + FirstName.Text.Replace("'", "_") + "','" + Email.Text.Replace("'", "_") + "')"

        SqlCommandPro.CommandText = "INSERT INTO ProgramsStudents (Username, CourseID) Values ('" + UserName.Text.Replace("'", "_") + "','" + DropDownListProgram.SelectedValue + "')"
 
 
 

        If Page.IsValid Then
 
 

            con.Open()
 

            SqlCommand.Connection = con

            SqlCommandPro.Connection = con
 

            SqlCommand.ExecuteNonQuery()

            SqlCommandPro.ExecuteNonQuery()
 
 

            con.Close()
 

        End If

    End Sub

Open in new window

0
Comment
Question by:Robertyue
  • 5
6 Comments
 

Author Comment

by:Robertyue
ID: 21808646
Plus, it says my insert statement is wrong...
0
 

Author Comment

by:Robertyue
ID: 21808889
My bad, the insert function works fine...But i need to know how to check username availability, thanks
0
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 500 total points
ID: 21809022
Check the tables before attempting to update.
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click

    If Page.IsValid Then

       Dim con As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\App_data\GLC.mdb"))
 

        'check if name is in table

        Dim sqlCheckStudent As String = "SELECT UserName FROM Students WHERE UserName='" + UserName.Text.Replace("'", "_") + "')"
 

        'insert data into table

        Dim sqlInsertStudent As String = "INSERT INTO Students ( Username, Password, SchoolID, LastName, FirstName, Email ) Values ('" + UserName.Text.Replace("'", "_") + "','" + Password.Text.Replace("'", "_") + "','" + DropDownListSchool.SelectedValue + "','" + LastName.Text.Replace("'", "_") + "','" + FirstName.Text.Replace("'", "_") + "','" + Email.Text.Replace("'", "_") + "')"
 

        'check if courseid is in table for that student

        Dim sqlCheckCourse As String = "SELECT CourseID FROM ProgramsStudents WHERE Username='" + UserName.Text.Replace("'", "_") + "' AND CourseID='" + DropDownListProgram.SelectedValue + "'"
 

        Dim sqlInsertCourse As String = "INSERT INTO ProgramsStudents (Username, CourseID) Values ('" + UserName.Text.Replace("'", "_") + "','" + DropDownListProgram.SelectedValue + "')"
 

        con.Open()

        Dim okayToContinue As Boolean = False

        Dim doCheckStudent As New Data.OleDb.OleDbCommand(sqlCheckStudent, con)

        If IsDBNull(doCheckStudent.ExecuteScalar) Then

            Dim doInsertStudent As New Data.OleDb.OleDbCommand(sqlCheckStudent, con)

            okayToContinue = (0 < doInsertStudent.ExecuteNonQuery())

        Else

            '----------------------------------------

            'The Username is already in the table.

            'Do something here.

            '----------------------------------------

            okayToContinue = False 'or true, depending on the above

        End If

        If okayToContinue Then

            Dim doCheckCourse As New Data.OleDb.OleDbCommand(sqlCheckCourse, con)

            If IsDBNull(doCheckCourse.ExecuteScalar) Then

                Dim doInsertCourse As New Data.OleDb.OleDbCommand(sqlInsertCourse, con)

                doInsertCourse.ExecuteNonQuery()

            End If

        End If

        con.close

    End If

End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Robertyue
ID: 21809339
What's okayToContinue for? If the Username is already exist, you cannot insert any data. On the other side, if the username is not in the database, you should be able to excute both insert command. I'm really confued with okayToContinue. Could you please explain a liitle? Thanks
0
 

Author Comment

by:Robertyue
ID: 21809398
No matter what username (not exist in the databse) I entered, it says the user is already exist. Please see my code
 Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click

        Dim con As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\App_data\GLC.mdb"))

        Dim SqlCommand As New Data.OleDb.OleDbCommand

        Dim SqlCommandPro As New Data.OleDb.OleDbCommand

        Dim sqlCheckStudent As String = "SELECT UserName FROM Students WHERE UserName='" + UserName.Text.Replace("'", "_") + "'"

        SqlCommand.CommandText = "INSERT INTO Students ( Username, [Password], SchoolID, LastName, FirstName, Email ) Values ('" + UserName.Text.Replace("'", "_") + "','" + Password.Text.Replace("'", "_") + "','" + DropDownListSchool.SelectedValue + "','" + LastName.Text.Replace("'", "_") + "','" + FirstName.Text.Replace("'", "_") + "','" + Email.Text.Replace("'", "_") + "')"

        SqlCommandPro.CommandText = "INSERT INTO ProgramsStudents (Username, CourseID) Values ('" + UserName.Text.Replace("'", "_") + "','" + DropDownListProgram.SelectedValue + "')"
 
 
 

        If Page.IsValid Then

            Try
 
 

                con.Open()
 

                Dim okayToContinue As Boolean = False

                Dim doCheckStudent As New Data.OleDb.OleDbCommand(sqlCheckStudent, con)
 

                If IsDBNull(doCheckStudent.ExecuteScalar) Then
 

                    SqlCommand.Connection = con

                    SqlCommandPro.Connection = con
 

                    SqlCommand.ExecuteNonQuery()

                    SqlCommandPro.ExecuteNonQuery()

                Else

                    ErrorMessage2.Text = "The Username is already exist!"

                End If
 

            Catch

                ErrorMessage.Text = "Error retrieving data, please try again later!"

            Finally

                con.Close()

            End Try

        Else

            ErrorMessage.Text = "Please complete everything!"
 

        End If

    End Sub

Open in new window

0
 

Author Comment

by:Robertyue
ID: 21817546
I figured out. Thank you anyway!
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click

       
 
 

        Dim con As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\App_data\GLC.mdb"))

        Dim Counter As String = "SELECT COUNT(Username) FROM STUDENTS WHERE Username=@Username"
 
 

        Dim SqlCommand As String = "INSERT INTO Students ( Username, [Password], SchoolID, LastName, FirstName, Email ) Values (@Username,@Password,@SchoolID,@LastName,@FirstName,@Email)"

        Dim SqlCommandPro As String = "INSERT INTO ProgramsStudents (Username, CourseID) Values (@Username,@CourseID)"
 
 
 

        If Page.IsValid Then
 
 

            con.Open()

            Dim Count As New Data.OleDb.OleDbCommand(Counter, con)

            Count.Parameters.AddWithValue("@Username", UserName.Text)
 

            Dim CountValue As Integer

            CountValue = CInt(Count.ExecuteScalar())
 

            Dim Mycommand As New Data.OleDb.OleDbCommand(SqlCommand, con)

            Dim Mycommandd As New Data.OleDb.OleDbCommand(SqlCommandPro, con)
 

            Mycommand.Parameters.AddWithValue("@Username", UserName.Text.Replace("'", "_"))

            Mycommand.Parameters.AddWithValue("@Password", Password.Text.Replace("'", "_"))

            Mycommand.Parameters.AddWithValue("@SchoolID", DropDownListSchool.SelectedValue)

            Mycommand.Parameters.AddWithValue("@LastName", LastName.Text.Replace("'", "_"))

            Mycommand.Parameters.AddWithValue("@FirstName", FirstName.Text.Replace("'", "_"))

            Mycommand.Parameters.AddWithValue("@Email", Email.Text.Replace("'", "_"))
 

            Mycommandd.Parameters.AddWithValue("@Username", UserName.Text.Replace("'", "_"))

            Mycommandd.Parameters.AddWithValue("@CourseID", DropDownListProgram.SelectedValue)
 
 

            If CountValue = 0 Then

                Mycommand.ExecuteNonQuery()

                Mycommandd.ExecuteNonQuery()
 
 

            Else : ErrorMessage2.Text = "User Name is Already Exist!"

            End If

            con.Close()
 
 

        End If
 

    End Sub

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Replacing HTML tags in Textarea/Textbox 5 46
Hovering effect 9 46
Trouble with References... 5 42
Not showing page correctly 3 29
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now