来自新手的问题 [英] Question From A Newbie

问题描述

大家好:
我需要协助.我试图将在线CV放在一起，却遇到了一个问题，试图使用C#中的存储过程来检索数据.代码是:

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)

我不断收到的错误是:
字符串[4]:Size属性的大小无效为0

关于我所缺少的任何建议吗?

Draykko

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

Any suggestions about what I''m missing?

Draykko

推荐答案

flashCardCmd.Parameters.Add(new SqlParameter("@subjectType", SqlDbType.NVarChar, 30));

flashCardCmd.Parameters.Add(new SqlParameter("@question", SqlDbType.NVarChar, 4000));

这篇关于来自新手的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！