Error on reporting services

I am using VS 2003 + SQL Server 2000 Reporting services.

When i want to print the report, clicking the trigger button, and pop up a browser and get the following error.

Error occurs when handling the report (rsProcessingAborted)

Request on dataset 'PG' fails (rsErrorExecutingCommand)

Must declare the variable'@.qcode'.

- I already got qcode in my url link... i would like to know what's the problems about.

Thank you.

Hi,

Can you run this report successfully when you browse the report in the VS.NET IDE?

I think, you are passing a parameter which is not declared in the stored procedure. Are you using a stored procedure as the datasource of the dataset PG by the way?

Since you have the parameter qcode in the url, either this parameter is not defined in the report or is not defined in the SP.

You know, while naming parameters in SQL RS, you do not use the "@." preior the parameter name, but you use it within the sql SP.

Eralper

http://www.kodyaz.com

Error on Passing parameter to stored procedure

Hi,

I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@.dtmWarrantyStart', which was not supplied" even though I supplied it in the code

which is

Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@.dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)

below is the stored procedure.

create Proc sp_AddBoard(@.BrandID int,
@.strPcName varchar(50),
@.bitAccounted bit,
@.dtmAccounted datetime,
@.dtmWarrantyStart datetime,
@.dtmWarrantyEnd datetime,
-- @.strDescription varchar(500),
@.intStatus int,
@.ModelNo varchar(50),
@.intMemorySlots int,
@.intMemSlotTaken int,
@.intAgpSlots int,
@.intPCI int,
@.bitWSound bit,
@.bitWLan bit,
@.bitWVideo bit,
@.dtmAcquired datetime,
@.stat bit output,
@.intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@.strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)

values

(@.BrandID,@.strPcName,@.bitAccounted,
@.dtmAccounted,@.dtmWarrantyStart,
@.dtmWarrantyEnd,--@.strDescription,
@.intStatus,
@.ModelNo,@.intMemorySlots,@.intMemSlotTaken,
@.intAgpSlots,@.intPCI,@.bitWLan,
@.bitWVideo,@.dtmAcquired,@.intFSB,@.bitWSound)
end
else
begin
set @.stat=1
end

The table is also designed to accept nulls on that field but still same error occured.

Please helpjust check if there is any value in the parameter value. the way i normally do is :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
myCommand.Parameters("@.cusbday").Value = DateTime.Parse(bday.Text)
End If

also you need to import the namespace System.Data.SqlTypes to use sqldatetime.null

<%@. import Namespace="System.Data.SqlTypes" %>

hthsql

Error Message: Must declare variable @ID

I'm having trouble with a datalist. The default view is the Item Template which has an Edit button. When I click the Edit button, I run the following code (for the EditCommand of the Datalist):

DataList1.EditItemIndex = e.Item.ItemIndex

DataBind()

It errors out with the message "Must declare variable @.ID".

I've used this process on other pages without problem.

The primary key for the recordsource that populates this datalist is a field named "AutoID". There is another field named ID that ties these records to a master table. The list of rows returned in the datalist is based off the ID field matching a value in a dropdown list on the page (outside of the datalist). So my SQLdatasource has a parameter to match the ID field to @.ID. For some reason, it's not finding it and I cannot determine why. I haven't had this issue on other pages.

Here's my markup of the SQLDataSource and the Datalist/Edit Template:

<asp:SqlDataSourceID="SqlDataSource4"runat="server"ConnectionString="<%$ ConnectionStrings:SMARTConnectionString%>"

DeleteCommand="DELETE FROM [tblSalesSupport] WHERE [NBID] = @.NBID"

InsertCommand="INSERT INTO [tblSalesSupport] ([ID], [NBNC], [NBEC], [Description], [Estimate], [CompanyID], [CompanyName], [ProjectNumber]) VALUES (@.ID, @.NBNC, @.NBEC, @.Description, @.Estimate, @.CompanyID, @.CompanyName, @.ProjectNumber)"

SelectCommand="SELECT * FROM [tblSalesSupport] WHERE ([ID] = @.ID)"

UpdateCommand="UPDATE [tblSalesSupport] SET [ID] = @.ID, [NBNC] = @.NBNC, [NBEC] = @.NBEC, [Description] = @.Description, [Estimate] = @.Estimate, [CompanyID] = @.CompanyID, [CompanyName] = @.CompanyName, [ProjectNumber] = @.ProjectNumber WHERE [NBID] = @.NBID">

<DeleteParameters>

<asp:ParameterName="NBID"Type="Int32"/>

</DeleteParameters>

<UpdateParameters>

<asp:ParameterName="ID"Type="Int32"/>

<asp:ParameterName="NBNC"Type="Boolean"/>

<asp:ParameterName="NBEC"Type="Boolean"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Estimate"Type="Decimal"/>

<asp:ParameterName="CompanyID"Type="Int32"/>

<asp:ParameterName="CompanyName"Type="String"/>

<asp:ParameterName="ProjectNumber"Type="String"/>

<asp:ParameterName="NBID"Type="Int32"/>

</UpdateParameters>

<SelectParameters>

<asp:ControlParameterControlID="ddlFind"Name="ID"PropertyName="SelectedValue"Type="Int32"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="ID"Type="Int32"/>

<asp:ParameterName="NBNC"Type="Boolean"/>

<asp:ParameterName="NBEC"Type="Boolean"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Estimate"Type="Decimal"/>

<asp:ParameterName="CompanyID"Type="Int32"/>

<asp:ParameterName="CompanyName"Type="String"/>

<asp:ParameterName="ProjectNumber"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:DataListCssClass="MainFormDisplay"ID="DataList1"runat="server"DataKeyField="NBID"DataSourceID="SqlDataSource1"width="100%">

<HeaderTemplate>….</HeaderTemplate>

<ItemTemplate>….</ItemTemplate>

<EditItemTemplate>

<tableborder="0" style="width: 100%">

<trclass="MainFormDisplay" valign="top">

<tdcolspan="8">

<asp:TextBoxID="txtNBID"runat="server"Text='<%# Eval("NBID")%>'Visible="true"></asp:TextBox>

<asp:TextBoxID="txtID"runat="server"Text='<%# Bind("ID")%>'Visible="True"></asp:TextBox></td>

</tr>

<trclass="MainFormDisplay">

<tdvalign="top"style="width: 100px"><asp:CheckboxID="chkNBNC"runat="server"Checked='<%# Bind("NBNC")%>'/></td>

<tdstyle="width: 100"><asp:CheckBoxID="chkNBEC"runat="server"Checked='<%# Bind("NBEC")%>'Width="100px"/></td>

<tdstyle="width: 100px"><asp:TextBoxID="txtCompanyName"runat="server"Text='<%# Bind("CompanyName")%>'Width="100px"></asp:TextBox></td>

<tdstyle="width: 100px"><asp:TextBoxID="txtProjectNumber"runat="server"Text='<%# Bind("ProjectNumber")%>'Width="100px"></asp:TextBox></td>

<tdstyle="width: 100px"><asp:TextBoxID="txtDescription"runat="server"Text='<%# Bind("Description")%>'Width="100px"></asp:TextBox></td>

<tdstyle="width: 100px"><asp:TextBoxID="txtEstimate"runat="server"Text='<%# Bind("Estimate","{0:N2}")%>'Width="100px"></asp:TextBox></td>

<tdstyle="width: 55px"><asp:CheckBoxID="ckDeleteFlag"runat="server"/></td>

<tdstyle="width: 100px"><asp:ButtonID="ItemSaveButton"runat="server"CommandName="Update"Text="Save"/>

<asp:ButtonID="ItemCancelButton"runat="server"CommandName="Cancel"Text="Cancel"/></td>

</tr>

</table>

</EditItemTemplate>

</asp:DataList><br/>

You need to replace

<asp:ParameterName="ID"Type="Int32"/>

in your UpdateParameters with

<asp:ControlParameterControlID="ddlFind"Name="ID"PropertyName="SelectedValue"Type="Int32"/>

|||

Thank you very much for your reply. I have tried this though and I still get the same error.

I believe it's because it's not running the UpdateCommand at all at this point. When I click the Edit button, it simply flips it from ItemTemplate to EditTemplate, passing the index of the row that the user click on so that while in EditTemplate mode, the user can make changes to data, click save and update. The code that runs when the Edit button is clicked is the following: (This

Protected Sub DataList1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList1.EditCommand
DataList1.EditItemIndex = e.Item.ItemIndex
DataBind()
End Sub