locked
How do I update an ID number rather than the value it represents in a drop down menu? RRS feed

  • Question

  • User743508062 posted

    Hi, I have a drop down menu which displays a list of styles to the user:

    Single Vision
    Bifocal
    Trifocal
    progressives

    These styles have an ID value, ie 1=Single Vision, 2=Bifocal, 3=Trifcol, 4=Progressives.

    The user selects the readable style and and updates the relevant table (tblLens) however I want to update the ID int value in column called styleid ie if the user selects Bifocal, the table column is updated by changing the ID value in this case 2.

    My code is:

    <div class="row">
                    <span class="label"><label for="styleID">Style ID:</label></span>
                    <select name="styleID" id="styleID">
                   <option value="@edstyleID"></option>
    
                        @{
                            foreach (var styleid in listSty){
                                if(styleid.Style == Request["styleid"]){
                                <option value="@edstyleID" selected ="selected">@edstyleID</option>
                            }
                            else{<option value="@styleid.Style">@styleid.Style</option>}
                        }
                            }
                        </select>
    
                </div>

    The update portion is:

    var q1 = "Select * FROM qryLens WHERE lenscode=@0";
        var d1 = db.QuerySingle(q1, lenscode);
    
        var edstyleID = d1.styleID;
        var eddesignID = d1.designID;
        var eddesc = d1.lensdescrip;
        var edfinish = d1.lensfinishing;
        var edcat = d1.lenscattype;
        var lid = d1.lenscode;
    
        if(IsPost){   
    
            edstyleID = Request["styleID"];
            eddesignID = Request["designID"];
            eddesc = Request["lensdescrip"];
            edfinish = Request["lensfinishing"];
            edcat = Request["lenscattype"];
            
            var esql = "UPDATE tblLens SET styleID=@0, designID=@1, lensdescrip=@2, lensfinishing=@3 , lenscattype=@4 WHERE lenscode=@5";      
         
            db.Execute(esql, edstyleID, eddesignID, eddesc, edfinish, edcat, lenscode);
    
            Response.Redirect("~/srchProduct/");
       
       }

    I would be grateful for your assistance. Thank you.

    Tuesday, July 23, 2013 5:00 AM

Answers

  • User379720387 posted

    Here is an example what I do.  My table Type has two columns: TypeID and Type

    1     One-way

    2     Round trip

    The dropdown below displays select, one-way, roundtrip.

    After the selection is made TypeID has the value, not the text.

    <tr><td>Type: (one way or round trip) @if (!ModelState.IsValidField("TypeID")) {<text>class="error-label"</text>}</td><td>
                <select data-val="true" data-val-required="Trip type is required." name="TypeID" id="TypeID">
                <option value="">Select</option>
                    @foreach (var T in Type)
                    { 
                    <option value="@T.TypeID">@T.Type</option>
                    }   
                </select><span data-valmsg-for="TypeID" data-valmsg-replace="true"></span></td></tr> 



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2013 4:53 AM

All replies

  • User743508062 posted

    Since my post above, I noticed that my code was a little... err, all over the place, I have since tidied it up, but still no joy, here is the 'tidy' code:

    @{
        Layout = "~/_SiteLayout.cshtml"; 
        Page.Title = "Edit: Product"; 
    
        var db = Database.Open("WholesaleSystemSQL");
    
        var lenscode = UrlData[0];
    
        var listSty =db.Query("Select * FROM tblLAStyle");
        var listDes =db.Query("Select * FROM tblLADesign");
    
        var q1 = "Select * FROM qryLens WHERE lenscode=@0";
        var d1 = db.QuerySingle(q1, lenscode);
    
        var edstyleid = d1.styleid;
        var eddesignid = d1.designid;
        var eddesc = d1.lensdescrip;
        var edfinish = d1.lensfinishing;
        var edcat = d1.lenscattype;
        var lid = d1.lenscode;
    
        if(IsPost){   
    
            edstyleid = Request["styleid"];
            eddesignid = Request["designid"];
            eddesc = Request["lensdescrip"];
            edfinish = Request["lensfinishing"];
            edcat = Request["lenscattype"];
            
            var esql = "UPDATE tblLens SET styleid=@0, designid=@1, lensdescrip=@2, lensfinishing=@3 , lenscattype=@4 WHERE lenscode=@5";      
         
            db.Execute(esql, edstyleid, eddesignid, eddesc, edfinish, edcat, lenscode);
    
            Response.Redirect("~/srchProduct/");
       
       }
    
          
    }
    
    @section featured {
    <section class="featured">
        <div class="content-wrapper">
            <hgroup class="title">
                <h2>Edit Product: Lens.</h2>
            </hgroup>
            <p>
               You can make changes to the description details for the wholesale product.
            </p>
        </div>
    </section>
    }
    
    <form action="" method="post">
                
               <div class="row">
                    <span class="label"><label for="styleid">Style ID:</label></span>
                    <select name="styleid" id="styleid">
                   <option value="@edstyleid"></option>
    
                        @{
                            foreach (var styleid in listSty){
                                if(styleid.style == Request["styleid"]){
                                <option value="@styleid" selected ="selected">@styleid</option>
                            } 
                            else{<option value="@styleid.style">@styleid.style</option>}
                        }
                            }
                        </select>
    
                </div>
    
               <div class="row">
                    <span class="label"><label for="designid">Design ID:</label></span>
                    <select name="designid" id="designid">
                        <option value="@eddesignid"></option>
                         @{
                            foreach (var designid in listDes){
                                if(designid.design == Request["designid"]){
                                <option value="@designid.design" selected ="selected">@designid.design</option>
                            } 
                            else{<option value="@designid.design">@designid.design</option>}
                        }
                            }
                        </select>
                </div>
    
               <div class="row">
                    <span class="label"><label for="description">Description:</label></span>
                    <input type="text" name="description" id="description" value="@eddesc"size="10" />
                </div>
    
               <div class="row">
                    <span class="label"><label for="finishing">Finishing:</label></span>
                    <input type="text" name="finishing" id="finishing" value="@edfinish"size="10" />
                </div>
    
               <div class="row">
                    <span class="label"><label for="catalogue">Catalogue:</label></span>
                    <input type="text" name="catalogue" id="catalogue" value="@edcat"size="10" />
                </div>
                            
        <input type="submit" value="Edit Product: Lens" /> 
    
          </form>

    So how can do this?

    The error I get is:

    Server Error in '/' Application.
    Conversion failed when converting the nvarchar value 'Easy' to data type int.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    
    Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'Easy' to data type int.
    
    Source Error:
    
    
    Line 30:         var esql = "UPDATE tblLens SET styleID=@0, designID=@1, lensdescrip=@2, lensfinishing=@3 , lenscattype=@4 WHERE lenscode=@5";      
    Line 31:      
    Line 32:         db.Execute(esql, edstyleID, eddesignID, eddesc, edfinish, edcat, lenscode);
    Line 33: 
    Line 34:         Response.Redirect("~/srchProduct/");

    Help. Thanks.

    Tuesday, July 23, 2013 5:55 AM
  • User743508062 posted

    anyone? Any hints, links, ideas?

    Tuesday, July 23, 2013 10:41 AM
  • User895691971 posted

    If its Primary it can't be changed! However, why do you wanna change that? Any idea please.

    Tuesday, July 23, 2013 10:59 AM
  • User743508062 posted

    Thanks for the reply.

    I want to update a table (tblLens) - the columns in that table are styleid etc.

    The column styleid is a FK to a lookup table from where we get the style linked by the styleid.

    So if we want to change the value of styleid it has to be a number or int.

    However, users do not remember the various styleid numbers and how they correspond with the various styles such as single vision, bifocal etc, so I have populated the drop down with a list that the user can understand and select. However back in my table I want the corresponding int to go into the styleid column.

    I hope that is clear (clear as mud maybe) and hence look forward to receiving further coding wisdom! Thanks.

    Tuesday, July 23, 2013 11:50 AM
  • User753101303 posted

    Hi,

    So it seems  you still try to assign some text to an int column. What if using "view source" in your browser ? In my opinion the underlying values for the dropdraw are strings rather than integers (as you are using the same value for both the option shown in the drop down and its underlying value). You likely have to somewhat fix the drop down (if I understand what you are trying to do).

    Else just examine the parameters when it happens and you'll likely find the incorrect value...

    Tuesday, July 23, 2013 12:10 PM
  • User895691971 posted

    Ummm clear a little bit!

    If you want to update the int value. Make sure its not primary. You can't change that! 

    However you can update the data in a table via SQL UPDATE clause. Here: 

    UPDATE table_name
    SET column1=value1,column2=value2,...
    WHERE some_column=some_value;

    Link I used: http://www.w3schools.com/sql/sql_update.asp

    Now, as per your work. You can update the styleid for that table! And you will get the result too. But you own the value for that update. As you have a dropdown. User cannot add any string value!

    Tuesday, July 23, 2013 12:12 PM
  • User743508062 posted

    Hi thanks for the help, I think I am not explaining this issue very well.

    Let me put it this way:

    I come from an Access background and in Access there is facility to consign corresponding values to IDs which are normally autonumbers PKs.

    so in my table called tblStyle I have:

    StyleID (PK) Style

    1          Single Vision
    2          Bifocal
    3          Trifocal
    4          Progressives

    This table is linked via StyleID (FK) to tblLens: 1 to many relationships

    Lenscode     StyleID(FK)      Description

    A1                1                     Single Vision Plastic Puck.

    etc...

    So I want to save StyleID value as an int, but I am displaying to the user the actual names of the styles. When the user selects say Trifocal I want 3 to be saved in StyleID in tblLens.

    However currently my code does not do that, it is trying to save Trifocal text into StyleID (int) and cannot as Trifocal is nvarchar.

    Server Error in '/' Application.
    Conversion failed when converting the nvarchar value 'Trifocal' to data type int.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
    
    Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value 'Trifocal' to data type int.
    
    Source Error:
    
    
    Line 30:         var esql = "UPDATE tblLens SET styleid=@0, designid=@1, lensdescrip=@2, lensfinishing=@3 , lenscattype=@4 WHERE lenscode=@5";      
    Line 31:      
    Line 32:         db.Execute(esql, edstyleid, eddesignid, eddesc, edfinish, edcat, lenscode);
    Line 33: 
    Line 34:         Response.Redirect("~/srchProduct/");

    What I want is for the drop down front end to show the text and in the background at SQL level to save the selection as the corresponding int value into StyleID.

    I hope that explains it better and hence someone can point me in the right direction.

    Thanks.

     

    Wednesday, July 24, 2013 4:40 AM
  • User379720387 posted

    Here is an example what I do.  My table Type has two columns: TypeID and Type

    1     One-way

    2     Round trip

    The dropdown below displays select, one-way, roundtrip.

    After the selection is made TypeID has the value, not the text.

    <tr><td>Type: (one way or round trip) @if (!ModelState.IsValidField("TypeID")) {<text>class="error-label"</text>}</td><td>
                <select data-val="true" data-val-required="Trip type is required." name="TypeID" id="TypeID">
                <option value="">Select</option>
                    @foreach (var T in Type)
                    { 
                    <option value="@T.TypeID">@T.Type</option>
                    }   
                </select><span data-valmsg-for="TypeID" data-valmsg-replace="true"></span></td></tr> 



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2013 4:53 AM
  • User895691971 posted

    The error you are providing, "Can't convert nvarchar to int" is because the value is in String! You need to use this code 

    var This = valueInString;
    var intValue = This.AsInt();

    This will convert the string to int. 

    However I am not sure whether you have a valid int (1, 2, 3..) or not! You should make sure, you have a valid int in the database col values. So that when it is converted to INT. It remains INT!

    Wednesday, July 24, 2013 5:11 AM
  • User743508062 posted

    Thanks buddy, that helps.

    here is my revised code:

    <div class="row">
                    <span class="label"><label for="styleid">Style:</label></span>
                    <select name="styleid" id="styleid">
                   <option value="">@edstyle</option>   
                                          
                            @{
                                    foreach (var styleid in listSty)
                                {
                                        if(styleid.style == Request["styleid"])
                                    {
                                        <option value="@styleid.styleid">@styleid.style</option>
                                    } 
                                      else 
                                    {
                                        <option value="@styleid.styleid">@styleid.style</option>
                                    }
                                }
                            }
                            
                        </select>
    
                </div>
    Wednesday, July 24, 2013 8:31 AM