|
yeah.. there r more operations that r to be performed on this temp table.. modifications, etc.. but i got to get this running b4 i put in the other stmts... SOS!!
|
|
|
|
|
use this
insert into #abc(ssn) select isnull(phone,'') from tbl_users
DAsh04
|
|
|
|
|
Try adding
set nocount on as the first line of your procedure. This stops SQL-Server from sending-back a running commentary of what its up to (which is probably being counted as the resultset for you current procedure).
|
|
|
|
|
|
Hi,
I would like to call an oracle stored procedure from SQL Server. My setup is like this...
Computer A: SQL Server installed.
Computer B: Oracle installed.
I want to call oracle stored procedure in Computer B from computer A. Ideally i want to get some return values/result sets from oracle stored procedure.
Any help would be much appreciated.
Many Thanks
Saurabh Hansalia
|
|
|
|
|
See sp_addlinkedserver. You can use any OLE DB data source as a linked server and query it using linked server syntax.
|
|
|
|
|
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..
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
