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(cmdcnn); 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(cmdcnn) 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(cmdcnn); // 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 Singh · 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.