Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.

I want to use data reader to read a record and send the result to an array string.

I use a stored procedure to select data from 2 tables.

string ReqID = "";
string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sqlc;
string[] textbox;
string ReqType;

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["ReqID"] != null)
        ReqID = this.Request.QueryString["ReqID"];

    sqlc = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "LoadReq";

    string[] textbox = new string[4];
    if (!this.IsPostBack)
    {
        if (ReqID != "")
        {
            cmd.Parameters.Add("@ReqID", SqlDbType.NChar).Value = ReqID;
            cmd.Connection = sqlc;
            sqlc.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                textbox[2] = reader.GetString(reader.GetOrdinal("FReqID"));
                textbox[3] = reader.GetString(reader.GetOrdinal("OrderDate"));
                textbox[4] = reader.GetString(reader.GetOrdinal("OrderTime"));
                textbox[5] = reader.GetString(reader.GetOrdinal("ReqIP"));
                textbox[6] = reader.GetString(reader.GetOrdinal("Status"));
                textbox[7] = reader.GetString(reader.GetOrdinal("FileCode"));
                textbox[8] = reader.GetString(reader.GetOrdinal("Type"));
                textbox[9] = reader.GetString(reader.GetOrdinal("DOI"));
                textbox[10] = reader.GetString(reader.GetOrdinal("PubMedID"));
                textbox[11] = reader.GetString(reader.GetOrdinal("PaperCode"));


                LoadCorrectForm(textbox[8], textbox);
            }
        }

And my stored procedure is:

CREATE PROCEDURE [dbo].LoadReq
@ReqID int = 0
AS
SELECT 
        Request.FReqID, Request.ReqIP, Request.OrderDate, Request.OrderTime,
        Request.[Status], Request.FileCode, Request.[Description],
        Details.[Type], Details.DOI, Details.PubMedID, Details.PaperCode, 
        Details.ISBN, Details.Title, Details.Journal, Details.BookTitle,
        Details.Conference, Details.[Year], Details.Publisher, Details.Edition, 
        Details.Vol, Details.Issue, Details.pp, Details.[Author(s)],
        Details.URL
     FROM 
        Request 
     INNER JOIN 
        Details ON Details.ID = Request.FreqID
     WHERE 
        (Request.ID = @ReqID)

But I get an error.

How should I edit my code to do this job?

share|improve this question
2  
you defined your array textbox with size of 4 and you are trying to set its element 11 ?? –  Habib Dec 13 '13 at 16:20
1  
Why don't use List? –  huMpty duMpty Dec 13 '13 at 16:27
    
@user30551479 Or a datatable ? This may be helpful for you msdn.microsoft.com/en-us/magazine/cc188717.aspx. –  Suraj Singh Dec 13 '13 at 16:29

1 Answer 1

As stated by Habib, your array is too small. Consider this modification. Instead of this:

string[] textbox = new string[4];

or even this:

string[] textbox = new string[11];

Get rid of that line and do this:

SqlDataReader reader = cmd.ExecuteReader();
string[] textbox = new string[reader.FieldCount];

Suggestions

In addition to the fix, there are some suggestions you should consider.

if (Request.QueryString["ReqID"] != null)
    ReqID = this.Request.QueryString["ReqID"];

using (SqlConnection sqlc = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("LoadReq", sqlc))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        if (!this.IsPostBack)
        {
            if (ReqID != "")
            {
                cmd.Parameters.Add("@ReqID", SqlDbType.NChar).Value = ReqID;

                sqlc.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    string[] textbox = new string[reader.FieldCount];

                    while (reader.Read())
                    {
                        textbox[2] = reader.GetString(reader.GetOrdinal("FReqID"));
                        textbox[3] = reader.GetString(reader.GetOrdinal("OrderDate"));
                        textbox[4] = reader.GetString(reader.GetOrdinal("OrderTime"));
                        textbox[5] = reader.GetString(reader.GetOrdinal("ReqIP"));
                        textbox[6] = reader.GetString(reader.GetOrdinal("Status"));
                        textbox[7] = reader.GetString(reader.GetOrdinal("FileCode"));
                        textbox[8] = reader.GetString(reader.GetOrdinal("Type"));
                        textbox[9] = reader.GetString(reader.GetOrdinal("DOI"));
                        textbox[10] = reader.GetString(reader.GetOrdinal("PubMedID"));
                        textbox[11] = reader.GetString(reader.GetOrdinal("PaperCode"));

                        LoadCorrectForm(textbox[8], textbox);
                    }
                }
            }

            ...
        }
    }
}

In the modified code there are a number of things to take note of:

  1. I'm leveraging the using statement.
  2. I'm leveraging a different .ctor of the SqlCommand to make it more concise.
  3. I'm leveraging the FieldCount to determine the size of the array.
  4. I'm not sharing a connection instance.
  5. There are probably better data structures to use here.
  6. Your current code flow presumably builds the connection and command even if they don't need to be used.

Point #1 is exceptionally important. The using statement ensures that the object created and used get disposed properly. Whenever you consume something that implements IDisposable, leverage the using statement.

Point #4 is almost as important as #1. Do not share connection instances. When you need one, build it, open it, use it, and dispose it.

Let me elaborate on #5 here. Instead of using a string[], how about a List<string>. The List<string> is mutable (i.e. it can change size automatically). The code would be much easier because you could do something like this:

textbox.Add(reader.GetString(reader.GetOrdinal("FReqID")));
textbox.Add(reader.GetString(reader.GetOrdinal("OrderDate")));
textbox.Add(reader.GetString(reader.GetOrdinal("OrderTime")));
textbox.Add(reader.GetString(reader.GetOrdinal("ReqIP")));
textbox.Add(reader.GetString(reader.GetOrdinal("Status")));
textbox.Add(reader.GetString(reader.GetOrdinal("FileCode")));
textbox.Add(reader.GetString(reader.GetOrdinal("Type")));
textbox.Add(reader.GetString(reader.GetOrdinal("DOI")));
textbox.Add(reader.GetString(reader.GetOrdinal("PubMedID")));
textbox.Add(reader.GetString(reader.GetOrdinal("PaperCode")));

Then, if you needed to get it to a string[] you could just do this:

textbox.ToArray();

But, that may not work well for you here because you have an odd offset. You don't start inserting into the array until index 2. So again, I can't really say with certainty there are better data structures.

Point #6 is just an interesting one to me. If it's a PostBack or the ReqID is an empty string you don't actually do anything. That leads me to believe that you should actually moving the creation of the connection and command objects inside those if statements, but that's just a logical flow I'd prefer over the current one.

share|improve this answer
    
Unable to cast object of type 'System.Int32' to type 'System.String'. –  user3051479 Dec 13 '13 at 16:30
1  
@user3051479, ehem, I'm pretty sure telling me what line that's on is pretty important to me being able to help you. –  Michael Perrenoud Dec 13 '13 at 16:31
    
Line 42: textbox[2] = reader.GetString(reader.GetOrdinal("FReqID")); –  user3051479 Dec 13 '13 at 16:39
    
@user3051479, is the value for that field null on that row? –  Michael Perrenoud Dec 13 '13 at 18:17
    
yes,maybe null in some rows –  user3051479 Dec 13 '13 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.