when using Stored Procedure called from C# ?

Dong Lam Trien

In SQL Server 2005 Stored Procedure I have the following:

Code:
CREATE FUNCTION [dbo].[LISTSTAFF]()
RETURNS TABLE
AS    
RETURN(select * from tblStaff);

In C#2005 I assign staff style table for gridview an error, can you help me fix this ?

Code:
...
SqlCommand cmd = new SqlCommand("LISTSTAFF", cnn);
cmd.CommandType = CommandType.StoredProcedure;
gridview.DataSource = cmd.ExecuteReader();// waring error in here
...

I want assign list database style table for gridview.DataSource =... how assign ?

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
Hi,
here your are creating SQL function and trying to fetch data like store procedure
try...
SqlCommand cmd = new SqlCommand("select * from [dbo].[LISTSTAFF]()", cnn);
cmd.CommandType = CommandType.Text;
gridview.DataSource = cmd.ExecuteReader();
hope this will help you.
0
Palle Technologies12y ago
use
gridView.DataSource=cmd.ExecuteReader();
gridView.DataBind();

Your User defined function is also correct and i dint identified any issues with it . Instead of using UDF it would be better if you use stored procedure.
0
Dong Lam Trien12y ago
Thank you all
0
sudipta sanyal12y ago
try to write your function this way
CREATE FUNCTION [dbo].[fn_test]()
RETURNS @temp_table TABLE
(
id int,
name nvarchar(80)
)
AS
BEGIN
INSERT @temp_table
SELECT top 10 id, name
FROM your_table
ORDER by id ASC
RETURN
END
GO

NOW go to your .cs page
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter("select * from [fn_test]()", "your_conn_string");
sda.Fill(dt);
0
Dong Lam Trien12y ago
Now, I write CREATE PROCEDURE return valued table. I write CREATE FUNCTION not run and warning error, from the above example, you can just help me rewrite the CREATE FUNCTION to return the table is not ? because I want to learn two ways to write CREATE PROCEDURE and CREATE FUNCTION.
0
Ranjit Powar12y ago
Hey, there are two types of user defined functions one is scalar which return single value and other is table valued function which return table.
0
Dong Lam Trien12y ago
Thank you for your attention to my question. I did it. but I hard understand declare procedures CREATE PROCEDURE return value list or table, while I declare CREATE FUNCTION don't return list or table. so if you write CREATE FUNCTION returns list or table are not ? replaced write CREATE PROCEDURE.
0
Ranjit Powar12y ago
hey, in your code your created table valued function not a stored procedure.
0
Dong Lam Trien12y ago
I'm writing style Stores Procedures, if I write SQL-style queries and use the DataSet or DataTable running normally. Have you got write Stores Procedures ?
0
Ranjit Powar12y ago
Try this DataSet ds=new DataSet(); SqlDataAdapter da = new SqlDataAdapter("Select *from LISTSTAFF()",cnn); da.Fill(ds); gridview.DataSource =ds.Tables[0];
0
Dong Lam Trien12y ago
still get an error when running: "The request for procedure 'LISTSTAFF' failed because 'LISTSTAFF' is a tables valued function object"

[CODE] try { SqlCommand cmd = new SqlCommand("LISTSTAFF", cnn); cmd.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); // warning error in here gridview.DataSource =ds.Tables[0]; cnn.Close(); } catch (SqlException ex) { cnn.Close(); MessageBox.Show("Error: " + ex.Message); } [/CODE]
0
Ranjit Powar12y ago
Sorry pass only cmd as parameter.
DataAdapter da=new DataAdapter(cmd);
0
Dong Lam Trien12y ago
if i have added your code and still run error:

Error 1 The best overloaded method match for 'System.Data.SqlClient.SqlDataAdapter.SqlDataAdapter(string, string)' has some invalid arguments, error = new DataAdapter(cmd,cnn)Error 2 Argument '1': cannot convert from 'System.Data.SqlClient.SqlCommand' to 'string',... error = cmd Error 3 Argument '2': cannot convert from 'System.Data.SqlClient.SqlConnection' to 'string' error = cnn [CODE] SqlCommand cmd = new SqlCommand("LISTSTAFF", cnn); cmd.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); DataAdapter da=new DataAdapter(cmd,cnn); // warning error in hereda.Fill(ds);gridview.DataSource =ds.Tables[0];[/CODE]
0
Ranjit Powar12y agoPosted Jun 21, 2014, 12:19 AM Hey instead of using DataReader use DataSet. SqlCommand cmd = new SqlCommand("LISTSTAFF", cnn); cmd.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); DataAdapter da=new DataAdapter(cmd,cnn); da.Fill(ds); gridview.DataSource =ds.Tables[0]; 0
DTDong Lam Trien12y agoPosted Jun 21, 2014, 12:05 AM I have added your code and still run error: "The request for procedure 'LISTSTAFF' failed because 'LISTSTAFF' is a tables valued function object" [CODE] SqlDataReader dr = cmd.ExecuteReader(); if (dr .HasRows) { gridview1.DataSource=dr; // warning error in here gridview1.databind(); } [/CODE] 0
Abhishek Kumar12y agoPosted Jun 20, 2014, 4:41 AM Please try the below code SqlDataReader dr = cmd.ExecuteReader(); if (dr .HasRows) { gridview1.DataSource=dr; gridview1.databind(); } Hope this helps. Abhishek 0
Ranjit Powar12y agoPosted Jun 20, 2014, 4:18 AM Hi, ExecuteReader() return datareader. And you cant assign datareader to DataSource property. If you want to assign rows from reader to gridview then use Read() method of DataReader and add rows one by one. 0