[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-06-26
3
Medium Priority
?
189 Views
Last Modified: 2013-12-16
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)

0
Comment
Question by:kwh3856
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 21878980
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
 

Author Closing Comment

by:kwh3856
ID: 31471155
YOUR AWESOME !!!!!!!!!!!!

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

Thanks
Kenny
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 21880637
Not a problem Kenny, glad I was able to help. ;=)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question