Server Error in ExecuteSPDataTable: how to write a function that can use store procedures and parameters and return a DataTable?

Question

I'm trying to run the following code, basically using an SQL query that's on my access database, adding parameters to it and getting a datatable from it:

    string spSQL = "spFindFullScheduleEvents";
    ArrayList prmList;
    OleDbParameter prm;
    int userID = (int)Session["UserID"];
    if (userID < 0 || Session["UserID"] == null)
        userID = 0;
    int i = 1;
    prmList = new ArrayList();

    prm = new OleDbParameter("IDUser", OleDbType.Integer);
    prm.Value = userID;
    prmList.Add(prm);

    DataTable dt = DoQueries.ExecuteSPDataTable(spSQL, prmList);

DoQueries Function:

 public static DataTable ExecuteSPDataTable(string strSQL, ArrayList prmList)
{
    DataTable dt = new DataTable();
    OleDbDataAdapter adp = new OleDbDataAdapter(strSQL, strConnection);
    foreach (OleDbParameter prm in prmList)
    {
        adp.InsertCommand.Parameters.Add(prm);
    }
    adp.Fill(dt);
    return dt;
}

but, for whatever reason, I get an "Object reference not set to an instance of an object", when trying to add the parameters, thought it was Session["UserID"] which was causing the problem, but even when I tried putting a regular int value (30), the problem still occures on this line:

  adp.InsertCommand.Parameters.Add(prm);

The SQL command goes as follows:

   SELECT *
   FROM tblFullSchedule
   WHERE IDUser = [@IDUser];

I should also mention that I'm using this for a school project and stuff here are pretty outdated unfortunately, the DoQueries class was made by my teacher, which is why I don't know if it's an issue with her code or I'm doing something wrong. Previously I was executing a similar function called ExecuteDataTable because I didn't need any parameters, which worked perfectly fine.

After finishing writing this down I have a suspicion that ExecuteSPDataTable accepts an SQL command and not a stored procedure, which is why the error occurs. if my suspicion is correct, then I would love help writing a DoQueries function that does accept a store procedure, because I'm an SQL/ OleDB beginner, and I have no idea how to do it.

Answer 1

So, I found a way to fix it - with this outdated code, if someone finds themselves in the same situation.
I can't believe I haven't figured this out - but the solution was to change InsertCommand to SelectCommand..

public static DataTable ExecuteSPDataTable(string strSQL, ArrayList prmList)
{
    DataTable dt = new DataTable();
    OleDbDataAdapter adp = new OleDbDataAdapter(strSQL, strConnection);

    foreach (OleDbParameter prm in prmList)
    {
        adp.SelectCommand.Parameters.Add(prm);
    }
    adp.Fill(dt);
    return dt;
}

and change the SQL string to:

 string strSQL = "SELECT * FROM tblFullSchedule WHERE IDUser = @IDUser";

Instead of the query name.... Now, I seriously gotta update this code..