locked
SQL and ASP.Net RRS feed

  • Question

  • User-1891900014 posted

    I have been task with to create a from .

    it has 2 text box 

    the first box is Code eg.120/10

    Second is the display a code when the first code is selected.

    eg. Code is 120/10

    secondbox is to create out 120/10/1

    if there is 120/10/1

    it will create 120/10/2

     how do i do it ?

    both are in a different

    different table.

    Friday, April 27, 2018 9:30 AM

Answers

  • User-369506445 posted

    I create a simple sample for fetch from database and bind to drop-down list

    please follow it step by step

    first create a database called FirstDB and run below script in SQL server 

    USE [FirstDB]
    GO
    /****** Object:  Table [dbo].[myTable]    Script Date: 5/1/2018 10:24:37 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[myTable](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [nchar](10) NULL,
     CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    insert into  [dbo].[myTable] values('120/10');
    insert into  [dbo].[myTable] values('120/10/1');

    and create a new WebForm  and put below code :

    In Html 

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <script src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
    <script>
        $(function () {
            $("#ddl").change(function () {
                var result = "";
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2)
                    result = firstTest.trim() + "/1";
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    result = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                }
    
                $("#txt").val(result);
            });
        });
    </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:DropDownList runat="server" ID="ddl" />
                <asp:TextBox runat="server" ID="txt"></asp:TextBox>
            </div>
        </form>
    </body>
    </html>

    in code behind

    protected void Page_Load(object sender, EventArgs e)
            {
                //fill drop dwon list
                string constr = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=FirstDB";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT Id,Name FROM myTable"))
                    {
                        cmd.Connection = con;
                        con.Open();
                        ddl.DataSource = cmd.ExecuteReader();
                        ddl.DataTextField = "Name";
                        ddl.DataValueField = "Id";
                        ddl.DataBind();
                        con.Close();
                    }
                }
                 
            }

    now when you run it , and you can see fill you dropdown and if change it , the text-box value be change ,

    now for bind the textbox to sql you have many way for do it,

    for example you can put a button and in Click event write below code :

    protected void btn_OnClick(object sender, EventArgs e)
            {
                // Insert into data base
                string constr = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=FirstDB";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable(Name) VALUES(@Name)"))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Name", txt.Text);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 1, 2018 6:17 AM
  • User-369506445 posted

    you can check it client side too.

    please replace below script to last script

    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#txt").val("");
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
                    
                    if (parameters.trim() == allitems[i].trim()) {
                        var arr = finaltext.split('/');
                        var num = parseInt(arr[2], 10);
                        num++;
                        finaltext = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>

    now when you change the drop down, first check for duplicate item and if was exists then add a number to it and if was not exists show the same value

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 8:03 AM
  • User-369506445 posted

    yes it's true,because your data format isn't same

    for example :120/01/01 is different with 120/01/1

    i fixed it and please try below code :

    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#txt").val(result);
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
                    var splitDDL = allitems[i].trim().split("/");
                    var splitParam = finaltext.split("/");
                    if (parseInt(splitDDL[0], 10) == parseInt(splitParam[0], 10) &&
                        parseInt(splitDDL[1], 10) == parseInt(splitParam[1], 10) &&
                        parseInt(splitDDL[2], 10) == parseInt(splitParam[2], 10)) {
                         
                        var num = parseInt(splitParam[2], 10);
                        num++;
                        finaltext = splitParam[0].trim() + "/" + splitParam[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 8:24 AM
  • User-369506445 posted

    also i create a sample with a user control with WebUserControl11 and change the selector of jqury from "ddl" to "#WebUserControl11_ddl" and "txt" to "#WebUserControl11_txt"

    <script src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#WebUserControl11_ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#WebUserControl11_txt").val("");
                var firstTest = $('#WebUserControl11_ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#WebUserControl11_txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
    
                    if (parameters.trim() == allitems[i].trim()) {
                        var arr = finaltext.split('/');
                        var num = parseInt(arr[2], 10);
                        num++;
                        finaltext = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>
     
            <div>
                <asp:DropDownList runat="server" ID="ddl" />
                <asp:TextBox runat="server" ID="txt"></asp:TextBox>
            </div>
     
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 9:52 AM

All replies

  • User-369506445 posted

    hi

    please try below code :

    <script src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
    <script>
        $(function () {
            $("#btn").click(function () {
    
                var result = "";
                var firstTest = $("#first").val();
                var arr = firstTest.split('/');
                if (arr.length == 2)
                    result = firstTest + "/1";
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    result = arr[0] + "/" + arr[1] + "/" + num;
                }
    
                $("#second").val(result);
            });
        });
    </script>
    <input type="text" id="first" />
    <input type="text" id="second" />
    <input type="button" value="Convert" id="btn" />

    now when you click on button, the first textbox value convert into second textbox

    Friday, April 27, 2018 9:57 AM
  • User-1891900014 posted

    Hi thanks for the help , how do i link it to my SQL ? the firstbox should be a dropdownlist.

    The first is the drop down list bind from a sql table ,

    the second is a textbox. when the dropdownlist has been selected,

    The the second box is also bind to a sql table .

    it suppose to auto generate the result when the drop downlist has been selected. 

    thanks alot for the help really, appreciate it . im a total beginner

    Monday, April 30, 2018 12:49 AM
  • User36583972 posted

    Hi JayRayJay,

    JayRayJay

    Hi thanks for the help , how do i link it to my SQL ? the firstbox should be a dropdownlist.

    You can refer the following sample to know how to link dropdownlist to a SQL Server database.

    Save (Insert) dynamic DropDownList Selected Value to database in ASP.Net using C# and VB.Net
    https://www.aspsnippets.com/Articles/Save-Insert-dynamic-DropDownList-Selected-Value-to-database-in-ASPNet-using-C-and-VBNet.aspx

    JayRayJay

    the second is a textbox. when the dropdownlist has been selected,

    The the second box is also bind to a sql table .

    it suppose to auto generate the result when the drop downlist has been selected. 

    You don't need to bind a textbox to a SQL table. When textbox auto generates the result after the drop downlist has been selected, you can save it in a different table.


    Besides, you can consider implementing a Cascading DropDownList.

    Populate Cascading DropDownList from Database in ASP.Net Example
    https://www.aspsnippets.com/Articles/Populate-Cascading-DropDownList-from-Database-in-ASPNet-Example.aspx

    Best Regards,

    Yong Lu

    Tuesday, May 1, 2018 5:21 AM
  • User-369506445 posted

    I create a simple sample for fetch from database and bind to drop-down list

    please follow it step by step

    first create a database called FirstDB and run below script in SQL server 

    USE [FirstDB]
    GO
    /****** Object:  Table [dbo].[myTable]    Script Date: 5/1/2018 10:24:37 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[myTable](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [nchar](10) NULL,
     CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    insert into  [dbo].[myTable] values('120/10');
    insert into  [dbo].[myTable] values('120/10/1');

    and create a new WebForm  and put below code :

    In Html 

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <script src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
    <script>
        $(function () {
            $("#ddl").change(function () {
                var result = "";
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2)
                    result = firstTest.trim() + "/1";
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    result = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                }
    
                $("#txt").val(result);
            });
        });
    </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:DropDownList runat="server" ID="ddl" />
                <asp:TextBox runat="server" ID="txt"></asp:TextBox>
            </div>
        </form>
    </body>
    </html>

    in code behind

    protected void Page_Load(object sender, EventArgs e)
            {
                //fill drop dwon list
                string constr = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=FirstDB";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT Id,Name FROM myTable"))
                    {
                        cmd.Connection = con;
                        con.Open();
                        ddl.DataSource = cmd.ExecuteReader();
                        ddl.DataTextField = "Name";
                        ddl.DataValueField = "Id";
                        ddl.DataBind();
                        con.Close();
                    }
                }
                 
            }

    now when you run it , and you can see fill you dropdown and if change it , the text-box value be change ,

    now for bind the textbox to sql you have many way for do it,

    for example you can put a button and in Click event write below code :

    protected void btn_OnClick(object sender, EventArgs e)
            {
                // Insert into data base
                string constr = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=FirstDB";
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable(Name) VALUES(@Name)"))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Name", txt.Text);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 1, 2018 6:17 AM
  • User-1891900014 posted

    Hi vahid bakkhi really appreciate the help and the code, it works out fine. 

    how do i select a new code if there is a existing one in the column ?

    exp. when i choose 120/10 from ddl.

    120/10/1 is suppose to be generated . if 120/10/1 is already in the column 120/10/2 will be generated.

    tableA is where i keep the data 120/10

    tableB is where i store 120/10/1

    how do i do it?

    Wednesday, May 2, 2018 2:50 AM
  • User36583972 posted

    Hi JayRayJay,

    You can find out if there is the same value in the database table B, then, create 120/10/X and save it to the database table B.

    You can refer the following sample to know how to use WebMethod to find out if there is the same value from the database table B.

    Populate (Bind) ASP.Net DropDownList using jQuery AJAX and JSON in C# and VB.Net:
    https://www.aspsnippets.com/Articles/Populate-Bind-ASPNet-DropDownList-using-jQuery-AJAX-and-JSON-in-C-and-VBNet.aspx


    Best Regards,

    Yong Lu

    Wednesday, May 2, 2018 7:31 AM
  • User-369506445 posted

    you can check it client side too.

    please replace below script to last script

    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#txt").val("");
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
                    
                    if (parameters.trim() == allitems[i].trim()) {
                        var arr = finaltext.split('/');
                        var num = parseInt(arr[2], 10);
                        num++;
                        finaltext = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>

    now when you change the drop down, first check for duplicate item and if was exists then add a number to it and if was not exists show the same value

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 8:03 AM
  • User-1849856862 posted

    Thanks Vahid bakkhi for your help. after i insert a 120/01/01 to the table. its still generates out 120/01/01 after selecting 120/01

    Wednesday, May 2, 2018 8:09 AM
  • User-1891900014 posted

    The codes works out fine. Really thanks for your help.

    Wednesday, May 2, 2018 8:13 AM
  • User-369506445 posted

    yes it's true,because your data format isn't same

    for example :120/01/01 is different with 120/01/1

    i fixed it and please try below code :

    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#txt").val(result);
                var firstTest = $('#ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
                    var splitDDL = allitems[i].trim().split("/");
                    var splitParam = finaltext.split("/");
                    if (parseInt(splitDDL[0], 10) == parseInt(splitParam[0], 10) &&
                        parseInt(splitDDL[1], 10) == parseInt(splitParam[1], 10) &&
                        parseInt(splitDDL[2], 10) == parseInt(splitParam[2], 10)) {
                         
                        var num = parseInt(splitParam[2], 10);
                        num++;
                        finaltext = splitParam[0].trim() + "/" + splitParam[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 8:24 AM
  • User-1891900014 posted

    last one question. how do i put it in .ASCX?

    Wednesday, May 2, 2018 9:17 AM
  • User-369506445 posted

    it's not different as same, you just put below script on your user control.

    but you must be cheerful about your controls id

    for example i create a user control called WebUserControl11 and when i put my code on it , asp.net while is rendering the page the name of user control added to all controls

    for example your drop-down id is "ddl" and when put into user control page be "yourusercontolname_ddl"

    also you can use developer tool for check it on browser

    Wednesday, May 2, 2018 9:50 AM
  • User-369506445 posted

    also i create a sample with a user control with WebUserControl11 and change the selector of jqury from "ddl" to "#WebUserControl11_ddl" and "txt" to "#WebUserControl11_txt"

    <script src="http://code.jquery.com/jquery-1.4.4.min.js"></script>
    <script>
        $(function () {
            var allitems = [];
            var finaltext = "";
            $("#WebUserControl11_ddl").change(function () {
                allitems = [];
                finaltext = "";
                getAllitem();
                var result = "";
                $("#WebUserControl11_txt").val("");
                var firstTest = $('#WebUserControl11_ddl :selected').text();
                var arr = firstTest.split('/');
                if (arr.length == 2) {
                    checkDuplicate(firstTest.trim() + "/1");
                    result = finaltext;
                }
                else if (arr.length == 3) {
                    var num = parseInt(arr[2], 10);
                    num++;
                    checkDuplicate(arr[0].trim() + "/" + arr[1].trim() + "/" + num);
                    result = finaltext;
                }
    
                $("#WebUserControl11_txt").val(result);
            });
    
            function getAllitem() {
                $("#ddl option").each(function () {
                    allitems.push($(this).text());
                });
            }
    
            function checkDuplicate(parameters) {
                finaltext = parameters;
                for (var i = 0; i < allitems.length; i++) {
    
                    if (parameters.trim() == allitems[i].trim()) {
                        var arr = finaltext.split('/');
                        var num = parseInt(arr[2], 10);
                        num++;
                        finaltext = arr[0].trim() + "/" + arr[1].trim() + "/" + num;
                        checkDuplicate(finaltext);
                    }
                }
            }
        });
    </script>
     
            <div>
                <asp:DropDownList runat="server" ID="ddl" />
                <asp:TextBox runat="server" ID="txt"></asp:TextBox>
            </div>
     
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 9:52 AM