Web Matrix with multi checkedbox insert into Database.

Question

User-2014067552 posted

This is my code work and can be insert into database but i would like to change Categories to be Multi CheckedBox. Please help. Thank you very much.

@{
   var db = Database.Open("Books");
   var commandText = string.Empty;
   var categories = db.Query("Select CategoryId, Category FROM Categories");
   var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");
 
   if(IsPost){
       commandText = @"INSERT INTO Books (Title, ISBN, AuthorId, CategoryId) VALUES (@0, @1, @2, @3)";
       var title = Request["title"];
       var isbn = Request["isbn"];
       var authorId = Request["authorId"];
       var categoryId = Request["categoryId"];
       db.Execute(commandText, title, isbn, authorId, categoryId);
   }
   commandText = @"SELECT Title, ISBN, FirstName + ' ' + LastName As Author, Category
                 FROM Books
                 INNER JOIN Authors ON Books.AuthorId = Authors.AuthorId
                 INNER JOIN Categories ON Books.CategoryId = Categories.CategoryId";
   var books = db.Query(commandText);
   var grid = new WebGrid(books, canPage: false, canSort:false);
 
}
    <form method="post" action="">
        <fieldset>
            <legend>ADD WORK</legend>
         <div class="row">
                <span class="label"><label for="title">Title:</label></span>
                <input type="text" name="title" id="title" size="50" value="@Request.Form["title"]" /> 
            </div>
            <div class="row">
                <span class="label"><label for="isbn">ISBN:</label></span>
                <input type="text" name="isbn" id="isbn" size="20" value="@Request.Form["isbn"]" /> 
             <div class="row">
                <span class="label"><label for="authorId">Author:</label></span>
                <select name="authorId" id="authorId">
                    <option value="">-- Select Author --</option>
                @{
                    foreach(var author in authors){
                        if(author.AuthorId == Request["authorId"].AsInt()){
                            <option value="@author.AuthorId" selected="selected">@author.AuthorName</option>
                        } else {
                            <option value="@author.AuthorId">@author.AuthorName</option>
                        }
                    }
                }
                </select>
            </div>
            <div class="row">
                <span class="label"><label for="categoryId">Category:</label></span>
                @foreach(var item in categories){
            <input type="checkbox" name="categoryid" value="@item.CategoryId" /> @item.Category
        }
                </select>
            </div>
           
            <div class="row">
                <span class="label">&nbsp;</span>
                <input type="submit" id="submit" name="submit" value="Submit" />
            </div> 
            </fieldset>
        </form>

Answer 1

User-2014067552 posted

I also try this..... but still does not work. Please help.

@{
   var db = Database.Open("Books");
   var commandText = string.Empty;
   var categories = db.Query("Select CategoryId, Category FROM Categories");
   var authors = db.Query("SELECT AuthorId, FirstName + ' ' + LastName AS AuthorName FROM Authors");
 
   if(IsPost){
       commandText = @"INSERT INTO Books (Title, ISBN, AuthorId, CategoryId) VALUES (@0, @1, @2, @3)";
       var title = Request["title"];
       var isbn = Request["isbn"];
       var authorId = Request["authorId"];
       var categoryId = Request["categoryId"];
 
        var temp = Request["categoryId"].Split(new char[]{','}, StringSplitOptions.RemoveEmptyEntries);
        var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
        var inclause = string.Join(",", parms);
        var sql = "SELECT Category FROM Categories WHERE CategoryId IN ({0})";
        categories = db.Query(String.Format(sql, inclause), temp);
 
       db.Execute(commandText, title, isbn, authorId, categoryId);
   }
   commandText = @"SELECT Title, ISBN, FirstName + ' ' + LastName As Author, Category
                 FROM Books
                 INNER JOIN Authors ON Books.AuthorId = Authors.AuthorId
                 INNER JOIN Categories ON Books.CategoryId = Categories.CategoryId";
   var books = db.Query(commandText);
   var grid = new WebGrid(books, canPage: false, canSort:false);
 
}
    <form method="post" action="">
        <fieldset>
            <legend>add</legend>
         <div class="row">
                <span class="label"><label for="title">Title:</label></span>
                <input type="text" name="title" id="title" size="50" value="@Request.Form["title"]" /> 
            </div>
            <div class="row">
                <span class="label"><label for="isbn">ISBN:</label></span>
                <input type="text" name="isbn" id="isbn" size="20" value="@Request.Form["isbn"]" /> 
             <div class="row">
                <span class="label"><label for="authorId">Author:</label></span>
                <select name="authorId" id="authorId">
                    <option value="">-- Select Author --</option>
                @{
                    foreach(var author in authors){
                        if(author.AuthorId == Request["authorId"].AsInt()){
                            <option value="@author.AuthorId" selected="selected">@author.AuthorName</option>
                        } else {
                            <option value="@author.AuthorId">@author.AuthorName</option>
                        }
                    }
                }
                </select>
            </div>
            <div class="row">
                <span class="label"><label for="categoryId">Category:</label></span>
                @foreach(var item in categories){
            <input type="checkbox" name="categoryid" value="@item.CategoryId" /> @item.Category
        }
                </select>
            </div>
           
            <div class="row">
                <span class="label">&nbsp;</span>
                <input type="submit" id="submit" name="submit" value="Submit" />
            </div> 
            </fieldset>
        </form>

Answer 2

User-166373564 posted

Hi iamnung ,

One solution would be saving the multi checked values into database with comma delimited string . For example :

        string strCheckValue = "";
        if (chk1.Checked)
        {
          strCheckValue = strCheckValue + "," + chk1.Text;
        }
        if (chk2.Checked)
        {
          strCheckValue = strCheckValue + "," + chk2.Text;
        }

When you want to show the Categories information , you could split your long string like :

string s = [yourlongstring];
string[] values = s.Split(',');

Then loop the values and create the checkbox type controls .

Best Regards,

Angie

Answer 3

User-2014067552 posted

My colleague help me on this. Its work but not perfect what i need.

   if (IsPost)  {
        if (Validation.IsValid()) {       
        var temp = Request["categoryId"].Split(new char[] { ',', '\"' }, StringSplitOptions.RemoveEmptyEntries);
        var parms = temp.Select((s, i) => "@" + i.ToString()).ToArray();
        var inclause = string.Join(", ", parms);
        var sql = "SELECT CategoryM FROM Categories WHERE CategoryId IN ({0}) ORDER BY CategoryId";
        categories = db.Query(String.Format(sql, inclause, parms), temp);
        var EqItem = new System.Text.StringBuilder(); //////My colleague help me