populate DataGridView from Stored Procedure

Jassim Rahma

I am using this code to populate to datagridview but nothing is happening and when I try DataBind() at the end it throws an error (dataGridView does not contain a definition for DataBind)

sql_connection = new SqlConnection("Data Source=.\\SQLEXPRESS;initial catalog=ws2connect;integrated security=true");

sql_command = new SqlCommand("sp_get_customers", sql_connection);

sql_command.CommandType = CommandType.StoredProcedure;

sql_command.Parameters.Add("@id_number", SqlDbType.VarChar).Value = "%731%";

sql_connection.Open();

sql_reader = sql_command.ExecuteReader(CommandBehavior.CloseConnection);

dataResult.DataSource = sql_reader;

EisenB

You can not directly bind a DataReader to a DatGridView. Use a DataAdapter to fill a DataTable, then bind that DataTable to the DataGridView.

To be more specific, as per MSDN description of DataSource:

The DataGridView class supports the standard Windows Forms data-binding model. This means the data source can be of any type that implements one of the following interfaces:

• The IList interface, including one-dimensional arrays.

• The IListSource interface, such as the DataTable and DataSet classes.

• The IBindingList interface, such as the BindingList class.

• The IBindingListView interface, such as the BindingSource class.

HTH

Ken Tucker

A simple method would be to load the DataReader into in a DataTable with the DataTable.Load method. You can then bind the datagridview to the datatable.

Jassim Rahma

ok.. can i ask the question this way..

what is the best way to populate data from SQL Server into DataGrid using Stored Procedure

Ken Tucker

SqlConnection sql_connection; SqlCommand sql_command; DataTable dt = new DataTable(); sql_connection = new SqlConnection("Data Source=.\\SQLEXPRESS;initial catalog=ws2connect;integrated security=true"); sql_command = new SqlCommand("sp_get_customers", sql_connection); sql_command.CommandType = CommandType.StoredProcedure; sql_command.Parameters.Add("@id_number", SqlDbType.VarChar).Value = "%731%"; SqlDataAdapter da = new SqlDataAdapter(sql_command); da.Fill(dt);

Jassim Rahma

i tried this code but I am not getting any record thought I have records contains the same entry I typed.

DataTable dt = new DataTable();

sql_connection = new SqlConnection("Data Source=.\\SQLEXPRESS;initial catalog=ws2connect;integrated security=true");

sql_command = new SqlCommand("sp_get_customers_by_id", sql_connection);

sql_command.CommandType = CommandType.StoredProcedure;

sql_command.Parameters.Add("@id_number", SqlDbType.VarChar).Value = "%" + id_number + "%";

SqlDataAdapter da = new SqlDataAdapter(sql_command);

da.Fill(dt);

dataResult.DataSource = da;

and this is the stored procedure:

USE ws2connect

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Jassim Rahma>

-- Description: <add a new client>

-- =============================================

ALTER PROCEDURE [dbo].[sp_get_customers_by_id]

@id_number VarChar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT * FROM clients_db WHERE id_number LIKE @id_number

END

hrubesh

-- Thats very Simple

-- If having a sp that has a normal Select , it will appear in

-- datasources, you can drag and drop like you would do for a

-- table. The adapter will be found in 'dataset.datasetTableadapters.spname'

-- If you have one that end with an exec - like Exec(@stmt),

-- There is a simple trick.

-- alter the sp --> comment out the exec part.

-- insert a select statement there. select(@stmt) to replace the ex. above.

-- save sp ( run )

-- now go to datasources and configure ds with wizard.,

-- remove the sp if it was already there. save.

-- rerun wizard, add sp.

-- it will appear as a table in the datasource window.

-- go to sql and re modify code,

-- it works:)

hrubesh

well thats straight forward it should appear in the datasource window. and acts like a table.