Database

27-May-07 22:50

hi
Could someone plz help me!

I am getting an "IndexOutOfRangeException not handled by user", in my code when im tryin to get a specific record, selected via a dropdownlist, and show the fields in textboxes.

my code is like this:

reader.Read();
ShipperDetails shipper = new ShipperDetails((int)reader["ShipperID"],
(string)reader["CompanyName"], (string)reader["Phone"]);

reader.Close();

this is where the error comes up.

Please help..

Colin Angus Mackay28-May-07 0:09

28-May-07 0:09

You have supplied a column name that does not exist.

Also, it is better for debugging if you expand your code like this, because then you will have a better idea about exactly which item failed.

int shipperId = (int)reader["ShipperID"];
string companyName = (string)reader["CompanyName"];
string phone = (string)reader["Phone"];
ShipperDetails shipper = new ShipperDetails(shipperId,
    companyName, phone);

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

rcwoods28-May-07 1:13

28-May-07 1:13

thanks for the reply.

it says im getting an error with the ShipperID. I used an arraylist to store the objects.
the problem is that its returning a shipperId = 0 when it reads the first row. and thats why its IndexoutOfRange.

How can i fix this?

Colin Angus Mackay28-May-07 1:19

28-May-07 1:19

rcwoods wrote:
the problem is that its returning a shipperId = 0 when it reads the first row

From the code that you showed returning a shipperId of zero will not produce the exception you are getting. Is there any code missing?

rcwoods28-May-07 1:33

28-May-07 1:33

here is more of my code
maybe you can see whats wrong.
this is from my web form:

ShipperDetails[] shipperArray = dac.GetAllShippers();

foreach (ShipperDetails shipper in shipperArray)
{
ListItem item = new ListItem();
item.Text = shipper.CompanyName;
item.Value = shipper.ID.ToString();

DropDownList1.Items.Add(item);
}

public ShipperDetails[] GetAllShippers()<br />
        {<br />
            SqlConnection con = new SqlConnection(connString);<br />
            SqlCommand cmd = new SqlCommand("GetAllShippers", con);<br />
            cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
            //collection for all shipper records<br />
            ArrayList shippers = new ArrayList();<br />
            try<br />
            {<br />
                con.Open();<br />
                SqlDataReader reader = cmd.ExecuteReader();<br />
<br />
                while (reader.Read())<br />
                {<br />
<br />
                    ShipperDetails shipper = new ShipperDetails((int)reader["ShipperID"], (string)reader["CompanyName"], (string)reader["Phone"]);<br />
<br />
                    shippers.Add(shipper);<br />
<br />
                }<br />
                reader.Close();<br />
<br />
                return (ShipperDetails[])shippers.ToArray(typeof(ShipperDetails)); <br />
            }<br />

this is called when i select a particular shipper from the dropDownList:

public ShipperDetails GetShipper(int shipperID)<br />
        {<br />
            SqlConnection con = new SqlConnection(connString);<br />
            SqlCommand cmd = new SqlCommand("GetShipper", con);<br />
            cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
            cmd.Parameters.Add(new SqlParameter("@ShipperID", SqlDbType.Int, 4));<br />
            cmd.Parameters["@ShipperID"].Value = shipperID;<br />
<br />
            try<br />
            {<br />
                con.Open();<br />
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);<br />
<br />
                //get 1st row<br />
                reader.Read();<br />
                int shipperId = (int)reader["ShipperID"];<br />
                string companyName = (string)reader["CompanyName"];<br />
                string phone = (string)reader["Phone"];<br />
                    <br />
                ShipperDetails shipper = new ShipperDetails(shipperId,companyName,phone);<br />
                <br />
                reader.Close();<br />
<br />
                return shipper;<br />
            }

Colin Angus Mackay28-May-07 1:48

28-May-07 1:48

In GetShipper(int shipperID), does the reader actually return anything? (i.e. reader.HasRows == true)

If you expect shipperID to be non-zero then perhaps the code that gets the shipperID out of the drop down isn't working properly...

rcwoods28-May-07 2:04

28-May-07 2:04

reader did return true for having rows.

this is how i get the shipperId out of the dropDownlist:

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)<br />
    {<br />
        <br />
        ShipperDetails shipper = dac.GetShipper(Convert.ToInt16(DropDownList1.SelectedItem.Value));<br />
<br />

Colin Angus Mackay28-May-07 2:17

28-May-07 2:17

You are shoving too much into one line of code. Separate out the method and property calls into separate lines:

ListItem selectedItem = DropDownList1.SelectedItem;
string dropValue = selectedItem.Value;
int shipperId = Convert.ToInt16(dropValue);
ShipperDetails shipper = dac.GetShipper(shipperId);

Now, you can step through the above and see what is actually happening.

Incidentally, what happens if you change SelectedItem for SelectedValue?

rcwoods28-May-07 3:55

28-May-07 3:55

i got it right!

turns out the error was in my stored procedure.

Thanks for the help!
much appreciated!

sam#28-May-07 2:03

sam#

28-May-07 2:03

check your StoredProcedure may be problem is there i.e. it is not returning the columns that you want to access

rcwoods wrote:
reader.Read();
int shipperId = (int)reader["ShipperID"];
string companyName = (string)reader["CompanyName"];
string phone = (string)reader["Phone"];

ShipperDetails shipper = new ShipperDetails(shipperId,companyName,phone);

and write this code like this

if(reader.Read())<br />
{<br />
 int shipperId = (int)reader["ShipperID"];<br />
 string companyName = (string)reader["CompanyName"];<br />
 string phone = (string)reader["Phone"];<br />
<br />
 ShipperDetails shipper = new ShipperDetails(shipperId,companyName,phone);<br />
}

rcwoods28-May-07 3:48

28-May-07 3:48

this is my stored procedure:

CREATE PROCEDURE GetShipper<br />
@ShipperID int<br />
AS<br />
SELECT ShipperID, CompanyName, Phone FROM<br />
Shippers<br />
WHERE ShipperID = @ShipperID<br />
RETURN

tried the code you said but its still giving same problem. IndexOutOfRange by:
int shipperId = (int)reader["ShipperID"];

rcwoods28-May-07 3:52

Stored procedure probs..., [modified]

28-May-07 3:52

yes!
you were right it was my stored procedure.
My stored procedure looked like this before:

CREATE PROCEDURE GetShipper<br />
@ShipperID int<br />
AS<br />
SELECT CompanyName, Phone FROM<br />
Shippers<br />
WHERE ShipperID = @ShipperID<br />
RETURN<br />

I was missing the ShipperID from my SELECT.

Thanks So much for your time!!

Member 387988127-May-07 19:11

27-May-07 19:11

Hi in this procedure whats the problem in that "bonus" field...,
See i want to check my Bonus in "check" condition...,
But i am getting error statement...,
so plz any one help me...,

CREATE PROCEDURE Bonus
(
@projectid as int,
@BDate as smalldatetime,
@BMonths as tinyint
)
As
declare
@days1 as tinyint
begin

SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblEmployeeMaster.joinDate,
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+(dbo.tblsalarydetails.Ota)+
(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+
(dbo.tblsalarydetails.Allow4)+(dbo.tblsalarydetails.Allow5) as GTotal,
datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate) as WMonths,
(
case when ( ((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=@BMonths) and
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))<12 )
)then
(
(
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+
(dbo.tblsalarydetails.Allow5)
)*datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate)
)/12
else
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+
(dbo.tblsalarydetails.Allow5)
else
0 end
) as Bonus FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblsalarydetails ON
dbo.tblEmployeeMaster.empId = dbo.tblsalarydetails.EmpId WHERE (dbo.tblEmployeeMaster.projectId = @projectid)

end
GO

-- modified at 1:28 Monday 28th May, 2007

-- modified at 1:31 Monday 28th May, 2007

Magi

Harini N K27-May-07 19:21

27-May-07 19:21

Hi
There is one error in your sql statement. Give one space between Bonus and From

as Bonus FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblsalarydetails ON

Harini

Member 387988127-May-07 19:27

27-May-07 19:27

Sorry actually already given space its not showing that error...,
its giving error in ">" symbol space...,

Magi

Harini N K27-May-07 19:37

27-May-07 19:37

Magh_M wrote:
else
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )

Chnage the above line to below

when ((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )<br />
then

You have missed 'when' keyword as you are checking a condition

Hope you got it..

Harini

Member 387988127-May-07 20:20

27-May-07 20:20

then its showing error:156 incorrect syntax near when like....,

Magi

Harini N K27-May-07 20:26

27-May-07 20:26

Hi
I dont understand what could be the problem... update it in your code project and let me try again ...

Harini

Harini N K27-May-07 20:27

27-May-07 20:27

Hi
I have modified and pasted here.. Let me know if there is any error:

<br />
CREATE PROCEDURE Bonus<br />
(<br />
@projectid as int,<br />
@BDate as smalldatetime,<br />
@BMonths as tinyint<br />
)<br />
As<br />
declare<br />
@days1 as tinyint<br />
begin<br />
<br />
<br />
SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblEmployeeMaster.joinDate, <br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+(dbo.tblsalarydetails.Ota)+<br />
(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+<br />
(dbo.tblsalarydetails.Allow4)+(dbo.tblsalarydetails.Allow5) as GTotal,<br />
datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate) as WMonths,<br />
(<br />
case when ( ((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=@BMonths) and <br />
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))<12 ) <br />
)then<br />
(<br />
(<br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+<br />
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+<br />
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+<br />
(dbo.tblsalarydetails.Allow5)<br />
)*datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate)<br />
)/12 <br />
when<br />
((datediff(Month,dbo.tblEmployeeMaster.joinDate,@BDate))>=12 )<br />
then <br />
<br />
(dbo.tblsalarydetails.BasSalary)+(dbo.tblsalarydetails.Hra)+(dbo.tblsalarydetails.Ca)+<br />
(dbo.tblsalarydetails.Ota)+(dbo.tblsalarydetails.Sha)+(dbo.tblsalarydetails.Allow1)+<br />
(dbo.tblsalarydetails.Allow2)+(dbo.tblsalarydetails.Allow3)+(dbo.tblsalarydetails.Allow4)+<br />
(dbo.tblsalarydetails.Allow5)<br />
else<br />
0 end<br />
) as Bonus FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblsalarydetails ON <br />
dbo.tblEmployeeMaster.empId = dbo.tblsalarydetails.EmpId WHERE (dbo.tblEmployeeMaster.projectId = @projectid)<br />
<br />
end<br />
GO

Harini

Member 387988127-May-07 20:41

27-May-07 20:41

ya its working nicely....,
But i also did same given what u said "when and then"...,
But in that time its shown syntax error near "when"...,
Anyway now its working good...,
Thanks lot...,
Keep in touch.........,

Magi

Harini N K27-May-07 20:45