|
Hi all,
Can anybody help me here ... how to run a procedure with outparameter...
please provide the code if u can... Thanks alot
T@SU
|
|
|
|
|
using (SqlConnection sc = new SqlConnection())
{
StringBuilder sql = new StringBuilder();
sql.Append("exec sp_YourProc");
string connStr = "...";
sc.ConnectionString = connStr;
sc.Open();
using (SqlCommand sco = new SqlCommand())
{
try
{
sco.CommandType = CommandType.Text;
sco.CommandText = sql.ToString();
sco.Connection = sc;
using (SqlDataReader sr = sco.ExecuteReader())
{
}
}
catch (SqlException exc)
{
}
}
SkyWalker
|
|
|
|
|
Where's the output param the OP asked about?
|
|
|
|
|
use sco.Parameters.Add()
SkyWalker
|
|
|
|
|
Same as any normal procedure, just make sure you add the output parameter and give it a Direction of Output (or InputOutput)
SqlParameter outParam = new SqlParameter("@myOutParam",SqlDbType.Int);<br />
outParam.Direction = ParameterDirection.Output;<br />
myCmd.Parameters.Add(outParam);
|
|
|
|
|
Thanks alot for ur replies....
after executing the procedure ...
how to retrive the result.. am trying like this but it dosent work..
dataset abc=cmd.ExecuteNonQuery();
i have done like this.....
SqlConnection conn = new SqlConnection(connc);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_abc";
SqlParameter paramPAF = new SqlParameter();
paramPAF.ParameterName = "P_Id";
paramPAF.SqlDbType = SqlDbType.Int;
paramPAF.Value = Section_Id;
paramPAF.Direction = ParameterDirection.Input;
SqlParameter paramNbr = new SqlParameter();
paramNbr.ParameterName = "ResultRS";
paramNbr.SqlDbType = SqlDbType.Variant;
paramNbr.Direction = ParameterDirection.Output;
cmd.Parameters.Add(P_Id); // parameter 1
cmd.Parameters.Add(sp_abc);
cmd.ExecuteNonQuery();
so now how to use third parameter "ResultRS" ?
Please help me here..
vikas da
|
|
|
|
|
You have to use the cmd.ExecuteReader() and not the cmd.ExecuteNonQuery() - the last method return an int of numbers of rows affected, usually used with insert query or update query (also useful with select count...).
Try this:
SqlDataReader reader = cmd.ExecuteReader();
reader.Close();
string result = reader["ResultRS"].ToString();
|
|
|
|
|
it is showinf error like "Procedure or function has too many arguments specified".
i am using "paramNbr.SqlDbType = SqlDbType.Variant;" in case out parameter sqldbtype is that fine..
T@SU
|
|
|
|
|
SqlConnection conn = new SqlConnection("CONNECTION HERE");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_abc";
//Create the first parameter
SqlParameter firstParam = new SqlParameter("P_Id", SqlDbType.Int);
firstParam.Value = 0; //Place your variable
firstParam.Direction = ParameterDirection.Input;
cmd.Parameters.Add(firstParam); //Parameter 1
//Create the second parameter
SqlParameter secondParam = new SqlParameter("ResultRS", SqlDbType.Variant);
secondParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(secondParam); //Parameter 2
int count = cmd.ExecuteNonQuery();
Console.WriteLine(secondParam.Value.ToString());
This is correct now, secondParam.Value and you read the value
News at http://support.microsoft.com/kb/308621[^]
modified on Monday, January 07, 2008 8:36:36 AM
|
|
|
|
|
I'm using an Access database in Visual C# - how can I delete a record from the database permanently? When I use .RemoveAt(int pos) it removes it only from the dataset, but when I reload the program the record is back.
|
|
|
|
|
You need to write delete query and execute it.
|
|
|
|
|
How I could get Last day of the current month in SQL Server?
I want to get records with respect to Date that lies with in the Current month or later. Please help me out.
|
|
|
|
|
AFAIK, there is no inbuilt functions available. Use Month() for comparing using month.
|
|
|
|
|
Please post this in the SQL forum. This forum is for C#.
|
|
|
|
|
I knocked this solution up for you:
select
dateadd(d,-1,
cast(cast(datepart(month,dateadd(m,1,getdate())) as varchar(2))+
'/'+
'1/'+
cast(datepart(year,dateadd(m,1,getdate()))as char(4))as datetime))
The way the query works is to calculate the first day of the next month then to subtract one day from this.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Hi friends,
I have a homework, i worked for days but I got stuck in a point. I can't go out of this point.
I am very glad if you can help me.
I make effort to get a query from from northwind database sample. I want max sales of employees according to product.
There are 9 employees(sales persons). There are 77 products type. Salespersons sell this products. I want ,which product was sold maximumly according to sales turnover. The result must be like below
salesperson product sales amount
salesperson1 product a x
salesperson2 product b y
. .
. . .
.
salesperson9 product h w.
I tryed below script, I can find max sales of each salespersons but I can't add productname column to this query.
select t.firstname,t.lastname,max(t.sales) as sales
from (select employees.Firstname,employees.lastname,products.productname,
sum("order details".unitprice*quantity*(1-discount)) as Sales from ((Employees inner join orders on employees.employeeID=orders.employeeID) inner join "order details" on orders.orderID="order details".orderID) inner join products on products.productID="order details".productID
group by employees.lastname,employees.firstname,products.productname
) as t
group by t.firstname,t.lastname
order by t.firstname
Note: I got answer from Jacob Sebastian like below, bu i didn't understand exactly that response. It works but I didn't understand, especially top1 clause. The result consists of 9 row but top1 gives one row according to I know and link between a and b is very complex.
SELECT
FirstName,
LastName,
(SELECT TOP 1 ProductName FROM (SELECT
e.FirstName,
e.LastName,
p.ProductName,
SUM(od.UnitPrice * od.Quantity*(1-Discount)) AS Sales
FROM Orders oh
INNER JOIN [Order Details] od ON oh.OrderID = od.OrderID
INNER JOIN Products p ON p.ProductID = od.ProductID
INNER JOIN Employees e ON e.EmployeeID = oh.EmployeeID
GROUP BY e.FirstName, e.LastName, p.ProductName ) b
WHERE a.FirstName = b.FirstName AND a.LastName = b.LastName
ORDER BY Sales DESC
) AS ProductName,
MAX(Sales)
FROM (
SELECT
e.FirstName,
e.LastName,
p.ProductName,
SUM(od.UnitPrice * od.Quantity*(1-Discount)) AS Sales
FROM Orders oh
INNER JOIN [Order Details] od ON oh.OrderID = od.OrderID
INNER JOIN Products p ON p.ProductID = od.ProductID
INNER JOIN Employees e ON e.EmployeeID = oh.EmployeeID
GROUP BY e.FirstName, e.LastName, p.ProductName
) a
GROUP BY FirstName, LastName
ORDER BY FirstName, LastName
modified on Monday, January 07, 2008 3:27:58 AM
|
|
|
|
|
Please post this on the SQL forum, especially as you have had this answered before. It's only polite to continue this on the original thread.
|
|
|
|
|
I am sorry, but there are less people in sql form. Here, replies are faster
and most of people in c# form know sql.
|
|
|
|
|
omegazafer wrote: I am sorry, but there are less people in sql form. Here, replies are faster
and most of people in c# form know sql.
That's an abuse of the forum system. How do I find an answer to a problem in future if it's not in a logical area? Why not ask a C# question in the C++ forum because the syntax is a little bit similar? There's a reason why these forums are organised the way they are, and it's not to provide a shortcut for somebody who's too lazy or incompetent to use the appropriate forum.
|
|
|
|
|
I have data in txt format and want to save the data in my table in SQL database. How can I do this without using DTS?
|
|
|
|
|
minniemooo wrote: I have data in txt format
txt file ? Then open it using your program, read contents and insert into DB.
|
|
|
|
|
|
Ok the following is a lambda:
Func<..> func = i => i * 3;
And this is an anonymous delegate
Func<..> func = delegate (int i) {return i * 3; ) ;
But what about this?:
Func<..> func = (int i) => { return i * 3;}
Is this considered a lambda, due to the fact it uses the lambda syntax..
Or is it considered an anonymous delegate due to the fact that it can not be converted to an linq.expression
Im I right if I say that it is infact an anonymous delegate and that only the "one line" forumulas w/o any { } body are real lambdas?
If it is considered a lambda, since it uses the lambda syntax after all. and it also benefits from type deduction and all that.
Is there any word or way to make a difference between one line lambdas that can be turned into linq.expressions and those that only can fill the role of plain old delegates?
modified on Monday, January 07, 2008 3:06:45 AM
|
|
|
|
|
I want to implement some reports in C#, what will be the best approach?
|
|
|
|
|