passing ISNULL value

waelhaj

hello [img]style_emoticons/<#EMO_DIR#>/unsure.gif" style="vertical-align:middle" emoid=":unsure:" border="0" alt="unsure.gif" /> i have sql query SELECT ID, sNumber, dbo.studentNameFromID(ID) AS fullName, dbo.sexName(sSex) AS sSex, sAge, sTel, sMobile, dbo.addressFromID(sAddress_ID) AS aAddress, dbo.studentBalanceAll(ID) AS _balance FROM student WHERE (ID = ISNULL(@student_ID, ID)) AND (sSex = ISNULL(@sSex, sSex)) AND (sAddress_ID = ISNULL(@sAddress, sAddress_ID)) when i pass @student_ID=3 its works correctly and i get me one student , but i want pass DBNull.Value to give me all students how i can pass DBNull value to parameter ? thanks alot[/img]

ipong

SELECT ID
FROM student
WHERE (ID = @student_ID OR @student_ID IS NULL)

waelhaj

ipong wrote: »

SELECT ID
FROM student
WHERE (ID = @student_ID OR @student_ID IS NULL)

appreciate for quick answer

why i should add two parameters one to sql query and another parameter to parameters to get a result.

https://drive.google.com/file/d/1p6A4qyGo4i...iew?usp=sharing

https://drive.google.com/file/d/1hQ7hlvq1-o...iew?usp=sharing

thanks

ipong

open your frx file with notepad:

'SelectCommand' is linked to 'commandparameter' then 'commandparameter' is linked to 'Parameter'

that is fastreport's logic

  <Dictionary>
    <MySqlDataConnection Name="Connection" ConnectionString="rijcmlqomabmqtwD7y3BaVO988Xrns8n0+Fpps4V7SdlS+EOKSt6VyZfqSt29GRRejVG9nCFPfhHgyUb95WzdT/V7aP9g==">
      <TableDataSource Name="Table" DataType="System.Int32" Enabled="true" SelectCommand="SELECT A.actor_id, A.first_name, A.last_name, A.last_update
FROM `actor` A
WHERE A.first_name=@p1 AND A.last_name=@p2
">
        <Column Name="actor_id" DataType="System.UInt16"/>
        <Column Name="first_name" DataType="System.String"/>
        <Column Name="last_name" DataType="System.String"/>
        <Column Name="last_update" DataType="System.DateTime"/>
        <CommandParameter Name="@p1" DataType="253" Size="45" Expression="[Parameter1]" DefaultValue="JOE"/>
        <CommandParameter Name="@p2" DataType="253" Size="45" Expression="[Parameter2]" DefaultValue="SWANK"/>
      </TableDataSource>
    </MySqlDataConnection>
    <Parameter Name="Parameter1" DataType="System.String"/>
    <Parameter Name="Parameter2" DataType="System.String"/>
  </Dictionary>