Solved

How to check username availability?

Posted on 2008-06-17
6
818 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
Comment Utility
Plus, it says my insert statement is wrong...
0
 

Author Comment

by:Robertyue
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Robertyue
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Combine two columns 3 37
Duplicate a row 2 29
Problem to picture file 3 38
File Upload Control on a ASP.NET Overlay Page 1 19
AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

15 Experts available now in Live!

Get 1:1 Help Now