Solved

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

Posted on 2008-10-28
6
539 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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