basically when in another page someone chooses a category it redirects him to this page and adds "?category=(category he choose)" to the url, then it checks in this page what's his category and pulls the ids from db where the category is equal to what it chose, then it chooses a random id.

            Uri uri = new Uri(HttpContext.Current.Request.Url.AbsoluteUri);
            string querystring = HttpUtility.ParseQueryString(uri.Query).Get("category");

            SqlCommand cmd = new SqlCommand();
            List<int> idList = new List<int>();  
            SqlConnection con = new SqlConnection(@"Data source");
            // I deleted the Data source but it's in the code
            try
            {

                cmd = new SqlCommand($"SELECT Id FROM Facts WHERE category='{querystring}'", con);
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                int i = 0;
                while ((bool)reader.Read())
                {
                    idList.Add((int)reader.GetValue(0));
                    i  ;
                }
                reader.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            Random rnd = new Random();
            int[] idArray = idList.ToArray();
            int randIndex = rnd.Next(idArray.Length);
            int random = idArray[randIndex];
            string SQL = $"SELECT facts FROM Facts WHERE id='{random}';";
            cmd = new SqlCommand(SQL, con);
            con.Open();
            object scalar = cmd.ExecuteScalar();
            con.Close();

this is the code, sometimes it works and sometimes it throws 'Index was outside the bounds of the array.'

really need an hand here, been working on it for a long time and i have no idea.

CodePudding user response：

It may be necessary to manage the case where there is no result :

...
if (idList.Count > 0)
{
    Random rnd = new Random();
    int[] idArray = idList.ToArray();
    int randIndex = rnd.Next(idArray.Length);
    int random = idArray[randIndex];
    ...

CodePudding user response：

If this fails:

int randIndex = rnd.Next(idArray.Length);
int random = idArray[randIndex];

Then either:

1. idArray is shared and is being swapped between the operations
2. the array is empty

We can rule out 1 because it is a local, not a field; therefore the problem is 2

(I'm not including the third option of Random.Next being broken, since that seems preposterously unlikely)

CodePudding user response：

using some of the ideas and answers brought here I managed to fix it, first of all I removed unused and unnecessary code (thanks to some advices) and debugged the code using System.Diagnostics.Debug.WriteLine(); then I took the entire code that gets the IDs and made a method with it -

List<int> GetData(string querystring)
        {
            // This method receives the URL query parameter of the category and outputs a list of all the fact ids in the category .
            SqlCommand cmd = new SqlCommand();
            List<int> idList = new List<int>();
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RotemCohen\source\repos\firstsite\firstsite\App_Data\db.mdf;Integrated Security=True");
            try
            {
                cmd = new SqlCommand($"SELECT Id FROM Facts WHERE category='{querystring}'", con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    idList.Add((int)reader.GetValue(0));
                    i  ;
                }
                reader.Close();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return idList;
        }

This part gets the facts id's and chooses a random one, then outputs it to HTML:

SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\RotemCohen\source\repos\firstsite\firstsite\App_Data\db.mdf;Integrated Security=True");
Uri uri = new Uri(HttpContext.Current.Request.Url.AbsoluteUri);
string category = HttpUtility.ParseQueryString(uri.Query).Get("category");
List<int> listID = GetData(category);
Random rnd = new Random();
int random = rnd.Next(0, listID.Count);
int randomFactID = listID[random];
string SQLQuery = $"SELECT facts FROM Facts WHERE id='{randomFactID}';";
cmd = new SqlCommand(SQLQuery, con);
con.Open();
object randomFact = cmd.ExecuteScalar();
con.Close();

NOTE: This is vulnerable to SQL Injection attacks, don't use it without parameterizing the SQLQuery variable first! See this to learn about it.