0

I have a problem with pulling data from a SQL database.

I simply have a table with only one entry (ID) 1 (user) admin (password) 123456. When I am using SELECT * FROM users, my Data-table contains that entry, but when I am using SELECT * FROM users WHERE user = 'admin' and password = '123456', my resulting Data-table is empty. Both fields are varchar(50) in the database.

This is how my code looks right now in order to be able to validate my credentials

        connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=C:\USERS\SAVIC\ONEDRIVE\DOCUMENTE\USERSDATABASE.MDF;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;Authentication='Active Directory Integrated';ApplicationIntent=ReadWrite;MultiSubnetFailover=False");

        SqlDataAdapter adapter = new SqlDataAdapter("Select * from users", connection);
        DataTable dt = new DataTable();

        adapter.Fill(dt);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if(dt.Rows[i][1].ToString() == userBox.Text && dt.Rows[i][2].ToString() == pwdBox.Text)
            {
                this.Hide();
                MainMenu main = new MainMenu(this);
                main.Show();
            } else
            {
                MessageBox.Show("Wrong username or password!", "Wrong credentials", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
            }
        }

I expect the output of SELECT * FROM users WHERE user = 'admin' and password = '123456' to be the actual entry matching those conditions. All values from the database are striped of white spaces and the field names match with those used in the query.

Maria Nazari
  • 660
  • 1
  • 9
  • 27
Gahum
  • 1
  • 1
  • Common problems *might* include case sensitivity and trailing spaces. Try running the query with just one filter (e.g. password = '1234' or id = 'admin'). Also, be careful, about reserved words. On some databases, the term "user" is a keyword that returns the currently logged in user name (although if that were true in your scenario, i woud hope that you would get a syntax error). This can limit portability. – GMc May 20 '19 at 22:24
  • 1
    if you run that query direct on the db give you any result? – Juan Carlos Oropeza May 20 '19 at 22:27
  • Solved the problem by replacing user with [user]. So, as GMc stated, user has to be a key word in this case. Thanks :) – Gahum May 20 '19 at 22:29
  • Good that you got to the bottom of it - but it would probably have been much easier to solve if you had removed the noise of the C# code and run the query directly as @JuanCarlosOropeza suggested. That should be the first step in your trouble shooting. – TomC May 21 '19 at 00:33
  • btw dont save password as plain text. https://stackoverflow.com/questions/1197417/why-are-plain-text-passwords-bad-and-how-do-i-convince-my-boss-that-his-treasur – Juan Carlos Oropeza May 21 '19 at 04:04

0 Answers0