Question From A Newbie

See more:

C#

ASP.NET

Hey All:
I need some help. I'm trying to put together an online CV and I've run into a problem trying to retrieve data using a stored procedure in C#. The code is:

protected void Button1_Click(object sender, EventArgs e)    {        #region Code        //string subject = ".NET";        //int qID = 5;        String questionResult = string.Empty;        try        {            ConnectionStringSettings flashCardConn = ConfigurationManager.ConnectionStrings["FlashCardConnectionString"];            SqlConnection fcConn = new SqlConnection(flashCardConn.ConnectionString);            #region Test Code            //SqlConnection fcConn = new SqlConnection(@"Data Source='.\SQLEXPRESS'; Initial Catalog=TechInterviewDB; Integrated Security=true");            //SqlCommand flashCardCmd = new SqlCommand("SELECT interviewQuestion FROM QuestionTable WHERE questLanguage = '.NET' AND questID = 5", fcConn);            #endregion Test Code            //SqlCommand flashCardCmd = new SqlCommand();            //flashCardCmd.Connection = fcConn;            //flashCardCmd.CommandType = CommandType.StoredProcedure;            //flashCardCmd.CommandText = "amsSP_RandomQuestionBySubject";            SqlCommand flashCardCmd = new SqlCommand("amsSP_RandomQuestionBySubject", fcConn);            flashCardCmd.CommandType = CommandType.StoredProcedure;            #region Inputs and Outputs            flashCardCmd.Parameters.Add(new SqlParameter("@subjectType", SqlDbType.NVarChar));            flashCardCmd.Parameters["@subjectType"].Direction = ParameterDirection.Input;            flashCardCmd.Parameters.Add(new SqlParameter("@Upper", SqlDbType.Int));            flashCardCmd.Parameters["@Upper"].Direction = ParameterDirection.Input;            flashCardCmd.Parameters.Add(new SqlParameter("@Lower", SqlDbType.Int));            flashCardCmd.Parameters["@Lower"].Direction = ParameterDirection.Input;            flashCardCmd.Parameters.Add(new SqlParameter("@Random", SqlDbType.Int));            flashCardCmd.Parameters["@Random"].Direction = ParameterDirection.Input;            //output the question            flashCardCmd.Parameters.Add(new SqlParameter("@question", SqlDbType.NVarChar));            flashCardCmd.Parameters["@question"].Direction = ParameterDirection.Output;            #endregion Inputs and Outputs            fcConn.Open();            flashCardCmd.Parameters["@subjectType"].Value = subject;            flashCardCmd.Parameters["@Upper"].Value = null;            //REM: initialize the input to zero            flashCardCmd.Parameters["@Lower"].Value = null;            //REM: initialize the input to zero            flashCardCmd.Parameters["@Random"].Value = null;           //REM: initialize the input to zero            flashCardCmd.ExecuteNonQuery();            questionResult = String.Format("{0}", flashCardCmd.Parameters["@question"].Value);            fcConn.Close();            TextBox1.Text = questionResult;                    }        catch (Exception ex)        {            TextBox1.Text = ex.Message;        }        #endregion Code
 protected void Button1_Click(object sender, EventArgs e)
 {
     #region Code
     //string subject = ".NET";
     //int qID = 5;
     String questionResult = string.Empty;

     try
     {
         ConnectionStringSettings flashCardConn = ConfigurationManager.ConnectionStrings["FlashCardConnectionString"];
         SqlConnection fcConn = new SqlConnection(flashCardConn.ConnectionString);

         #region Test Code
         //SqlConnection fcConn = new SqlConnection(@"Data Source='.\SQLEXPRESS'; Initial Catalog=TechInterviewDB; Integrated Security=true");
         //SqlCommand flashCardCmd = new SqlCommand("SELECT interviewQuestion FROM QuestionTable WHERE questLanguage = '.NET' AND questID = 5", fcConn);
         #endregion Test Code

         //SqlCommand flashCardCmd = new SqlCommand();
         //flashCardCmd.Connection = fcConn;
         //flashCardCmd.CommandType = CommandType.StoredProcedure;
         //flashCardCmd.CommandText = "amsSP_RandomQuestionBySubject";

         SqlCommand flashCardCmd = new SqlCommand("amsSP_RandomQuestionBySubject", fcConn);
         flashCardCmd.CommandType = CommandType.StoredProcedure;

         #region Inputs and Outputs
         flashCardCmd.Parameters.Add(new SqlParameter("@subjectType", SqlDbType.NVarChar));
         flashCardCmd.Parameters["@subjectType"].Direction = ParameterDirection.Input;

         flashCardCmd.Parameters.Add(new SqlParameter("@Upper", SqlDbType.Int));
         flashCardCmd.Parameters["@Upper"].Direction = ParameterDirection.Input;

         flashCardCmd.Parameters.Add(new SqlParameter("@Lower", SqlDbType.Int));
         flashCardCmd.Parameters["@Lower"].Direction = ParameterDirection.Input;

         flashCardCmd.Parameters.Add(new SqlParameter("@Random", SqlDbType.Int));
         flashCardCmd.Parameters["@Random"].Direction = ParameterDirection.Input;

         //output the question
         flashCardCmd.Parameters.Add(new SqlParameter("@question", SqlDbType.NVarChar));
         flashCardCmd.Parameters["@question"].Direction = ParameterDirection.Output;
         #endregion Inputs and Outputs

         fcConn.Open();

         flashCardCmd.Parameters["@subjectType"].Value = subject;
         flashCardCmd.Parameters["@Upper"].Value = null;            //REM: initialize the input to zero
         flashCardCmd.Parameters["@Lower"].Value = null;            //REM: initialize the input to zero
         flashCardCmd.Parameters["@Random"].Value = null;           //REM: initialize the input to zero

         flashCardCmd.ExecuteNonQuery();
         questionResult = String.Format("{0}", flashCardCmd.Parameters["@question"].Value);

         fcConn.Close();

         TextBox1.Text = questionResult;


     }
     catch (Exception ex)
     {
         TextBox1.Text = ex.Message;
     }
     #endregion Code

The stored procedure is:

ALTER PROCEDURE [dbo].[amsSP_RandomQuestionBySubject]
@subjectType NVARCHAR(30),
@Upper INT,
@Lower INT,
@Random INT,
@question NVARCHAR(MAX) OUTPUT 

AS

--SELECT 'The number of .NET questions is: ', @questionNum

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = (SELECT COUNT(*) FROM QuestionTable WHERE questLanguage = @subjectType) ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @Random AS 'Question Number'

SET @question = (SELECT interviewQuestion AS 'Question' 
FROM QuestionTable 
WHERE questID = @Random)

The error I keep getting is:
String[4]: The Size property has invalid size of 0

Any suggestions about what I'm missing?

Draykko