locked
error RRS feed

  • Question

  • This is the error am getting:

    System.IndexOutOfRangeException: Cannot find column 1. at System.Data.DataColumnCollection.get_Item(Int32 index) at System.Data.DataRow.get_Item(Int32 columnIndex) at WebApplication1._Default.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\sgovardhan\My Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\Default.aspx.cs:line 59

     

    Here is a piece of code I wrote. What I am trying to do here is read from a table, check some business rules and write the passed record to another table. I read the table using ADO.net, to write back into another table I wrote an update stored Procedure. I am getting the above mentioned error. Please tell me what I am doing wrong (if you think my stored procedure is wrong, please let me know, il post that too) thanks

    C# code:

     

    using System;

    using System.Collections;

    using System.Configuration;

    using System.Data;

    using System.Linq;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.HtmlControls;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Xml.Linq;

    using System.Data.SqlClient;

     

     

     

    namespace WebApplication1

    {

        public partial class _Default : System.Web.UI.Page

        {

            protected void Page_Load(object sender, EventArgs e)

            {

     

                string tableName = "table1"

     

     

                //1. Instantiate the connection

                SqlConnection conn = new SqlConnection();

               // conn.Open();

     

                // 2. instantiate a new DataSet

                DataSet dsCustomers = new DataSet();

     

                try

                {

                    conn.ConnectionString = "Data Source=(local);Initial Catalog=S_Warehouse; Trusted_Connection=Yes";

     

                    // 3. init SqlDataAdapter with select command and connection

                    SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT [ID] FROM [S_Warehouse].[dbo].[table1] (nolock)", conn);

     

                    // 4.  create a command object identifying the stored procedure

                    SqlCommand cmd = new SqlCommand("pass_table", conn);

     

                    // 5. fill the dataset

                    daCustomers.Fill(dsCustomers, tableName);

     

                    // fill in insert, update, and delete commands

                    SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);

     

                    int cnt = 0;

                    for (cnt = 0; cnt <= dsCustomers.Tables[tableName].Rows.Count - 1; cnt++)

                    {

                        if (dsCustomers.Tables[tableName].Rows[cnt][0].ToString() == "121")

                        {

     

                       //     add parameters to command object

                            cmd.Parameters.Add(new SqlParameter("@ID", dsCustomers.Tables[tableName].Rows[cnt][0]));

                            cmd.Parameters.Add(new SqlParameter("@DateAdded", dsCustomers.Tables[tableName].Rows[cnt][1]));

                            cmd.Parameters.Add(new SqlParameter("@Message", dsCustomers.Tables[tableName].Rows[cnt][2]));

                            cmd.Parameters.Add(new SqlParameter("@UserName", dsCustomers.Tables[tableName].Rows[cnt][3]));   

     

                    // 4. set the command object so it knows to execute a stored procedure

                    cmd.CommandType = CommandType.StoredProcedure;

     

                    // 5. execute the command

                    cmd.ExecuteNonQuery();

     

                  Response.Write("One record inserted.");

                }

     

     

                        }

                    }

                catch (Exception ex)

                {

                    Response.Write(ex.ToString());

                }

                finally

                {

                    dsCustomers.Dispose();

                    conn.Close();

                }

            }

        }

    }


     

    Wednesday, December 1, 2010 5:05 AM

Answers

  • From a quick glance it seems that your SqlDataAdapter returns only one column, the ID.

     SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT [ID] FROM [S_Warehouse].[dbo].[table1] (nolock)", conn);

    that would then mean that this fails:

     cmd.Parameters.Add(new SqlParameter("@DateAdded", dsCustomers.Tables[tableName].Rows[cnt][1]));

    since there are no columns beyond index 0.

    Try to set the select to something like this.

     "SELECT [ID], [DateAdded], [Message], [UserName] FROM [S_Warehouse].[dbo].[table1] (nolock)"

    depending one what your columns are called in the database.

     


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Hans Wallis Wednesday, December 1, 2010 3:14 PM
    Wednesday, December 1, 2010 8:59 AM

All replies

  • From a quick glance it seems that your SqlDataAdapter returns only one column, the ID.

     SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT [ID] FROM [S_Warehouse].[dbo].[table1] (nolock)", conn);

    that would then mean that this fails:

     cmd.Parameters.Add(new SqlParameter("@DateAdded", dsCustomers.Tables[tableName].Rows[cnt][1]));

    since there are no columns beyond index 0.

    Try to set the select to something like this.

     "SELECT [ID], [DateAdded], [Message], [UserName] FROM [S_Warehouse].[dbo].[table1] (nolock)"

    depending one what your columns are called in the database.

     


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Hans Wallis Wednesday, December 1, 2010 3:14 PM
    Wednesday, December 1, 2010 8:59 AM
  • yes that was the problem, Thanks. I have one more doubt. Although my code says "one record inserted" when I do an sql query I dont see any record inserted. This is my stored procedure. Is there any thing wrong?

    or I dont think this statements are getting the value

    cmd.Parameters.Add(new SqlParameter("@ID", dsCustomers.Tables[tableName].Rows[cnt][0]));

    STORED PROC

    USE

     

    [S_Warehouse]

    GO

    /****** Object: StoredProcedure [dbo].[pass_table] Script Date: 11/30/2010 20:54:19 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

     

     

    create

     

    PROC [dbo].[pass_table]

    @ID

    int,

    @DateAdded

    datetime,

    @Message

    uniqueidentifier,

    @UserName

    nvarchar(255)

    AS

    update

     

    [pass_table]

    set

     

    ID=@ID, DateAdded=@DateAdded, Message=@Message, UserName=@UserNamefrom [pass_table]

    where

     

    ID=@ID

    Wednesday, December 1, 2010 2:48 PM
  • Hi again Hans

    The ExecuteNonQuery returns the number of rows affected:

    "SqlCommand.ExecuteNonQuery Method"
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
      "Executes a Transact-SQL statement against the connection and returns the number of rows affected."

    This means 0 if no rows are updated, 5 if 5 rows are updated, etc.

    However, your code is always returning "One record inserted.", no matter how many rows are affected:

    cmd.ExecuteNonQuery(); // <-- Could be 0 or 5
    Response.Write("One record inserted.");

    Change that to, for example,

            int affectedRows = cmd.ExecuteNonQuery();
            Response.Write(String.Format("{0} record(s) updated", affectedRows.ToString());

    and you should get the right output.


    This posting is provided "AS IS" with no warranties.
    Wednesday, December 1, 2010 3:32 PM
  • Thank you very much it worked
    Wednesday, December 1, 2010 3:37 PM