Order by Alphanumeric values in SQL query using C# and VB.Net in ASP.Net

lingers
 
on Nov 10, 2021 08:29 PM
Sample_370830.zip
1407 Views

I have this values in my tables. I used this code to display the values in autocomplete textbox but Order by not working

INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (1, 15, NULL, N'72473-1B',  1)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (2, 15, NULL, N'72473-2B',  2)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (3, 15, NULL, N'72473-3B',  3)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (4, 15, NULL, N'72473-4B',  4)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (5, 15, NULL, N'72473-5B',  5)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (6, 15, NULL, N'72473-6B',  6)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (7, 15, NULL, N'72473-7B',  7)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (8, 15, NULL, N'72473-8B',  8)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (9, 15, NULL, N'72473-9B',  9)
INSERT [dbo].[job_cylinder] ([id], [pid], [ppid], [posino], [unit]) VALUES (10, 15, NULL, N'72473-10B', 10)

 

[WebMethod]
public static List<string> GetAutoCompleteData3(string searchTerm, string id)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NERO-SIGBENU\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=arpackaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("select id,pid,posino from job_cylinder where pid=@Id" order by posino, con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", searchTerm);
            cmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["posino"], dr["id"]));
            }
            return result;
        }
    }
}

 

function SearchText3() {
    $(".autosuggest3").autocomplete({
        source: function (request, response) {
            $.ajax({
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: "Order.aspx/GetAutoCompleteData3",
                data: "{ 'searchTerm': '" + request.term + "', id: '" + $('[id*=hfjobid]').val() + "'}",
                dataType: "json",
                success: function (data) {
                    if (data.d.length > 0) {
                        response($.map(data.d, function (item) {
                            return {
                                label: item.split('/')[0],
                                val: item.split('/')[1]
                            }
                        }));
                    }
                    else {
                        response([{ label: 'No Records Found', val: -1}]);
                    }
                },
                error: function (result) {
                    alert(result.responseText);
                }
            });
        },
        select: function (event, ui) {
            if (ui.item.val == -1) {
                return false;
            }
            $('[id*=hfcylinderid]').val(ui.item.val);
            $(event.target).autocomplete("close");
            setTimeout(function () {
                $(event.target).blur();
            });
        },
        minLength: 0
    }).focus(function () {
        $(this).autocomplete("search");
    });
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Dec 01, 2021 03:24 AM

Hi Lingers, 

 Please refer below sample.

HTML

<table>
    <tr>
        <th>Name:</th>
        <td><asp:TextBox runat="server" ID="txtName" CssClass="autosuggest3" /></td>
        <td><asp:Button Text="Search" runat="server" ID="btnSearch" /></td>
    </tr>
</table>
<asp:HiddenField ID="hfjobid" runat="server" Value="15" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<script>
    $(function () {
        SearchText3();
    });
    function SearchText3() {
        $(".autosuggest3").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Default.aspx/GetAutoCompleteData3",
                    data: "{ 'searchTerm': '" + request.term + "', id: '" + $('[id*=hfjobid]').val() + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1 }]);
                        }
                    },
                    error: function (result) {
                        alert(result.responseText);
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
                $('[id*=hfcylinderid]').val(ui.item.val);
                $(event.target).autocomplete("close");
                setTimeout(function () {
                    $(event.target).blur();
                });
            },
            minLength: 0
        }).focus(function () {
            $(this).autocomplete("search");
        });
    }
</script>

Namespaces

C#

using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<string> GetAutoCompleteData3(string searchTerm, string id)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=192.168.0.100\\SQL2019;UID=sa;PWD=pass@123;Initial Catalog=test;"))        
    {
        using (SqlCommand cmd = new SqlCommand("SELECT id, pid, posino FROM job_cylinder where posino LIKE @SearchText+'%' AND pid=@Id ORDER BY CAST(LEFT(posino, CHARINDEX('-', posino)-1)  AS INT), CAST(LEFT(RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)), PATINDEX('%[^0-9]%', RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))+'.') - 1)  AS INT), RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", searchTerm);
            cmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["posino"], dr["id"]));
            }
            return result;
        }
    }
}

VB.Net

<WebMethod>
Public Shared Function GetAutoCompleteData3(ByVal searchTerm As String, ByVal id As String) As List(Of String)
    Dim result As List(Of String) = New List(Of String)()
    Using con As SqlConnection = New SqlConnection("Data Source=192.168.0.100\SQL2019;UID=sa;PWD=pass@123;Initial Catalog=test;")
        Using cmd As SqlCommand = New SqlCommand("SELECT id, pid, posino FROM job_cylinder where posino LIKE @SearchText+'%' AND pid=@Id ORDER BY CAST(LEFT(posino, CHARINDEX('-', posino)-1)  AS INT), CAST(LEFT(RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)), PATINDEX('%[^0-9]%', RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)) + '.') - 1)  AS INT), RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))", con)
            con.Open()
            cmd.Parameters.AddWithValue("@SearchText", searchTerm)
            cmd.Parameters.AddWithValue("@Id", id)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            While dr.Read()
                result.Add(String.Format("{0}/{1}", dr("posino"), dr("id")))
            End While
            Return result
        End Using
    End Using
End Function

Screenshot