Problem with Case statement

not sure what's missing here but it's not liking my select with parameter check. The syntax is wrong here but not sure what.

CASE @.BKYChapter

WHEN 7 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK07', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '992', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

WHEN 13 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK13', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '993', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

[

ELSE SET @.Error = 'Chapter not valid'

]

END

CASE is not a control of flow statement. It is an expression. You have to use if...else to perform control of flow.|||thanks much!

problem with cascading parameters after CTP2

In the standard report manager I'm running a report with 5 parameter choices. The first 3 are determinants of the 4th, and the combined 1-4 determine the 5th.

Although this works fine in my VS BIDS (sp1) with SQL 2005 sp2 CTP, when I deploy it to my sp2 CTP Report server, it throws an error as soon as I select the 3rd parameter.

One or more data sources is missing credentials

I found this answer from MS in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923684&SiteID=1) and since it deals with my problem, wanted to pass it along to anyone else that is dealing with this problem.

" This is a known regression with CTP2 that will be fixed before SP2 is released. The problem is not specific to multivalue parameters. It has to do with dependencies between parameters.

In some cases, you can work around the issue if you give the dependent parameter a default value. You can also work around the issue by removing the valid values dependency between the parameters, though this may not be possible for a given report."

The suggested workaround does work! I can get beyond the 3rd parameter now because I applied a default value!

The workaround is useless. It only alleviated the problem when I stayed with that default value for my 3rd parameter. As soon as I changed it I'm right back to the 'one or more data sources is missing credentials'

SP2 cannot be soon enough...

|||

The workaround suggested can work, but it's tricky, especially with nested parameters that are dependent on each other. I finally got a six parameter functional again but with some pain. I made all the dependent parameter lists 'multi-select' and gave them a functional default value. Only then do they populate correctly.

And this all because I was hoping to use these reports in WSS 3.0 with the report server addin. Got very close and now have stumbled across similar bug with multiple parameters in Sharepoint. Will post entry to Sharepoint forums.

sql

Problem with Boolean Nullable parameter in Report Viewer

I have a problem with a nullable boolean parameter.
By default, the value is Null and that's working fine.

The User clicks either true or false, view the report: fine again.

However, from that point on, if the user checks the Null checkbox and
submits the reports, it reverts automatically back to the last True or
False value.

Is this a known problem? Or am I doing something wrong?

Eric

You aren't doing anything wrong. This is a bug. Unfortunately, there is no workaround. If this is causing you signficant problems, please contact customer support directly for a solution.

http://www.microsoft.com/services/microsoftservices/srv_support.mspx

|||

Thanks for the answer.

Is it reasonable to assume that this will get fixed in the next service pack, whenever this is?

|||I can't make any promises at this point (there are many factors involved), but I am pushing for it.|||Thanks

problem with an xpath parameter to StoredProc

Hey,
I am getting a parse error on this SP expression. I'd assumed this work
work.
error is "Incorrect syntax near the keyword 'exists'."
CREATE PROCEDURE dbo.sp_ListTemplates
(
@.PropXPath varchar(1024),
)
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
SELECT fileName, docProps, version
FROM Template
WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
END
GO
Chris Harrington
Active Interface, Inc.
http://www.activeinterface.comNever mind - "exist" not "exists"
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Seems it doesn't work after all. SP compiles but when I pass in an XPath
expression, it returns all records - regardless of the XPath I pass in:
EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
GO
-- returns all records, not just those which match xpath
Obviously I don't fully understand the use of sql:variable() here.
Anyone got a solution for passing a string param which is used as an XPath
expression?
Chris
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
> Hey,
> I am getting a parse error on this SP expression. I'd assumed this work
> work.
> error is "Incorrect syntax near the keyword 'exists'."
> CREATE PROCEDURE dbo.sp_ListTemplates
> (
> @.PropXPath varchar(1024),
> )
> AS
> BEGIN
> SET XACT_ABORT ON
> SET NOCOUNT ON
> SELECT fileName, docProps, version
> FROM Template
> WHERE docProps.exists('sql:variable("@.PropXPath")') = 1
> END
> GO
> Chris Harrington
> Active Interface, Inc.
> http://www.activeinterface.com
>|||Chris,
The behavior you're experiencing is by design. When you use sql:variable()
the way you do you construct a text node, and therefore the exist method wil
l
always return one since the expression didn't return the empty sequence. The
contents of the variable are in no way interpreted as an XPath expression.
Currently there is no way to parameterize the expression used by the exist
method. You can however construct the whole T-SQL statement in a string vari
able
and run it using sp_executesql.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:uTDXOEKlGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Seems it doesn't work after all. SP compiles but when I pass in an XPath
> expression, it returns all records - regardless of the XPath I pass in:
> EXEC dbo.sp_ListTemplates '/o:CustomDocumentProperties[o:Category="6"]';
> GO
> -- returns all records, not just those which match xpath
> Obviously I don't fully understand the use of sql:variable() here.
> Anyone got a solution for passing a string param which is used as an XPath
> expression?
> Chris
>
> "ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
> news:%23TqFrlBlGHA.4444@.TK2MSFTNGP02.phx.gbl...
>

problem with a date parameter in ASP.Net page

The date format in the 'weekending' textbox is 5/5/2007
Here's what I've got:

With cmd.Parameters
.Add(New SqlParameter("@.SQLweekending", SqlDbType.DateTime,weekending.Text))
the error message is:
"Conversion from string "4/8/2007" to type 'Integer' is not valid."

Then, I tried:
.Add(

New SqlParameter("@.SQLweekending", SqlDbType.DateTime,CDate(weekending.Text)))

but, before I even tried it, it gave me a blue squiggly line, with an error message:
"Error 2 Value of type 'Date' cannot be converted to 'Integer'"

How can I get this to work?

Hi,

What is the data type for yourSQLweekending column in your table? Is it a DateTime column or Integer type? You need a DateTime column for this.

|||Nope - it's datetime also|||

Here is sample in VB.NET that inserts date to a datetime column:

Dim MySQLAsString ="Insert into Dates (id, tDate) values (@.id, @.tDate)"Dim myConnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString.ToString())Dim CmdAsNew SQLCommand(MySQL, MyConn)

Cmd.Parameters.Add(

New SqlParameter("@.id", txtid.Text))

Cmd.Parameters.Add(

New SqlParameter("@.tDate",txttDate.Text))

MyConn.Open()

cmd.ExecuteNonQuery()

MyConn.Close()sql

Problem with &rc:toolbar=false parameter

I am having a problem turning the toolbar off in reports. When I add the
&rc:toolbar=false parameter to the URL, the report is displayed with two
vertical scroll bars. The inner scroll bar scrolls the report and the
external scroll bar scrolls down to what appears to be another report
containing the following:
"Reporting Services Error
----
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Cannot redirect after HTTP headers have been sent."
With &rc:toolbar=true or without the toolbar parameter, the report displays
fine with only one scroll bar. In this case the toolbar is shown and the
beginning of the URL changes from "http://web01/ReportServer" to
"http://web01/ReportServer/Pages/ReportViewer.aspx" followed by the
parameters.
Since the URL with ReportViewer.aspx displays the report correctly, I tried
to use it with the &rc:toolbar=true parameter but when the report is
displayed, the URL reverts to the one without ReportViewer.aspx and the two
scroll bars appear.
I am using RS2005 and displaying the report in a Windows application and if
the toolbar is shown, it wraps to two lines and consumes too much of the web
browser, so I would like to be able to turn the toolbar off but can't find a
way without getting the error.
Does anyone know how to turn the toolbar off or of anything in the report
that could be causing this behavior?
Thanks,
TomHi Tom,
Thank you for the post.
I have tested in my side, it works fine for me.
The report rendered in a whole html page.
Does this occured on all the report?
Here is a article about the Device information settings, you may try to
follow it to set up.
Reporting Services Device Information Settings
http://msdn2.microsoft.com/en-us/library/ms155397.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Most of our reports use groups with drill-down and those that do use
drill-down exhibit the problem with the toolbar being hidden. Did the
report you tested use drill-down?
The only option we have for setting toolbar=false is in the URL because when
we use a browser to show the reports, we want the toolbar but when the
report is displayed in the Windows app, we want the toolbar hidden.
Thanks,
Tom
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:PCCj9tHWGHA.4620@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Thank you for the post.
> I have tested in my side, it works fine for me.
> The report rendered in a whole html page.
> Does this occured on all the report?
> Here is a article about the Device information settings, you may try to
> follow it to set up.
> Reporting Services Device Information Settings
> http://msdn2.microsoft.com/en-us/library/ms155397.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tom,
I also tested with drill down report. It works fine.
Would you please try to post your error log here? By default, the error log
files are located at \Microsoft SQL Server\<SQL Server Instance>\Reporting
Services\LogFiles
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Problem with &

Hai

Iam using asp.net 2.0 with sql server 2005 as back end..

i need to pass the data to stored procedure via parameter containg (&) symbol

for ex:

If i pass string like "sample&ss"

it gives the error xml parsing how to avoid this..welcome for your suggestion.

Regards

Senthil

hi,

ampersand doesn't seems to cause an error on Sql server as the statement below

executes succesfully

select 'sample&ss'

I think the error happens in dot net since

where & is an operator such as

a$='xxx'&'yyy'

regards,

joey

|||

If the error is in parsing XML try this: "sample&amp;ss"

'&' is a special character in XML, and if you catually want to use it you need to represent it as &amp;

|||Or...use ASCII...it works all the time sql

problem with # sign

I have a stored proc that takes a parameter and finds the correct row.
Simple and easy. Unfortunately the data contains various symbols such as #
and -. When I run the proc and try to pass a value that contains a symbol I
get an error. The sp works fine when there are no symbols. Any
suggestions?
Laura KOh and here is the sp.
@.ProductCode nvarchar (5)
AS
SELECT pc.strcolorID, p.intproductID
FROM dbo.tblProducts p join tblJctProductsColors pc
On p.IntProductID=pc.intProductID
WHERE strProductCode = @.ProductCode
Simple sp. but wont work with symbols. I ran the proc with #322 and I get
an error. Sorry I am a newbie. Any help appreciated.
Thanks
Laura K
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a stored proc that takes a parameter and finds the correct row.
>Simple and easy. Unfortunately the data contains various symbols such as #
>and -. When I run the proc and try to pass a value that contains a symbol
>I get an error. The sp works fine when there are no symbols. Any
>suggestions?
> Laura K
>|||What about the table structures? You don't say what the column definitions
are.
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Hi
You may want to put checks into the client to stop data being passed that
will not be valid. You do not say what the error is, as #322 I would expect
no rows to be returned rather than any specific error.
John
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I get specific errors.
When I use 86-283 there error is
Line 1: Incorrect syntax near '-'
I also have spaces in some of the codes so when I use
spGetProductColor TUK Elvis W
Line 1: Incorrect syntax near 'Elvis'.
Upon closer inspection I have no problems with the # sign thus making the
title of my post obsolete. I do however have problems with the - and
spaces.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OnpT6tLoFHA.632@.tk2msftngp13.phx.gbl...
> Hi
> You may want to put checks into the client to stop data being passed that
> will not be valid. You do not say what the error is, as #322 I would
> expect no rows to be returned rather than any specific error.
> John
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||The column in question is a brandcode which may include numbers, letters or
what have you depending on the manufactures usage. The datatype is
nvarchar. It is not related to any other table. The table is related by the
productID to many other tables.
Not sure if this is what you are asking.
Laura
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:5rDLe.88549$G8.69248@.text.news.blueyonder.co.uk...
> What about the table structures? You don't say what the column
> definitions are.
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Laura,
try
exec spGetProductColor 'TUK Elvis W'
and see if that fixes it.
Stu|||It takes care of the error but now it returns nothing. However when I put
quotes around a value that has no spaces or dashes it still returns a color.
Laura
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124043325.349594.239730@.o13g2000cwo.googlegroups.com...
> Laura,
> try
> exec spGetProductColor 'TUK Elvis W'
> and see if that fixes it.
> Stu
>|||Excuse the fact that I am new at this but let me know how to post DDL.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124041314.536726.261720@.f14g2000cwb.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>

Problem when passing parameter to Execute SQL Task

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?\ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?\ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?\ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?

Thank you!

Did you set the "BypassPrepare" to True on the execute task? That gets me every time I use variables.

If you don't set that, it treats the ? as a literal and fails.|||

Hi!

Yes, BypassPrepare is set to true...

Thank you!

|||Setup another variable to hold your full string so that in your SQL you only have the ? placeholder instead of ?/ASPNET.

So, in my test, I created a new variable, set its expression to concatenate the User::ServerName variable and the /ASPNET string. Set the new variable to EvaluateAsExpression and then use it in the parameter mapping of the ExecuteSQL task.

Does that do what you're looking for?|||

Hi!

I tried what you suggest and I still have the error... Then I tried something simpler, CREATE LOGIN ? WITH PASSWORD = 'lalala', and I got the following error:

Executing the query "CREATE LOGIN ? WITH PASSWORD = 'lalala'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And if I change it to CREATE LOGIN test WITH PASSWORD = 'lalala' it works fine, so I guess it's not a connection problem. I guess I'm missing something silly!

Thank you!

|||What type of connection are you using? OLE DB, ADO, ADO.NET, ODBC, etc..

The connection type that you use will dictate how you name the parameter and then use a parameter in your SQL.|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx|||

I'm using an OLE DB connection, and it seems the parameter name is ok... Posting the code would help?

Thank you!

|||I have a variable (User::Test) set as a string with a value of "P0160".

In the Execute SQL Task editor, my sql statement is:
update client set clientdesc = 'testing' where client = ?

Then, in the parameter mapping section, I added a variable and selected User::Test as the variable name. Its data type is VARCHAR. The Parameter Name is simply, 0.

That's it. I run the task and it works correctly.|||It works now. Thank you very much for you help!

Problem using the Jump to report with a Parameter in a chart repor

I created a chart report and then created a second report which has a table
in it. The second report has a report parameter called DealStatus. I went to
the Chart report and edited the action tab on the edit chart values and
select the Jump to report and selected my report and clicked on the
Parameters button.
Entered DealStatus for the Paremeter Name and =Fields!value.Value for the
Prameter Value. First I had no default value and I was getting an
rsReadOnlyReportParameter error so I added a default value for DealStatus,
and now the chart report jumps to the second report but always uses the
default value.
MikeI found the problem I forgot to add the @.DealStatus to the Dataset query
"Hotwheels" wrote:
> I created a chart report and then created a second report which has a table
> in it. The second report has a report parameter called DealStatus. I went to
> the Chart report and edited the action tab on the edit chart values and
> select the Jump to report and selected my report and clicked on the
> Parameters button.
> Entered DealStatus for the Paremeter Name and =Fields!value.Value for the
> Prameter Value. First I had no default value and I was getting an
> rsReadOnlyReportParameter error so I added a default value for DealStatus,
> and now the chart report jumps to the second report but always uses the
> default value.
> Mike
>

Problem using parameter on SQL source data control

I have an ASP page which uses an SQL data source control, as shown below:

<asp:SqlDataSource ID="mySQL" runat="server"
ConnectionString="<%$ ConnectionStrings:mySQLConnectionString %>"
SelectCommand="SELECT device FROM device_table WHERE (device_id IN (@.devicelist))">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="devicelist" Type="String" />
</SelectParameters>
</asp:SqlDataSource
The problem is getting the "IN" clause of the select statement to work via the ASP page with code behind in C#. If I test the query in the page designer when configuring the SQL control by inserting 'device-1','device-2','device-3' as the value for the "devicelist" parameter, everything works as expected. However, when that same value is written programmatically into the DefaultValue of the parameter, the resulting query contains no results (as if the string contents are invalid). If I alter my program to supply a single entry (with no commas) it seems to work as well. I suspect the comma (and possibly the single-quote) characters in my string when multiple entries are involved are causing the issue.

Is there a proper technique for getting the requisite quote-delimited, comma-separated list into the SQL control command parameter correctly? Should I be using some sort of escape sequence for the single-quote and comma characters? Should I be using another "type" for the parameter; one that accepts an array of entries? I have not been able to find any examples or documentation that provides an answer.

A slight adjustment to the statements made in my earlier post: Using the "Test query" in the page design view for the control does NOT work. What does work is using SQL Server 2005 to run an equivalent query. So the page design view test query functionality and the ASP page functionality do indeed match, unlike what I stated earlier. I apologize for any confusion this may have caused.

But the problem still remains: how can I get the list of values passed into the control via the parameter?

Problem using a parameter in a subquery

The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
DuncanYou do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>

Problem using a parameter in a subquery

The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
Duncan
You do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>

Problem use sqldatasource to access stored procedure and get data bind to label control

Hi every experts

I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput...), after that, whatever I try, I click on Test Query Button at last part, I always get error messageThe Query did not return any data table.

My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web?

Thanks anyone, who can give me any advice.

Satoshi

Here's an example for you:

ASPX

<asp:label id="Label1" runat="server" /><asp:gridview id="GridView1" runat="server" datakeynames="ProductID" datasourceid="SqlDataSource1"></asp:gridview><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onselected="SqlDataSource1_Selected" selectcommand="SELECT * FROM [Products]; SELECT @.count = COUNT(*) FROM [Products];"><selectparameters><asp:parameter direction="Output" name="count" type="int32" /></selectparameters></asp:sqldatasource>

CODE-BEHIND

protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){Label1.Text = String.Concat("Records returned:", e.Command.Parameters["@.count"].Value);}}

|||

Hi ecbruck

Thanks very much your help, I'm able to make it work now.

PROBLEM- Use parameter to populate field in headerpage

Hi all,
I use hidden report parameters to populate textboxes in the pageheader and
come across this problem.
When the query for the default value for a parameter returns no rows, I get
an error "Parameter validation failed. It is not possible to provide valid
values for all parameters. (rsParameterError)".
This error is expected in this case. My question is is there a way to get
around this?
ThanksIn the query for the parameters dataset, force a record to be returned.
Union is a good function for this. I.e.,
Select Name From Table1 Where Type = 'whatever'
UNION
Select '(blank)'
Or you could do
If Exists(Select * From Table1 Where Type = 'whatever')
Select Name From Table1 Where Type = 'whatever'
Else
Select '(blank)' As Name
First option is more efficient but may have side effects for you.
Chris
Bach wrote:
> Hi all,
> I use hidden report parameters to populate textboxes in the
> pageheader and come across this problem.
> When the query for the default value for a parameter returns no rows,
> I get an error "Parameter validation failed. It is not possible to
> provide valid values for all parameters. (rsParameterError)".
> This error is expected in this case. My question is is there a way to
> get around this?
> Thanks