Solved

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

Posted on 2008-10-28
6
536 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now