Error on generating report

I am using VS2003 and SQL Server 2000 with Reporting Services.

I try to generate the report but get an error "must declare the variable '@.qcode'

I check the file quotation.rdl and i found it should have declared... it's like

</PageFooter>
<ReportParameters>
<ReportParameter Name="qcode">
<DataType>String</DataType>
<Nullable>true</Nullable>
<DefaultValue>
<DataSetReference>
<DataSetName>DS_quot_code</DataSetName>
<ValueField>quot_code</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Quotation Code:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DS_quot_code</DataSetName>
<ValueField>quot_code</ValueField>
<LabelField>quot_code</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

I would like to know what trigger the problem and what shall i do so as to solve it?

Thank you.

This sounds for me like a SQL Exception not a Reporting Services error. Can you run the command which is specified as the dataset command separately, or does it come up with the same error (I guess this is the case)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thank you for your answer.

Does you mean that the problem comes from the SQL query stated in the rdl file?

|||Exactly.

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

Error message for null at SET or aggregate

The following code works perfectly when run by itself:

DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime

SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)

However, it is part of a long stored procedure, and when it runs there, I get the following instead of the line confirming the two dates:

Warning: Null value is eliminated by an aggregate or other SET operation.

What could be interfering with this section of the stored procedure?

Further research has detected that the following code:

DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)

although the following part isolated returns the correct value:

DECLARE @.DateTransaction smalldatetime
SET @.DateTransaction = (SELECT TOP 1 (DateTransaction) FROM tblImport ORDER BY DateTransaction DESC)

Why is the variable not being set?

|||

WHEN THE FOLLOWING CODE IS RUN IN ISOLATION:

DECLARE @.DateTransaction smalldatetime
DECLARE @.TradeDate smalldatetime

SET @.DateTransaction = (SELECT Max(DateTransaction) FROM tblImport)
SET @.TradeDate = (SELECT Max(DateTrade) FROM tblSystem)
PRINT 'The Transaction Date is ' + CAST(@.DateTransaction as varchar) + '. The Trade Date is ' + CAST(@.TradeDate as varchar)

IT RESULTS IN THIS:

The Transaction Date is May 15 2006 12:00AM. The Trade Date is May 15 2006 12:00AM

WHEN THE CODE IS RUN AS PART OF A LONGER STORED PROCEDURE, IT RESULTS IN THIS:

The Transaction Date is null. The Trade Date is May 15 2006 12:00AM

WHAT IS HAPPENING?

|||

I added an update earlier in the stored procedure and it solved the problem.

Thanks for your help.

Error Message 443

Hi,

I'm trying to create the following function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SET @.id = (SELECT id FROM dbo.language WHERE name = @.name)
IF @.@.ROWCOUNT = 0
RAISERROR('Specified item does not exist', 16, 1)

RETURN @.id
END
GO

And I get the following error message:
Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

I can't find any references to the use of RAISERROR within functions or stored procedures, so I have no idea why this is not working.
What I'm trying to do is check for the existence of the row inside the function and not outside, on all the places I need to call this function. I need to have an exception in my code if this happens, that's why I thought RAISERROR is what I needed...

Any help would be appreciated!

Thanks,
Florin

You cannot use RAISERROR from within TSQL UDFs. You need to put this logic in a stored procedure or use a SQLCLR UDF. Please take a look at http://msdn2.microsoft.com/en-us/library/ms191320(en-US,SQL.90).aspx topic also.|||

Thanks, I eventually figured out that I can't use RAISERROR within a function, so I changed it to a stored procedure. My problem now is that RAISERROR doesn't stop the execution of my stored procedure, but continues with the next statement. Is there a way to achieve the same kind of functionality as with primary key or constraint violations? I'm testing some conditions inside my stored procedure and I want to stop the execution under certain circumstances and get an exception in my .NET calling code. Is that at all possible with RAISERROR?
I noticed that when an error is produced by the SQL Server engine, besides the red error message (which I can also create using RAISERROR) there is another message saying "The statement has been terminated". How can I achieve the same? :-)

Thanks, Florin

|||While you can't raise an error in a udf. . .

this function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SELECT @.id = id FROM dbo.language WHERE name = @.name
RETURN @.id
END
GO

will return a null if name is not found.

Now, I contend, trying to select an id for a name that doesn't exist shouldn't be an error. . . It should return a null. Trying to use the null in a place that requires non-null will raise the error. . . and that might make perfect logical sense.

Just a thought.

On the other hand, if you want to use a procedure, look up @.@.ERROR in the help file.|||

Well, in the application logic, the foreign key can be null, which means that record has no references, but in this particular case I need to throw an error because the calling client has given a foreign key value that does not exist. Null is a valid case, non-existing referenced record is not. That's why I need the error. I want to do as much as I can on the database in one stored procedure call, to avoid multiple database calls/roundtrips...

Florin

|||

huh? I think you misunderstood. . .

run this script. . .

-- BEGIN SCRIPT =========================

create table Song(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Band(ID int not null identity(1,1) primary key, name varchar(255))

go

create table Record(BandID int not null references Band(ID), SongID int not null references Song(ID) )

go

CREATE FUNCTION dbo.getSongID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Song WHERE name = @.name

RETURN @.id

END

go

CREATE FUNCTION dbo.getBandID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Band WHERE name = @.name

RETURN @.id

END

go

Insert into Song(Name) values('Love Will Tear Us Apart')

Insert into Band(Name) values('Joy Division')

-- This works

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Captain and Tennille'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Keep Us Together'))

-- END SCRIPT =========================