Must declare the scalar variable "@SearchCity"

I am attempting to fill the schema in a data set and keep getting a "Must declare the scalar variable "@SearchCity".  The problem is that I think I have already defined the variable.  Can anyone see what I am missing.  Here is my code.

Thanks
Kenny


  protected SqlDataAdapter CreateProviderAdapter()
    {
        SqlConnection conn = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(ProviderSearch, conn);

        if
            (txtSearchCity.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchCity", txtSearchCity.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchCity", string.Empty));
               
        if
            (txtSearchState.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchState", txtSearchState.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchState", string.Empty));

        if (txtSearchZip.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchZip", txtSearchZip.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchZip", string.Empty));

       
        SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);
        sqlDA.FillSchema(HDSDataSet, SchemaType.Mapped, "PROVIDER");
        sqlDA.Fill(HDSDataSet, "PROVIDER");

        return sqlDA;
    }

    private String ConnString
    {
        get
        {
            //HDS ConnectionString comes from the Web.Config
            return ConfigurationManager.ConnectionStrings["Chart RelayConnectionString"].ConnectionString;

        }
    }

    private String ProviderSearch
    {
        get
            {
                 String srchCmd = "Select PHYSICIAN_NPI, LAST_NAME, FIRST_NAME, ADDRESS_1, CITY, STATE, PHONE from Provider";

                 string whereClause = string.Empty;

                 if (txtSearchCity.Text.Length > 0)
                    whereClause = " WHERE CITY = @SearchCity";
                 if (txtSearchState.Text.Length > 0)
                      {
                         if (whereClause.Length > 0)
                              whereClause += " AND ";

                         else
                             whereClause = " WHERE ";
                             whereClause += "STATE = @SearchState";
                      }
                 if (txtSearchZip.Text != "")
                      {
                         if (whereClause.Length > 0)
                             whereClause += " AND ";

                         else
                             whereClause = " WHERE ";
                             whereClause += "ZIP = @SearchZip";
                      }
                      return srchCmd + whereClause + " ORDER BY WEIGHT";
            }
    }



    protected SqlDataAdapter CreateProviderInsAdapter()
    {
        SqlConnection connProvIns = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(SearchProviderIns, connProvIns);


        if (txtSearchInsur.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchInsur", txtSearchInsur.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchInsur", string.Empty));

        SqlDataAdapter sqlDAphyins = new SqlDataAdapter(cmd);
        sqlDAphyins.FillSchema(HDSDataSet, SchemaType.Mapped, "PHYSINS");
        sqlDAphyins.Fill(HDSDataSet, "PHYSINS");-------------------------<<<<<<<<<<<<<<<<<<<<-------------ERROR Occurs Here
        return sqlDAphyins;
    }

    protected SqlDataAdapter CreateProviderSpecialAdapter()
    {
        SqlConnection connPhysSpecial = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(SearchProvSpecialty, connPhysSpecial);

        if
            (txtSearchSpecial.Text != "")
             cmd.Parameters.Add(new SqlParameter("@SearchSpecialty", txtSearchSpecial.Text));
         else
             cmd.Parameters.Add(new SqlParameter("@SearchSpecialty", string.Empty));

        SqlDataAdapter sqlDAphyspecial = new SqlDataAdapter(cmd);
        sqlDAphyspecial.FillSchema(HDSDataSet, SchemaType.Mapped, "PHYS_SPECIALTIES");
        sqlDAphyspecial.Fill(HDSDataSet, "PHYS_SPECIALTIES");

        return sqlDAphyspecial;
    }


kwh3856Asked:
Who is Participating?
 
vbturboConnect With a Mentor Commented:
perhaps

declare your cmd as cmd1

SqlCommand cmd1 = new SqlCommand(SearchProviderIns, connProvIns);
SqlDataAdapter sqlDAphyins = new SqlDataAdapter(cmd1);

as you have a cmd variable declared once
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as you do this:
>                 if (txtSearchCity.Text.Length > 0)
>                    whereClause = " WHERE CITY = @SearchCity";

you don't need the "else" part in here:

  if (txtSearchCity.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchCity", txtSearchCity.Text));
//        else
//            cmd.Parameters.Add(new SqlParameter("@SearchCity", string.Empty));
               


0
 
kwh3856Author Commented:
I commented those lines and still get the error message.  Is there something else I am missing?

Thanks
Kenny
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
vbturboCommented:
though it should be a new instance of the command object and also should outside scope of the first declaration
but according the err msg it looks like it use's the first cmd object in the second fill

vbturbo
0
 
DotNetThinkerCommented:
Have you tried clearing the DataSet before each Fill?
0
 
townsmaCommented:
I may be going blind in my old age, but there is no definition shown above for the "SearchProviderIns", can you please show this.  I suggect this is probably needing a parameter called @SearchCity, but you are not providing it.  
0
 
kwh3856Author Commented:
Here is all the code.  I did not realize it was not pasted in.

Thanks
Kenny






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

private DataSet mDataSet = null;
    public DataSet HDSDataSet
    {
        get
        {
            if (mDataSet == null)
                mDataSet = new DataSet("HDSDataSet");
            return mDataSet;
        }
    }


   

    protected void PopulateProviderTable()
    {

       
        SqlDataAdapter sqlDA = CreateProviderAdapter();
        SqlDataAdapter sqlDAphyins = CreateProviderInsAdapter();
        SqlDataAdapter sqlDAphyspecial = CreateProviderSpecialAdapter();
        SqlDataAdapter sqlDAphyspecialproc = CreateProviderSpecialProcAdapter();
       
       
        errormessage.Text = ProviderSearch;
        errormessage2.Text = SearchProviderIns;
        errormessage3.Text = SearchProvSpecialty;

       // Create Relations - Setup Key Fields
        DataColumn doctorId = HDSDataSet.Tables["PROVIDER"].Columns["PHYSICIAN_NPI"];
        DataColumn insurId = HDSDataSet.Tables["PHYSINS"].Columns["PHYSICIAN_NPI"];
        DataColumn specialtiesId = HDSDataSet.Tables["PHYS_SPECIALTIES"].Columns["PHYSICIAN_NPI"];

        // Create Relations - Build Relation
        DataRelation specialtiesRelation = new DataRelation("specialtiesRelation", doctorId, specialtiesId);
        DataRelation insurRelation = new DataRelation("insurRelation", doctorId, insurId);
        HDSDataSet.Relations.Clear();
        HDSDataSet.Relations.Add(insurRelation);
        HDSDataSet.Relations.Add(specialtiesRelation);
 
       
    }


    protected SqlDataAdapter CreateProviderAdapter()
    {
        SqlConnection conn = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(ProviderSearch, conn);

        if
            (txtSearchCity.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchCity", txtSearchCity.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchCity", string.Empty));
               
        if
            (txtSearchState.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchState", txtSearchState.Text));
   //     else
    //        cmd.Parameters.Add(new SqlParameter("@SearchState", string.Empty));

        if (txtSearchZip.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchZip", txtSearchZip.Text));
   //     else
     //       cmd.Parameters.Add(new SqlParameter("@SearchZip", string.Empty));

       
        SqlDataAdapter sqlDA = new SqlDataAdapter(cmd);
       
        sqlDA.FillSchema(HDSDataSet, SchemaType.Mapped, "PROVIDER");
        sqlDA.Fill(HDSDataSet, "PROVIDER");

        return sqlDA;
    }

    private String ConnString
    {
        get
        {
            //HDS ConnectionString comes from the Web.Config
            return ConfigurationManager.ConnectionStrings["Chart RelayConnectionString"].ConnectionString;

        }
    }

    private String ProviderSearch
    {
        get
            {
                 String srchCmd = "Select PHYSICIAN_NPI, LAST_NAME, FIRST_NAME, ADDRESS_1, CITY, STATE, PHONE from Provider";

                 string whereClause = string.Empty;

                 if (txtSearchCity.Text.Length > 0)
                    whereClause = " WHERE CITY = @SearchCity";
                 if (txtSearchState.Text.Length > 0)
                      {
                         if (whereClause.Length > 0)
                              whereClause += " AND ";

                         else
                             whereClause = " WHERE ";
                             whereClause += "STATE = @SearchState";
                      }
                 if (txtSearchZip.Text != "")
                      {
                         if (whereClause.Length > 0)
                             whereClause += " AND ";

                         else
                             whereClause = " WHERE ";
                             whereClause += "ZIP = @SearchZip";
                      }
                      return srchCmd + whereClause + " ORDER BY WEIGHT";
            }
    }



    protected SqlDataAdapter CreateProviderInsAdapter()
    {
        SqlConnection connProvIns = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(SearchProviderIns, connProvIns);


        if (txtSearchInsur.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchInsur", txtSearchInsur.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchInsur", string.Empty));

        SqlDataAdapter sqlDAphyins = new SqlDataAdapter(cmd);
        sqlDAphyins.FillSchema(HDSDataSet, SchemaType.Mapped, "PHYSINS");
        sqlDAphyins.Fill(HDSDataSet, "PHYSINS");
        return sqlDAphyins;
    }

    protected SqlDataAdapter CreateProviderSpecialAdapter()
    {
        SqlConnection connPhysSpecial = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(SearchProvSpecialty, connPhysSpecial);

        if
            (txtSearchSpecial.Text != "")
             cmd.Parameters.Add(new SqlParameter("@SearchSpecialty", txtSearchSpecial.Text));
         else
             cmd.Parameters.Add(new SqlParameter("@SearchSpecialty", string.Empty));

        SqlDataAdapter sqlDAphyspecial = new SqlDataAdapter(cmd);
        sqlDAphyspecial.FillSchema(HDSDataSet, SchemaType.Mapped, "PHYS_SPECIALTIES");
        sqlDAphyspecial.Fill(HDSDataSet, "PHYS_SPECIALTIES");

        return sqlDAphyspecial;
    }

    protected SqlDataAdapter CreateProviderSpecialProcAdapter()
    {
        SqlConnection connPhysSpecialProc = new SqlConnection(ConnString);
        SqlCommand cmd = new SqlCommand(SearchProvSpecialtyProc, connPhysSpecialProc);

        if
            (txtSearchSpecialProcCode.Text != "")
            cmd.Parameters.Add(new SqlParameter("@SearchSpecialtyProc", txtSearchSpecialProcCode.Text));
        else
            cmd.Parameters.Add(new SqlParameter("@SearchSpecialtyProc", string.Empty));

        SqlDataAdapter sqlDAphyspecialproc = new SqlDataAdapter(cmd);
        sqlDAphyspecialproc.FillSchema(HDSDataSet, SchemaType.Mapped, "SPECIAL_PROCEDURES");
        sqlDAphyspecialproc.Fill(HDSDataSet, "SPECIAL_PROCEDURES");
        return sqlDAphyspecialproc;
    }


   

   

    private String SearchProviderIns
    {
        get
        {
            // String srchCmd = "Select * from PHYSINS";
            String srchCmd = "Select * from PHYSINS where INSURANCE_CARRIER_NAME = @SearchInsur AND PHYSICIAN_NPI in(SELECT PHYSICIAN_NPI FROM Provider)";
            srchCmd += ProviderSearch;

            if (txtSearchInsur.Text != "")
                srchCmd += " where INSURANCE_CARRIER_NAME = @SearchInsur";
           
            return srchCmd;

        }
    }

    private String SearchProvSpecialty
    {
        get
        {
            String srchCmd = "Select * from PHYS_SPECIALITIES";

            if(txtSearchSpecial.Text != "")
                srchCmd += " where DESCRIPTION = @SearchSpecialty";

            return srchCmd;

        }
    }

    private String SearchProvSpecialtyProc
    {
        get
        {
            String srchCmd = "Select * from SPECIAL_PROCEDURES";

            if (txtSearchSpecialProcCode.Text != "")
                srchCmd += " where CPT = @SearchSpecialtyProc";

            if (txtSearchSpecialProcDesc.Text != "")
                srchCmd += " where DESCRIPTION = @SearchSpecialtyProc";

            return srchCmd;

        }
    }
0
 
kwh3856Author Commented:
After I reconfigured it properly I got it to work with your idea.  Thanks
0
 
DotNetThinkerCommented:
Is it working for you now?
0
 
kwh3856Author Commented:
DotNetThinker,
Yes, it is working now but I have another problem with the search not returning the expected results.  Please look at my post to see if you have any ideas.

Thanks
Kenny
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.