when using Stored Procedure called from C# ?

Question

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 ?

Ranjit Powar · Answer

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];

Jignesh Trivedi · Answer

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.

Palle Technologies · Answer

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.

Dong Lam Trien · Answer

Thank you all

sudipta sanyal · Answer

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);

Dong Lam Trien · Answer

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 re write the CREATE FUNCTION to return the table is not ? because I want to learn two ways to write CREATE PROCEDURE and CREATE FUNCTION.

Ranjit Powar · Answer

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.

Dong Lam Trien · Answer

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 w rite CREATE FUNCTION returns list or table are not ? replaced write CREATE PROCEDURE.

Ranjit Powar · Answer

hey, in your code your created table valued function not a stored procedure.

Dong Lam Trien · Answer

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 ?

Ranjit Powar · Answer

Try this DataSet ds=new DataSet(); SqlDataAdapter da = new SqlDataAdapter("Select *from LISTSTAFF()",cnn); da.Fill(ds); gridview.DataSource =ds.Tables[0];

Dong Lam Trien · Answer

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]

Ranjit Powar · Answer

Sorry pass only cmd as parameter. DataAdapter da=new DataAdapter(cmd);

Dong Lam Trien · Answer

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 here da.Fill(ds); gridview.DataSource =ds.Tables[0]; [/CODE]

Dong Lam Trien · Answer

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]

Abhishek Kumar · Answer

Please try the below code SqlDataReader dr = cmd.ExecuteReader(); if ( dr .HasRows) { gridview1.DataSource=dr; gridview1.databind(); } Hope this helps. Abhishek

Ranjit Powar · Answer

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.