CODEWITHSUNDEEP
mysqlsqlwhere-clausemysql-workbench
user682417
user682417

Reputation: 1518

trying to attach the where clause to sql statement

HiI am trying to attach the where clause to sql statement like this 

    public static DataTable paymentType(string paymenttype, string ddproviders, string overdue)
    { 

                string paymenttypestr = "";
    string ddproviderstr = "";


    if (paymenttype != "")
    {
        paymenttypestr = string.Format("AND membertomships.memberToMship_PayMethod = '{0}'", paymenttype);        

    }
    if (ddproviders != "")
    {
        ddproviderstr = string.Format("AND ddproviders.ddProvider_Name = '{0}'", ddproviders);

    }
    if (overdue == "OverDue-Now")
    { 
       string sql += @"WHERE memberpaysched.memberPaySched_dateDue < NOW() AND memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid ";

    }

    string  sql = string.Format(
                @"SELECT    members.member_Id,
                   members.member_Lastname As Last_name,
                   members.member_Firstname AS First_name,
                   members.member_PostCode  As Post_Code, 
                   ddaccounts.ddAccount_DdReference As dd_reference,
                   ddproviders.ddProvider_Name As dd_providername,
                   memberToMship_ChargePerPeriod As monthly_amount,
                   mshiptypes.mshipType_Name As Membership_type,
                   mshipstatustypes.mshipStatusType_Name As Status,
                   membertomships.memberToMship_EndDate As Expiry_Date,
                   membertomships.memberToMship_PayMethod As payment_method
                   FROM members 
                   LEFT JOIN membertomships ON membertomships.member_Id = members.member_Id
                   LEFT JOIN memberpaysched ON memberpaysched.memberPaySched_memberId = members.member_Id
                   LEFT OUTER JOIN ddaccounts ON ddaccounts.member_Id = members.member_Id
                   LEFT OUTER JOIN  ddproviders ON  ddaccounts.ddProvider_Id=ddproviders.ddProvider_Id 
                   LEFT JOIN   mshipstatustypes ON mshipstatustypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
                   LEFT JOIN   mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
                   LEFT JOIN   mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
                   WHERE       members.member_Active LIKE 'y%'
                   AND               mshipoptions.mshipOption_Period = 'month' 
                   AND               (mshipstatustypes.mshipStatusType_Id <> 5)
                   {0}
                   {1}     
                   ORDER BY members.member_Lastname",
                   paymenttypestr, ddproviderstr);
      return getdata(sql,mf);
    }

but it was giving error at this line like ";" this symbol is needed;

    string sql += @"WHERE memberpaysched.memberPaySched_dateDue < NOW() AND memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid ";

I want to add this where clause if overdue == "overdue-now"

how can i add this where clause according to condition

would any one help on this ....

Modified Query :   string sql += @"memberpaysched.memberPaySched_dateDue < NOW() AND memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid";

still it was showing this error "Invalid EXPRESSION TERM +="

Upvotes: 0

Views: 181

Answers (3)

abarrington
abarrington

Reputation: 166

So if overdue == "OverDue-Now" you are declaring a string of sql and +=ing it? But then you overwrite the string you thought you just created.

I think this is what you're trying to accomplish:

if (overdue == "OverDue-Now")     
{
   string isoverduestr = @"AND memberpaysched.memberPaySched_dateDue < NOW() AND memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid ";      
}
else
{
   string isoverduestr = @"";
}

string  sql = string.Format(
            @"SELECT    members.member_Id,
               members.member_Lastname As Last_name,
               members.member_Firstname AS First_name,
               members.member_PostCode  As Post_Code, 
               ddaccounts.ddAccount_DdReference As dd_reference,
               ddproviders.ddProvider_Name As dd_providername,
               memberToMship_ChargePerPeriod As monthly_amount,
               mshiptypes.mshipType_Name As Membership_type,
               mshipstatustypes.mshipStatusType_Name As Status,
               membertomships.memberToMship_EndDate As Expiry_Date,
               membertomships.memberToMship_PayMethod As payment_method
               FROM members 
               LEFT JOIN membertomships ON membertomships.member_Id = members.member_Id
               LEFT JOIN memberpaysched ON memberpaysched.memberPaySched_memberId = members.member_Id
               LEFT OUTER JOIN ddaccounts ON ddaccounts.member_Id = members.member_Id
               LEFT OUTER JOIN  ddproviders ON  ddaccounts.ddProvider_Id=ddproviders.ddProvider_Id 
               LEFT JOIN   mshipstatustypes ON mshipstatustypes.mshipStatusType_Id = membertomships.mshipStatusType_Id
               LEFT JOIN   mshipoptions ON mshipoptions.mshipOption_Id = membertomships.mshipOption_Id
               LEFT JOIN   mshiptypes ON mshiptypes.mshipType_Id = mshipoptions.mshipType_Id
               WHERE       members.member_Active LIKE 'y%'
               AND               mshipoptions.mshipOption_Period = 'month' 
               AND               (mshipstatustypes.mshipStatusType_Id <> 5)
               {0}
               {1}
               {2}     
               ORDER BY members.member_Lastname",
               paymenttypestr, ddproviderstr, isoverduestr);

Upvotes: 1

StevieG
StevieG

Reputation: 8709

I'm no .NET expert, but I'd probably change:

if (overdue == "OverDue-Now")     
{
     string sql += @"WHERE memberpaysched.memberPaySched_dateDue < NOW() AND memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid ";      
}

to:

if (overdue == "OverDue-Now")     
{
     sql += @"AND memberpaysched.memberPaySched_dateDue < NOW() AND   memberpaysched.memberPaySched_amountDue > memberpaysched.memberPaySched_amountPaid ";      
}

(i.e. remove the string keyword, and substitute AND for WHERE)

and put it AFTER the main SQL block (i.e. where you're declaring your string sql variable)..

Upvotes: 0

Ian Jacobs
Ian Jacobs

Reputation: 5501

On 1st glance you have 2 where clauses. Change the one you're adding to an AND and see what happens.

Upvotes: 1

Related Questions