[college_trained]

Hey all,

I'm having an issue with a piece of code that has been continuously perplexing me and I'm finally breaking down and asking for a bit of help. I am trying to create an ASP.NET application with C# as the code behind language. (Please keep in mind that I am fairly new to ASP.NET and somewhat new to C#.) I am trying to code the Authentication event for the Login control and am getting hung up. In my code below, I am using bits of sample code found various places on the web and tweaked it for my application. Where I am getting hung up is the

object obj = m_Command.ExecuteScalar();

line. Whenever I run the code, it gets to this point and fails. I have added the Message Boxes purely for debugging purposes but cannot figure out why that line is failing.

I am running this on Windows 7 x64, Microsoft SQL Server 2008 R2, Visual Studio 2010.

Any helpful thoughts would be greatly appreciated.
Here is the code:

private bool DBValidate(string uName, string pWord)
        {
            MessageBox.Show("Hello");
            //bool bflag = false;
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            MessageBox.Show("Connection String: " + connString);    
            //"Server=(local)\\Development;Database=InventoryTracker;Uid=sa;Pwd=trinity;";
            string strSQL = "SELECT COUNT(*) FROM tblUsers WHERE (tblUsers.username = '" + uName + "') AND (tblUsers.password = " + pWord + ")";
            MessageBox.Show(strSQL);
            SqlConnection m_conn = new SqlConnection(connString);
            SqlCommand m_Command = new SqlCommand(strSQL);
            try
            {
                //m_Command.CommandType = System.Data.CommandType.Text;
                m_conn.Open();
                //m_Command.CommandType = CommandType.Text;
                MessageBox.Show("String");
                object obj = m_Command.ExecuteScalar();
                MessageBox.Show("obj =" + obj);
                if (obj.Equals('0'))
                    return false;
                else
                    return true;
            }
            catch (Exception ex)
            {
                return false;
            }
	    }

My complete web.config looks like this:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="Connection" connectionString="Data Source=localhost\DEVELOPMENT;Initial Catalog=InventoryTracker;User ID=sa;Password=trinity"/>
  </connectionStrings>
  <system.web>
  <authentication mode="Forms">
      <forms loginUrl="login.aspx"/>
    </authentication>
    <authorization>
      <allow users="*"/>
      <deny users="?"/>
    </authorization>
    <compilation debug="true">
      <assemblies>
        <add assembly="System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.ServiceModel.Activation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Web.ApplicationServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Web.Services, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Drawing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.ServiceModel.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.Data.Services.Client, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Data.Services.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.Web.DynamicData, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.ComponentModel.DataAnnotations, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.Web.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Xml.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
      </assemblies>
    </compilation>
  </system.web>
  <system.webServer>
    <directoryBrowse enabled="true"/>
  </system.webServer>
</configuration>

It only looks long and complicated and I'm really hoping it's something easy I may have overlooked.

[racidon]

I'm making an assumption here so sorry if I'm incorrect as MSDN doesn't exactly say whether SqlConnection is compatible with anything in paticular. My assumption is that it's only compatible with Microsoft's SQL Database.

So my first question is, what kind of SQL Database Process are you using?

My next question is does this type of SQL Database Process require you to end your SQL queries with a semi-colon? Since your's does not contain one:

string strSQL = "SELECT COUNT(*) FROM tblUsers WHERE (tblUsers.username = '" + uName + "') AND (tblUsers.password = " + pWord + ")";

Another question is, have you tried the following:

try
{
......Insert problem code here.....
}
catch(Exception ex){MessageBox.show(ex.Message);}

This is purely for debugging purposes so be sure to remove it after coding as it can be very annoying.

Finally does you SQL Database Process allow you to see when a user has logged in to the database? This will tell you if your SQLConnection is connecting when the attempt is being made.

[RexGrammer]

Here's a walkthrough on creating a web site with membership and login controls:

Walkthrough: Creating a Web Site with Membership and User Login (MSDN Walkthrough)

The example uses SQL Express but will do with any database.

You could also use the SqlCommandBuilder. Some info on that:

SqlCommandBuilder Class (MSDN Article)

Another thing to look at is the SqlCommand. Some info on that:

SqlCommand Class (MSDN Article)

This post has been edited by RexGrammer: 15 December 2011 - 06:06 AM

[college_trained]

Thanks racidon and RexGrammer,

I will take a look at all of the options you provided after I get home from work today and will let you know.

[college_trained]

Success! Sure enough it was something I overlooked. Using racidon's suggestion, I used a try catch block around just that string and received an error that I forgot to add the connection parameter to the SqlCommand as the 2nd argument. Thank you both for your suggestions.