distinct name and email.

hey guys

.

you see how fenix.sn has a couple of entries BUT the same email and DIFFErENT BET IDS.

okay this is what i wanna do.

i wanna send an email to fenix.sn (just ONE email) saying you have won the following bets: xxx, xxx, xxx, xxx

then i wanna delete the entreis from the table.

masfenix,

There are several ways to accomplish this. Let me give you one of them here.

SELECT
[Username],
[Useremail],
WonBetIDs = REPLACE(
(
SELECT
WonBetID AS [data()]
FROM
tblUser tIn
WHERE
tIn.[Username] = tOut.[Username]
ORDER BY
WonBetID
FOR XML PATH ('')
), ' ', ',')
FROM
tblUser tOut
GROUP BY
[Username], [Useremail]

Hope this helps.|||

thanks for that ,but i am not gonna lie. i do not understand htat code at all. i am looking for a function.

|||It's good to be frank. Here is another implementation that may help you to understand easily. Since there isn't any function that does what you had mentioned, I have UDF to do the same for you here. Hope this helps.

Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.getWonBetIdsOfUser
(
@.Username VARCHAR(20)
)
RETURNS VARCHAR(2048)
AS
BEGIN
DECLARE @.WonBetIds VARCHAR(2048)

SELECT
@.WonBetIds = COALESCE(@.WonBetIds + ',', '') + [WonBetID]
FROM
tblUser
WHERE
[Username] = @.Username

RETURN @.WonBetIds

END
GO

Calling function
SELECT [Username], dbo.getWonBetIdsOfUser([Username])
FROM tblUser
GROUP BY [Username]|||

how do i actually call that stored proceudre?

i dont understand the "calling function".

note: i just wanna make a button called "send email" and it should automatically go through the list and send the emails. (but once for each user)

|||Use the query specified under Calling Function part in SqlCommand in your .NET app to get a dataset/datatable containing all username/email and bet ids. You can loop thru each records in the table and mail them from within your .NET application.

displaying reports w/o having to login

Hey guys, I finally got my report working and connections working to my webhost. Only problem is when I navigate to my reportserver, it prompts me for a username and password that corresponds to the system login. After that I have people enter their credentials that are cross referenced with the db. However, I don't want to give out the system password, otherwise other people who know the report manager url can get in and mess around. How do I avoid having the report server prompt me for the system user/pass?

Thanks.

Set an ASP.NET web site with the ReportViewer control on one of the pages. Grant run report to the ASP.NET user and then any user who can run the web site, can run a given report.|||

If you are using the report viewer, you can impersonate a user. You need to set it in the web.config of the application. It falls under the System.Web section of the web.config and looks something like this:

<identity impersonate="true" userName="UserName" password="PassWord"/>

This way when someone accesses the web application they are impersonating the user and they are not promted. What I would do if possible is setup a seperate user then admin of course that has access to the report server though for security reasons. That user should just need to be able to access the report server, and any other credentials he may need to run your application. Just my thoughts...

Dapanther99

|||

Thing is I cannot add that to the web.config where the reportserver resides. Otherwise, If I add that to the web.config where my asp files are located wouldn't that interfere with my existing scripts such as login pages?

Also, when you guys say report viewer, are you referring to the reportserver that allows me to view my reports but not manage them? Or is reportviewer as seperate item?

|||

The report viewer is a control in ASP.NET 2.0. I resides under the data controls. In respect to the impersonated user, we have applications here that use impersonation without any issues. What you need to do is put it in the web.config of you application, not with the report server. If you have access to create additional users then you would create a user that has access to the report server. You will also need to impersonate this same user in the dataset the report uses. As well as make sure that he has DB access. The good thing about doing it this way is you create a trail in case you are using audit tables in your DB...

Dapanther99

|||

ah ok, so by your definition, I am not using the report viewer, I'm using the direct url to the report server given to me by my hosting company,

(e.g.http://sqlserver/reportserver)

So I need to create an asp.net page that utilizes report viewer to access the reports from the report server. Am I correct up to this point?

Also, I did a search on my hosting company's KB, and they suggest using the following script:

<%
dim objHTTP
set objHTTP = Server.CreateObject("Microsoft.XMLHTTP")
url = "http://sqlreport01.mysite4now.com/ReportServer/Pages/ReportViewer.aspx?%2mysite%2fmyreport&rs:Command=Render"
objHTTP.open "get", url, false, "username", "password"
objHTTP.send
response.write objHTTP.responsetext
%>

Is this something I can skip? and just proceed to look up articles regarding the report viewer? (I've tried using that script in an asp page and I got nothing, thats why im asking)

Thanks

|||

If you are writing it in .NET 2.0 I would go with the report viewer. You might want to check with the hosting company to make sure that it is installed on the servers because if I remember correctly we had to manually install it on our servers when we deployed our first reports using it...

Dapanther99

|||

Great, I got the report viewer to work, now to get passed the iis authentication. I tried impersonate, but it's not working. When I do just <identity impersonate="true" /> the script runs, but the moment I add username and password, i get a run time error. Also, the script that I wrote above, from what i'm getting at is, that script opens up that page and inputs the username and password but doesn't display the page so in a sense you're opening a session. Am I correct?

I'd like to implement that if I can't get this impersonate thing to work but when I add that to my asp.net page, i also get a run time error.

any ideas guys?

|||

Does the error display or do you have access to the error logs? Let me know what you are getting and I'll try to figure it out. It sounds like you may have the impersonate tag in the wrong place. Also, make sure you have the authentication mode set to forms. Let me know...

Dapanther99

|||

Ok I added the line that gives me the actual error. It's saying that my username or password is incorrect. Now I'd like to get the concept straight.

I have a sql report server.
I have an asp page with the report viewer on another server. In that same folder I have a web.config file that has

<configuration>
<system.web>
<customErrors mode="Off"/>
<authentication mode="forms" />
<identity impersonate="true" userName="myuser" password="mypw" />
</system.web>

</configuration
1) Isn't it suppose to be windows auth? since the popup is IIS auth. (not that changing it to windows worked)
2) I'm getting the impression that it's looking for a local username on that server and not forwarding the request over to the report server.
3) This folder in which I have my asp.net page does not have user authentication.
4) This server and the report server both have the same user name and pw.

any ideas?

Displaying previous transaction result

Hi Guys,

I want to display a report like this

Date Balance on Date Credit Debit Current Balance
02/05/06 0 1200 0 1200
05/05/06 1200 500 0 1700
10/05/06 1700 0 200 1500
15/05/06 1500 200 0 1700

I mean for the new date the balnace of the previous day transaction should be displayed.
How can it be done?
Any help is greately appreciated.
Thanks in advanceIs the day's balance a value you calculate, or read straight from the database?
If the latter then use the 'Previous' function. See help.|||or

Create a formul having the code
{Credit}-{Debit}

and create running total based on that formula and use that in report

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de