Validating a User Imput with SQL Database (Login)

[Shannanigin]

OK this is what I want to do:

1. Log the User in by checking with a SQL Database with a table that has user name and password. Also that table is linked to another table with all there personal information
2. After they login, they have to enter information into the field and send it to the database or they can get presetsfrom a database and and edit them if need be. From there they will send there specific information to a diffrent table which will contain there data.
   

A program like this has alreadh been created, its called FS ACARS that program also has some other cool functions, that I dont need.

The problem I am having trouble with is when the user enters there user name and password into 2 different text boxes, how do I check that information with the database?

So an example:

User name [ txtbox1 ] Password [ txtbox2 ]

I need to search the database to find that txtbox1.text is a valid user name. and if so, does txtbox2.text = the corresponding password?

I hope this is enough detail!

Thanks ahead of time!!!!

[Hamrick]

Send the text of those two text boxes as arguments to a stored procedure. The stored procedure should check if the username exists and the password matches. Something like this I guess.

create procedure ValidateUserLogin
  @UserName varchar(30)
  , @Password varchar(30)
as
begin
  if exists (select * from UsersTable as ut
    where ut.UserName = @UserName AND ut.Password = @Password)
    select 1;
  else
    select 0;
end

In your C# program you can run it like this.

private bool IsValidatedUser( string username, string password ) {
  try {
    bool rv = false;

    using ( SqlConnection con = new SqlConnection( connectionString ) ) {
      using ( SqlCommand cmd = new SqlCommand() ) {
        con.Open();

        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "ValidateUserLogin";

        cmd.Parameters.Add( "@UserName", SqlDbType.VarChar, 30 ).Value = username;
        cmd.Parameters.Add( "@Password", SqlDbType.VarChar, 30 ).Value = password;

        rv = Convert.ToBoolean( cmd.ExecuteScalar() );

        con.Close();
      }
    }

    return rv;
  } catch ( Exception ex ) {
    // Log errors
    throw;
  }
}

[Shannanigin]

Error 1 } expected 19 10 Costal
Error 2 Type or namespace definition, or end-of-file expected 68 5 Costal

And I am kinda confused on a couple of things:

1. Where do I put the C# code... would it go into the button_1 click event handler. OR should that be its own class??

How does it grab the data from the text boxes to validate it?

[Hamrick]

Where do I put the C# code...

I don't know. That's not even code for your project, it's just something to give you ideas on how you might do it for yourself. I can help you place it if you describe how your project is laid out...

[Shannanigin]

I would really appreciate it... do you have an MSN or AIM or ICQ?? That way we can meet up sometime and you could help me? Or we can do it all here up to you!

[Hamrick]

It's easier to do it all here.

[Shannanigin]

Ok here goes:

I have a button with two text boxes above it on its own form.

The button says OK. after the user enters his/her username & Password they click thte ok button and it validates them.

On the database side I have a .mdf file with 1 table in it that has three columns. UserName Password and userID. The UserID is for me its an identity column so i can keep track of users.

I also have the procedure you gave me above.

Where do i begin?

I know i need to get the data from the textboxes. and put them into variables.

then send them to the procedure to check in the database

then the procedure needs to send back to code. and change a bool variable to true... or give an error.

Thats what I know.. am I missing anything?

[Hamrick]

I guess you could just put the method in the form class. Then you call it in the button's click event.

void buttonOK_Click( object sender, EventArgs e ) {
  bool isValid = this.IsValidatedUser(
    this.textBoxUserName.Text
    , this.textBoxPassword.Text );

  if ( isValid ) {
    // Do whatever for a valid login
  } else {
    // Show an error for an invalid login
  }
}

[Shannanigin]

Error 1 The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?) 17 24 Costal
Error 2 The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?) 17 48 Costal
Error 3 The name 'connectionString' does not exist in the current context 17 62 Costal
Error 4 The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?) 20 28 Costal
Error 5 The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?) 20 49 Costal
Error 6 The name 'CommandType' does not exist in the current context 27 43 Costal
Error 7 The name 'SqlDbType' does not exist in the current context 31 57 Costal
Error 8 The name 'SqlDbType' does not exist in the current context 33 57 Costal

I get all these errors?

What I did:

I made a new class file and put that method into the class. Is this what you meant or did you want me to put it into the program.cs file or put it into the form1.cs file?

[Hamrick]

You need to add the SqlClient reference to your project and add a using statement wherever you use those classes.

using System.Data.SqlClient;

You need to store the connection string somewhere, that's what connectionString is; a variable in your application that stores a SQL connection string.

[Shannanigin]

Ok I got the include statment. Now how do I get thte connection string. I know it has to go into the app.config. but how do I make it a variable? I have the string as well.

[Hamrick]

It doesn't have to go in the app.config. You can put it anywhere you want because _you're_ the programmer. :) Try adding it as a public property to the main program class for an easy way to get access all over the application.

namespace AwesomeApplication {
  public class Program {
    private string _connectionString = "the string goes here";
    public string ConnectionString {
      get { return _connectionString; }
      set { _connectionString = value; }
    }

    [STAThread]
    static void Main() {
      Application.EnableVisualStyles();
      Application.SetCompatibleTextRenderingDefault( false );
      Application.Run( new MainForm() );
    }
  }
}

[Shannanigin]

I swear I have responded like 3 times and each time it doesnt show up. So lets try it again. I go the include statemate in. That is fine now.

So my next question is. How to get the COnnectionString as a variable. I have the string now how do i use it?

[Hamrick]

The connection string is just a string variable. You make it the same way you make any other string. You pass it around the same way you would any other string. There's no secret connection string magic going on. ;) I don't think I understand the problem.

[Shannanigin]

Sorry about the double response i didn't realize that the post had gone to two pages!!!

But on too it. let me just double check. The connection string is provided correct.

Let me explain. When I click on properties, the connection string looks like this

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Drummer Boy\My Documents\Coastal.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

So I put that into the " " and I get a whole slew of errors!

How do I make the string a mere string!

[Hamrick]

So I put that into the " " and I get a whole slew of errors!

Right, because you have backslashes in the string. C# sees that and doesn't recognize them as escape characters, so it throws up compilation errors. To get a literal backslash you escape the character with a backslash or use a verbatim string. Escaping looks like this.

"Data Source=.\\SQLEXPRESS;AttachDbFilename="C:\\Documents and Settings\\Drummer Boy\\My Documents\\Coastal.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"

A verbatim string looks like this.

@"Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Drummer Boy\My Documents\Coastal.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"

[pankajkundlia]

@Hamrick
thanks a lot ur code for c# cleared all my doubts

[pradeepsimha]

Try like this,

using System.Data.SqlClient;

public bool validate(String uname,String pass) 
{
        using(SqlConnection conn=new SqlConnection(connStr))
        {
                   string str="select * from users where username=@uname and password=@pass";
                   conn.open();
                   SqlCommand cmd=new SqlCommand(str,conn);
                   cmd.Parameters.AddWithValue("@uname",uname);
                   cmd.Parameters.AddWithValue("@pass",pass);
                   SqlDataReader reader=cmd.ExecuteReader();
                   if(reader.hasRows==true) 
                           return true;
                   else
                           return false;

         }
}

so using tat u can verify that user is valid user or not, if it returns true he is valid user else invalid user.
This is basic validation, if u need further u can ask me.

[khusiaaaan]

I tried out this code in my projects but it is giving following errors :

cannot find login

login is my database name where I am storing username and passwords in a table named login_tb

i have substituted corresponding names in the field but still getting this error .

please help