2

I have a database(username(key),Fullname,Pass), I want to search for the username and write out the Fullname for this username in a console. I have these:

static DataTable dt = new DataTable();
static SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""path\login.mdf"";Integrated Security=True");

con.Open();
string username="mate";
    SqlDataAdapter sda = new SqlDataAdapter("SELECT Nev FROM Login WHERE username='" + username+ "'", con);
        sda.Fill(dt);
        string fullname;
        if (dt.Rows[0][0].ToString() == "1")then?

"Nev" means Full name

I want it to write the Fullname into the "fullname" string, then just:

Console.Writeline("Welcome {0}",fullname);

Can you help me with this code?
I just don't know how to continue my code. Users can login and register to the app, and when they login i want to greet them like "Hello FullName" when they register their username, fullname and password is stored, and they login with username & pass. I just don't know how to find the Fullname if i know the username. (I'm a beginner in SQL)

Improve this question
11
  • You don't seem to have opened a connection. Commented Oct 6, 2017 at 15:56
  • Just missed a line, there's a con.open @chaosifier Commented Oct 6, 2017 at 15:58
  • 1
    Why are you using DataAdapter? you just want to get ONE username, yes? Then use ExecuteScalar (it will directly return the string you need, and it's much faster) Commented Oct 6, 2017 at 15:59
  • 2
    You should not concatenate a SQL statement like that especially if the value you are concatenating with (username) comes from user input it leaves you open to SQL injection. you should use parameters. An in the case you are looking for a single distinct value you can use ExecteScalar. There are a lot of examples of this on SO Commented Oct 6, 2017 at 16:04
  • 1
    one example: stackoverflow.com/questions/542510/…. Without parameters someone could literally drop all of your data, delete users, delete databases, and worse Commented Oct 6, 2017 at 16:06

2 Answers 2

Reset to default
2

I think this is what you're after:

string fullname = null;
using(var con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=""path\login.mdf"";Integrated Security=True")) {
    string username = "mate";
    SqlCommand cmd = new SqlCommand("SELECT Nev FROM Login WHERE username=@Username", con);
    cmd.Parameters.AddWithValue("@Username", username);

    con.open();
    fullname = cmd.ExecuteScalar()?.ToString();
}

if (fullname == null) {
    Console.WriteLine("The username could not be found!");
}

The using ensures that the connection is cleaned up once the block is exited. Also, you should use Parameters to be more resilient to SQL injection.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

System.TypeInitializationException was unhandled Message: An unhandled exception of type 'System.TypeInitializationException' occurred in mscorlib.dll - app goes to break mode"
Are you sure your connection string ("Data Source=(LocalDB)..." is correct?
Now it is. But still " Object reference not set to any object instance."
@Skickpause Are you absolutely certain that there is a username 'mate' in your database? To me it sounds like the query isn't returning any results, so cmd.ExecuteScalar() is coming back null.
YES. YOU'RE AWESOME :D
|
0

For your specific problem, try this code:

if(dt.Rows.Count > 0)
      fullname = dt.Rows[0]["Nev"].ToString();

But here's a better and complete solution to your problem:

string userName = "mate";
string fullName = string.Empty;

string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=path\login.mdf;Initial Catalog=Login;Integrated Security=True";
string query = "SELECT Nev FROM Login WHERE username=@UserName;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
     SqlCommand command = new SqlCommand(query, connection);
     command.Parameters.Add("@UserName", SqlDbType.NVarChar);
     command.Parameters["@UserName"].Value = userName;

     try
     {
         connection.Open();
         fullName = command.ExecuteScalar().ToString();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
}

Console.WriteLine("Hello {0}!", fullName);
Improve this answer

9 Comments

Object reference not set to any object instance. "Hello !'
What connection string have you used? It seems that you've been using an incorrect connection string.
No, it can't be incorrect. I checked it a thousand times
Are you sure there's data in the table?
imgur.com/a/qfDtL I am 1000% sure. or is it a problem that the database is in the other project's folder? But the connection string is correct
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.