SQL command returning null for value

Question

I am previously only familiar with Linq and the like for data access. I am working on something now that requires me to use actual SQL commands on the back end to return a single value. My code compiles and runs, however it is returning null for a value that I know should be returning something besides an empty string...

Is my structure off on this? Or is something else missing?

Below is my code:

  internal string GetSexDescription(string sex, int id_merchant)
    {
        string newSex = "";

        var builder = new ConnectionStringHelper();
        var connString = builder.getCasinoDBString(id_merchant);

        using (SqlConnection conn = new SqlConnection(connString))
        {
            string sql = "SELECT Description FROM person_gender_lookup WHERE ID = @sex";

            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.Parameters.Add("@Sex", SqlDbType.VarChar).Value = sex;

                newSex = cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return newSex;
        }
    }

Here is a picture of the result set of the table:

This looks good, have u tried running the query in the DBMS? — Vidmantas Blazevicius, Jun 11 '19 at 15:30
@OlivierJacot-Descombes but "sex" is the actual parameter name being passed into this function — Jacked_Nerd, Jun 11 '19 at 15:45
I am not speaking of the C# method parameter but of the SQL parameter `@sex` which is completely unrelated to C# variable names. You add the command parameter as `@Sex` but specify `@sex` in the sql command text. According to the SO question [Case sensitive variables in SQL Server](https://stackoverflow.com/questions/6559254/case-sensitive-variables-in-sql-server), this [answer](https://stackoverflow.com/a/6559516/880990) says: *"The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance."*. — Olivier Jacot-Descombes, Jun 11 '19 at 15:50
Can you show the table definition for the person_gender_lookup table? You are comparing the ID from that table to a text string, but generally ID fields are unique Integer columns (probably auto incrementing), so, I'd like to confirm the data type of the ID field. — Michael Dougan, Jun 11 '19 at 15:52
@OlivierJacot-Descombes Yes, i saw that. Thought it was capitalized as well.. I changed it to accommodate and still returning a null value. — Jacked_Nerd, Jun 11 '19 at 15:52
@MichaelDougan yes, unfortunately the ID is being passed from a data within a model that I did not create.. It was poorly structured this way. I can't change it without affecting too many other things. ID should be a "1". — Jacked_Nerd, Jun 11 '19 at 15:53
How is the column declared? As `ID int` or as `ID varchar(10)` or something? — Olivier Jacot-Descombes, Jun 11 '19 at 15:55
Did you check the console for an exception message? You're not opening the connection, which will lead to an exception being thrown. — Joshua Robinson, Jun 11 '19 at 15:55
Can you verify that you are getting a valid connect string, and that the table in that database has data that matches your test criteria? — Michael Dougan, Jun 11 '19 at 15:56
@MichaelDougan table does have data. Let me check the conn string — Jacked_Nerd, Jun 11 '19 at 15:56

score 2 · Answer 1 · answered Jun 11 '19 at 16:10

Open the connection.

internal string GetSexDescription(string sex, int id_merchant)
{
   string newSex = "";

   var builder = new ConnectionStringHelper();
   var connString = builder.getCasinoDBString(id_merchant);

   using (SqlConnection conn = new SqlConnection(connString))
   {
      conn.Open(); //<- This line here.
      string sql = "SELECT Description FROM person_gender_lookup WHERE ID = @sex";

      SqlCommand cmd = new SqlCommand(sql, conn);
      try
      {
         cmd.Parameters.Add("@Sex", SqlDbType.VarChar).Value = sex;

         newSex = cmd.ExecuteScalar().ToString();
      }
      catch(Exception ex)
      {
         Console.WriteLine(ex.Message);
      }

      return newSex;
   }
}

cmd.ExecuteScalar() is probably throwing an InvalidOperationException because you haven't opened the connection. The exception is being caught, outputted to the console, then the initial value of newSex is begin returned since the call to ExecuteScalar threw.

You are correct. Also had to close it after I assigned the variable to the output. — Jacked_Nerd, Jun 11 '19 at 16:14

score 1 · Answer 2 · answered Jun 11 '19 at 16:23

1

ID is a int or varchar? If is int use:

cmd.Parameters.Add("@sex", SqlDbType.Int).Value = sex;

instead of:

cmd.Parameters.Add("@Sex", SqlDbType.VarChar).Value = sex;

P.S. Query parameters and parameter add into cmd.Parameters is case sensitive.

Write

@sex

instead of

@Sex

answered Jun 11 '19 at 16:23

L.Zoffoli

129
3
10

ID in the table is an int... however how the rest of the project is set up, it was wrongfully passing a string for ID instead of an int. If I were to re-structure, I would have to undo a lot of things already in motion unfortunately.. – Jacked_Nerd Jun 11 '19 at 16:25
You also miss conn.Open(); – L.Zoffoli Jun 11 '19 at 16:36

score 0 · Answer 3 · answered Jun 11 '19 at 16:13

Figured it out. Had to open the cmd and close it AFTER I set the newSex variable to the value being pulled.

   internal string GetSexDescription(string sex, int id_merchant)
    {
        string newSex = "";

        var builder = new ConnectionStringHelper();
        var connString = builder.getCasinoDBString(id_merchant);

        DataSet ds = new DataSet();

        using (SqlDataAdapter adapter = new SqlDataAdapter())
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sql = "SELECT Description FROM person_gender_lookup WHERE ID = @Sex";

                SqlCommand cmd = new SqlCommand(sql, conn);
                try
                {
                    conn.Open();
                    cmd.Connection = conn;
                    adapter.SelectCommand = cmd;
                    cmd.Parameters.Add("@Sex", SqlDbType.VarChar).Value = sex;
                    adapter.Fill(ds);

                    newSex = cmd.ExecuteScalar().ToString();

                    conn.Close();

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

                return newSex;
            }
        }

    }

What are SqlDataAdapter and DataSet doing here? They are not necessary. — Olivier Jacot-Descombes, Jun 11 '19 at 19:05

score -1 · Answer 4 · edited Jun 11 '19 at 15:31

-1

Try this:

internal string GetSexDescription(string sex, int id_merchant)
{
    string newSex = "";

    var builder = new ConnectionStringHelper();
    var connString = builder.getCasinoDBString(id_merchant);

    using (SqlConnection conn = new SqlConnection(connString))
    {
        string sql = "SELECT Description FROM person_gender_lookup WHERE ID" +  sex;;

        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {


            newSex = cmd.ExecuteScalar().ToString();
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        return newSex;
    }
}

edited Jun 11 '19 at 15:31

Olivier Jacot-Descombes

104,806
13
138
188

answered Jun 11 '19 at 15:30

John Meek

173
1
9

3

Don't do this. See: [SQL Injection (w3schools)](https://www.w3schools.com/sql/sql_injection.asp), [SQL injection (Wikipedia)](https://en.wikipedia.org/wiki/SQL_injection). – Olivier Jacot-Descombes Jun 11 '19 at 15:34
2

Don't even think the SQL string is valid anyway. – spodger Jun 11 '19 at 15:49

SQL command returning null for value

4 Answers4