How do you get a grid to display the results of a stored procedure?

I am trying to perform a simple lookup on a table and return the results in a webgrid.  I am getting no errors but the webgrid shows nothing after the postback.  I have attached a Microsoft SQL Data source to the webgrid and I have told my Microsoft SQL Data Source to use a stored procedure.  I then pass the stored procedure the data through @variables.  Can someone tell me what I am missing.  If I use the "Test Query" in the Microsoft SQL Datasource, I get data so I know there should be results returned.  Any help is greatly appreciated.  Here is the code and stored procedure I am using.

C# Code:
protected void btnSearchCarrierId_Click(object sender, Infragistics.WebUI.WebDataInput.ButtonEventArgs e)
    {
       
        SqlConnection InsLookUp;
        InsLookUp = new SqlConnection();
        InsLookUp.ConnectionString = ConfigurationManager.ConnectionStrings["123123"].ConnectionString;

        SqlCommand cmd;

        InsLookUp.Open();

        cmd = new SqlCommand("InsCompLookup", InsLookUp);
        cmd.CommandType = CommandType.StoredProcedure;

       
        // Input Parameters
        cmd.Parameters.Add("@CARRIER_NAME", SqlDbType.VarChar, 36).Value = tbCarrierName.Text;
        cmd.Parameters.Add("@CARRIER_ID", SqlDbType.VarChar, 15).Value = tbCarrierId.Text;
        cmd.Parameters.Add("@CITY", SqlDbType.VarChar, 20).Value = tbCarrierCity.Text;
        cmd.Parameters.Add("@STATE", SqlDbType.VarChar, 2).Value = tbCarrierState.Text;


        cmd.Parameters.Add("@ZIP", SqlDbType.VarChar, 9).Value = tbCarrierZip.Text;
        cmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 11).Value = tbCarrierPhone.Text;


        //    execute the command and read the current Referral ID
        cmd.ExecuteNonQuery();
        this.UltraWebGrid3.DataBind();

        if (InsLookUp != null)
        {
            InsLookUp.Close();
        }


    }




SQL Stored Procedure:
USE [Chart Relay]
GO
/****** Object:  StoredProcedure [dbo].[InsCompLookup]    Script Date: 06/25/2008 09:16:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[InsCompLookup]

(
@CARRIER_NAME VARCHAR (36),
@CARRIER_ID VARCHAR (15),
@CITY VARCHAR (20),
@STATE VARCHAR (2),
@ZIP VARCHAR (9),
@PHONE VARCHAR (11)
)

AS
SET NOCOUNT ON

SELECT     INSURANCE_NPI, NAME, CITY, STATE, ZIP, PHONE
FROM         INSURANCE_COMPANY
WHERE     (INSURANCE_COMPANY.INSURANCE_NPI = @CARRIER_ID OR INSURANCE_COMPANY.NAME = @CARRIER_NAME OR INSURANCE_COMPANY.CITY = @CITY OR INSURANCE_COMPANY.STATE = @STATE OR INSURANCE_COMPANY.ZIP = @ZIP)

kwh3856Asked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi kwh3856;

Your most likely cause of your problem is these lines.

        //    execute the command and read the current Referral ID
        cmd.ExecuteNonQuery();

The, cmd.ExecuteNonQuery(), command just returns the number of rows effected by the query, which you are not even using, no data is returned.

Try doing this:

DataSet ds = new DataSet();    // You may want to place this at class level, access from other areas
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "InsCompLookup");   // The second parameter is just a locally defined table name to reference the table

Make sure to set the data source of the grid to ds.Table["InsCompLookup"] and remove the following lines of code.

  InsLookUp.Open();

  if (InsLookUp != null)
  {
      InsLookUp.Close();
  }

And replace this, cmd.ExecuteNonQuery();, with the above 3 lines of code. The SqlDataAdapter will take care of opening and closing the connection automatically.

Fernando
0
 
kwh3856Author Commented:
YOUR AWESOME !!!!!!!!!!!!

That was EXACTLY the answer I was looking for.  Many Many thanks!!!!

Thanks
Kenny
0
 
Fernando SotoRetiredCommented:
Not a problem Kenny, glad I was able to help. ;=)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.