C# Code error with SQL Server stuff

Question

I was using Access database on my application and I switched to SQL Server, but it is not working properly... This is a Login screen, the Connect button click event.

When I connect it just skips and closes the login window with whatever info I put in it.

This is the error I'm getting. It's red at the cmd field, and I don't know why.

https://i.stack.imgur.com/gJ5hm.png

Code:

private void btnconectar_Click(object sender, EventArgs e)
    {
        if (!(empty(boxlogin.Text) && empty(boxsenha.Text)))
        {
        SqlCommand cmd = new SqlCommand("SELECT * from Usuarios where StrCmp(login, '" + boxlogin.Text + "')=0  and StrCmp(senha, '" + boxsenha.Text + "')=0",connection);
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 15; 
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            nome =  reader["nome"].ToString();
            login = reader["login"].ToString();
            senha = reader["senha"].ToString();
            msg("Login realizado com sucesso!\nBem vindo(a), " + nome.Substring(0, nome.IndexOf(" ")),Color.Green, false);
                connection.Close();
                timer4.Start();
        }
        else
        {
            msg("Usuário e/ou senha incorretos!", Color.Red, false);
        }
        }
        else msg("Os campos não podem ficar em branco!", Color.Red, false);
        connection.Close();
    }

Output after execution:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll 'WindowsFormsApplication1.vshost.exe' (CLR v4.0.30319: WindowsFormsApplication1.vshost.exe): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\mscorlib.resources\v4.0_4.0.0.0_pt-BR_b77a5c561934e089\mscorlib.resources.dll'. Module was built without symbols.

Do you get an exception ? If could appear in the Output or the Execution window. — Irwene, Apr 17 '15 at 19:37
I see several problems with the code, but nothing that would cause an exception at that line. It's not database related, as it's not there that the query is executed. What is the exception that you get? — Guffa, Apr 17 '15 at 19:43
also the red color you note in the dubugger for `cmd` happens when the value of the variable changes; not when there's an error — Fred, Apr 17 '15 at 19:50
the msg is a method i made, it works perfectly, and the "empty" returns a string.isemptyorwhitespace, just to make it simpler. — Phiter, Apr 17 '15 at 19:51
Just a suggestion, never select * when using a login query. To be more precise, never query for the password. It should NEVER be included in the data returned by the query (for security reasons) — Sharon Dorot, Apr 17 '15 at 22:31

score 4 · Accepted Answer · answered Apr 17 '15 at 19:56

As you get an SqlException, the problem is not at the line that is marked. It will be in the line with ExecuteReader, where the query is executed.

There is no StrCmp function in T-SQL, use the = operator to compare the strings.

You should use data parameters, the query is wide open for SQL injection attacks when you concatenate the values into the query without escaping them properly.

To require both fields to be filled in, you should use the || operator in the condition, not the && operator.

You are not closing the data reader, and you are closing the connection twice. You should dispose the command and the data reader. The most convenient way to handle that safely is using a using block.

You shouldn't use select * in production code. Select the fields that you want to get from the query.

private void btnconectar_Click(object sender, EventArgs e) {
  if (!(empty(boxlogin.Text) || empty(boxsenha.Text))) {
    using (SqlCommand cmd = new SqlCommand("SELECT nome, login, senha from Usuarios where login = @Login and senha = @Senha", connection)) {
      cmp.Parameters.AddWithValue("@Login", boxlogin.Text);
      cmp.Parameters.AddWithValue("@Senha", boxsenha.Text);
      cmd.CommandType = CommandType.Text;
      cmd.CommandTimeout = 15; 
      connection.Open();
      using (SqlDataReader reader = cmd.ExecuteReader()) {
        if (reader.Read()) {
          nome =  reader["nome"].ToString();
          login = reader["login"].ToString();
          senha = reader["senha"].ToString();
          msg("Login realizado com sucesso!\nBem vindo(a), " + nome.Substring(0, nome.IndexOf(" ")),Color.Green, false);
          timer4.Start();
        } else {
          msg("Usuário e/ou senha incorretos!", Color.Red, false);
        }
      }
    } else {
      msg("Os campos não podem ficar em branco!", Color.Red, false);
    }
  }
  connection.Close();
}

Thank you very much, can you tell me why i should use "using(blablabla)"? — Phiter, Apr 17 '15 at 20:03
@PhiterFernandes in c# `using(blablabla)` automatically calls the Dispose method on an object when it moves out of scope (block completes or an error is thrown). see http://stackoverflow.com/questions/75401/uses-of-using-in-c-sharp for more info — RubberChickenLeader, Apr 17 '15 at 20:28

score 0 · Answer 2 · answered Apr 17 '15 at 19:54

0

I think the problem could be that StrCmp is not valid sql.

In SQL Server you can compare two varchar for equality using =.

answered Apr 17 '15 at 19:54

John Boker

82,559
17
97
130

your solution worked, thanks! But there is a catch: it's not case sensitive, that's why i used STRCMP. the login field on the database is phiter, and i had put in Phiter and it still worked... so, any help? – Phiter Apr 17 '15 at 19:57
http://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare have a look at that question and answer. – John Boker Apr 17 '15 at 19:58
i had seen that yesterday, i tried using at MS Access query and didn't work, that's why i used strcmp, didn't think about trying it on SQL Server. Thanks for helping, very appreciated :D – Phiter Apr 17 '15 at 20:06

C# Code error with SQL Server stuff

2 Answers2