line continuation in asp.net markup

Hello experts,
I have a very long sql in my asp.net markup like this. What line continuation character do I use? The compiler says "if this attribute value is enclosed in quotation marks, the quotation marks must match."

            <asp:SqlDataSource ID="sdsBonuses" runat="server" 
                ConnectionString="<%$ ConnectionStrings:AdHocReportingConnectionString %>" 
                SelectCommand = "select approved_denied_by=case when b.bonus_status = 'Denied' then denied_by when b.bonus_status = 'Approved' then approved_by else null end,
                                            approved_denied_date=case when b.bonus_status = 'Denied' then denied_date when b.bonus_status = 'Approved' then approved_date else null end, b.*,rtrim(p.first_name)+' '+rtrim(p.last_name) Name,rtrim(ps.first_name)+' '+rtrim(ps.last_name) submitter_name 
                                    from bonus_local b, people p , people ps
                                    where ps.network_account = b.created_by and p.employee_id = b.employee_id and b.is_deleted=0 and b.bonus_status in ('Pending Approval','Denied','Approved')
                                    /* and employee_status<>'terminated'  */  
                                    and case b.bonus_status when 'Denied' then datediff(dd,denied_date,getdate()) 
															when 'Approved' then datediff(dd,approved_date,getdate())
															else 0 end < 1
                                    and p.charge_to_location in 
                                    (
	                                    select distinct location_num
	                                    from role_location a, gldm_location b
	                                    where scheme_cd ='PRO' and role_cd <>'spprt'
	                                    and employee_id = (select employee_id from people where network_account = '" + Session["uid"].ToString() + @"')
	                                    and 
	                                    (
	                                    a.location_cd_prefixed = 'div0'+rtrim(b.division_num)
	                                    or
	                                    a.location_cd_prefixed = 'com0'+rtrim(b.company_num)
	                                    or
	                                    a.location_cd_prefixed = 'OPE0'+rtrim(b.operation_num)
	                                    or
	                                    a.location_cd_prefixed = 'BUS0'+rtrim(b.business_unit_num)
	                                    or
	                                    a.location_cd_prefixed = 'TER0'+rtrim(b.territory_num)
	                                    or
	                                    a.location_cd_prefixed = 'MAR0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'ARE0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'Meg0'+rtrim(b.mega_location_num) 
	                                    or
	                                    a.location_cd_prefixed = location_num
                                        )
                                    )
                                    order by rtrim(p.first_name)+' '+rtrim(p.last_name) "
               >
            </asp:SqlDataSource>

Open in new window

LVL 3
dpiccoAsked:
Who is Participating?
 
Vikram Singh SainiConnect With a Mentor Software Engineer cum AD DeveloperCommented:
Hi,

I have a very long sql in my asp.net markup like this. What line continuation character do I use?

There is no line continuation in .aspx or .html (as .aspx page would get converted to html one after compilation or at runtime). But solution exists for prob.(The HTML Document Character Set)

I have changed some of characters (<,> and ") in query to their Decimal Code(&#60;, &#62; and &quot;) respectively. No need to worry for them as they would get converted to their respective characters in html generated.

However I was not able to test your query. Let us know if it helped you or not. Below is modified code(only special characters):

<asp:SqlDataSource ID="sdsBonuses" runat="server" ConnectionString="<%$ ConnectionStrings:AdHocReportingConnectionString %>"
            SelectCommand="select approved_denied_by=case when b.bonus_status = 'Denied' then denied_by when b.bonus_status = 'Approved' then approved_by else null end,
                                            approved_denied_date=case when b.bonus_status = 'Denied' then denied_date when b.bonus_status = 'Approved' then approved_date else null end, b.*,rtrim(p.first_name)+' '+rtrim(p.last_name) Name,rtrim(ps.first_name)+' '+rtrim(ps.last_name) submitter_name 
                                    from bonus_local b, people p , people ps
                                    where ps.network_account = b.created_by and p.employee_id = b.employee_id and b.is_deleted=0 and b.bonus_status in ('Pending Approval','Denied','Approved')
                                    /* and employee_status&#60;&#62;'terminated'  */  
                                    and case b.bonus_status when 'Denied' then datediff(dd,denied_date,getdate()) 
															when 'Approved' then datediff(dd,approved_date,getdate())
															else 0 end &#60; 1
                                    and p.charge_to_location in 
                                    (
	                                    select distinct location_num
	                                    from role_location a, gldm_location b
	                                    where scheme_cd ='PRO' and role_cd &#60;&#62;'spprt'
	                                    and employee_id = (select employee_id from people where network_account = '&quot; + Session[&quot;uid&quot;].ToString() + &quot;')
	                                    and 
	                                    (
	                                    a.location_cd_prefixed = 'div0'+rtrim(b.division_num)
	                                    or
	                                    a.location_cd_prefixed = 'com0'+rtrim(b.company_num)
	                                    or
	                                    a.location_cd_prefixed = 'OPE0'+rtrim(b.operation_num)
	                                    or
	                                    a.location_cd_prefixed = 'BUS0'+rtrim(b.business_unit_num)
	                                    or
	                                    a.location_cd_prefixed = 'TER0'+rtrim(b.territory_num)
	                                    or
	                                    a.location_cd_prefixed = 'MAR0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'ARE0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'Meg0'+rtrim(b.mega_location_num) 
	                                    or
	                                    a.location_cd_prefixed = location_num
                                        )
                                    )
                                    order by rtrim(p.first_name)+' '+rtrim(p.last_name)"></asp:SqlDataSource>

Open in new window

0
 
abhinayp86Commented:
try this

SelectCommand = '<%# "select approved_denied_by=case when b.bonus_status = 'Denied' then denied_by when b.bonus_status = 'Approved' then approved_by else null end, _
                                            approved_denied_date=case when b.bonus_status = 'Denied' then denied_date when b.bonus_status = 'Approved' then approved_date else null end, b.*,rtrim(p.first_name)+' '+rtrim(p.last_name) Name,rtrim(ps.first_name)+' '+rtrim(ps.last_name) submitter_name 
                                    from bonus_local b, people p , people ps
                                    where ps.network_account = b.created_by and p.employee_id = b.employee_id and b.is_deleted=0 and b.bonus_status in ('Pending Approval','Denied','Approved')
                                    and employee_status<>'terminated'  */  
                                    and case b.bonus_status when 'Denied' then datediff(dd,denied_date,getdate()) 
															when 'Approved' then datediff(dd,approved_date,getdate())
															else 0 end < 1
                                    and p.charge_to_location in 
                                    (
	                                    select distinct location_num
	                                    from role_location a, gldm_location b
	                                    where scheme_cd ='PRO' and role_cd <>'spprt'
	                                    and employee_id = (select employee_id from people where network_account = '" + Session["uid"].ToString() + @"')
	                                    and 
	                                    (
	                                    a.location_cd_prefixed = 'div0'+rtrim(b.division_num)
	                                    or
	                                    a.location_cd_prefixed = 'com0'+rtrim(b.company_num)
	                                    or
	                                    a.location_cd_prefixed = 'OPE0'+rtrim(b.operation_num)
	                                    or
	                                    a.location_cd_prefixed = 'BUS0'+rtrim(b.business_unit_num)
	                                    or
	                                    a.location_cd_prefixed = 'TER0'+rtrim(b.territory_num)
	                                    or
	                                    a.location_cd_prefixed = 'MAR0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'ARE0'+rtrim(b.market_num)
	                                    or
	                                    a.location_cd_prefixed = 'Meg0'+rtrim(b.mega_location_num) 
	                                    or
	                                    a.location_cd_prefixed = location_num
                                        )
                                    )
                                    order by rtrim(p.first_name)+' '+rtrim(p.last_name)"%>' 

Open in new window


its either <%# (asp.net 3.5) or <%$(not sure about 4.0)
0
 
Paul JacksonSoftware EngineerCommented:
The continuation character is an underscore ensure there is a space between the last character on the line and the underscore.
I think that you will possibly need to close each line with a " and use an & as well like this :


"some string " & _
"another string " & _
...
0
 
käµfm³d 👽Connect With a Mentor Commented:
You won't be able to do the concatenation you have within this line:

and employee_id = (select employee_id from people where network_account = '" + Session["uid"].ToString() + @"')

Open in new window


You would need to add a placeholder and add a SessionParameter to the command's SelectParameters collection:

...

                            and employee_id = (select employee_id from people where network_account = @uid)
                            and 
                            (
                            a.location_cd_prefixed = 'div0'+rtrim(b.division_num)
                            or
                            a.location_cd_prefixed = 'com0'+rtrim(b.company_num)
                            or
                            a.location_cd_prefixed = 'OPE0'+rtrim(b.operation_num)
                            or
                            a.location_cd_prefixed = 'BUS0'+rtrim(b.business_unit_num)
                            or
                            a.location_cd_prefixed = 'TER0'+rtrim(b.territory_num)
                            or
                            a.location_cd_prefixed = 'MAR0'+rtrim(b.market_num)
                            or
                            a.location_cd_prefixed = 'ARE0'+rtrim(b.market_num)
                            or
                            a.location_cd_prefixed = 'Meg0'+rtrim(b.mega_location_num) 
                            or
                            a.location_cd_prefixed = location_num
                            )
                        )
                        order by rtrim(p.first_name)+' '+rtrim(p.last_name) "
   >
   <SelectParameters>
       <asp:SessionParameter SessionField="uid" Name="@uid" />
   </SelectParameters>
</asp:SqlDataSource>

Open in new window

0
 
dpiccoAuthor Commented:
thank you. That worked.
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.