1

First of all I have to take obvious things out of the way; I have searched for the same problem, there is an answer to this question on StackOverFlow but it didn't worked for me.

Problem: I am getting an error

SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size

on the line with this statement 

cmd.Prepare();

DB schema:

enter image description here

Code:

//alreadyAnsweredQues is Global -- List<Int32>
var parameters = new string[alreadyAnsweredQues.Count];
var cmd = new SqlCommand();

for (int i = 0; i < alreadyAnsweredQues.Count; i++)
{
    parameters[i] = string.Format("@qid{0}", i);

    cmd.Parameters.Add(parameters[i].ToString(), SqlDbType.Int).Value = alreadyAnsweredQues[i];
}

cmd.Parameters.Add("@cid", SqlDbType.Int).Value = Convert.ToInt32(Session["c_id"].ToString());

cmd.CommandText = string.Format("SELECT TOP 1 * FROM questions WHERE c_id = @cid AND q_id NOT IN ({0}) ORDER BY NEWID() ;", string.Join(", ", parameters));
System.Diagnostics.Debug.WriteLine(string.Format("SELECT TOP 1 * FROM questions WHERE c_id = @cid AND q_id NOT IN ({0}) ORDER BY NEWID() ;", string.Join(", ", parameters)));

cmd.Connection = new SqlConnection(ConnectionClass.constr);
cmd.Connection.Open();

cmd.Prepare();

SqlDataReader sdr = cmd.ExecuteReader();
zsubzwary
  • 1,196
  • 1
  • 14
  • 22

3 Answers3

1
for (int i = 0; i < alreadyAnsweredQues.Count; i++)
{
    parameters[i] = string.Format("@qid{0}", i);

    cmd.Parameters.Add(parameters[i].ToString(), SqlDbType.Int).Value = alreadyAnsweredQues[i];
}

cmd.Parameters.Add("@cid", SqlDbType.Int).Value = Convert.ToInt32(Session["c_id"].ToString());

should be changed to:

cmd.Parameters.Add("@cid", SqlDbType.Int).Value = Convert.ToInt32(Session["c_id"].ToString());

for (int i = 0; i < alreadyAnsweredQues.Count; i++)
{
    parameters[i] = string.Format("@qid{0}", i);
    cmd.Parameters.Add(parameters[i].ToString(), SqlDbType.Int).Value = alreadyAnsweredQues[i];
}

There is no need to specify a Size for an int since it has a fixed (4 byte) size.

I would also recommend you comment out:

cmd.Prepare();

since there is minimal benefit:

In SQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server reuses execution plans

mjwills
  • 23,389
  • 6
  • 40
  • 63
0

You need to set the SqlParameter.Size property on each parameter object.

SoronelHaetir
  • 14,104
  • 1
  • 12
  • 23
-1

As @SoronelHaetir asked in his answer that

You need to set the SqlParameter.Size property on each parameter object.

After that the problem was that I didn't knew the size of the integer that was going to database, so what I did was that I put System.Int32.MaxValue and it worked fine.

Code: 

var parameters = new string[alreadyAnsweredQues.Count];
var cmd = new SqlCommand();
for (int i = 0; i < alreadyAnsweredQues.Count; i++)
{
    parameters[i] = string.Format("@qid{0}", i);


    cmd.Parameters.Add(parameters[i].ToString(), SqlDbType.Int,System.Int32.MaxValue).Value = alreadyAnsweredQues[i];
}


cmd.Parameters.Add("@cid", SqlDbType.Int, System.Int32.MaxValue).Value = Convert.ToInt32(Session["c_id"].ToString());



cmd.CommandText = string.Format("SELECT TOP 1 * FROM questions WHERE c_id = @cid AND q_id NOT IN ({0}) ORDER BY NEWID() ;", string.Join(", ", parameters));
System.Diagnostics.Debug.WriteLine(string.Format("SELECT TOP 1 * FROM questions WHERE c_id = @cid AND q_id NOT IN ({0}) ORDER BY NEWID() ;", string.Join(", ", parameters)));
cmd.Connection = new SqlConnection(ConnectionClass.constr);
cmd.Connection.Open();
cmd.Prepare();

P.S: This might not be the best way to handle this, but it worked for me.

zsubzwary
  • 1,196
  • 1
  • 14
  • 22
  • 2
    If the values are ints, why are you sending them as varchar? You should change to SqlDbType.Int like you do for the @cid parameter (which should also allow you to remove the size component, though I believe "4" would be the correct value if you must pass it). – pinkfloydx33 Dec 24 '17 at 11:06
  • `System.Int32.MaxValue` Size is **not** the same thing as MaxValue. – mjwills Dec 24 '17 at 11:37
  • @mjwills, Actually this code is inside a function, and this function is executed at least 10 times – zsubzwary Dec 24 '17 at 11:52
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161894/discussion-between-zakawat-and-mjwills). – zsubzwary Dec 24 '17 at 11:57