Hide Dropdown parameter values based on User

I need to hide dropdown parameter values based on UserId. Any help with
User!UserId and custom code will be helpful. This is a Non-queried dropdown
and not based on query/dataset.
Thanks!To show either with drop down or without drop down, is not present in RS.
you didnt mention if it is not there, then do what ? means whether you want
emtpy or with one value or it should not show the drop down instead it should
run the report directly with some default values.
A round about way is to have a plain menu sort of report where you have two
options, I mean here is that when ever user clicks the reports, a screen will
be there with menu options like two hyperlinks (can be done using action)
before that you need to create a copy of the report, so you have now two
same reports one with drop down and the other without drop down.
Now depending on the user you can select or hide the textbox itself so the
user will be left with selecting one option.
If you dont understand I will put it in other words.
Before your two reports (one with dropdown and the other without dropdown)
one plain just hyperlink report will be there, where in user will select
the link to go to the respective reports.
Let me know if you have doubts.
Amarnath, MCTS
"RAGHAVAN JAYARAMAN" wrote:
> I need to hide dropdown parameter values based on UserId. Any help with
> User!UserId and custom code will be helpful. This is a Non-queried dropdown
> and not based on query/dataset.
> Thanks!|||On Mar 14, 3:01 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> To show either with drop down or without drop down, is not present in RS.
> you didnt mention if it is not there, then do what ? means whether you want
> emtpy or with one value or it should not show the drop down instead it should
> run the report directly with some default values.
> A round about way is to have a plain menu sort of report where you have two
> options, I mean here is that when ever user clicks the reports, a screen will
> be there with menu options like two hyperlinks (can be done using action)
> before that you need to create a copy of the report, so you have now two
> same reports one with drop down and the other without drop down.
> Now depending on the user you can select or hide the textbox itself so the
> user will be left with selecting one option.
> If you dont understand I will put it in other words.
> Before your two reports (one with dropdown and the other without dropdown)
> one plain just hyperlink report will be there, where in user will select
> the link to go to the respective reports.
> Let me know if you have doubts.
> Amarnath, MCTS
> "RAGHAVAN JAYARAMAN" wrote:
> > I need to hide dropdown parameter values based on UserId. Any help with
> > User!UserId and custom code will be helpful. This is a Non-queried dropdown
> > and not based on query/dataset.
> > Thanks!
Another way of doing it is to use '=User!UserID.ToString' as the input
parameter to the dataset (stored procedure/query) and in the stored
procedure/query, give a default value when UserID matches certain
criteria. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I guess i didn't mention properly, In this case say for instance User 1 , i
would want to display 2 values and for user 2 i would want to display 5 value
on the dropdown.
However if use a query dataset , then i could pass UserId as per Enrique
suggestion.
Thanks!
"EMartinez" wrote:
> On Mar 14, 3:01 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > To show either with drop down or without drop down, is not present in RS.
> > you didnt mention if it is not there, then do what ? means whether you want
> > emtpy or with one value or it should not show the drop down instead it should
> > run the report directly with some default values.
> > A round about way is to have a plain menu sort of report where you have two
> > options, I mean here is that when ever user clicks the reports, a screen will
> > be there with menu options like two hyperlinks (can be done using action)
> > before that you need to create a copy of the report, so you have now two
> > same reports one with drop down and the other without drop down.
> > Now depending on the user you can select or hide the textbox itself so the
> > user will be left with selecting one option.
> > If you dont understand I will put it in other words.
> >
> > Before your two reports (one with dropdown and the other without dropdown)
> > one plain just hyperlink report will be there, where in user will select
> > the link to go to the respective reports.
> > Let me know if you have doubts.
> >
> > Amarnath, MCTS
> >
> > "RAGHAVAN JAYARAMAN" wrote:
> > > I need to hide dropdown parameter values based on UserId. Any help with
> > > User!UserId and custom code will be helpful. This is a Non-queried dropdown
> > > and not based on query/dataset.
> >
> > > Thanks!
> Another way of doing it is to use '=User!UserID.ToString' as the input
> parameter to the dataset (stored procedure/query) and in the stored
> procedure/query, give a default value when UserID matches certain
> criteria. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Ok, So in that case the query which you have for displaying the drop down,
you can pass user id and get the filtered records depending on the user. But
you need to have queried drop down only. Otherwise it is not possible on a
non queried.
Amarnath, MCTS
"RAGHAVAN JAYARAMAN" wrote:
> I guess i didn't mention properly, In this case say for instance User 1 , i
> would want to display 2 values and for user 2 i would want to display 5 value
> on the dropdown.
> However if use a query dataset , then i could pass UserId as per Enrique
> suggestion.
> Thanks!
> "EMartinez" wrote:
> > On Mar 14, 3:01 am, Amarnath <Amarn...@.discussions.microsoft.com>
> > wrote:
> > > To show either with drop down or without drop down, is not present in RS.
> > > you didnt mention if it is not there, then do what ? means whether you want
> > > emtpy or with one value or it should not show the drop down instead it should
> > > run the report directly with some default values.
> > > A round about way is to have a plain menu sort of report where you have two
> > > options, I mean here is that when ever user clicks the reports, a screen will
> > > be there with menu options like two hyperlinks (can be done using action)
> > > before that you need to create a copy of the report, so you have now two
> > > same reports one with drop down and the other without drop down.
> > > Now depending on the user you can select or hide the textbox itself so the
> > > user will be left with selecting one option.
> > > If you dont understand I will put it in other words.
> > >
> > > Before your two reports (one with dropdown and the other without dropdown)
> > > one plain just hyperlink report will be there, where in user will select
> > > the link to go to the respective reports.
> > > Let me know if you have doubts.
> > >
> > > Amarnath, MCTS
> > >
> > > "RAGHAVAN JAYARAMAN" wrote:
> > > > I need to hide dropdown parameter values based on UserId. Any help with
> > > > User!UserId and custom code will be helpful. This is a Non-queried dropdown
> > > > and not based on query/dataset.
> > >
> > > > Thanks!
> >
> > Another way of doing it is to use '=User!UserID.ToString' as the input
> > parameter to the dataset (stored procedure/query) and in the stored
> > procedure/query, give a default value when UserID matches certain
> > criteria. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >

Hide and Show Background image dynamically

I am trying to place a background image on a report based on a database
field called watermark. I have not found a way to create an expresion
or code that would allow me to display this background image on some
pages based on the water mark field. Is it possible to do this?I have been working hard to try to figure this out. I would really
appreciate any help. I need to be able to stamp certain pages on a
multiple paged report with a watermark that says "PAST DUE".
Thanks in advance for any help you can give me|||> I have been working hard to try to figure this out. I would really
> appreciate any help. I need to be able to stamp certain pages on a
> multiple paged report with a watermark that says "PAST DUE".
We use an expression in the background image property of the Report Body.
This is how we do it:
=IIF(your condition, "image name", Nothing)
Hope this works for you...
MK

Hidding column in query

Here is my code.

SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
[Condo].[Weekly Rate], [Condo].[Linens]

FROM [Owner], [Condo]

WHERE [Condo].[Linens]=True

AND [Owner].[Owner ID]=[Condo].[Owner ID]

ORDER BY [Condo].[Unit Number];

I am trying to not display "Linens" column in my query result.Any idea how
can i achive that. Thanks in advanceW. Adam (wogla@.sbcglobal.net) writes:
> Here is my code.
> SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
> [Condo].[Weekly Rate], [Condo].[Linens]
> FROM [Owner], [Condo]
> WHERE [Condo].[Linens]=True
> AND [Owner].[Owner ID]=[Condo].[Owner ID]
> ORDER BY [Condo].[Unit Number];
> I am trying to not display "Linens" column in my query result.Any idea how
> can i achive that. Thanks in advance

Well, just don't display it!

Seriously, this is likely to be a GUI issue, and you should probably
ask in a forum devoted to the GUI tool you are using.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"W. Adam" <wogla@.sbcglobal.net> wrote in message
news:tL5Cb.11236$aw2.5741825@.newssrv26.news.prodig y.com...
> Here is my code.
> SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
> [Condo].[Weekly Rate], [Condo].[Linens]
> FROM [Owner], [Condo]
> WHERE [Condo].[Linens]=True
> AND [Owner].[Owner ID]=[Condo].[Owner ID]
> ORDER BY [Condo].[Unit Number];
>
>
> I am trying to not display "Linens" column in my query result.Any idea how
> can i achive that. Thanks in advance

There are two answers to this question. The first being, just don't select
the field

SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
[Condo].[Weekly Rate]
FROM [Owner], [Condo]
WHERE [Condo].[Linens]=True
AND [Owner].[Owner ID]=[Condo].[Owner ID]
ORDER BY [Condo].[Unit Number];

- or -

If you need the linens value but don't want to display it, handle this in
business layer (class, dll, module, mts, etc.), or your presentation layer
(application, web page, etc.).

BV.
www.iheartmypond.com

Hidden Expression enquiry

Help!!!
I have a group that is grouped on a category code...
Under that group is a details line that lists the transactions of that
grouped code
I then have a group footer that has a subtotal...
On my report I also have two parameters Date From and Date To...
In order to show only the category codes and details that are in the user
defined period I have entered the following into the hidden expression
dialog box on the three rows..
=iif(Fields!DATE.Value >= Parameters!Report_Parameter_FROM.Value AND
Fields!DATE.Value <= Parameters!Report_Parameter_To.Value,False, True)
Now for my question:
On my report the details is correct... it shows only the details that fall
between the two data parameters. However, the categoryID sometimes will be
displayed and other times will not. I think it has something to do with the
grouping... but I cannot for the life of me solve it.
Can anyone assist?All,
I have got around the problem through the use of a subreport... but can
anyone explain why this happened?
"AshVsAOD" <.> wrote in message
news:u3tcSIbhEHA.1644@.tk2msftngp13.phx.gbl...
> Help!!!
> I have a group that is grouped on a category code...
> Under that group is a details line that lists the transactions of that
> grouped code
> I then have a group footer that has a subtotal...
> On my report I also have two parameters Date From and Date To...
> In order to show only the category codes and details that are in the user
> defined period I have entered the following into the hidden expression
> dialog box on the three rows..
> =iif(Fields!DATE.Value >= Parameters!Report_Parameter_FROM.Value AND
> Fields!DATE.Value <= Parameters!Report_Parameter_To.Value,False, True)
> Now for my question:
> On my report the details is correct... it shows only the details that fall
> between the two data parameters. However, the categoryID sometimes will
be
> displayed and other times will not. I think it has something to do with
the
> grouping... but I cannot for the life of me solve it.
>
> Can anyone assist?
>

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.

Hidden Cascading parameters error with postback/refresh?

Hi

I have a report with a few cascading parameters. In my code I pass the value of the parameter to the report and set the flag to hide the parameter to true (isAdministrator).

ReportParameter dataEntity = new ReportParameter("DataEntity", "1", !isAdministrator);

Report.SetParameters(new ReportParameter[] { dataEntity } );

This works fine but as soon as I change any of the parameters the control viewer refreshes and loses my default value of the hidden parameter and sets it to the default I have set on the report when I designed it.

If I do not hide the "DataEntity" parameter the refresh works fine.

I need to hide this parameter or disable it so that the user can not change the value if they do not have permistion.

I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||

HI,

I am having the same problem. Have you found a resolution yet?

Thanks,

|||No, in the end I am not hiding the parameter.|||Can anyone shed some light on this? Is this a bug?
I am having same problem as the parameter does not refresh if some other parameter is changed.

For example if I change the event in the list box, the appropriate begin and end date is not modified?

Hi guyz.. IF Then

Can anyone tell me wat is the error in this code
SELECT dept_master.dept_name,
dept_master.dept_cd,
If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
Then Count(emp_master.emp_code) EndIf as leave,
If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
Then Count(emp_master.emp_code) EndIf as working,
If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
EndIf as term
FROM dept_master,
emp_master,
system_parametersHi
You can't use if within a select statement. Checkout the Case function in
Books Online. It is best to post DDL and example data, then you will get a
meaningful example see http://www.aspfaq.com/etiquette.asp?id=5006
You have no join conditions either so assuming you have a dept_code
SELECT d.dept_name,
d.dept_cd,
SUM(CASE WHEN e.leave_stat = 'L' AND e.emp_stat = 'A'
Then 1 ELSE 0 End) as leave,
SUM(CASE WHEN e.leave_stat = 'N' AND e.emp_stat = 'A'
Then 1 ELSE 0 End) as working,
SUM(CASE WHEN e.emp_stat = 'T' Then 1 ELSE 0 End) as term
FROM dept_master d
JOIN emp_master e ON e.dept_code = e.dept_code
GROUP BY d.dept_name, d.dept_cd
John
"pal" wrote:
> Can anyone tell me wat is the error in this code
> SELECT dept_master.dept_name,
> dept_master.dept_cd,
> If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as leave,
> If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as working,
> If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
> EndIf as term
> FROM dept_master,
> emp_master,
> system_parameters
>|||pal
Take a look at CASE expression in the BOL
"pal" <jayadevpaleri@.gmail.com> wrote in message
news:1189493775.303896.266250@.22g2000hsm.googlegroups.com...
> Can anyone tell me wat is the error in this code
> SELECT dept_master.dept_name,
> dept_master.dept_cd,
> If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as leave,
> If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as working,
> If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
> EndIf as term
> FROM dept_master,
> emp_master,
> system_parameters
>sql

Hi Everyone

trying to login to ... SQL.Exception occurs

The result is SQL Server does not exist or access denied . . .

The code Im working with connection string is a forum fix from

http://p2p.wrox.com/topic.asp?TOPIC_ID37898

and the user account has been established.

<appSettings>

<add key="connection String" Value="server=(local)\MYSQLSERVERNAME; database=bids; Trusted_Connection=true"/>

</appSetting>

SQL Service Manager shows Server: SNOEMASTER

Services: MSSQLServerOLAPService

SQL Server Enterprise Manager indicates my SQL Server Group consists of

(local) (Windows NT)

and somewhere along the line I ran into something telling me to rename

(local) (windows NT) = MSSQLServer

Although when I do switch it over through the new registration wizard the connection fails. Stating MSSQLServer does not exist and come up with a error 1722 (RPC Server Unavailable) although in componet services it shows it running.

Initially I tried correctinig this because SQL SERVER repilcation does not support nicknames such as "." or (local) as server names and was given a message to

DELETE THE SQL SERVER REGISTRATION FOR THE SERVER AND REGISTER IT USING THE ACTUAL SERVER NAME MSSQLServer which component services confirmed in the properties tab "Dispaly Name" = MSSQLSERVER.

The problem seems to become two -fold because in the above connection string it maintains (local) as part of the string and instructs me to add on MYSQLSERVERNAME. ex. (local)\MYSQLSERVERNAME; database="bids"

Note: The database folder is named Bid.

Thanks for any input on this one anyone.

What you need to do is to replace MYSQLSERVERNAME with whatever your instance name is. If your SQL Server is the default instance, then user (local) or . directly rather than (local)\MSSQLSERVER or .\MSSQLSERVER

|||

Thank You!

i'm getting an access denied error but i can reset the user property from here. its better then the other errors I was recieving!

It clears up alot.! will make additional instance's of it in the future, one step at a time of course. I just installed SQL 2000 recently and want to make sure i have a solid understanding of what was happening.

http://192.168.1.65/Bid/default.aspx

icon_snowman

|||

After having reinstalled the Bid database and set the IUSR_SNOEMASTER permissions for the it from the SQL enterprise manager Bid \Properties\Security directory , i'm still recieving the double error . SQL server does not exist or access is denied..

I tried several combinations off the above resolution you mentioned but , not working yet. Will try Later.

On the second try I recieved a popup box to remember the passwaord and a confirmation that the user was added to the pool although checking the web.config file I realized I did not replace its original extension and had left it at web.txt to edit it.

Ohhhh is me

|||

System.Data.SqlClient.SqlException: Cannot open database requested in login 'bids'. Login fails. Login failed for user 'SNOEMASTER\IUSR_SNOEMASTER'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at Bid.Bid.Person.AddCustomer(String familyname, String givenname, String emailaddress, String password, String add1, String add2, String city, String state, String zip, String country) in C:\development\Wrox\6195\Bid\Components\Person.vb:line 169

---

Latest SQL.Exception recieved using

<appSettings>
<add key="ConnectionString" value="server=(local);database=bids;Trusted_Connection=true" />
</appSettings>

Anyone have any ideas or previous same problem.?

icon_snowman

|||From your latest error, your client is able to find the SQL server. The new problem is that you login account may not have login privilege to the database "bids". In order to login successfully, the login account must have login priviledge to the initial database (i.e. bids for your case).|||

In the above example the user (IUSER_SNOEMASTER) was left out of the connection string on purpose.

If I include the user in the connection string ( value="server=(local)\(IUSR_SNOEMASTER);databes=bids; . . . )then the error SQL Server does not exsist or access is denied to the user.

System.Data.SqlClient.SqlException: SQL Server does not exist or access denied. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction

SQL Server Enterprise Manager:

Console Root|Microsoft SQL Servers|SQL Server Group|(local)(Windows NT)|Databases||bid|Users

In the right pane windows "Login Name" SNOEMASTER\ISUR_SNOEMASTER

"Database Access" status is Permit

If I use value="server=(local)\(SNOEMASTER\IUSR_SNOEMASTER); database=bids the following error occurs

Server Error in '/bid' Application.

---

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Access is denied: 'Bid'.
Source Error:

Line 256: <add assembly="System.EnterpriseServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/> Line 257: <add assembly="System.Web.Mobile, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/> Line 258: <add assembly="*"/> Line 259: </assemblies> Line 260:

Source File: c:\windows\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 258
Assembly Load Trace: The following information can be helpful to determine why the assembly 'Bid' could not be loaded.

=== Pre-bind state information === LOG: DisplayName = Bid (Partial) LOG: Appbase = file:///C:/Bid LOG: Initial PrivatePath = bin Calling assembly : (Unknown). === LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind). LOG: Post-policy reference: Bid LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/bid/4a7e3fd3/b5ae6ea7/Bid.DLL. LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/bid/4a7e3fd3/b5ae6ea7/Bid/Bid.DLL. LOG: Attempting download of new URL file:///C:/Bid/bin/Bid.DLL. LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind). LOG: Post-policy reference: Bid, Version=1.0.885.31008, Culture=neutral, PublicKeyToken=null

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

Using the code below:

<appSettings>
<add key="ConnectionString" value="server=(local);database=bids;Trusted_Connection=true" />
</appSettings>

<appSettings>
<add key="ConnectionString" value="server=.\;database=bids;Trusted_Connection=true" />
</appSettings>

The following error is created:

System.Data.SqlClient.SqlException: Cannot open database requested in login 'bids'. Login fails. Login failed for user 'SNOEMASTER\IUSR_SNOEMASTER'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction

|||

server=(local)\(IUSR_SNOEMASTER);
Here (IUSR_SNOEMASTER) should be the instance name, NOT USER ACCOUNT. If your SQL Server is a default instance, then just use "(local)" or "." The client user account is determined by under which Windows account your client application is running if Windows authentication is used. From your last exemple, it looks like you have a default instance and your client app is running under 'SNOEMASTER\IUSR_SNOEMASTER'. So, you need to make sure this account has login priviledge to database "bids", as I said before.

|||

OK Have Login Working:

<appSettings>
<add key="ConnectionString" value="server=(local);database=Bid;Trusted_Connection=true" />
</appSettings>

The problem was that the database=bids was not allowing it to access the Bid folder.

Initially building the data base as prescribed in the http://p2p.wrox.com/toTOPIC_ID=37898 pic.asp?the query uses a plural version of the database "bids" so I continued with this in the connection string. Doesnt work. I read about two lines in the SQL Server Books Online stating that the database names are case sensitive and made the adjustment from bids=Bid and a slight adjustment in the permissions by :

Console Root|Microsoft SQL Servers|(local)(Windows NT)|Databases|bid

In the right-hand pane right clicked properties, highlight SNOEMASTER\IUSR_SNOEMASTER

Clicked Permissions and set settings as instructed http://p2p.wrox.com/toTOPIC_ID=37898 pic.asp?

Clicked Ok then clicked Properties on the bottom. Check that Public is selected.(For what ever reason mine was not. Possibly an over sight having reinstalled the database and permissions.)

Once again set the settings as instructed http://p2p.wrox.com/toTOPIC_ID=37898 pic.asp?and clicked apply and OK and everything seems to be running as it is supposed to.

Thank You Xinwei Hong your response helped me sort it out. Knowing better I should have immediately changed bids=Bid If you recall I made a note of this in my first post at the bottom of the name difference.

HELP: problem with using xp_sendmail

below is code snippet (which im assuming is the cause of the problem) from a certain stored procedure

EXEC Master.dbo.xp_sendmail
@.dbuse = 'DanoneSSS',
@.recipients = 'xxx@.yyy.co.jp',
@.subject = 'mmm nnn',
@.message = @.parMessage,
@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE

as in my case, when i execute this particular stored procedure, SQL Server sends me the email AND with the necessary attachment

however, when i create a Job with "EXEC dbo.SOSOStoredProcedure," though the Job executes successfully, no EMAIL is sent to me.

the funny thing is, when i comment out the following lines

@.query = 'SELECT CHECK_JS_CODE, CHECK_TYPE FROM T_CHECK_LOG WHERE CHECK_STATUS = 0 ORDER BY CHECK_ID',
@.attachments = 'ResultSet.txt',
@.attach_results = TRUE,
@.no_header = TRUE,
@.separator = '',
@.ansi_attachment = TRUE

the Job executes successfully with an EMAIL being sent to me

does SQL Server's job CANNOT handle xp_sendmail that returns a RESULT set? or i may be mistaken somewhere...

thanks!Who is the owner of the job and what is the permission level ?|||Originally posted by rnealejr
Who is the owner of the job and what is the permission level ?

thanks for the reply...

but i did getting it working though...

by carefully reading through BO regarding xp_sendmail, it seems that in using xp_sendmail to send file attachments, the specific procedure must be defined in the master database. this is what i have done.

then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.

i may have gotten it working... but there still may be a gap in my understanding, and i would appreciate if anyone could check on this - Books Online could get so scarce in regards to explanations and examples.

again, thanks...|||the specific procedure must be defined in the master database

This is only done if the sa wants to conceal the behavior of xp_sendmail.
Which if that were the case you would have been unsuccessful regardless of where you ran the stored procedure.

then, defined a permission level for the procedure's properties, and the specific entry in SQL Agent.

Can you elaborate ?|||Alter the SP.
In the same sp, use exec master..xp_cmdshell 'isql "Your query" -o drive:\filename.txt etc'
Then remove the query portion from xp_sendmail.
Run the job again. It would work.|||I beleive (rightly or wrongly) that you are unable to run an open query within an xp_sendmail.

I am trying to run a stored procedure with an open query within it. wrapping it within an xp_sendmail.

Which fails.

Any sugestions on how to get this running.

Thanks

help: Passing values to formula fields of subreport from the code in vb.net

hi,
i'm new in CR, i made a subreports(ReCaisse) in a main report(rptCaisseAll) and i established many formula fields in it, i want to fill the formula fields from the code in vb.net.
this is the code:
***********************************************
Dim SoCaisse As New ClsCaisse() //object caisse
Dim Report As New rptCaisseAll //main report
Dim r1 As New ReCaisse //Subreport

//
SoCaisse.GetRecord("Exchrate,AmountUS,AmountFc,s.Notes,s.DbCr,AccName,s.AccNo", "EntryDate=convert(datetime,''" & Me.txtDate.Text & "'',103)", "s.DbCr") //GetRecord establish sql statement

SoCaisse.SelectspecialMethod() //stored procedure

Report.Database.Tables.Item(0).SetDataSource(SoCaisse.CaisseSet.Tables(0))
Report.DataDefinition.FormulaFields.Item("Date").Text = "totext('" & Me.txtDate.Text & "')" // Date is a formula fields in rptCaisseAll-main report
Report.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & Me.txtCUs.Text & "')" //SoldeUS is a formula fields in ReCaisse-subreport
**********************************************************
it passed the value to "Date" formula coz "Date" is a formula in the main report, but it not passed the value to "SoldeUS" formula fields; It appears blank.
so i tried:
***********************************
r1.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & Me.txtCUs.Text & "')"
***********************************
but also not passed.

any help plz...
thanks.If Me.txtCUs.Text is a date then, format it

r1.DataDefinition.FormulaFields.Item("SoldeUS").Text = "totext('" & format(Me.txtCUs.Text,"dd-mmm-yyyy") & "')"

Help: Append Parameters in ASP classic

Hi,

Please tell me what is wrong in the following code to get the RecordSet by calling the Stored Procedure.

'==============================================
Dim StaffRS, objComm, objParam1, objParam2, objParam3

'Set RecordSet, ADODB.Command
Set StaffRs = Server.CreateObject("ADODB.RECORDSET")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = conn 'conn was set earler
objComm.CommandText = "GetStaffReport" 'Stored Procedure
objComm.CommandType = adCmdStoredProc '**Error line**

'Append Parameters
set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, "123")
objComm.Parameters.Append objparam1
set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, 02/02/2006)
objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateHigh", adDate, adParamInput, 02/15/2006)
objComm.Parameters.Append objparam3

set StaffRS = objComm.Execute
'Close up work...
'============================================

The Stored Procedure is:
'********************************************
CREATE PROCEDURE GetStaffReport
(@.People_ID varchar(32), @.Weeklow datetime, @.Weekhigh datetime)

AS

SELECT * FROM view_People WHERE
Main_People = @.People_ID AND Main_Ending >= @.WeekLow AND Main_Ending <= @.Weekhigh
Order by Main_Ending DESC

RETURN
GO
'*********************************************

Everything looks correct, but I got an error:
ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/folder/folder/file.asp, line (**Error line**)

Please give me any information about this error, and how to correct it.

Thank you.

Looking at your code

set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, "123")
objComm.Parameters.Append objparam1
set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, "02/02/2006")
objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateHigh", adDate, adParamInput, "02/15/2006")
objComm.Parameters.Append objparam3

Won't you qoute two parameter values listed above? Without quotes you pass 02 divided by 02 divided by 2006 (very small number) as a value for datetime.

WBR, Evergray
--
Words mean nothing...

|||

Thank you, WBR,

Actually, the "02/02/2006" is what I put here in the forum, there is a real parameter Week1 with a gaven format: Week1= FormatDateTime (WeekEndingLow,2), which is 2/02/2006. The "02/15/2006" is the same way. So the lines in my real code is:

set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, Staff_ID)
objComm.Parameters.Append objparam1

set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, Week1)objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, Week2)objComm.Parameters.Append objparam3

But it still has the error:"800a0bb9"

By the way, the stored procedure I called is reading the data from a view:

"SELECT * FROM view_People" the view_People is a view, not a table, I don't know if this is a problem.

Thank you for your help.

|||

maybe you should do this;

objComm.CommandType = adCmdStoredProc '**Error line**
objComm.CommandType = 4

good luck

Help: Append Parameters in ASP classic

Hi,

Please tell me what is wrong in the following code to get the RecordSet by calling the Stored Procedure.

'==============================================
Dim StaffRS, objComm, objParam1, objParam2, objParam3

'Set RecordSet, ADODB.Command
Set StaffRs = Server.CreateObject("ADODB.RECORDSET")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = conn 'conn was set earler
objComm.CommandText = "GetStaffReport" 'Stored Procedure
objComm.CommandType = adCmdStoredProc '**Error line**

'Append Parameters
set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, "123")
objComm.Parameters.Append objparam1
set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, 02/02/2006)
objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateHigh", adDate, adParamInput, 02/15/2006)
objComm.Parameters.Append objparam3

set StaffRS = objComm.Execute
'Close up work...
'============================================

The Stored Procedure is:
'********************************************
CREATE PROCEDURE GetStaffReport
(@.People_ID varchar(32), @.Weeklow datetime, @.Weekhigh datetime)

AS

SELECT * FROM view_People WHERE
Main_People = @.People_ID AND Main_Ending >= @.WeekLow AND Main_Ending <= @.Weekhigh
Order by Main_Ending DESC

RETURN
GO
'*********************************************

Everything looks correct, but I got an error:
ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/folder/folder/file.asp, line (**Error line**)

Please give me any information about this error, and how to correct it.

Thank you.

Looking at your code

set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, "123")
objComm.Parameters.Append objparam1
set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, "02/02/2006")
objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateHigh", adDate, adParamInput, "02/15/2006")
objComm.Parameters.Append objparam3

Won't you qoute two parameter values listed above? Without quotes you pass 02 divided by 02 divided by 2006 (very small number) as a value for datetime.

WBR, Evergray
--
Words mean nothing...

|||

Thank you, WBR,

Actually, the "02/02/2006" is what I put here in the forum, there is a real parameter Week1 with a gaven format: Week1= FormatDateTime (WeekEndingLow,2), which is 2/02/2006. The "02/15/2006" is the same way. So the lines in my real code is:

set objParam1 = objcomm.CreateParameter ("People_ID", adVarChar, adParamInput, Staff_ID)
objComm.Parameters.Append objparam1

set objparam2 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, Week1)objComm.Parameters.Append objparam2
set objparam3 = objcomm.CreateParameter ("DateLow", adDate, adParamInput, Week2)objComm.Parameters.Append objparam3

But it still has the error:"800a0bb9"

By the way, the stored procedure I called is reading the data from a view:

"SELECT * FROM view_People" the view_People is a view, not a table, I don't know if this is a problem.

Thank you for your help.

|||

maybe you should do this;

objComm.CommandType = adCmdStoredProc '**Error line**
objComm.CommandType = 4

good luck

Help..Error connecting to SQL Server

Hi,

I am writing my first aspx file to connect to a standalone SQL Server which is separated from the IIS webserver. The code is

<HTML>
<HEAD>
<TITLE>Store Locator</TITLE>
<script runat="server">
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
if Not IsPostBack Then
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
DBConn = New OleDbConnection("Provider=sqloledb;" _
& "server=dailyplanet;" _
& "Initial Catalog=JAVSTORE;" _
& "User Id=javtrader;" _
& "Password=rage123;")
DBCommand = New OleDbDataAdapter _
("Select * " _
& "From JAVInventory " _
& "Where ID > ((Select COUNT(ID) " _
& "From JAVInventory ) - 100)" _
& "Order By ID DESC", DBConn)
DBCommand.Fill(DSPageData, _
"RecentJAVs")
' ddlZipCode.DataSource = _
' DSPageData.Tables("RecentJAVs").DefaultView
' ddlZipCode.DataBind()
End If
End Sub

I used Visual .Net and Web Matrix and was able to connect to the sql server with the Data Connection to view the tables. However, when I use IIS or the Visual .Net debugger to run the aspx file, it get the following error.. please help..

Server Error in '/javtrade' Application.
------------------------

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Source Error:

Line 21: & "From JAVInventory ) - 100)" _
Line 22: & "Order By ID DESC", DBConn)
Line 23: DBCommand.Fill(DSPageData, _
Line 24: "RecentJAVs")
Line 25: ' ddlZipCode.DataSource = _

Source File: c:\inetpub\wwwroot\javtrade\management.aspx Line: 23

Stack Trace:

[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.management_aspx.Page_Load(Object Sender, EventArgs E) in c:\inetpub\wwwroot\javtrade\management.aspx:23
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731Two suggestions, one is to supply a 'server' name of localhost. The second is to make sure that the ASP.NET user has access to the SQL server.

Josh