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 Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

parameter entry / view report

I'm fairly new to RS, but have created some reports and deployed them.
I have parameter value list that is generate from a SELECT statement.
To be a bit more user-friendly, I'd like the user to be able to click on an
entry in the parameter list and have the sytem run the report without having
to click the View Report .
Is there a way to avoid having the user click the View Report ? It would
save a step and is something that can become annoying as the user moves from
one report to another based on the parameter chosen.
TIA,
DougIf you use the enter key the View Report button is the default. I do this
instead of clicking on it sometime.
RS does not know when you are on the last parameter, you need to either
click on view report or use the enter key.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:5F6FC4F0-F033-4B3D-9565-DE61F0FD0E34@.microsoft.com...
> I'm fairly new to RS, but have created some reports and deployed them.
> I have parameter value list that is generate from a SELECT statement.
> To be a bit more user-friendly, I'd like the user to be able to click on
> an
> entry in the parameter list and have the sytem run the report without
> having
> to click the View Report .
> Is there a way to avoid having the user click the View Report ? It would
> save a step and is something that can become annoying as the user moves
> from
> one report to another based on the parameter chosen.
> TIA,
> Doug

Parameter Change and Report Clears

When I change the value of a parameter the report is cleared on a post back to the server and I have to click on the View Report button to see any results. Is there a way to either not post back to the server when the parameter is changed or automatically post back and retrieve the new results?

I found this link below here and it sounds like I am out of luck since both of my parameters are dates with functions to define them. I have tried changing their order with no luck.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284284&SiteID=1

Does anyone know if this is an issue without a work around?

Thanks!

Looks like I am out of luck.

Parameter

Hi all:
I am new in RS. I use Report Designer to create a report and use Report
Manger to view it. My question is I want to use parameter passing to let the
end-user to select multiple item in the parameter list, however, by using
Report Designer the parameter list only alow user to select one item. Anyone
know the good solution for this problem.
Thanks,
KentKent,
Version 1.0 of Reporting Services doesn't support multi-value parameters. As
a workaround, consider building your own front end to support multi
selection. Once this is in place, you can make your report query expression
based, e.g. by using the SQL IN clause.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"kent kent" <kentkent@.discussions.microsoft.com> wrote in message
news:7FC417C5-078E-433D-98BF-C511EAB28D9A@.microsoft.com...
> Hi all:
> I am new in RS. I use Report Designer to create a report and use
Report
> Manger to view it. My question is I want to use parameter passing to let
the
> end-user to select multiple item in the parameter list, however, by using
> Report Designer the parameter list only alow user to select one item.
Anyone
> know the good solution for this problem.
> Thanks,
> Kent
>

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.

Paging large Results in SQL 2005

lets say we have more than 100 000 rows in Table1, and we want to view each 10 rows alone... and by pressing on a NEXT button we will see the other 10 pages...

there is 2 buttons : NEXT and PREVIOUS

so can anyone tell me how to do that in SQL 2005, and what is correctly called.

I have found a code that does use ROW_NUMBER in order to view results between 2 numbers,

example: rows between 10 and 50...
but It is not what I want, so please I need some help, thank you

By Uncle SamIf you code this by program, you can use ADO object that contains "PageSize" property to set how many records could be shown in a page.|||you can do some trick with the NTILE function to get the result. though it wont perform good. also with NTILE function you need back calculate the number of pages if 10 rows per page is to be displayed....

select * from (select *,ntile(2) over(order by COL1) as PgNo from test) as TempTbl where TempTbl.PgNo = 2|||Ok can you tell me how to do that, because I am beginner|||+1 : bad idea to solve this issue using cursor or any other server-side trick. You must manage that in your client-side...|||ok but is there any Stored procedure that does it|||Order your result set by a primary (natural) key. Write your NEXT procedure to take a starting key and a requested record count and return that number of records starting at that point in the result set. Your application just needs to know that last pkey it received in order to request the next page. Similar logic works for PREVIOUS recordsets.|||okay but please can you write for me the code, because I am still a beginner and this is my project. Thank you so much|||Are you doing your project for free? Because I generally charge something for my services...

I encourage you to either hire a dba to help you, or learn advanced SQL real fast.|||sorry but I can't afford paying an advice|||You're not asking for advice. You are asking for somebody to do the work for you.

Paging large Results in SQL 2005

lets say we have more than 100 000 rows in Table1, and we want to view each 10 rows alone.... and by pressing on a NEXT button we will see the other 10 pages....

there is 2 buttons : NEXT and PREVIOUS

so can anyone tell me how to do that in SQL 2005, and what is correctly called.

I have found a code that does use ROW_NUMBER in order to view results between 2 numbers,

example: rows between 10 and 50....
but It is not what I want, so please I need some help, thank you

By Uncle Sam

You can use the ROW_NUMBER approach to page through results. You need to vary the row number ranges depending on the page. The other approach is to use TOP logic. Please take a look at the link below for the various techniques.

http://www.aspfaq.com/show.asp?id=2120

|||

Well I've been through that problem.. not only 100 000 rows but more than 1 000 000 records with multiple table lookup.. I've try every methods available on web and I found out the solution by combining all ideas.. You can look at my blog in this link and if you have any questions just email me..

http://weblogs.sqlteam.com/randyp/archive/2005/06/23/6335.aspx

|||okay but is there any creation for a button NEXT that jumps to the next page of the results.

Page Width view AS default

Hello all,

When viewing a report, there is an option in the left corner for the size ie. 100%, 150%, page width, whole page etc from the dropdown list.

I was wondering is there a way to make the ''Page Width" option the default view for a particular report or site ?

There is no way to set the default zoom within a report. However, if you can call your reports from a URL, you can use the Zoom parameter and specify Page Width as the value. See Using URL Access Parameters for more information:

http://technet.microsoft.com/en-us/library/ms152835.aspx

-Jessica

page setup on crystal report 8.5

dear babu,
i want to ask you a question on crystal report 8.5.
i'm using crystal report viewer from visual basic 6.0 to view reports but i'm found problems to setup page margin until this time. can you help me for a while ??

how to view page setup on my crystal report viewer so end user have flexibility to customize the margin ??

thanks for your help.

emmileI think you cannot do that at run time
You need to design it in Design time