|
SELECT DISTINCT does not work the way I want.
SELECT DISTINCT works for the class column but not for name_event column
If "MyId" is e.g. CW and there are 4 lines there but name_event should only be 2 pcs, but it shows 4 pcs
Now display:
World Championships
French Champioships
World Championships
World Championships
It should be:
World Championships
French Champioships
What am I doing wrong?
<pre> Dim MyDataAdapter As New SqlDataAdapter("SELECT DISTINCT class, nationality, name_event from statistics_results Where class = @class", MyConnection)
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@class", SqlDbType.VarChar, 10))
MyDataAdapter.SelectCommand.Parameters("@class").Value = MyId
Try
Dim dt As New DataTable()
MyDataAdapter.Fill(dt)
Dim natlist = dt.AsEnumerable().[Select](Function(r) r("nationality").ToString())
Dim natvalue As String = String.Join("- ", natlist)
litNat.Text = natvalue
Dim eventlist = dt.AsEnumerable().[Select](Function(r) r("name_event").ToString())
Dim eventvalue As String = String.Join("<br>", eventlist)
litEvent.Text = eventvalue
Catch exc As SqlException
Finally
MyConnection.Close()
MyConnection.Dispose()
End Try
|
|
|
|
|
What is your data?
kjell.ek wrote: SELECT DISTINCT class, nationality, name_event from statistics_results Where class = @class
All three columns (Class, Nationality, name_event) make it distinct. if you look at the data, those 4 rows are distinct from each other when you include all columns.
In other words if you do a
SELECT DISTINCT name_event from statistics_results Where class = @class
You will get two rows.
HTH
Jack of all trades, master of none, though often times better than master of one.
|
|
|
|
|
Hello Kjell.ek !
as distinct can be apply on one column,
you can use it this way :
SELECT id, distinct(name_event), class, nationality from statistics_results;
the sql engine will keep in the record with each name_event only one time.
DISTINCT statement acts at column level.
|
|
|
|
|
The OP is using a SqlDataAdapter, which means they're using Microsoft SQL Server.
That is not how DISTINCT works in SQL Server.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Sorry I missed the data source, yes I use MS SQL server. Have tried the variants above but I get the wrong number of "name_event"
I get numbers according to how many rows there are in the selected class.
This is how it will be at a selected class. It should be 1 World Championships note 6
Tips on how to solve it?
World Championships
World Championships
World Championships
World Championships
World Championships
World Championships
DEU - FIN - FRA - NOR - POL - SWE
modified 6-Mar-23 8:07am.
|
|
|
|
|
Can you show what the result set is without the distinct please..
SELECT class, nationality, name_event from statistics_results Where class = 'xx'
|
|
|
|
|
Having trouble importing competition results from Excel to MS SQL Server with Asp.Net. Worked well until I added milliseconds. They are lost on import. Anyone with good tips on what the problem is?
MS SQL server Data Type: time(7)
Excel formatting for time results (Swedish Excel): tt:mm:ss,0
In asp.net
MyCommand.Parameters.Add(New SqlParameter("@total", SqlDbType.Time, 7))
MyCommand.Parameters("@total").Value = Trim(rad("Total"))
Time in Excel: 00:26:32,4
In SQL server: 00:26:32
I lost the milliseconds.
|
|
|
|
|
That Trim call suggests you're treating the data as a String. You should parse it to the correct type (TimeSpan) before passing it to SQL.
Since your data is formatted for a culture which uses the comma as the decimal separator, you will need to ensure you use the correct CultureInfo when you parse the value.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for the tip 
|
|
|
|
|
Format 109 is like "Jun 17 2012 2:00:29:147PM", so obviously [Start Date] is varchar already before the convert.
I suspect that that problem is that Excel thinks that this is a datetime value, and that's why the milliseconds are lost in transport. Excel is not really the best venue for strict data-typing, but rather the drivers makes some guesswork. You could try to format the column as text, but I am not sure it would help.
I never use OPENROWSET/OPENDATASOURCE to read Excel files myself. For one-offs I save the file as a CSV and import it with BCP. For imports on regular basis, I would write a client program for the task. Many people would go for a soluton with Integration Services.
|
|
|
|
|
Hi all,
where in the lifecycle of an asp.net page do you load the data for a gridview?
In Page_Load (which runs twice and check on !IsPostback is needed), in Page_LoadComplete (runs once) or in Page_Prerender (runs once)
I see better/smoother working of the pages when loading in Page_Loadcomplete event. How are your experiences?
I use Page_Prerender to set properties of a GridView other than DataSource and DataBind.
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
|
This is a horrible suggestion. Only tested SelectMethod.
AJAX starts yelling about Registerscripts issues
GridView can't set styling anymore via my Library (Node isues ??)
And Async leads to exception it needs a return Task (which it is).
Back to my way
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
So... yeah.
Don't show the code you tried, don't ask questions, don't describe any problems with proper detail, ...
Just bash the suggestion with summary judgement because it wasn't as simple to implement as you expected.
Sure.
|
|
|
|
|
Been working all weekend on it. Using the SelectMethod and Creating the method in codebehind is no problem. But when everything stays yelling except loading data I am done with it. Read enough on it. Have over 11,000 users of this site of mine and I am not gonna take risks.
Besides it is really strange that the SelectMethod in a page leads to a break in an UpdateProgress control in the masterpage. If I put that in comments, it is claiming for reducible nodes.
In Word you can only store 2 bytes. That is why I use Writer.
modified 2-May-22 10:14am.
|
|
|
|
|
And now in CODE
(used the ScottGu mentioned code in the link from first reply)
Created a clean asp.net 4.7.2 webforms project.
I have an ODATA service which is read via Unchase OData library
Index.Asp has the gridview:
<asp:GridView runat="server" ID="GrdTest"
AutoGenerateColumns="false" DataKeyNames="Id"
ItemType="MarinGlobalService.Models.User"
SelectMethod="GrdTest_GetData"
>
<Columns>
<asp:DynamicField DataField="Name" HeaderText="Name" />
<asp:DynamicField DataField="SurName" HeaderText="Surname" />
<asp:DynamicField DataField="GivenName" HeaderText="GivenName" />
<asp:TemplateField HeaderText="# of Usergroups">
<ItemTemplate><%# Item.UserGroup.Count %></ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
My codebehind:
namespace TestGridView
{
public partial class Index : Page
{
private Default.Container container;
protected void Page_Init(object sender, EventArgs e)
{
Uri uri = new Uri("<a href="http:
container = new Default.Container(uri)
{
Credentials = CredentialCache.DefaultNetworkCredentials,
Timeout=3600
};
}
protected void Page_LoadComplete(object sender, EventArgs e)
{
GrdTest.BackColor = System.Drawing.Color.LightGreen;
GrdTest.AlternatingRowStyle.BackColor = System.Drawing.Color.LightBlue;
GrdTest.RowStyle.BackColor = System.Drawing.Color.LightGray;
}
public IQueryable<User> GrdTest_GetData(int? maximumRows, int? startRowIndex)
{
if (maximumRows == null || startRowIndex == null)
return container.Users;
return container.Users.Skip(startRowIndex ?? 0).Take(maximumRows ?? 0);
}
}
}
This will lead to 1 gridview without paging and shows data.
Then I change my GridView to:
<asp:GridView runat="server" ID="GrdTest"
AutoGenerateColumns="false" DataKeyNames="Id"
ItemType="MarinGlobalService.Models.User"
SelectMethod="GrdTest_GetData"
AllowPaging="true" PageSize="5"
>
<Columns>
<asp:DynamicField DataField="Name" HeaderText="Name" />
<asp:DynamicField DataField="SurName" HeaderText="Surname" />
<asp:DynamicField DataField="GivenName" HeaderText="GivenName" />
<asp:TemplateField HeaderText="# of Usergroups">
<ItemTemplate><%# Item.UserGroup.Count %></ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
And the answer is:
[ArgumentException: must be reducible node]
System.Linq.Expressions.Expression.VisitChildren(ExpressionVisitor visitor) +97
System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node) +11
System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor) +12
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) +20
System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes) +60
System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) +43
System.Web.Util.OrderingMethodFinder.VisitMethodCall(MethodCallExpression node) +43
System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) +12
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) +20
System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes) +60
System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) +43
System.Web.Util.OrderingMethodFinder.VisitMethodCall(MethodCallExpression node) +43
System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) +12
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) +20
System.Web.Util.OrderingMethodFinder.OrderMethodExists(Expression expression) +38
System.Web.UI.WebControls.QueryableHelpers.IsOrderingMethodFound(IQueryable`1 queryable) +16
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +168
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +105
System.Web.UI.WebControls.ModelDataSourceView.ProcessSelectMethodResult(DataSourceSelectArguments arguments, DataSourceSelectResultProcessingOptions selectResultProcessingOptions, ModelDataMethodResult result) +393
System.Web.UI.WebControls.ModelDataSourceView.GetSelectMethodResult(DataSourceSelectArguments arguments) +86
System.Web.UI.WebControls.ModelDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +14
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.ModelDataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +81
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +169
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +71
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +93
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
System.Web.UI.Control.EnsureChildControls() +116
System.Web.UI.Control.PreRenderRecursiveInternal() +49
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4719
So why does Scott Guthrie gets paging and doesn't it work for me? And what is this reducible node?
Changing the GrdTest_Getdata method to:
public IQueryable<User> GrdTest_GetData(int? maximumRows, int? startRowIndex, out int? totalRowCount, string sortByExpression)
{
var users = container.Users;
totalRowCount = users.Count();
return users;
<pre>
}</pre>
will allow to have sortByExpression is set, but the collection does not get ordered by it.So AllowSorting will not give issues, only AllowPaging does.
In Word you can only store 2 bytes. That is why I use Writer.
modified 3-May-22 11:22am.
|
|
|
|
|
Sorting needs to be applied before the paging.
In your first example, you've returned a page of unsorted records, and are then trying to sort them. That's the most likely cause of the error.
In your second example, you've defined the sorting parameter, so your code is responsible for sorting the data. The model binding won't try to sort the returned data again.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Even if I set in the GridView:
AllowSorting="true" AllowPaging="true" PageSize="10"
Still I have
[ArgumentException: must be reducible node]
System.Linq.Expressions.Expression.VisitChildren(ExpressionVisitor visitor) +97
System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node) +11
System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor) +12
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) +20
System.Web.Util.OrderingMethodFinder.OrderMethodExists(Expression expression) +38
System.Web.UI.WebControls.QueryableHelpers.IsOrderingMethodFound(IQueryable`1 queryable) +16
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +168
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +105
System.Web.UI.WebControls.ModelDataSourceView.ProcessSelectMethodResult(DataSourceSelectArguments arguments, DataSourceSelectResultProcessingOptions selectResultProcessingOptions, ModelDataMethodResult result) +393
System.Web.UI.WebControls.ModelDataSourceView.GetSelectMethodResult(DataSourceSelectArguments arguments) +86
System.Web.UI.WebControls.ModelDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +14
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.ModelDataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +81
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +169
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +71
System.Web.UI.WebControls.GridView.DataBind() +5
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +93
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
System.Web.UI.Control.EnsureChildControls() +116
System.Web.UI.Control.PreRenderRecursiveInternal() +49
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Control.PreRenderRecursiveInternal() +236
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4719
Scott uses EF directly in his examples, I use OData service (which uses EF). Would that make the difference?
Where I work direct DB connections are not allowed. Scott says in his document that paging is done automatically and that EF is doing the right steps for you.
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
It's almost certainly the OData service which is causing the problem.
Try turning off sorting and adding .OrderBy(x => x.Name) to see if you get the same error.
public IQueryable<User> GrdTest_GetData(int? maximumRows, int? startRowIndex)
{
IQueryable<User> result = container.Users.OrderBy(x => x.Name);
if (maximumRows != null && startRowIndex != null)
{
result = result.Skip(startRowIndex ?? 0).Take(maximumRows ?? 0);
}
return result;
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
That just confirms that your OData service client doesn't support LINQ ordering.
I'm not familiar with the library you're using. Does it provide some other method to sort the results?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
The Unchase OData client (UOC) creates the connection between the client (WebForm) and the OData service (over IIS). In IIS OData controllers send the data over the HTTPGet when requested. The UOC uses linq to translate the linq query to OData HTTP request.
The Reference.cs created by the UOC shows even the connections between tables in the DB over ODATA. What I now suspect is that the controller in the IIS does:
return db.Users.Where(x => !x.IsDeleted)
It is not using Include("UserGroups") to fill these nodes with data.
The linq query translates to requerst Uri:
http://localhost/api/global/Users?$orderby=Id
In Word you can only store 2 bytes. That is why I use Writer.
modified 4-May-22 4:23am.
|
|
|
|
|
I am getting the error below
Microsoft VBScript compilation error '800a0408'
Invalid character
/AspClassic/mail.asp, line 24
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = teckchandanirp@gmail.com
my code is as below, what am I doing wrong
<%@LANGUAGE="VBSCRIPT"%>
<%
'Step 1: create an object of CDO.Message
dim objMail
Set objMail = Server.CreateObject("CDO.Message")
'Step 2: set the smtp server, user name and password for authentication
dim smtpServer, yourEmail, yourPassword
smtpServer = "smtp.gmail.com"
yourEmail = "teckchandanirp@gmail.com" 'replace with a valid gmail account
yourPassword = "32233223" 'replace with a valid password for the gmail account set in yourEmail
'Step 4: set the email address to which email will be sent
sendEmailTo = "teckchandanirp@gmail.com"
'Step 5: set the configuration properties of objMail object
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp.gmail.com
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = true
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = teckchandanirp@gmail.com
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = 32233223
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'Step 6: update the configuration after setting all the required items
objMail.Configuration.Fields.Update
'Step 7: prepare your email like set subject, body, from, to etc.
objMail.From = teckchandanirp@gmail.com
objMail.To = teckchandanirp@gmail.com
objMail.Subject="Application Form Registration Details"
objMail.htmlBody = "This is test message"
'Step 8: send the email
objMail.Send
|
|
|
|
|
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = teckchandanirp@gmail.com
You need to put double quote characters around string constants, thus:
objMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "teckchandanirp@gmail.com"
|
|
|
|
|
I am building an app using MVC 5 and I ran into a common issue that has been asked over and over but I can't seem to find the right answer using .NET Identity. Once the user logs in, they should not be able to go back to the login page and the same is true when they log out they should not be able to go back to a restricted page.
The problem that I'm having is that if a user does not enter the correct password and the server catches the error then I successfully login I am able to go back to the login page. If I login on the first try it works like it should. How can I correct this problem?
The other problem that I'm having is when the user logs out, Is there any way without using javascript to not allow the user to access the restricted pages by using the back button?
I have included
filters.Add(new System.Web.Mvc.AuthorizeAttribute());
My controller looks like this:
[AllowAnonymous]
[OutputCache(NoStore = true, Location = OutputCacheLocation.None)]
public ActionResult Login(string returnUrl)
{
if (User.Identity.IsAuthenticated)
{
return RedirectToAction("Dashboard", "App");
}
ViewBag.ReturnUrl = returnUrl;
return View();
}
[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Login(LoginViewModel model, string returnUrl)
{
if (!ModelState.IsValid)
{
return View(model);
}
var user = await UserManager.FindByNameAsync(model.Email);
if (user != null)
{
if (!await UserManager.IsEmailConfirmedAsync(user.Id))
{
string callbackUrl = await SendEmailConfirmationTokenAsync(user.Id, "Confirm your account-Resend");
ViewBag.errorMessage = "You must have a confirmed email to log on.";
return View("Error");
}
}
var result = await SignInManager.PasswordSignInAsync(model.Email, model.Password, model.RememberMe, shouldLockout: false);
switch (result)
{
case SignInStatus.Success:
return RedirectToAction("Dashboard","App");
case SignInStatus.LockedOut:
return View("Lockout");
case SignInStatus.RequiresVerification:
return RedirectToAction("SendCode", new { ReturnUrl = returnUrl, RememberMe = model.RememberMe });
case SignInStatus.Failure:
default:
ModelState.AddModelError("", "Invalid login attempt.");
return View(model);
}
}
My Logout Controller is:
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult LogOff()
{
AuthenticationManager.SignOut(DefaultAuthenticationTypes.ApplicationCookie);
return RedirectToAction("Index", "Home");
}
I didn't add anything special to my view. How do I prevent the logged in user from going back to the login page which they can do if they have an error. and how can I prevent the user from going back after logout and seeing a restricted page. What I really want to know is if there is any way to do this with Identity?
I know the issue is caching in the browser that allows the user to go back. I've cleared the cache when I logout, I used the
[OutputCache(NoStore = true, Location = OutputCacheLocation.None)]
I do not want to disable cashing for the whole app (performance issues) and I do not want to use javascript (since it can be disabled by the user). Is there any other way to do it?
I know this question has been asked a million times but I haven't really found a definitive answer that works on all browsers.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
