ASP.net Visual Studio 2013 C# user already exists

0
votes

I have this piece of code to test in case the user already exists in the database

if (IsPostBack)
{
    SqlConnection conn = new SqlConnection(
         ConfigurationManager.ConnectionStrings["AssignmentDBConnectionString"]
         .ConnectionString);
    conn.Open();

    //selects count from userdata and checks if username exists in the database
    string checkUser = "select count(*) from [AsTable] where Username ='" 
         + TextBoxUsername.Text + "'";

    SqlCommand com = new SqlCommand(checkUser, conn);
    com.ExecuteNonQuery();
    int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
    if (temp > 0)
    {
        Response.Write("User Already Exists");
    }
    conn.Close();
}

However when I try to register an already existing user it doesn't display the user already exists message and throws this error

InvalidOperationException was unhandled by user code An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code
Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

And this is the stack trace

[InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.]

System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +5356096 System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +146 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +16 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) +94 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +110 System.Data.SqlClient.SqlConnection.Open() +96 Registration.Page_Load(Object sender, EventArgs e) in c:\Users\Michalis\Documents\Visual Studio 2013\WebSites\Assignment - ASP\Registration.aspx.cs:40 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51 System.Web.UI.Control.OnLoad(EventArgs e) +92 System.Web.UI.Control.LoadRecursive() +54 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

3
c#sql-servervisual-studio-2013ado.net
Please post full stacktrace! – Arindam Nayak
This code is bad in so many ways... Please, please, please read up on: SqlParameter to prevent SQL injection (msdn.microsoft.com/en-us/library/…), on casting to remove your need for Convert.ToInt32(.ToString) (fxcopcontrib.codeplex.com/…) – jessehouwing
I suspect you need to replace [AsTable] with the actual name of the table that holds the users – jessehouwing
AsTable is the table name that holds the users!! I have edited the post and included the whole error log that appears – michalis

3 Answers

1
votes

Try this:

 if (IsPostBack)
 {
 int result = 0;
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssignmentDBConnectionString"].ConnectionString);
     conn.Open();

//selects count from userdata and checks if username exists in the database
string checkUser = "select count(*) from [AsTable] where Username = @username";

SqlCommand com = new SqlCommand(checkUser, conn);
com.Parameters.AddWithValue("@username", TextBoxUsername.Text);
result = (int)com.ExecuteScalar()
if (result > 0)
{
    Response.Write("User Already Exists");
}
conn.Close();
}
0
votes

Please use return; after the check if it return exists thus it will stop executing the rest of your code.. good luck.

0
votes

Found It!! I declared temp above the page_load code and modified it like this

int temp = 0;

protected void Page_Load(object sender, EventArgs e)
{
    if (IsPostBack)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssignmentDBConnectionString"].ConnectionString);
        conn.Open();
        string checkUser = "select count(*) from [AsTable] where Username ='" + TextBoxUsername.Text + "'";
        SqlCommand com = new SqlCommand(checkUser, conn);
        com.ExecuteNonQuery();
        temp = Convert.ToInt32(com.ExecuteScalar().ToString());
        if (temp != 0)
        {
            Response.Write("User Already Exists");
        }
        conn.Close();
    }
}
 .
 .

Code that follows!!!