Parameter not passing on Delete

Interesting problem, selecting and updating (with reference to a Form View) works great. Whenever I try to delete I get this error:

Must declare the variable '@.template_id'.

Description: Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@.template_id'.

Here is my code:

<asp:SqlDataSource ID="SqlTemplateDS" runat="server"
SelectCommand="SELECT [template_id], [client_id], [language], [label], [email_default], [email_template], [event_template] FROM [CMT] ORDER BY [template_id]"
DeleteCommand="DELETE FROM CMT WHERE (template_id = @.template_id)"
ConnectionString="<%$ ConnectionStrings:BrandMSConnectionString %>">
</asp:SqlDataSource
<asp:GridView ID="grdTemplates" runat="server" AutoGenerateColumns="False"
DataKeyNames="template_id"
DataSourceID="SqlTemplateDS"
Font-Names="Verdana"
Font-Size="Small"
ForeColor="Black"
Width="100%"
OnSelectedIndexChanged="grdTemplates_SelectedIndexChanged"
CellPadding="2">
<Columns>
<asp:BoundField DataField="template_id" HeaderText="ID" ReadOnly="True" SortExpression="template_id" />
<asp:BoundField DataField="client_id" HeaderText="Client" SortExpression="client_id" />
<asp:BoundField DataField="language" HeaderText="Language" SortExpression="language" />
<asp:BoundField DataField="label" HeaderText="Label" SortExpression="label" />
<asp:CheckBoxField DataField="email_default" HeaderText="Default" SortExpression="email_default" />
<asp:CheckBoxField DataField="email_template" HeaderText="Email" SortExpression="email_template" />
<asp:CheckBoxField DataField="event_template" HeaderText="Event" SortExpression="event_template" />
<asp:TemplateField HeaderText="Options">
<ItemTemplate>
<asp:LinkButton ID="btnModify" CommandName="Select" runat="server" ForeColor="Blue">Edit</asp:LinkButton> |
<asp:LinkButton ID="btnDelete" CommandName="Delete" runat="server" ForeColor="Blue">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="Khaki" />
<AlternatingRowStyle BackColor="Beige" />
</asp:GridView>

Any ideas?

ChrisI solved the problem by using the "original_" prefix in front sql variable so it reads:

DeleteCommand="DELETE FROM CMT WHERE (template_id = @.original_template_id)"

I think its pretty poor that there wasn't better documentation regarding this issue. Apparently ifany of the items in DataKeyNames areshown in a databound column, updating and deleting actions need to specificy whick key value to use. In this case using the prefix "original_" signals to the control which parameter value to use.

I hope this helps anyone who is having similar problems in the future.

Parameter as Column Name?

Greetings,

I am developing a search form that uses a DDL to select a column name and a text box to define a search term. Code follows:

1cmdSearch = New SqlCommand( "select * from sites where @.columnName like @.sTerm order by site_name", conSites)23cmdSearch.Parameters.Add( "@.columnName", SqlDbType.Text).Value=ColumnParam4cmdSearch.Parameters.Add( "@.sTerm", SqlDbType.Text).Value=searchParam

The problem I'm running into is that the @.variable appears to imply single quotes about the value. This is fine for @.sTerm, I just removed the quotes from the concatenation and it reads the value correctly, but it appears to break @.columnName and the query returns no values.

If I manually substitute an appropriate column for @.columnName sans single quotes, it works.

How can I eliminate the implied single quotes from @.columnName so that the query will read it properly?

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

Thanks for the advice, but I can't use stored procedures for this project, or I would've done it that way from the beginning.

Is there any way to define the @.variable to eliminate the implied quotes?

ps2goat:

You need to build your statement dynamically within your xp and execute it using sp_executesql system stored procedure. (Don't use the column variable in your sql.)

You should create a stored procedure to handle the building and execution of the string to make the process easier and reusable. Use parameters (like you did in your current sql statement, just not for a column or table name) to prevent injection attacks.

http://msdn2.microsoft.com/en-us/library/ms175170.aspx

|||

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

|||

Thanks, this did the trick. I'd tried doing it this way before but couldn't work out the syntax.

Much obliged.

ps2goat:

You can build the SQL dynamically with the column name, but still keep parameters for everything else. This will still limit sql injections.

A quick example:

cmdSearch =New SqlCommand("select * from sites where " & ColumnParam &" like @.sTerm order by site_name", conSites)' continue with code as normal, but no column parameter.

sql

Paging Reports

Hi All

I created a report with a matrix on the form and managed to get the results "not" to page when viewed on the web by selecting the "Fit matrix to one page if possible" checkbox.

I created a new report looking at the same data this time using a table and no matter what i do the report keeps paging!!!

Anyone got any advice on how to stop this? Why does it handle a table differently than a table.

Thanks very much

KeepTogether is not currently supported in interactive renderers (HTML and preview). You can try changing the InteractiveHeight property on the report to 0 in order to get the entire report on one page.|||

Setting the Interactive Height to 0in did the trick

aka

<InteractiveHeight>0in</InteractiveHeight>

Cheers

Pagination Issue when Rending to PDF

I am having a pagination issue with a report when I render to PDF. The
report is a patient form that is based on a single record that is returned
from a stored procedure. Most of the records are output fine, but some
records are not paginating properly and leave 3/4 page of blank space. The
field that follows on the next page is short and should definitely fit on
that page. The report renders fine in HTML, but the requirement is a PDF
format.
The body of the report is contained in a table. Each field of the report is
contained in its own row. If the field is blank, the entire row is hidden so
that only populated data is output in the report.
In troubleshooting this, I have removed the logic that hides the rows, but
the pagination is still not correct.
I have also made sure that the data is clean and doesn't have whitespace.
Any suggestions as to how to fix this problem?
TIAOn Dec 18, 8:47 pm, JC <J...@.discussions.microsoft.com> wrote:
> I am having a pagination issue with a report when I render to PDF. The
> report is a patient form that is based on a single record that is returned
> from a stored procedure. Most of the records are output fine, but some
> records are not paginating properly and leave 3/4 page of blank space. The
> field that follows on the next page is short and should definitely fit on
> that page. The report renders fine in HTML, but the requirement is a PDF
> format.
> The body of the report is contained in a table. Each field of the report is
> contained in its own row. If the field is blank, the entire row is hidden so
> that only populated data is output in the report.
> In troubleshooting this, I have removed the logic that hides the rows, but
> the pagination is still not correct.
> I have also made sure that the data is clean and doesn't have whitespace.
> Any suggestions as to how to fix this problem?
> TIA
You will want to check the width of the report. Normally, if it is
wider than 6.5" in design view, it will wrap once exported to PDF.
Also, if you are using a table control, you will want to put it into a
rectangle control, this should shrink the extra space. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the quick reply. The report is slightly wider than 6.5" in design
mode to incorporate the header (which renders fine). The fields before and
after the blank space are very short (like 1 word and a label).
I did try putting the table in a table control, but that didn't do anything.|||On Dec 18, 9:43 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks for the quick reply. The report is slightly wider than 6.5" in design
> mode to incorporate the header (which renders fine). The fields before and
> after the blank space are very short (like 1 word and a label).
> I did try putting the table in a table control, but that didn't do anything.
You're welcome. Did you mean you put it into a table control or a
rectangle control?
Enrique Martinez
Sr. Software Consultant|||Sorry. I meant that I put the table control inside the rectangle control. I
just removed all code that controls the visibility property for each row, and
the pagination problem went away.
The problem is that the customer only wants rows of the table to appear that
have data. Here's the code on the visiblity property of the tablerow:
=iif(Fields!FirstSignature.Value = string.empty, True, False)
It does work as it is supposed to, but it seems that the space is still
being allocated for the row.
BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
the same results.|||On Dec 18, 11:05 pm, JC <J...@.discussions.microsoft.com> wrote:
> Sorry. I meant that I put the table control inside the rectangle control. I
> just removed all code that controls the visibility property for each row, and
> the pagination problem went away.
> The problem is that the customer only wants rows of the table to appear that
> have data. Here's the code on the visiblity property of the tablerow:
> =iif(Fields!FirstSignature.Value = string.empty, True, False)
> It does work as it is supposed to, but it seems that the space is still
> being allocated for the row.
> BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
> the same results.
You might try =IIF(Fields!FirstSignature.Value = Nothing, True, False)
or =IIF(IsNothing(Fields!FirstSignature.Value), True, False)
Either should do what you want. Make sure you set the property for
the row and not the individual cells/textboxes.|||Thanks, Toolman.
I will try that too. Is using the table + row format to hide empty fields
the best practice to accomplish this? I was also going to try to set the
height to 0 if it was going to be hidden. It just seems that SSRS is taking
the empty rows into consideration when making the pagination decision.|||On Dec 19, 2:32 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks, Toolman.
> I will try that too. Is using the table + row format to hide empty fields
> the best practice to accomplish this? I was also going to try to set the
> height to 0 if it was going to be hidden. It just seems that SSRS is taking
> the empty rows into consideration when making the pagination decision.
I believe that it is. If for no other reason than the convenience of
not having to enter the expression for each textbox/cell. Also, my
experience has been that using a table rather than a collection of
boxes just works better in general. I know that trying to hide the
entire row by hiding all the cells doesn't seem to close up the white
space.

Page Setup button in Report Viewer object

I have develop a Windows Form Application that have a Report Viewer object
that rendering a report .rdlc.
I have a problem with the PageSetup Button. The button for set the layout of
the page. If I click that button the type of the page is on Letter format and
i must always set the margin on A4 format of the page before print the
report. But that appends in someone PC...unfortunately it appends on the PC
of the client (in the driver of the printer the page layout is set to A4).
How I can resolve that problem?
Another problem is the set of the margin in PageSetup Button.
If I click on 'Page setup' button the margins left & right are not what I
set on the rdlc file at design time (0,5cm) they are 2 mm
If I click OK on the 'Page Setup' form the Report margins change to the
margins from 'Page setup'
If I open ''Page setup' again the margin settings are now less than
previously set and clicking OK results in the report margins changing again
The margin change from 2 to 0,8 -> from 0,8 to 0,3 -> from 0,3 to 0,1 ->
from 0,1 to 0.
I have Visual Studio 2005 service pack 1Are you doing the 'ChangeService' voodoo that needs to be done to save the
report?
Check 'www.gotreportviewer.com' for an example at what you are doing.
I am no expert at SSRS myself.. just started my fight with it last week so...
- Tanmay
"Marco82bg" wrote:
> I have develop a Windows Form Application that have a Report Viewer object
> that rendering a report .rdlc.
> I have a problem with the PageSetup Button. The button for set the layout of
> the page. If I click that button the type of the page is on Letter format and
> i must always set the margin on A4 format of the page before print the
> report. But that appends in someone PC...unfortunately it appends on the PC
> of the client (in the driver of the printer the page layout is set to A4).
> How I can resolve that problem?
> Another problem is the set of the margin in PageSetup Button.
> If I click on 'Page setup' button the margins left & right are not what I
> set on the rdlc file at design time (0,5cm) they are 2 mm
> If I click OK on the 'Page Setup' form the Report margins change to the
> margins from 'Page setup'
> If I open ''Page setup' again the margin settings are now less than
> previously set and clicking OK results in the report margins changing again
> The margin change from 2 to 0,8 -> from 0,8 to 0,3 -> from 0,3 to 0,1 ->
> from 0,1 to 0.
> I have Visual Studio 2005 service pack 1