尝试将 where 子句附加到 sql 语句

Question

您好，我正在尝试将 where 子句附加到这样的 sql 语句中，

    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);
    }

但它在这一行给出了错误，如“;”需要这个符号；

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

我想添加这个 where 子句 if overdue == "overdue-now"

我怎样才能根据条件添加这个 where 子句，

任何人都会对此提供帮助....

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

仍然显示此错误 “无效的表达术语+=”

Answer 1

乍一看你有 2 个 where 子句。更改要添加到 AND 的值，看看会发生什么。

Answer 2

因此，如果 overdue == "OverDue-Now" 您正在声明一串 sql 并 += ing 它？但随后您会覆盖您认为刚刚创建的字符串。

我认为这就是您想要实现的目标：

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);

Answer 3

我不是 .NET 专家，但我可能会将：更改

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

为：（

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

即删除字符串关键字，并用 AND 替换 WHERE）

并将其放在主 SQL 块之后（即声明字符串 sql 变量的位置） ..