Jumping drill-down report

I have a report with drill down grouped data and static header with
interactive sorting enabled.
1. When the user expands (by clicking + image) some groups and click on
table header for sorting, all the drill-down details which were expanded get
collapsed, can we configure the report to show the report in the same state
with expanded/collapsed data after sorted?
2. When the user click on the '+' symbol to exand the details with static
header and scrolled to the bottom, the report jumps and the selected row
gets hidden behind the header. How can we fix this?
Thanks and Regards,
SridharI also suffer with that problem and wrote the same thing to many other
places as well as this group but had no response. I have also
installed Sql Server SP2 and VS SP1.

Jump to URL...

Hey All,
I have a report that tells the user all of the applied published fixes
that are out there. The report has an ODBC connect to a .txt file on a
defferent server. That is finally working great (i hate permissions
:)). Now I have to take the Fix_nbr field (which is a calculated field)
and add a hyperlink to it. User is to click on the fix number in the
report and it should go to the defferent server and pick up the HTM
file about that particular fix number and display it to the user. What
happens is when I click on the fix number, the sign in box for the
defferent server comes up, I sign in...then I get "Page Cannot be
Found...HTTP Error 404 - File or directory not found."
The expression below is what I am using and I have tried different
variations of this.
="http://cmbsssharrisdev/Fixes/Applied - Published/" &
Fields!Fix_Nbr.Value & ".htm"
Anyone have any suggestions of why this might be happening, any help
would be great!
I have to have this done by EOD tomorrow.
Thanks in advance!!!!
KerrieCheck in the IIS logs of the different server and see what URL you are
actually attempting to view. Could it be that you need to put %20 in for the
spaces in the url?
"Kerrie" wrote:
> Hey All,
> I have a report that tells the user all of the applied published fixes
> that are out there. The report has an ODBC connect to a .txt file on a
> defferent server. That is finally working great (i hate permissions
> :)). Now I have to take the Fix_nbr field (which is a calculated field)
> and add a hyperlink to it. User is to click on the fix number in the
> report and it should go to the defferent server and pick up the HTM
> file about that particular fix number and display it to the user. What
> happens is when I click on the fix number, the sign in box for the
> defferent server comes up, I sign in...then I get "Page Cannot be
> Found...HTTP Error 404 - File or directory not found."
> The expression below is what I am using and I have tried different
> variations of this.
> ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> Fields!Fix_Nbr.Value & ".htm"
> Anyone have any suggestions of why this might be happening, any help
> would be great!
> I have to have this done by EOD tomorrow.
> Thanks in advance!!!!
> Kerrie
>|||I tried what you have suggested and it did not help. Any other
suggestions?
Do you think maybe that it is a HTM file i am trying to get from a
shared folder from a different server?
lsmithmier wrote:
> Check in the IIS logs of the different server and see what URL you are
> actually attempting to view. Could it be that you need to put %20 in for the
> spaces in the url?
> "Kerrie" wrote:
> > Hey All,
> >
> > I have a report that tells the user all of the applied published fixes
> > that are out there. The report has an ODBC connect to a .txt file on a
> > defferent server. That is finally working great (i hate permissions
> > :)). Now I have to take the Fix_nbr field (which is a calculated field)
> > and add a hyperlink to it. User is to click on the fix number in the
> > report and it should go to the defferent server and pick up the HTM
> > file about that particular fix number and display it to the user. What
> > happens is when I click on the fix number, the sign in box for the
> > defferent server comes up, I sign in...then I get "Page Cannot be
> > Found...HTTP Error 404 - File or directory not found."
> >
> > The expression below is what I am using and I have tried different
> > variations of this.
> >
> > ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> > Fields!Fix_Nbr.Value & ".htm"
> >
> > Anyone have any suggestions of why this might be happening, any help
> > would be great!
> > I have to have this done by EOD tomorrow.
> >
> > Thanks in advance!!!!
> > Kerrie
> >
> >|||NEVERMIND...I GOT IT WORKING! The directory was not regisiter through
IIS!!!
THANKS SO MUCH FOR THE SUGGESTION!
Kerrie wrote:
> I tried what you have suggested and it did not help. Any other
> suggestions?
> Do you think maybe that it is a HTM file i am trying to get from a
> shared folder from a different server?
>
> lsmithmier wrote:
> > Check in the IIS logs of the different server and see what URL you are
> > actually attempting to view. Could it be that you need to put %20 in for the
> > spaces in the url?
> >
> > "Kerrie" wrote:
> >
> > > Hey All,
> > >
> > > I have a report that tells the user all of the applied published fixes
> > > that are out there. The report has an ODBC connect to a .txt file on a
> > > defferent server. That is finally working great (i hate permissions
> > > :)). Now I have to take the Fix_nbr field (which is a calculated field)
> > > and add a hyperlink to it. User is to click on the fix number in the
> > > report and it should go to the defferent server and pick up the HTM
> > > file about that particular fix number and display it to the user. What
> > > happens is when I click on the fix number, the sign in box for the
> > > defferent server comes up, I sign in...then I get "Page Cannot be
> > > Found...HTTP Error 404 - File or directory not found."
> > >
> > > The expression below is what I am using and I have tried different
> > > variations of this.
> > >
> > > ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> > > Fields!Fix_Nbr.Value & ".htm"
> > >
> > > Anyone have any suggestions of why this might be happening, any help
> > > would be great!
> > > I have to have this done by EOD tomorrow.
> > >
> > > Thanks in advance!!!!
> > > Kerrie
> > >
> > >

Jump to report value for parameter, cube data

Have a problem with passing information between reports using Jump to report
facility.
User clicking on field value in report to pass to second report. This works
with the second report showing the value of field user has selected in the
parameter box (Available Values set to non-queried) but report shows an error
of:
restrictions imposed by the constrained flag in the STRTOSET function were
violated
Have tried many settings to correct this but with no luck. Does anybody
know how to correct this problem.
Thanks
IanIn case this is helpful to others I have found that the field parameter was
causing the problem. I was using Fields!FieldName.Value and should have used
Fields!FieldName.UniqueName
Ian
"IanP" wrote:
> Have a problem with passing information between reports using Jump to report
> facility.
> User clicking on field value in report to pass to second report. This works
> with the second report showing the value of field user has selected in the
> parameter box (Available Values set to non-queried) but report shows an error
> of:
> restrictions imposed by the constrained flag in the STRTOSET function were
> violated
> Have tried many settings to correct this but with no luck. Does anybody
> know how to correct this problem.
> Thanks
> Ian

Jump to Report Not Functioning Correctly

Hi all,
I'm having a problem with a series of reports I'm developing that allow the
user to design their own reports. They involve passing parameters (and
sometimes not passing parameters) between many different reports. The issue
I have is this:
I have an opening page with no parameters. When I jump to the next report
(using Jump to Report in Properties -> Navigation tab) it begins to load the
next report, but the parameter bar is missing (not hidden) and the report
itself doesn't load (although the report title is displayed in the top
frame).
If I use "Jump To URL", these issues appear to be less of a problem but
passing parameters is more difficult.
A couple of questions for you:
1) Has anyone else experienced this issue?
2) How can I pass parameters in a Jump to URL string, based on either report
parameters or report datasets?
I've had a look around on the forums and have found some similar problems,
which is why I ended up looking at the Jump to URL option. I haven't found
anyone with the same issue though. Can anyone help?!
Thanks in advance,
JonA little more information for you.
I've been trying to diagnose the issue, and I thought the problem was due to
passing parameters between reports but it now appears this isn't the case.
I have one report that has a subreport containing jumps to four other
reports. 3 of the reports do not work, but one does. None of the reports
require parameters to be passed, but the 3 that do not work have a parameter
that requires selection before the report will be shown.
If I place a default value in the report I'm jumping to, for the parameter
being passed (in this case, an ID for the report) it works fine. So the
issue appears to be when there is no default value for the parameter in the
report I'm jumping to. Unfortunately, I can't just set a default value in
this case.
Has anyone come across this or know a workaround other than setting a
default?
Thanks again,
Jon
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:%23SQ6CYsNFHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I'm having a problem with a series of reports I'm developing that allow
> the user to design their own reports. They involve passing parameters
> (and sometimes not passing parameters) between many different reports.
> The issue I have is this:
> I have an opening page with no parameters. When I jump to the next report
> (using Jump to Report in Properties -> Navigation tab) it begins to load
> the next report, but the parameter bar is missing (not hidden) and the
> report itself doesn't load (although the report title is displayed in the
> top frame).
> If I use "Jump To URL", these issues appear to be less of a problem but
> passing parameters is more difficult.
> A couple of questions for you:
> 1) Has anyone else experienced this issue?
> 2) How can I pass parameters in a Jump to URL string, based on either
> report parameters or report datasets?
> I've had a look around on the forums and have found some similar problems,
> which is why I ended up looking at the Jump to URL option. I haven't
> found anyone with the same issue though. Can anyone help?!
> Thanks in advance,
>
> Jon
>|||you have to set all parameters for the reports you are jumping to. This is
easier with the "Jump to Report" option - just click the Parameters button.
You can set hardcoded values are use other report items as input parameters.
For URL jumps - use "?paramenter_name=parameter_value" after the URL address
"Jonathan Martin" wrote:
> A little more information for you.
> I've been trying to diagnose the issue, and I thought the problem was due to
> passing parameters between reports but it now appears this isn't the case.
> I have one report that has a subreport containing jumps to four other
> reports. 3 of the reports do not work, but one does. None of the reports
> require parameters to be passed, but the 3 that do not work have a parameter
> that requires selection before the report will be shown.
> If I place a default value in the report I'm jumping to, for the parameter
> being passed (in this case, an ID for the report) it works fine. So the
> issue appears to be when there is no default value for the parameter in the
> report I'm jumping to. Unfortunately, I can't just set a default value in
> this case.
> Has anyone come across this or know a workaround other than setting a
> default?
> Thanks again,
>
> Jon
>
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:%23SQ6CYsNFHA.3188@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> >
> > I'm having a problem with a series of reports I'm developing that allow
> > the user to design their own reports. They involve passing parameters
> > (and sometimes not passing parameters) between many different reports.
> > The issue I have is this:
> >
> > I have an opening page with no parameters. When I jump to the next report
> > (using Jump to Report in Properties -> Navigation tab) it begins to load
> > the next report, but the parameter bar is missing (not hidden) and the
> > report itself doesn't load (although the report title is displayed in the
> > top frame).
> >
> > If I use "Jump To URL", these issues appear to be less of a problem but
> > passing parameters is more difficult.
> >
> > A couple of questions for you:
> >
> > 1) Has anyone else experienced this issue?
> > 2) How can I pass parameters in a Jump to URL string, based on either
> > report parameters or report datasets?
> >
> > I've had a look around on the forums and have found some similar problems,
> > which is why I ended up looking at the Jump to URL option. I haven't
> > found anyone with the same issue though. Can anyone help?!
> >
> > Thanks in advance,
> >
> >
> > Jon
> >
>
>|||Unfortunately it's still causing issues with passing the parameter values.
It does work fine with passing the parameter through the URL though.
No idea why, but it's working!
Thanks for the help.
"jj#10" <jj10@.discussions.microsoft.com> wrote in message
news:DDB82610-A1B2-4518-AB28-20D55D6B4BEF@.microsoft.com...
> you have to set all parameters for the reports you are jumping to. This
> is
> easier with the "Jump to Report" option - just click the Parameters
> button.
> You can set hardcoded values are use other report items as input
> parameters.
> For URL jumps - use "?paramenter_name=parameter_value" after the URL
> address
> "Jonathan Martin" wrote:
>> A little more information for you.
>> I've been trying to diagnose the issue, and I thought the problem was due
>> to
>> passing parameters between reports but it now appears this isn't the
>> case.
>> I have one report that has a subreport containing jumps to four other
>> reports. 3 of the reports do not work, but one does. None of the
>> reports
>> require parameters to be passed, but the 3 that do not work have a
>> parameter
>> that requires selection before the report will be shown.
>> If I place a default value in the report I'm jumping to, for the
>> parameter
>> being passed (in this case, an ID for the report) it works fine. So the
>> issue appears to be when there is no default value for the parameter in
>> the
>> report I'm jumping to. Unfortunately, I can't just set a default value
>> in
>> this case.
>> Has anyone come across this or know a workaround other than setting a
>> default?
>> Thanks again,
>>
>> Jon
>>
>> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
>> news:%23SQ6CYsNFHA.3188@.TK2MSFTNGP10.phx.gbl...
>> > Hi all,
>> >
>> > I'm having a problem with a series of reports I'm developing that allow
>> > the user to design their own reports. They involve passing parameters
>> > (and sometimes not passing parameters) between many different reports.
>> > The issue I have is this:
>> >
>> > I have an opening page with no parameters. When I jump to the next
>> > report
>> > (using Jump to Report in Properties -> Navigation tab) it begins to
>> > load
>> > the next report, but the parameter bar is missing (not hidden) and the
>> > report itself doesn't load (although the report title is displayed in
>> > the
>> > top frame).
>> >
>> > If I use "Jump To URL", these issues appear to be less of a problem but
>> > passing parameters is more difficult.
>> >
>> > A couple of questions for you:
>> >
>> > 1) Has anyone else experienced this issue?
>> > 2) How can I pass parameters in a Jump to URL string, based on either
>> > report parameters or report datasets?
>> >
>> > I've had a look around on the forums and have found some similar
>> > problems,
>> > which is why I ended up looking at the Jump to URL option. I haven't
>> > found anyone with the same issue though. Can anyone help?!
>> >
>> > Thanks in advance,
>> >
>> >
>> > Jon
>> >
>>|||I am having a similar problem, but I use jump to report and it works fine in
development environment (i.e. visual studio), but does not work live.
"Jonathan Martin" wrote:
> Unfortunately it's still causing issues with passing the parameter values.
> It does work fine with passing the parameter through the URL though.
> No idea why, but it's working!
> Thanks for the help.
> "jj#10" <jj10@.discussions.microsoft.com> wrote in message
> news:DDB82610-A1B2-4518-AB28-20D55D6B4BEF@.microsoft.com...
> > you have to set all parameters for the reports you are jumping to. This
> > is
> > easier with the "Jump to Report" option - just click the Parameters
> > button.
> > You can set hardcoded values are use other report items as input
> > parameters.
> >
> > For URL jumps - use "?paramenter_name=parameter_value" after the URL
> > address
> >
> > "Jonathan Martin" wrote:
> >
> >> A little more information for you.
> >>
> >> I've been trying to diagnose the issue, and I thought the problem was due
> >> to
> >> passing parameters between reports but it now appears this isn't the
> >> case.
> >>
> >> I have one report that has a subreport containing jumps to four other
> >> reports. 3 of the reports do not work, but one does. None of the
> >> reports
> >> require parameters to be passed, but the 3 that do not work have a
> >> parameter
> >> that requires selection before the report will be shown.
> >>
> >> If I place a default value in the report I'm jumping to, for the
> >> parameter
> >> being passed (in this case, an ID for the report) it works fine. So the
> >> issue appears to be when there is no default value for the parameter in
> >> the
> >> report I'm jumping to. Unfortunately, I can't just set a default value
> >> in
> >> this case.
> >>
> >> Has anyone come across this or know a workaround other than setting a
> >> default?
> >>
> >> Thanks again,
> >>
> >>
> >> Jon
> >>
> >>
> >> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> >> news:%23SQ6CYsNFHA.3188@.TK2MSFTNGP10.phx.gbl...
> >> > Hi all,
> >> >
> >> > I'm having a problem with a series of reports I'm developing that allow
> >> > the user to design their own reports. They involve passing parameters
> >> > (and sometimes not passing parameters) between many different reports.
> >> > The issue I have is this:
> >> >
> >> > I have an opening page with no parameters. When I jump to the next
> >> > report
> >> > (using Jump to Report in Properties -> Navigation tab) it begins to
> >> > load
> >> > the next report, but the parameter bar is missing (not hidden) and the
> >> > report itself doesn't load (although the report title is displayed in
> >> > the
> >> > top frame).
> >> >
> >> > If I use "Jump To URL", these issues appear to be less of a problem but
> >> > passing parameters is more difficult.
> >> >
> >> > A couple of questions for you:
> >> >
> >> > 1) Has anyone else experienced this issue?
> >> > 2) How can I pass parameters in a Jump to URL string, based on either
> >> > report parameters or report datasets?
> >> >
> >> > I've had a look around on the forums and have found some similar
> >> > problems,
> >> > which is why I ended up looking at the Jump to URL option. I haven't
> >> > found anyone with the same issue though. Can anyone help?!
> >> >
> >> > Thanks in advance,
> >> >
> >> >
> >> > Jon
> >> >
> >>
> >>
> >>
>
>|||Is the URL mapped to the correct "live" server (i.e. Not http://localhost/...)
Message posted via http://www.sqlmonster.com|||URL is mapped correctly and remember I am using "jump to report"
"Corey Crumbaugh via SQLMonster.com" wrote:
> Is the URL mapped to the correct "live" server (i.e. Not http://localhost/...)
>
> --
> Message posted via http://www.sqlmonster.com
>|||My guess is that the report in question (that you are jumping to) has a
credential problem. Can you run the report you are trying to jump to by
itself?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bamboo" <bamboo@.discussions.microsoft.com> wrote in message
news:A1910C53-32D2-40D2-BB75-D67AF6779793@.microsoft.com...
> URL is mapped correctly and remember I am using "jump to report"
> "Corey Crumbaugh via SQLMonster.com" wrote:
>> Is the URL mapped to the correct "live" server (i.e. Not
>> http://localhost/...)
>>
>> --
>> Message posted via http://www.sqlmonster.com|||I found that the problem is the 2nd report has to be runnable right away
(i.e. cannot prompt user to enter parameters when it is being executed from
another report). This is not a good function.
Thus, this goes back to what jonathan posed.
"Bruce L-C [MVP]" wrote:
> My guess is that the report in question (that you are jumping to) has a
> credential problem. Can you run the report you are trying to jump to by
> itself?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bamboo" <bamboo@.discussions.microsoft.com> wrote in message
> news:A1910C53-32D2-40D2-BB75-D67AF6779793@.microsoft.com...
> > URL is mapped correctly and remember I am using "jump to report"
> >
> > "Corey Crumbaugh via SQLMonster.com" wrote:
> >
> >> Is the URL mapped to the correct "live" server (i.e. Not
> >> http://localhost/...)
> >>
> >>
> >> --
> >> Message posted via http://www.sqlmonster.com
> >>
>
>

Jump to report and also URL based on parameters values

Hi,
i am doing a page whereby user can navigate to other report by clicking on
the respective column data and i am now using this navigating expression:
=Switch(Fields!Status.Value = "Pending", "EvaluatePending",
Fields!Status.Value = "Rejected", "ViewRejected", Fields!Status.Value = "Accepted", "ViewAccepted")
This expression only allows me to jump to other report but not to a URL
because i try using the following statement but got an error:
=Switch(Fields!Status.Value = "Pending",
"="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
Fields!SuggestionID.Value", Fields!Status.Value = "Rejected", "ViewRejected",
Fields!Status.Value = "Accepted", "ViewAccepted")
For the above statement what i want to achieved was that when user click on
the column with the Status=Pending, it will go to the
URL="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
Fields!SuggestionID.Value. If Status=Rejected, it will jump to the report
ViewRejected and lastly if Status=Accepted, it will jump to the report
ViewAccepted. Hope somebody can guide me with the correct expression.
Thanks in advance. =)Hello, is anybody able to help me with this problem? really need help with
this still not sure how to solve. Thanks
"JiaN" wrote:
> Hi,
> i am doing a page whereby user can navigate to other report by clicking on
> the respective column data and i am now using this navigating expression:
> =Switch(Fields!Status.Value = "Pending", "EvaluatePending",
> Fields!Status.Value = "Rejected", "ViewRejected", Fields!Status.Value => "Accepted", "ViewAccepted")
> This expression only allows me to jump to other report but not to a URL
> because i try using the following statement but got an error:
> =Switch(Fields!Status.Value = "Pending",
> "="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
> Fields!SuggestionID.Value", Fields!Status.Value = "Rejected", "ViewRejected",
> Fields!Status.Value = "Accepted", "ViewAccepted")
> For the above statement what i want to achieved was that when user click on
> the column with the Status=Pending, it will go to the
> URL="http://localhost/SSS-ASP/EvaluatePending.aspx?SuggestionID=" &
> Fields!SuggestionID.Value. If Status=Rejected, it will jump to the report
> ViewRejected and lastly if Status=Accepted, it will jump to the report
> ViewAccepted. Hope somebody can guide me with the correct expression.
> Thanks in advance. =)

Jump to report - ERR - One or more data sources is missing credent

I have a chart where I want to jump to a report when the user selects a
certain piece of the pie. In development, this works fine. ALSO from the
report viewer the report being JUMPed TO works fine individual, but when I
try to jump to it from the chart, I get the error, "One or more data sources
is missing credentials".
Anyone have any input on this?Just go to your data sources and go to credentials tab and key in your
passwrod again and save it and try running it. It will work.
Amarnath
"MSSQLServerDeveloper" wrote:
> I have a chart where I want to jump to a report when the user selects a
> certain piece of the pie. In development, this works fine. ALSO from the
> report viewer the report being JUMPed TO works fine individual, but when I
> try to jump to it from the chart, I get the error, "One or more data sources
> is missing credentials".
> Anyone have any input on this?|||'Data Sources' - You mean in the report manager? Will the end user have to
do this? I will try on Monday and let you know. Thanks in advance
"Amarnath" wrote:
> Just go to your data sources and go to credentials tab and key in your
> passwrod again and save it and try running it. It will work.
> Amarnath
>
> "MSSQLServerDeveloper" wrote:
> > I have a chart where I want to jump to a report when the user selects a
> > certain piece of the pie. In development, this works fine. ALSO from the
> > report viewer the report being JUMPed TO works fine individual, but when I
> > try to jump to it from the chart, I get the error, "One or more data sources
> > is missing credentials".
> >
> > Anyone have any input on this?sql

Jump to a group section of a subreport

I have a parent report, whenever the user click a item, the user will jump
from parent report to a corresponding section of subreport like a group
section.
How do I get such function?
Thanks for your kind help.I suggest instead of a subreport, implement this a a drill through. I.e.
when they click on it you jump to the report. Modify your subreport to
include another parameter so that the report shows just the subset of data
you want. This is very powerful and users understand it quickly. Drillthough
is a great way to present data to the user.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sally" <Sally@.discussions.microsoft.com> wrote in message
news:C9B8B574-EFBD-4790-9914-A2192BDF3C73@.microsoft.com...
> I have a parent report, whenever the user click a item, the user will
jump
> from parent report to a corresponding section of subreport like a group
> section.
> How do I get such function?
> Thanks for your kind help.

JScript Error when printing / exporting

I have one user who can't print or export a report. For over 100 other users, they are not having this issue.

This one user gets:

MicrosoftVisual Studio Debugger

Microsoft JScriptruntime error: Object doesn't support this property or method

...then another IE modal error of:

IE cannot download Format=EXCEL from "domain name"

IE was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

The user is on IE 6, sp2 which is a standard browser for all users.

Okay..finally found out the issue:

User had to install the ActiveX control when first viewing the reports!!!!!!!!!

Joins on uniqueidentifier columns

I have recently introduced Microsoft'suser/roles facility by running aspnet_regsql.exe against my database so that Iwould be able to link activities with staff members.

In testing I was able to

1. Join 2 tables (Reservations and aspnet_Users) with the join fields being of type uniqueidentifier, using SQL Server Manager Studio Express diagram facility;
2. Create a record in aspnet_Users using Microsoft's Website Admin tool
3. Create a record in Reservations and paste in the contents of uniqueidentifier field in aspnet_Users using SQL Server Manager Studio Express

All the basic tests had been fine so I created a web pagewith a detailsview of the Reservations table and made a templated field (in theinsertitemtemplate), replaced the text box with a dropdown and did a bind ofthat dropdown to the relevant records in the aspnet_Users table i.e. UserNameand UserId columns with UserName being displayed. The source is as follows:

<asp:TemplateFieldHeaderText="Taken By"SortExpression="RES_Taken_By_Staff_ID">

<EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="SDSStaff"DataTextField="UserName"

DataValueField="UserId">

</asp:DropDownList><br/>

<asp:SqlDataSourceID="SDSStaff"runat="server"ConnectionString="<%$ ConnectionStrings:ReservationsDBConnectionString%>"

SelectCommand="SELECT[UserId], [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Everything looks OK, the correct data appears in the dropdownbut when I hit the INSERT button I receive the following failure message:

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Description:An unhandled exceptionoccurred during the execution of the current web request. Please review thestack trace for more information about the error and where it originated in thecode.

Exception Details:System.Data.SqlClient.SqlException: Implicitconversion from data type sql_variant to uniqueidentifier is not allowed. Usethe CONVERT function to run this query.

I can't quite see what I should do so I would begrateful for any help.

HI

I hopeInserting with a SqlDataSource Using uniqueidentifier Parameters can help you.

|||

Hi,

Thank you for this. The references did not cover Template Fields where SqlDataSource does not have a type attribute so I'm still stymied!

|||

Me,too. There is clearly something strange about inserting a uniqueidentifier that is not your primary key column into SQL from ASP.net (like a foreign key).

I followed the same advice quoted in the article above. It worked for my select & update statements, but not for my insert statements.

I cannot insert a foreign key into a table from ASP.net at all. Please help if you found a fix or workaround for this problem.

|||

Hi,

Yes, very odd! I've reported this as a bug so maybe confirmation/correction may be available from MS

|||

My workaround for now is to convert the uniqueidentifier foreign key to an integer.

I managed to get it working, but it's not ideal

- Roger

|||

Please try delete all TemplateField filds in grid.

Bag in grid:

I haved

<asp:GridView ID="GridViewMain" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="FAQID" DataSourceID="SqlDataSourceMain"
EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="LotName" HeaderText="LotName" SortExpression="LotName" />
<asp:TemplateField HeaderText="From User" SortExpression="fromUser">
<ItemTemplate>
<asp:HyperLink ID="LinkFromUser" runat="server" Text='<%# Bind("[fromUser]") %>'
NavigateUrl='<%# "~/UserManagment/UserView.aspx?Filter=" + Eval("[From]") %>'>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
<EditItemTemplate>
</EditItemTemplate>

without EditItemTemplate i have error

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

sql

Joining two tables with repeating rows

Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.

UserAccess table:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User2 1 1

Modules table:

ModuleID Description

1 Mod1

2 Mod2

3 Mod3

4 Mod4

5 Mod5

What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User1 3 Null

User1 4 Null

User1 5 Null

User2 1 1

User2 2 Null

User2 3 Null

User2 4 Null

User2 5 Null

I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible? Thanks!!!

The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel

from

(select distinct UserID from UserAccess) u

cross join

Modules m

left join

UserAccess ua

on ua.moduleid = m.moduleid

and ua.userid = u.userid

Hope this works for you!

Rob|||

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId

|||Apparently I worked on my solution for at least 15 minutes :)|||15 minutes? Really?|||

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!

|||

You try to find out in

www.sqlzoo.com

join statement required

I have the following tables

tblFavourites
FavouriteSince datetime
UserCodeOwner int (the usercode of the user whose favouritelist this is)
UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)

EXAMPLE DATA
10/14/2006 7:32:30 PM 4 7
10/16/2006 11:24:01 PM 4 5
10/16/2006 10:55:08 PM 5 4

tblUserData
UserID uniqueidentifier
UserName nvarchar(50)
UserCode int

aspnet_Users
UserID uniqueidentifier
LastActivityDate datetime

Now I need a join statement that selects the fields
aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSince
for all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4

Could someone provide me with the join statement because I dont get it anymore :)

I think I'm a bit confused on your relationships... tblFavourites appears to have two columns that link to user records but its not clear where. Which table and column does UserCodeOwner and UserCodeFavourite link to? Do they link to the UserCode or UserID column of tblUserData or do they link to the aspnet_Users table instead?

Regards,

ZD

|||And I think I didnt explain that very well :)
The colums in the tblFavourites table link to the tblUserData.UserCode field.|||SELECT c.UserID,c.LastActivityDate,b.Username,b.UserCode,a.FavouriteSince
FROM tblFavourites a LEFT OUTER JOIN tblUserData b ON a.UserCodeOwner=b.UserCode
LEFT OUTER JOIN aspnet_Users c ON c.UserID=b.UserID
WHERE a.UserCodeOwner=4|||Thanks, but this statement selects the data for the UserCodeOwner (in this case 4).
I need the statement to select the data for all the UserCodeFavourite in which record the UserCodeOwner is 4.

Here's the content of tblUserData
UserID UserCode UserName

8ff3f242-2aab-499c-b0bf-f450969590ad4Jodie23e72b1a-b701-4b6d-9c39-83018dae8db15Jean71e1974f-8f1d-4e12-a0a6-7c8f3be87bc66Marie18ebec6d-adfa-4249-8c46-8daacca724e57Simon

Here's the content of tblFavourites:

FavouriteSince UserCodeOwner UserCodeFavourite

10/14/2006 7:32:30 PM4710/16/2006 10:55:08 PM5410/16/2006 11:24:01 PM4510/24/2006 10:07:46 PM79

And here's the content of aspnet_Users
UserID LastActivityDate
8ff3f242-2aab-499c-b0bf-f450969590ad 10/22/2006 10:32:44 PM
23e72b1a-b701-4b6d-9c39-83018dae8db1 10/16/2006 8:55:15 PM
230b9534-0d1e-4163-92f1-5f80927cac13 9/18/2006 7:53:49 PM
18ebec6d-adfa-4249-8c46-8daacca724e5 10/23/2006 9:55:45 PM

So in this case I want the LastActivityDate, UserName, FavouriteSince from UserCode 5 and 7
I need a select statement that for parameter usercode (value in this case 4) return:

LastActivityDate FavouriteSince UserName
10/16/2006 8:55:15 PM 10/16/2006 11:24:01 PM Jean
10/23/2006 9:55:45 PM 10/14/2006 7:32:30 PM Simon

I know this is tricky because a record in the aspnet_Users table is identified based on UserID, whereas in tblFavourites its on UserCode, so I need to get the matching UserCode and UserID from tblUserData in order to get the right lastactivitydate for a specific user.

Can you help me with the right statement?|||

SELECT u.LastActivityDate,f.FavouriteSince,ud.UserName

FROM tblFavourites f

JOIN tblUserData ud ON (ud.UserCode=f.UserCodeFavourite)

JOIN aspnet_Users u ON (u.UserID=ud.UserID)

WHERE f.UserCodeOwner=4

|||

If I understand your problem correctly, the following should work:

SELECT u.LastActivityDate
, f.FavouriteSince
, n.UserName
FROM aspnet_Users u, tblFavourites f, tblUserData n
WHERE n.UserID = u.UserID
AND n.UserCode = f.UserCodeOwner
AND n.UserCode = '4'

|||Sorry Motley - Didn't mean to step on your toes there. I didn't see your post until after I saved mine.|||Heh, no problem. As long as the question gets answered.|||

and that it is! :)

join multi server

hi,

consider this:

i have american users in a db called "user" in a server called AMERICA

and rest of the world users in a db called "user" in a server called OTHER

how can i do a join betwhen American's users and Rest of the word's User db?

SELECT *

FROM AMERICA.user.dbo.MyTable t1

JOIN OTHER.user.dbo.MyTable t2 ON t1.id=t2.id

|||Make sure you have set uplinked server to the remote SQL|||ok many thanks, it will be very usefull for my