Incorrect syntax near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect syntax near ',' using a Multi-value parameter

I created a report in Reporting Services 2005 where I added multi-value parameters. When I run my report, and try to select more than one parameter, I get an error: Incorrect syntax near ','I fixed it by changing my '=' signs to 'IN', so that it would search for the multi-value parameter within the values selected.

Incorrect syntax near '?' when trying to use parameters

I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?

I'm mostly an Oracle and PL/SQL type so I'm a little lost here...

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"

AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"

Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">

<HeaderStyle HorizontalAlign="Left" />

<Columns>

<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />

<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"

SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">

<SelectParameters>

<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />

</SelectParameters>

</asp:SqlDataSource>

Update, I changed WHERE PackagingItemNo = ?

to WHERE PackagingItemNo = @.PackItemNo

and now the DefaultValue value from the <SelectParemeters><SessionParameter> property is used in the query. However, the actual session value isn't used, just whatever the DefaultValue property is set to. Using ResponseWrite to display Session("PackItemNo") does display the session value as it was set by previous pages, however, so I'm not sure what I'm missing now.

|||

Hi,

I too faced the same problem while using the parameterized query in asp:SqlDataSource

I found the following solution

My Command was

SelectCommand="SELECT DISTINCT Suppliers.CompanyName FROM Suppliers INNER JOIN Products ON Products.SupplierID = Suppliers.SupplierID WHERE Products.CategoryID = @.category ORDER BY CompanyName;"

and my Parameters are like this.

<SelectParameters>

<asp:SessionParameter SessionField="category" Type=Int64 Name="category" DefaultValue=1 />

</SelectParameters>

Be sure to match your parameter names. It takes the default value 1 for the first time, then it changes according to the session variable value. Hope u have solved it too.

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
[Stored Procedure Code]
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;
"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph
|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.arkansas.net ...[vbcol=seagreen]
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>
|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralph

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
----
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
----
[Stored Procedure Code]
----
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
----
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.ar
kansas.net...
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)[vbcol=seagreen]
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralphsql

incorrect format of date report parameters via datepicker

OK - truck loads of folk have reported this problem - but I cannot solve it - so apologies for repeating the question...

My SQL Server 2005 Report is developed on the same server as it is run.

I have SQL Server 2005 SP2 installed. The report language is set as en-GB.

The browser language setting is en-NZ.

The report previews fine - I pick May 22 from the picker and it appears as 22/05/2007 in the report parameter text box - which since I am in NZ is exactly as I want it.

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs - selecting May 22 from the date picker produces 05/22/2007 in the report parameter text box and produces the error

"The value provided for the report parameter 'StartScheduledDate' is not valid for its type. (rsReportParameterTypeMismatch)"

thanks for any help here - this problem has made this a ridiculously expensive report!

honestbob wrote:

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs

Pardon me, what's the difference between the 2 sentenses? They both sound like you run from http://localhost/Reports?

I'd suggest looking at the Regional settings in your machine, and server, to make sure they're the same

e.g. Canada default is DD/MM/YYYY, while USA default is MM/DD/YYYY

|||

bob,

what i have done with this in the past is always do a CDate() on the output of the calendar control before trying to use that output, then i can Format() it any way i like. I also found when trying to force it to a non-US date format i couldn't then use the Preview tab in the IDE to check my report, as that version of the control insists on rendering/outputting everything in US format, regardless of what culture you have your report/IE/server set to.

So in summary, i find the web version of that control works fine if you CDate everything before using it. And like the previous poster said, check the regional settings on your report server.

|||

I was having the same problem but i managed to fix it.

edit the file "ReportViewer.aspx" on the reporting services machine.

it's in the "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\Pages" by default,

and add the highlighted part with the desired culture value.

It solved the problem in my case.

<%@. Register TagPrefix="RS" Namespace="Microsoft.ReportingServices.WebServer" Assembly="ReportingServicesWebServer" %>

<%@. Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="pt-PT"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head id="headID" runat="server">

<title>Report Viewer</title>

</head>

<body style="margin: 0px; overflow: auto">

<form runat="server" ID="ReportViewerForm">

<RS:ReportViewerHost ID="ReportViewerControl" runat="server" />

</form>

</body>

</html>

Hope this will solve your problem too.

Tiago Certal

|||

yes, that solved my problem too. I, too have tried fiddling with the regional settings on the database, the server, the local, the client computers etc. I would keep having the problem in IE, but not in Firefox. Firefox was fine.

so I tried to add the culture thing in, like Tiago suggested. only problem is that I think PT is portugese, so I had to use "en-CA" instead. Which then magically made the reports displayable in IE. Thank you Tiago!

problem is that now Firefox is having the same error. WHAT IS GOING ON?

Incorrect Data appearing in report

I have setup a report. I am calling the report from a page which passes parameters in a query string to the report.
The parameters are to be used in a stored procedure which the "Object Data Source" of the report refers to.
The stored procedure queries the data ina table and this data should be displayed in the report.

What actually happens is that the report appears but it has only the first record in the table but this is not the record
specified by the parameters passed to the stored procedure. It look as if the report is attaching to the correct table but
the query is not being executed properly.

Anyone any ideas how to fix this?
macca

Does ur storeprocedure return the right data when u run in SSMS or in the Data layout of ur report?

|||

Thanks for the reply Karenros but how do I test what you are suggesting.

Thanks,

macca

|||

Go to sql server ManagementStudio and then go to the database u wanna access and then click on new query.. in the new query window type in ur sproc name and give the parameters for ur sproc and u should see the results in the same window...

Or u can go to ur report in VS2005 and then click on the data part of ur report and u will see the name of the dataset and the sproc name.. next to the sproc click the exceute button and it will prompt u for parameters and after u enter them they should display the results...

Hope this helps.

Regards

Karen

|||Thanks for that Karen that works.I am getting another problem though. I am calling the report via a Response.redirect but am getting an error with the dates formats. The error message is as follows:"An error has occurred during report processing.· String was not recognized as a valid DateTime." I am calling the report and the url looks like this:· · "/PaymentReport.aspx?Section=107&DateFrom=02/10/2007&DateTo=19/10/2007". When I try the method you suggested above I must put in the DateFrom as 10/2/2007 and DateTo as 10/19/2007. The report is obviously not getting the dates in the query string in the correct format and therefore the error.

|||

How are u storing ur date time in ur database. looks like the format you are using is dd/mm/yyyy... and by any chance is the date in ur database stored as mm/dd/yyyy?

Regards

Karen

|||

Macca,

while u are giving ur date Parameter try putting it in Single quote... take a look at this code.

Declare @.reqDeldatedatetimeSet @.reqDeldate ='1/31/2007'Select OrderId, OrderDate, PlanId, RequiredDeliveryDateFrom [Order]Where RequiredDeliveryDate = @.reqDeldate

If i just @.reqDeldate as 1/31/2007 instead of '1/31/2007' nothing shows up.

 So try giving it quotes..

Regards

Karen

|||

Thanks Karen that worked.

I now have another problem. The report is not bringing back the correct results from the query. It appears to just bring back one record multiple times.

But if I run the query it brings back the correct results.

I don't know why it is doing this.

Any ideas?

macca

|||

can u pls post ur store procedure code? and have u tried refreshing the data in VS studio.. cause sometimes i have seen display the old data... and after i refresh it works fine... and try deploying it to the report server to see if it gives the correct results.

Hope this helps.

Regards

Karen

|||

Here is my Stored procedure:

CREATE PROCEDURE sproc_PaymentReports

(
@.DateFrom Datetime,
@.DateTo Datetime,
@.Status Int,
@.Section Int
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_amt
FROM RecCheqs
WHERE cheq_DateRec >= coalesce(nullif(@.DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@.DateTo, ' '),cheq_DateRec)
AND cheq_dept = coalesce(nullif(@.Section , ' '),cheq_dept)
AND cheq_added = coalesce(nullif(@.Status , ' '),cheq_added)
END
GO

How do you deploy to the report server?

macca

|||

Do u have a report server setup? if yes

then in VS go to Project and then click on properties and in the window that comes up Give a

Name for the TargetReportFolder. and in the TargetServerURL ... give http:/Either localhost or an ipaddress/ReportServer

and click ok ... for the first time the overwriteDatasources to false...but sometimes i would have to make changes to the report to the dataset so i just set it to true.

Once u set ur project properties... Go to Build -- Deploy Project name and it should prompt for a user name and password if required...

and to run ur report from the report server... go to the address that u specified in the TargetReportServerURL and then navigate to the folder name and click on the main report that runs it... and then its like .... runing it from Visual studio

Hope this helps...

Regards

Karen

|||

I don't have a report server setup so can't do any of that. This is not doing it for me at all.

In .net 1.1 I used to create reports using a Repeater and I would run the stored procedure and it would "Bind" the data to the repeater and it would work fine.

I have tried this with the ReportViewer using the following code but cannot get it to Bind:

Dim oDSAs DataSet

Dim oChequeAsNew Cheque

Try

oDS =New DataSet

oDS = oCheque.GPAssignedReport(dateFrom, dateTo, Section, Status)

If oDS.Tables(0).Rows.Count > 0Then

rptVwPayment.DataBind()

rptVwPayment.Visible =True

This code works for the repeaters in 1.1 but cannot get it to bind to report.

Any ideas, as I am ready to abandon Reports as a pack of rubbish.

macca

|||

i have used the report viewer control... but only done it using remote processing cause all my reports are located in the report server.

|||

Karen,

Thanks for all your help I got that resolved.

macca

Inconsistent behaviour when setting up parameters

Hi guys,

i am having an issue setting up two parameters in the report designer (SSRS 2005). The report is calling a stored proc, these two parameters are optional, when the user decides to run a report i don't want them to input anything for these parameters, they should both be set to null when calling the stored proc (changing the signature of the sproc is not an option).

So, in the Report Parameter dialog, i set both parameters to "Allow null value", "Internal", and default value of null. This is when the first issue strikes: if i "OK" out of the dialog, then save, then go back into the dialog, the designer has "forgotten" that one of the params was internal (the check box is not checked) - but the other one is still okay. How can i prevent the designer forgetting things like this?

The second problem is that even though (at least one of) the parameters is set to internal, when using IE to connect to the report server and run the report the input fields for those params still show up - i don't want the user seeing them. These param input fields are both hidden if i run the report through the reporting services control (ie just right click on the report in the solution explorer and select "run"). Why the inconsistent behaviour? How can i prevent the user seeing those fields when using IE? (if i just set them to hidden then i get an error message "

The 'poolList' parameter is missing a value" when i try to run the report).

The rdl xml for these two paramaters is:

<ReportParameter Name="poolList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

<ReportParameter Name="poolGroupList">

<DataType>String</DataType>

<Nullable>true</Nullable>

</ReportParameter>

Nowhere in there do i see that the parameter is internal - where does the designer stick that sort of information?

Thanks for any answers!

sluggy

A report parameter being internal means that it does not have a prompt. Hence, in the underlying RDL file, there is no <Prompt> element under the <ReportParameter> element. So, from the RDL snippet shown in your posting everything looks correct.

Regarding report server: I recommend that you delete the already published report from the report server before republishing the report with updated parameter information. Otherwise, the old parameter settings and the new parameter settings may get merged (because the administrator on the report server could have decided to change the default value etc. for the published report).

-- Robert

|||

Hi Robert,

thanks for the heads-up regarding the merging of the reports, i will watch out for it when this project goes live as we won't have control over that report server.

I have fixed one of my problems. To get rid of the "The 'blah' parameter is missing a value" message all i had to do was remove those parameters in the parameters tab of the configure dataset dialog. As they are named params and they are assigned default values in the sproc i didn't even need to try to pass them. This also means i don't have those input fields showing up in IE but not the control.

Although this still doesn't explain why the designer was persistently forgetting settings :)

sluggy

Including Report Parameters in SQL Query

I have created a DAtaset for using in my report. Everything works. My SQL
contains Top 4 ... which I want to pass as a parameter.
I created a report parameter called "Months". How can I replace top 4 by top
(parameters!months.value) '
However I try to include the parameter in my SQL query it fails to work.I think in this case you need to use an expression for the source. Use the
generic query designer and do this:
= "some sql statements blah blah Top " & parameters!Months.value & " rest of
SQL Statement"
Remember that when refering to a parameter it is case sensitive so it much
match exactly.
When I do this I will sometimes first work out the expression using a report
with a single textbox and my parameters and set the textbox to the
expression so I can see it prior to using it for the source of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>I have created a DAtaset for using in my report. Everything works. My SQL
> contains Top 4 ... which I want to pass as a parameter.
> I created a report parameter called "Months". How can I replace top 4 by
> top
> (parameters!months.value) '
> However I try to include the parameter in my SQL query it fails to work.|||Thanks fror your reply.
I have tried that. What I don't understand is where should I stop the SQL
String with " ? I have created the query in VS 2003 on the data tab with the
grafical query creator (looking like SQL Enterprise manager).
There is no SQL String inside quotation marks I could interrupt, enter the
parameter and continue the sql string with quotation marks. Do you get my
problem?
markus
"Bruce L-C [MVP]" wrote:
> I think in this case you need to use an expression for the source. Use the
> generic query designer and do this:
> = "some sql statements blah blah Top " & parameters!Months.value & " rest of
> SQL Statement"
> Remember that when refering to a parameter it is case sensitive so it much
> match exactly.
> When I do this I will sometimes first work out the expression using a report
> with a single textbox and my parameters and set the textbox to the
> expression so I can see it prior to using it for the source of the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >I have created a DAtaset for using in my report. Everything works. My SQL
> > contains Top 4 ... which I want to pass as a parameter.
> >
> > I created a report parameter called "Months". How can I replace top 4 by
> > top
> > (parameters!months.value) '
> >
> > However I try to include the parameter in my SQL query it fails to work.
>
>|||The issue is that you are in the graphical query instead of the generic
query designer. There is a button (hover over them) to switch to the other
designer. Then put an = sign in from of it.
For instance, let's say that your query looks like this:
select * from sometable
Click on the button (to the right of the ...). Now do this:
= "select * from sometable"
It should work for you. Now modify it to include your parameter as I had it
below.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> Thanks fror your reply.
> I have tried that. What I don't understand is where should I stop the SQL
> String with " ? I have created the query in VS 2003 on the data tab with
> the
> grafical query creator (looking like SQL Enterprise manager).
> There is no SQL String inside quotation marks I could interrupt, enter the
> parameter and continue the sql string with quotation marks. Do you get my
> problem?
> markus
> "Bruce L-C [MVP]" wrote:
>> I think in this case you need to use an expression for the source. Use
>> the
>> generic query designer and do this:
>> = "some sql statements blah blah Top " & parameters!Months.value & " rest
>> of
>> SQL Statement"
>> Remember that when refering to a parameter it is case sensitive so it
>> much
>> match exactly.
>> When I do this I will sometimes first work out the expression using a
>> report
>> with a single textbox and my parameters and set the textbox to the
>> expression so I can see it prior to using it for the source of the
>> dataset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
>> message
>> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>> >I have created a DAtaset for using in my report. Everything works. My
>> >SQL
>> > contains Top 4 ... which I want to pass as a parameter.
>> >
>> > I created a report parameter called "Months". How can I replace top 4
>> > by
>> > top
>> > (parameters!months.value) '
>> >
>> > However I try to include the parameter in my SQL query it fails to
>> > work.
>>|||Ok, I found it. I changed my SQL to:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM dbo.v_Calls_Statistik_Sum
ORDER BY Jahr DESC, Monat DESC"
But after editing my SQL I get an error:
"Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler: [BC30648]
Zeichenfolgenliterale müssen mit einem doppelten Anführungszeichen enden."
~
"The expression for Object 'DATASETNAME' contains error BC30648. Literals
have to end with double quotation marks."
My Parameter is an integer. Why or where do I have to use quotation marks?
Thank you so much,
markus
"Bruce L-C [MVP]" wrote:
> The issue is that you are in the graphical query instead of the generic
> query designer. There is a button (hover over them) to switch to the other
> designer. Then put an = sign in from of it.
> For instance, let's say that your query looks like this:
> select * from sometable
> Click on the button (to the right of the ...). Now do this:
> = "select * from sometable"
> It should work for you. Now modify it to include your parameter as I had it
> below.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> > Thanks fror your reply.
> >
> > I have tried that. What I don't understand is where should I stop the SQL
> > String with " ? I have created the query in VS 2003 on the data tab with
> > the
> > grafical query creator (looking like SQL Enterprise manager).
> > There is no SQL String inside quotation marks I could interrupt, enter the
> > parameter and continue the sql string with quotation marks. Do you get my
> > problem?
> > markus
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I think in this case you need to use an expression for the source. Use
> >> the
> >> generic query designer and do this:
> >> = "some sql statements blah blah Top " & parameters!Months.value & " rest
> >> of
> >> SQL Statement"
> >>
> >> Remember that when refering to a parameter it is case sensitive so it
> >> much
> >> match exactly.
> >>
> >> When I do this I will sometimes first work out the expression using a
> >> report
> >> with a single textbox and my parameters and set the textbox to the
> >> expression so I can see it prior to using it for the source of the
> >> dataset.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
> >> message
> >> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >> >I have created a DAtaset for using in my report. Everything works. My
> >> >SQL
> >> > contains Top 4 ... which I want to pass as a parameter.
> >> >
> >> > I created a report parameter called "Months". How can I replace top 4
> >> > by
> >> > top
> >> > (parameters!months.value) '
> >> >
> >> > However I try to include the parameter in my SQL query it fails to
> >> > work.
> >>
> >>
> >>
>
>|||MarkusPoehler wrote:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> But after editing my SQL I get an error:
> "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> Anführungszeichen enden." ~
Markus,
schreib das mal so:
write it in that way:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
Du muss den ganzen String quoten
You have to quote the whole string with "
best regards
Frank
--
www.xax.de|||Frank Matthiesen wrote:
nochmal wg. Umbruch
= "SELECT TOP " +
" & parameters!anzMonate.Value & " +
" dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum " +
" ORDER BY Jahr DESC, Monat DESC"
gruss
frank|||Hi Frank,
Hi Fränk, :)
vielen Dank, das war ein entscheidender Hinweis. Darauf wäre ich nie
gekommen, dass dieser Text-SQL Editor plötzlich Zeilen unterscheidet.
Thank you! This was the crux.
Markus
"Frank Matthiesen" wrote:
> MarkusPoehler wrote:
> > = "SELECT TOP " & parameters!anzMonate.Value & "
> > dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> > dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> >
> > But after editing my SQL I get an error:
> >
> > "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> > [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> > Anführungszeichen enden." ~
>
> Markus,
> schreib das mal so:
> write it in that way:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, " +
> " Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> Du muss den ganzen String quoten
> You have to quote the whole string with "
> best regards
> Frank
> --
> www.xax.de
>
>
>

Include User's Parameters in Report Header?

Hi, I'm getting stuck on what is probably a very simple/common task: Adding a textbox that displays the parameters that a user has selected for the current report.

For example, if the user chooses a @.startdate and @.enddate using date-pickers, a textbox in the header will display/print those parameters:

="Report by Department from " + @.startdate+ " to " + @.enddate

Seems simple, but I keep getting errors:

"Return statement in a Function, Get or Operator must return a value".

Can anyone help out with the proper syntax for repeating a user's parameters in the header or body of a report?

Thanks!

try

="Report by Department from " & Parameters!startdate.value " to " & Parameters!enddate.value

you can also right click at your textbox in the header and choose Expression to edit expression..

in there , choose Parameters in the left pane and double click the needed parameters in the right pane...

the expression will show at the top pane...

HTH

|||Ah, of course - Thankl you!|||What about something more general like looping through the parameters and writing them out by name and value? Any ideas?|||

do you mean multivalue parameters ?

if it is, u can try =Join(Parameters!status.label, ",")

.label for name; .value for value