Solved

How do you debug a C# data relation that does not seem to be working properly?

Posted on 2008-10-28
6
540 Views
Last Modified: 2013-12-17
I am trying to build a relation between two tables.  Each table has two records in it.  The key field is MPI.  It exist in both tables and has the same data in each of the tables for the MPI.  Should be very simple but as luck would have it, it is not working out.  

When I set a break point and view the data set with the magnifying glass I can see each table with the two records.  Is their any tool or command I can use to display the relationship in the dataset?

Here is my code in case someone can see what I might be doing wrong.

---------------------------------------------------------------------------------------------------------------------------------------------------------

protected void btnPreAuthSearch_Click(object sender, Infragistics.WebUI.WebDataInput.ButtonEventArgs e)
    {
        bool authenticated;
            authenticated = Convert.ToBoolean(Session["authenticated"]);
            
            
            if (authenticated)
            {
                  PopulatePatientDS();
           
            }

      }

      //------------   Patient Search Tab
      public DataSet HDSDataSet
      {
            get
            {
                  if (Cache["HDSDataSet"] == null)
                        Cache["HDSDataSet"] = new DataSet("HDSDataSet");

                  return (DataSet)Cache["HDSDataSet"];
            }
      }
      //------------   Patient Search Tab
      protected void PopulatePatientDS()
      {

        SqlDataAdapter sqlDA = CreatePatientAdapter();
        SqlDataAdapter sqlDAPreAuth = CreatePreAuthAdapter();
        HDSDataSet.Clear();
        sqlDA.FillSchema(HDSDataSet, SchemaType.Source);
        sqlDA.Fill(HDSDataSet, "Patient");
        sqlDAPreAuth.Fill(HDSDataSet, "Pre_Auth");


       
        try
        {
            HDSDataSet.Relations.Remove("PatientRelation");
            HDSDataSet.Relations.Add("PatientRelation",
                HDSDataSet.Tables["Patient"].Columns["MPI"],
                HDSDataSet.Tables["Pre_Auth"].Columns["MPI"]);
        }
        catch (System.Exception x)
        {
            string s = x.Message;
            string m = s;
        }


        this.PreAuthWebGrid1.DataSource = HDSDataSet.Tables["Patient"];
        this.PreAuthWebGrid1.DisplayLayout.AutoGenerateColumns = false;
        PreAuthWebGrid1.DisplayLayout.Bands.Clear();
        this.PreAuthWebGrid1.DataBind();

       
       
       
    }



      //------------   Patient Adapter
      protected SqlDataAdapter CreatePatientAdapter()
      {

        string physicianNPI;
        physicianNPI = Session["physicianNPI"].ToString();

            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(SearchString, conn);
        cmd.Parameters.Add("@REFERRING_PROVIDER_NPI", SqlDbType.VarChar).Value = physicianNPI;
        cmd.Parameters.Add(new SqlParameter("@SearchFn", tbPreAuthFname.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchLn", tbPreAuthLname.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchAddress", tbPreAuthAddress.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchDOB", tbPreAuthDOB.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchSSN", tbPreAuthSSN.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchDL", tbPreAuthDL.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchPhn", tbPreAuthPhone.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchBillingId", tbPreAuthAccountNum.Text));
        cmd.Parameters.Add(new SqlParameter("@SearchSex", tbPreAuthSex.Text));


            SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);
            return sqlDA;
      }

      //------------   Patient Search Tab
      private String ConnString
      {
            get
            {
                  //HDS ConnectionString comes from the Web.Config
                  return ConfigurationManager.ConnectionStrings["123232ConnectionString"].ConnectionString;

            }
      }

      //------------   Patient Search Tab
      private String SearchString
      {
            get
            {
            String srchCmd = "SELECT PATIENT.MPI, PATIENT.LAST_NAME, PATIENT.FIRST_NAME, PRIMARY_INSURANCE.PLAN_NAME, PATIENT.DATE_OF_BIRTH, PATIENT.SEX FROM PATIENT LEFT JOIN PRIMARY_INSURANCE ON PATIENT.MPI = PRIMARY_INSURANCE.MPI WHERE PATIENT.LAST_NAME = @SearchLn OR PATIENT.FIRST_NAME = @SearchFn";
                  return srchCmd;

            }
    }



    //------------   PreAuthAdapter
    protected SqlDataAdapter CreatePreAuthAdapter()
    {

        SqlConnection conn = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(PreAuthSearchString, conn);
        SqlDataAdapter sqlDAPreAuth = new SqlDataAdapter(cmd);
        return sqlDAPreAuth;
    }

   

    //------------   PreAuth Search Command
    private String PreAuthSearchString
    {
        get
        {
            String srchCmd = "SELECT PRE_AUTH.MPI, PRE_AUTH.INSURANCE_NAME, PRE_AUTH.PRE_AUTH_NUM, PRE_AUTH.EXPIRY_DATE, PRE_AUTH.VISITS FROM PRE_AUTH";
            return srchCmd;

        }
    }







--------------------------------------------
Here is the primary table layout

CREATE TABLE [dbo].[PATIENT](
      [MPI] [numeric](35, 0) NOT NULL,
      [NEWPATIENT] [bit] NULL,
      [SSN] [numeric](9, 0) NULL,
      [DRIVER_LICENSE] [numeric](15, 0) NULL,
      [LAST_NAME] [varchar](20) NULL,
      [FIRST_NAME] [varchar](20) NULL,
      [FIRST_NAME_SEC] [numeric](4, 0) NULL,
      [ADDRESS1] [varchar](20) NULL,
      [ADDRESS2] [varchar](20) NULL,
      [CITY] [varchar](20) NULL,
      [STATE] [varchar](3) NULL,
      [ZIP] [numeric](9, 0) NULL,
      [SEX] [varchar](1) NULL,
 CONSTRAINT [PK__PATIENT__7E6CC920] PRIMARY KEY CLUSTERED
(
      [MPI] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]




-------------------------------------------------
Here is the child table layout



CREATE TABLE [dbo].[PRE_AUTH](
      [MPI] [numeric](35, 0) NOT NULL,
      [REFERRAL_QUEUE_ID] [numeric](15, 0) NULL,
      [INSURANCE_COMPANY_CODE] [varchar](35) NULL,
      [INSURANCE_NAME] [varchar](35) NULL,
      [PRE_AUTH_NUM] [varchar](20) NULL,
      [EXPIRY_DATE] [datetime] NULL,
      [VISITS] [int] NULL
) ON [PRIMARY]


0
Comment
Question by:kwh3856
  • 4
  • 2
6 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22830527
Are you having problems displaying any data in an Infragistics UltraWebGrid, by any chance?
0
 

Author Comment

by:kwh3856
ID: 22832026
Yes exactly.  When I go to add the columns the first table which is band 0 works fine.  Whenever I add the second tables columns band 1 it throws a null exception.  The oddest thing is I have done this before and it worked with no problems.  I only assumed it had to be with the data relation except as best as I can tell there is no problems with the data relation.  Here is the code I am using to setup the grid.  Can you see if I am missing anything?
 
-----------------------------------------------------------------------------------------------

protected void PreAuthWebGrid1_InitializeLayout(object sender, LayoutEventArgs e)
{
UltraGridColumn mpiCol = new UltraGridColumn();
mpiCol.BaseColumnName = "MPI";
mpiCol.Key = "MPI";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(mpiCol);
UltraGridColumn lnCol = new UltraGridColumn();
lnCol.BaseColumnName = "LAST_NAME";
lnCol.Key = "LAST_NAME";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(lnCol);
UltraGridColumn fnCol = new UltraGridColumn();
fnCol.BaseColumnName = "FIRST_NAME";
fnCol.Key = "FIRST_NAME";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(fnCol);
UltraGridColumn dobCol = new UltraGridColumn();
dobCol.BaseColumnName = "DATE_OF_BIRTH";
dobCol.Key = "DATE_OF_BIRTH";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(dobCol);
UltraGridColumn addrCol = new UltraGridColumn();
addrCol.BaseColumnName = "ADDRESS1";
addrCol.Key = "ADDRESS1";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(addrCol);
UltraGridColumn cityCol = new UltraGridColumn();
cityCol.BaseColumnName = "CITY";
cityCol.Key = "CITY";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(cityCol);
UltraGridColumn stCol = new UltraGridColumn();
stCol.BaseColumnName = "STATE";
stCol.Key = "STATE";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(stCol);
 
UltraGridColumn ssnCol = new UltraGridColumn();
ssnCol.BaseColumnName = "SSN";
ssnCol.Key = "SSN";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(ssnCol);
UltraGridColumn phnCol = new UltraGridColumn();
phnCol.BaseColumnName = "PHONE";
phnCol.Key = "PHONE";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns.Add(phnCol);
UltraGridColumn insNameCol = new UltraGridColumn();
insNameCol.BaseColumnName = "INSURANCE_NAME";
insNameCol.Key = "INSURANCE_NAME";
this.PreAuthWebGrid1.DisplayLayout.Bands[1].Columns.Add(insNameCol);
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[0].Header.Caption = "MPI";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[1].Header.Caption = "LAST_NAME";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[2].Header.Caption = "FIRST_NAME";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[3].Header.Caption = "DOB";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[4].Header.Caption = "ADDRESS";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[5].Header.Caption = "CITY";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[6].Header.Caption = "STATE";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[7].Header.Caption = "SSN";
this.PreAuthWebGrid1.DisplayLayout.Bands[0].Columns[8].Header.Caption = "HM Phone";
this.PreAuthWebGrid1.DisplayLayout.Bands[1].Columns[0].Header.Caption = "INS Name";
 
 
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 22832334
Why are you clearing the Bands first?

PreAuthWebGrid1.DisplayLayout.Bands.Clear();

Or, do you have the number of band objects that you expect after you set the DataSource for the grid?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:kwh3856
ID: 22832862
I had originally did that because sometimes the layout would show previous data that was searched.  The new searches would just show appended data to the grid view.  I tried taking it out but still got the same problem.
Object reference not set to an instance of an object.
 
 
 
0
 

Author Comment

by:kwh3856
ID: 22849341
Found my problem. I did not realize it but I was trying to create a relation to a table that was a child of a child.  I was trying to go from A to C instead of from A to B and then B to C.
0
 

Author Closing Comment

by:kwh3856
ID: 31510977
Thank you very much for trying to help me with this problem.  I realize now that I did not provide enough information for the problem to be solved.  As it turns out, it was how the tables were related to each other and not the actual data relation setup in C#.  I wanted to award you these points for trying to help me.

Thanks
Kenny
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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