BerGEX BerGEX - 3 months ago 24
ASP.NET (C#) Question

Login form with SQL Server database in asp.net (C#)

I am trying to create a login system in ASP.net. It shows no error, but still doesn't work.

This is the code:

SqlConnection connectionstring = new SqlConnection("Server=.\\SQLEXPRESS;Database=TestDB;User Id=AMS; Password = password; ");

public void LoadData() //<-- call this when the button is pressed
{
try
{
SqlCommand SelectCommand = new SqlCommand("SELECT `Username`, `Password` FROM `LoginData` WHERE `Username` = '" + TextBox1.Text + "' AND `Password` = '" + TextBox2.Text + "'", connectionstring);

SqlDataReader myReader;
connectionstring.Open();

myReader = SelectCommand.ExecuteReader();
int count = 0;

while (myReader.Read())
{
count = count + 1;
}

if (count == 1)
{
string script = "alert(\"Login successful!\");";
ScriptManager.RegisterStartupScript(this, GetType(),"ServerControlScript", script, true);
connectionstring.Close();
}
else if (count == 0)
{
string script = "alert(\"Login Failed!\");";
ScriptManager.RegisterStartupScript(this, GetType(),"ServerControlScript", script, true);
connectionstring.Close();
}
else
{
}
}
catch (Exception ex)
{
connectionstring.Close();
}
}


It's simply not working. And I don't know why.

This is the code for the button in CSS

#Button1{

background-color: #0066CC;
font-family: 'Segoe UI',Tahoma,Arial,Helvetica,sans-serif;
margin-right: 10px;
cursor: pointer;
color: #fff;
padding: 10px 15px;
font-size: 14px;
display: inline-block;
margin-bottom: 18px;
z-index: 1;

position: absolute;
top: 203px;
left: 383px;
width: 130px;
}


and this is in the asp.net

<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />


It shows no error, or anything, just refreshes the page when I click the button.
What is the problem? I am unable to find it. Maybe is in the SQL connection string?

EDIT: suggested by Steve to remove the try/catch

This is the error I get


An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL

Answer

After fixing the error caused by the invalid configuration of Sql Server Express (probably the required protocol is not enabled (TCP) you should also note that Sql Server doesn't use the backticks around the field names and tablenames. If it is necessary to have a distinction between field names and reserved words Sql Server uses the square brackets.

Said that your code should be rewritten to use parameters and not a string concatenation. String concatenation is well know as a wrong way to build queries because it is easy to introduce bugs and allowing Sql Injection attacks

public void LoadData() 
{

    string cmdText = @"SELECT 1 FROM LoginData 
                       WHERE Username = @name AND AND Password = @pass";

    using(SqlConnection cnn = new SqlConnection("Server=.\\SQLEXPRESS;Database=TestDB;Trusted_Connection=True;"))
    using(SqlCommand SelectCommand = new SqlCommand(cmdText, cnn))
    {
       SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = TextBox1.Text;
       SelectCommand.Parameters.Add("@pass", SqlDbType.NVarChar).Value = TextBox2.Text;
       cnn.Open();
       using(myReader = SelectCommand.ExecuteReader())
       {
           // No need to read anything. If your reader has rows then
           // the user and its password exists 
           // (you don't allow two users have the same name right?)
           if(myReader.HasRows)
           {
             string script = "alert(\"Login successful!\");";
             ScriptManager.RegisterStartupScript(this, GetType(),"ServerControlScript", script, true);
           }
           else
           {
              string script = "alert(\"Login Failed!\");";
              ScriptManager.RegisterStartupScript(this, GetType(),"ServerControlScript", script, true);
           }
      }         
   }
}

Notice that I have used the using statement around the disposable objects to be sure that these objects are closed and disposed also in case of exceptions.

Another thing to do is to use TrustedConnection=True to use Windows Authentication against your database server and not needing a specific user to log in. Of course this will change in case you need to deploy your program to a customer pc and depends on the customer requirements.