ASP.NET

19-May-22 4:16

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

Ron Nicholson19-May-22 7:21

Ron Nicholson

19-May-22 7:21

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

SQL

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.

Member 1562749519-May-22 12:11

Member 15627495

19-May-22 12:11

Hello Kjell.ek !

as distinct can be apply on one column,

you can use it this way :

SQL

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.

Richard Deeming19-May-22 22:42

19-May-22 22:42

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

kjell.ek20-May-22 3:58

20-May-22 3:58

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.

Alaren23-Jun-22 6:23

Alaren

23-Jun-22 6:23

Can you show what the result set is without the distinct please..

SELECT class, nationality, name_event from statistics_results Where class = 'xx'

Milliseconds from excel

kjell.ek16-May-22 11:10

Re: Milliseconds from excel

16-May-22 11:10

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.

Richard Deeming16-May-22 22:35

Re: Milliseconds from excel

16-May-22 22:35

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

kjell.ek17-May-22 0:15

Re: Milliseconds from excel

17-May-22 0:15

Thanks for the tip

Raj Kumar7918-Oct-22 6:12

Raj Kumar79

18-Oct-22 6:12

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.

ASP.NET Gridview load data

Herman<T>.Instance28-Apr-22 4:16

28-Apr-22 4:16

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.

Richard Deeming28-Apr-22 5:40

28-Apr-22 5:40

Assuming you're using ASP.NET 4.5 or later, don't use any of the above. Instead, set the SelectMethod on the control to the name of a method in your code-behind which returns the data. The control will then take care of calling the method whenever necessary.

ScottGu's Blog - Web Forms Model Binding Part 1: Selecting Data (ASP.NET vNext Series)[^]
ScottGu's Blog - Web Forms Model Binding Part 2: Filtering Data (ASP.NET vNext Series)[^]

You can also use InsertMethod, UpdateMethod, and DeleteMethod to perform basic CRUD operations on the data.

ScottGu's Blog - Web Forms Model Binding Part 3: Updating and Validation (ASP.NET 4.5 Series)[^]

And from ASP.NET 4.6 onwards, you can make the SelectMethod a Task-returning async method as well.

Cool ASP.NET Web Forms Features in 2015 – Async Model Binding - .NET Blog[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Herman<T>.Instance2-May-22 1:47

2-May-22 1:47

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.

Dave Kreskowiak2-May-22 3:12

Dave Kreskowiak

2-May-22 3:12

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.

Asking questions is a skill
CodeProject Forum Guidelines
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

Herman<T>.Instance2-May-22 4:23

2-May-22 4:23

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.

Herman<T>.Instance3-May-22 5:46

3-May-22 5:46

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.NET

<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; // is for working with the OData Service

        protected void Page_Init(object sender, EventArgs e)
        {
            Uri uri = new Uri("<a href="http://localhost/api/global">http://localhost/api/global</a>");
            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;
        }

        // The return type can be changed to IEnumerable, however to support
        // paging and sorting, the following parameters must be added:
        //     int maximumRows
        //     int startRowIndex
        //     out int totalRowCount
        //     string sortByExpression
        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.NET

<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)
        {
            // if (maximumRows == null || startRowIndex == null)
            var users = container.Users;
            totalRowCount = users.Count();
            return users;

<pre>
        //return container.Users.Skip(startRowIndex ?? 0).Take(maximumRows ?? 0).AsQueryable();
    }</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.

Richard Deeming3-May-22 6:31

3-May-22 6:31

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

Herman<T>.Instance3-May-22 6:38

3-May-22 6:38

Even if I set in the GridView:

ASP.NET

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.

Richard Deeming3-May-22 6:48

3-May-22 6:48

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

Herman<T>.Instance3-May-22 8:12

3-May-22 8:12

Unfortunately the error stays....

Maybe some help: Expression.VisitChildren(ExpressionVisitor) Method (System.Linq.Expressions) | Microsoft Docs

In Word you can only store 2 bytes. That is why I use Writer.

modified 3-May-22 13:50pm.

Richard Deeming3-May-22 22:38

3-May-22 22:38

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

Herman<T>.Instance3-May-22 23:03