|
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);
|
|
|
|
|
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?
|
|
|
|
|
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?
|
|
|
|
|
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 />
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 />
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 />
}
|
|
|
|
|
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...
|
|
|
|
|
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 />
|
|
|
|
|
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 ?
|
|
|
|
|
i got it right!
turns out the error was in my stored procedure.
Thanks for the help!
much appreciated!
|
|
|
|
|
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 />
}
|
|
|
|
|
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"];
|
|
|
|
|
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!!
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Sorry actually already given space its not showing that error...,
its giving error in ">" symbol space...,
Magi
|
|
|
|
|
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
|
|
|
|
|
then its showing error:156 incorrect syntax near when like....,
Magi
|
|
|
|
|
Hi
I dont understand what could be the problem... update it in your code project and let me try again ...
Harini
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
|
Hi
Iam using xp_smtp_sendmail procedure to send mails in SQL 2000. Now I want to know how to receive an acknowledgement when the recipient receive the mail.
Thanks
|
|
|
|
|
You don't. Unless the recpient answers "Yes" to the little question about sending a receipt back to your mailbox, you'll never know they got the message.
The return receipt will go back to the mailbox in the "From" field you set when you sent the message. SQL Server doesn't have a POP3 client, so you'll have to check your mailboxd from an email app, or write your own code to check the mailbox.
|
|
|
|
|
I use a lot of comments in my stored procedures to document my code for future developers that might have to modify / debug it.
However, when I view my stored procedure script or pick the "modify" option in SQL Server Management Studio the code comes up with blank lines where my comments are supposed to be.
I see other stored procedures that have retained thier comments (although they are much smaller in size) using the same "--" single line comment that I use.
Anyone know why that is happening?
Thanks,
Ron
|
|
|
|
|
Nevermind, I figured it out.
The query tool I am using ( WinSQL - very handy tool ) has an option to "Parse Comments Locally" which was turned on - so it was removing the comments *before* sending the to SQL Server to save.
Ron
|
|
|
|
|
Hello!
I am developing a .NET 2.0 solution (C#) using ADO.NET
I have a question regarding the escape sequences in SQL. Consider the following query
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee's cost'
Now when executed, this query generates an exception. Very well. The problem with with the
single quote symbol. so what I do, I write like this:
SELECT * FROM ClauseTranslations WHERE Clause LIKE 'trainee\'s cost'
But this still gives error... What the way out?
Regards,
Mohsin
Polite Programmer
More Object Oriented then C#
|
|
|
|
|