reading from SQL doesn't put value in string list

Question

I am trying to add data from sql in string list but it keeps saynig that my string list = null.

code on the button:

    protected void Button2_Click(object sender, EventArgs e)
        {
            // go back to the menu
        }
        protected void Button3_Click(object sender, EventArgs e)
        {
            // code to display the books by sanctuary
            ListBox1.Items.Clear();
            List<String> s = DBConnectivity.LoadBooksBySanctuary2(3/*int.Parse(DDSanctuary.SelectedValue)*/);

            foreach (var detail in s)
            {
               ListBox1.Items.Add(detail);
            }




        }

code to read from SQL and put into string list. I know my connection works fine because I can add pets

 public static List<String> LoadPetsBySanctuary2(int sID)
        {
            List<String> saName = new List<String>();
            SqlConnection myConnection = GetConnection();
            // string myQuery = "SELECT  Name, categId, cName FROM Book, Category WHERE Book.CategID=Category.ID AND Book.authorId = " + sID;

            string myQuery = "SELECT  Name  FROM pet WHERE sanID = " + saName;

            SqlCommand myCommand = new SqlCommand(myQuery, myConnection);

            try
            {
                myConnection.Open();
                SqlDataReader myReader = myCommand.ExecuteReader();

                while (myReader.Read())
                {
                 //   saName.Add(myReader["Name"].ToString());

                    saName.Add(myReader["Name"].ToString());

                }
                return saName;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception in DBHandler", ex);
                return null;
            }
            finally
            {
                myConnection.Close();
            }
        }

this: `DBConnectivity.LoadBooksBySanctuary2(3/*int.Parse(DDSanctuary.SelectedValue)*/);` likely doesn't return a `List` (so null likely) and there is no `EMERGENCY` ok... — EpicKip, Dec 08 '17 at 13:24
`but it keeps saynig that my string list = null.` Is that **exactly** what it said? What **specifically** did it say? And what caused it (i.e. which line)? — mjwills, Dec 08 '17 at 13:25
You are passing `saName` instead of your `sID` parameter to your query. The exception should have told you that your query is broken. — Filburt, Dec 08 '17 at 13:26
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Dec 08 '17 at 13:27
This would be a perfect time to learn to use the debugger, step through the code, and see precisely what's happening. It's also impossible for us to tell you what's wrong with `LoadBooksBySanctuary2` when you show us the code for `LoadPetsBySanctuary2` instead. Books are not Pets, unless your society is vastly different than mine. — Ken White, Dec 08 '17 at 13:28
@mjwills I agree that OP could use a look there but in no way a duplicate — EpicKip, Dec 08 '17 at 13:28
EpicKip thank you for spotting this mistake. But it wasn't the problem. — Karlos, Dec 08 '17 at 13:44
@mjwills solved problem. Sorry for messy post and code. I tried to configure code to solve it, so it got really mess. thank you for other answers. I'm using this site for first time and I will try better next time :) — Karlos, Dec 08 '17 at 13:47

score 2 · Answer 1 · answered Dec 08 '17 at 13:26

If the list is coming back as null, then you're hitting the catch(Exception ex) block. That is the only place that returns null. Hint: stop swallowing exceptions. The exception will tell you what the problem is.

Also: use parameters - never ever append input like this.

You probably meant to use sId instead of saName.

May I humbly suggest some "Dapper" here:

public static List<String> LoadPetsBySanctuary2(int sID)
{
    return GetConnection().Query<string>(
        "SELECT  Name  FROM pet WHERE sanID = @sID",
        new { sID }).AsList();
}

note: it isn't clear whether GetConnection passes ownership of a connection or not; if it does, you should use using:

public static List<String> LoadPetsBySanctuary2(int sID)
{
    using(var myConnection = GetConnection())
    {
        return myConnection.Query<string>(
            "SELECT  Name  FROM pet WHERE sanID = @sID",
            new { sID }).AsList();
    }
}

reading from SQL doesn't put value in string list

1 Answers1