passing stored procedure to grid view

[tcl4p]

I have a grid view bound to a stored procedure.  The question is how do I pass a parameter to the stored procedure at run time, for instance on load or on the click of a button?  I'm using visual basic.

Thanks,
Tom

[Tim Weckx]

Tom,

Look at these examples on MSDN for SqlCommand and SqlDataAdapter to retrieve your data
http://msdn.microsoft.com/en-us/library/879f39d8(v=VS.90).aspx and
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand(v=VS.90).aspx.

Use the DataAdapter to fill a DataSet or DataTable that you then bind to your grid.
(If you are only retrieving data, you only need to set the SelectCommand property on the DataAdapter)

[Julian Benkov]

Hello Thomas,

For this scenario you can use the following code snippet:

Public Function GetRentedTapesByCustomerID(ByVal CustomerID As Integer)
As DataTable
    Dim cn As OleDb.OleDbConnection = Settings.GetConnection()
    Dim cmd As New OleDb.OleDbCommand
    Dim da As New OleDb.OleDbDataAdapter
    Dim dt As New DataTable
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "RentedTapesByCustomerID"
    cmd.Parameters.Add("@CustomerID", CustomerID)
    cn.Open()
    cmd.Connection = cn
    da.SelectCommand = cmd
    da.Fill(dt)
    cn.Close()

    Return dt
End Function

Public Function BindGrid()
    Me.RadGridView1.DataSource = GetRentedTapesByCustomerID(1)
End Function

Thank you for your suggestions and links, Tim. Your Telerik points have been updated for the community effort.

Kind regards,
Julian Benkov
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Jeffrey]

Doing the same thing in C# and setting the Datasource of the Grid to the dataset filled in but the grid does not show the data eventhough data is loaded into the database (the count is accurate. 

        private DataSet dtSetDataset(DataSet dtDataset,
                                     string strQueryString,
                                     string strParameter = "",
                                     int    iValue = 0)
        {
            int iCheck = -1;
            string strConnString = "";
            strConnString = ConfigurationManager.ConnectionStrings["Pisceas.Properties.Settings.ConnectionString"].ConnectionString;

            using (SqlConnection connection = new SqlConnection(strConnString))
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = strQueryString;

                if (strParameter.Length > 0)
                {
                    sqlCommand.Parameters.Add(strParameter, iValue);
                }
                sqlCommand.Connection = connection;
                
                SqlDataAdapter adapter = new SqlDataAdapter();                
                adapter.SelectCommand = sqlCommand;
                iCheck = adapter.Fill(dtDataset);
                return (dtDataset);
            }
        }


                        dtGridData = dtSetDataset(dtGridData, strSQL, "Quote_ID", iQuoteID);
                        GRD_Items.DataSource = dtGridData;
                        GRD_Items.Refresh();

[Julian Benkov]

Hi Jeffrey,

When you setup the DataSource of RadGridView to a DataSet you also must setup the DataMember property to name of the DataTable hosted in the DataSet. 

dtGridData = dtSetDataset(dtGridData, strSQL, "Quote_ID", iQuoteID);
GRD_Items.DataMember = "Name_Of_The_Table";
GRD_Items.DataSource = dtGridData;

Another option is to set DataSource directly to DataTable. In this case the setup of DataMember is not needed:

dtGridData = dtSetDataset(dtGridData, strSQL, "Quote_ID", iQuoteID);
GRD_Items.DataSource = dtGridData. Tables[0];

More information about DataSet binding you can in this stackoverflow thread.

I hope this helps.Kind regards,
Julian Benkov
the Telerik team