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.

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

Paged Result Sets

What is the recommended mechanism for selecting paged results from SQL.

Presently I pass various params including the request Max Items Per Page and the requested page.

The I execute the query as a count with the search params.

Then comes the paging logic, which validates the page number against the request page and number of hits etc.

Then a temp table and record variables are created for the results.

Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).

Then I return the temp table & some additional return params with the Total Hits etc.

The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.

Thanks for any input,

Martin.

hi martin,

i think this is a front end issue not SQL's

vb.net objects like grids, detailsview and formview

supports paging builtin to them without the need of relying

to Sql server.

you can read the data into the dataset and present it with vb using

objects that support paging.

regards,

joey

|||

Hi Joey,

I am more than capable with ADO.Net. This is not a front end issue.

I have say 200,000 records and I want 15 items, page 30 in a result set displaying 15 items per page. I do not wish to return 450 items to a dataset or to a datareader that has to do multiple round trips to the server to get top the records I require.

I want the SPROC to return the 15 items that I am requesting. Do you understand what I am saying taking this in context with what I have said above?

I can presently do this. Though, I am looking for some one of you SQL Pro's to tell how I should be doing it.

|||

edited

hi martin,

sorry just clarifying. Anyway try this

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

exec pagemynorthwindorders 1

regards,

joey

|||

Hello again!

Looking at this this means that I am selecting all - say using Top 1000 - one thousand records into a temp table?

into #dummyorders I am unfamiliar with this syntax.

Inner queries and stuff! Is this more effeective than the Cursor approach? Is it possible you could break out the SPROC above a little with some comments.

It is much appreciated.

|||

no problem

use northwind

create proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15 -- in case you want to change your paging size

-- create a temp table with its own identity column starting from 1

-- since order id has its own identity i need translate it to a string using str

-- there can only be one identity column in the table

-- i need only the pk (orderid) since it can identify the records i need

-- the #dummyorders record returns my own id_num and the orderid

-- id_num shall be used for paging , orderid is to identify what records

belong to the page

select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders

-- this get the records from orderid that exist in the required page

select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize and (@.page)*@.pagesize
)

--(@.page-1)*@.pagesize+1 and (@.page)*@.pagesize

let say you want page 2 it gets the record between

-- (2-1)*15+1 and (2*15)

-- between 16 and 30 which is actually page 2

--at the end of the procedure #dummyorders destroys itself

exec pagemynorthwindorders 1

|||

final query, just improve it to suite you needs

use northwind

alter proc pagemynorthwindorders(@.page int)
as
declare @.pagesize int
select @.pagesize=15
select IDENTITY(int, 1,1) AS ID_Num ,str(orderId)as orderid
into #dummyorders from orders
select * from orders where orderid in
(
select orderid from #dummyorders orders
where id_num between (@.page-1)*@.pagesize+1 and (@.page)*@.pagesize
)

go

exec pagemynorthwindorders 2

|||

This is a very common question and there are actually many good answers.

If you are using SQL2005 there are two really cool features you could use:

- the tops now accept a variable: select top @.variable

- the ROW_NUMBER function, that creates an additional column with the ID of the row

Try this:

declare @.ipagesize as int,

@.ipage as int

set @.ipagesize = 5

set @.ipage = 0

select top ( @.ipagesize ) *

from

(

select ROW_NUMBER () over ( order by orderID ) as row_order , *

from orders

)

ordered_set

where ordered_set.row_order > @.ipagesize * @.ipage

|||

Thanks people,

Both are cleaner than my current solution.

Though, is there a way to get an estimate of the total hits from the same statement or must I use an additional count?

Also, I know there is a knew Table or .Table statement for SQL 2005 which I thought may have popped up in the answers. The Top Param non variable limitation was an anoying so that is also valuable info.

|||new!