passing and retrieving data from stored proc

[coder91]

Hi, I have a stored procedure that I have to send one parameter to. I then have to use the same stored procedure to display results, I'm not sure how to do this. Basically the system should allow the user to select an item, push a button (which is when I have to send the username to the stored proc, at the same time I have to retrieve data from this stored proc and display it). Any help would be great, thank you.

[yssirhc]

Stored procedure example:

USE db_name;
GO
CREATE PROCEDURE some_name
(
    @parameter_name varchar(50)  -- or whatever type it is
)
AS
BEGIN
    SELECT *
    FROM table_name
    WHERE column = @parameter_name
END

Code example:

SqlCommand command = new SqlCommand(storedProc, scanConnect);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@computer", location);

SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
missingEvents.DataSource = ds; //missingEvents is the gridview ID
missingEvents.DataBind();

[JorgeM]

at the same time I have to retrieve data from this stored proc and display it

You need to be more specific regarding how you want to display the results. yssirhc's example shows display the results possibly to a gridview.

Maybe you want to build the HTML and display in a placeholder? The code for displaying the results depends on the data being returned (one record with one field, one record with many fields, many records with many fields) and to which manner you are displaying the data in.

[coder91]

The way someone in work has told me to it is:

conn = new SqlConnector(strConn);


            SqlCommand sql = new SqlCommand("sp_name");
            sql.Parameters.AddWithValue("@name", name);
            sql.Parameters.Add(new SqlParameter()
            {
                ParameterName = "RETURN_VALUE",
                Direction = ParameterDirection.Output
            });


            var dataTable = conn.SPExecuteDataTable(sql);

            int returnNumber = (int)sql.Parameters["RETURN_VALUE"].Value;

            if (dataTable.Rows.Count > 0)
            {

            }

But not sure how I use this to display the data on the screen when I try adding a gridview and binding it to the datatable in the if statment an error is displayed 'String[1]: the Size property has an invalid size of 0.' which appears in another file for the dataAdapter.Fill(dsTemp);

Any ideas how I can fix this. Thanks

[JorgeM]

Here is a very simple example i tested with a stored procedure and it works although there is no return value in my case so its slightly different than yours..

aspx

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    SqlConnection sQLDatabaseConnection = new SqlConnection(.. your connection info.. );
    SqlCommand command = new SqlCommand(".. spName ..");
    command.CommandType = CommandType.StoredProcedure;
    command.Connection = sQLDatabaseConnection;
    command.Parameters.AddWithValue("@param1", 0);
    command.Parameters.AddWithValue("@param2", "string");

    DataSet ds = new DataSet();
    SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
    sqlAdapter.Fill(ds);
    DataTable dt = ds.Tables[0];

    GridView1.DataSource = dt;
    GridView1.DataBind();
}

In your example, you can drop a label control in your aspx page then just assign that value to the Text property of the control.

Label1.Text = // the value you got from your sql query

[coder91]

Thank you, that's great.