How it works in multi checkedbox IN CLAUSE and insert into Database (Category)

Question

User-2014067552 posted

Here is my code.... It works with insert into Database. I've followed this tutorial by Mike. Please help.

https://www.mikesdotnetting.com/article/155/webmatrix-and-jquery-forms

Mine is multi checkedbox

@{
    var db = Database.Open("Test");
        // Coumputer name
    String UsernameLog = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
    String UserNamee = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Substring(9);
    var dt = DateTime.Now;
    String ThisMachine;
    ThisMachine = Server.MachineName;
    var dateinsert = DateTime.Now;
    var Userlogon = UserNamee;
    var StaffEmail = Userlogon+"@test.com";
 
    var categories = db.Query("Select CategoryId, CategoryM FROM Categories");  
    var authors = db.Query("SELECT AuthorId, LastName + ', ' + FirstName AS AuthorName FROM Authors");
    // query categories table

    //var message="";
    //Validation.RequireField("OnBehalf", "On behalf is required.");
    //Validation.RequireField("DateReq", "Request Date is required.");
    //Validation.RequireField("DateReturn", "Expect Return Date is required.");
                   
    //if(IsPost){
    //if (IsPost && Validation.IsValid()){
        
    if (IsPost)  {
        // Before processing anything, make sure that all user input is valid.
        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);
 
    //
      foreach(var item in categories){
            String UserName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Substring(9);
            var db1 = Database.Open("TSD");
            var Status= "Pending";
             var commandText = @"INSERT INTO ReqMobile (UserName, OnBehalf, DateReq, DateReturn, CategoryId, Status_m, noted, StaffEmail, dateinsert) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8)";
             db1.Execute(commandText, UserName, @Request.Form["OnBehalf"], @Request.Form["DateReq"], @Request.Form["DateReturn"], @item.CategoryM, Status, @Request.Form["noted"],@StaffEmail, @dateinsert);
             
             
             commandText = @"SELECT UserName, OnBehalf, AuthorId, LastName + ', ' + FirstName As Author, CategoryM,
                 FROM ReqMobile
                 INNER JOIN Authors ON ReqMobile.AuthorId = Authors.AuthorId
                 INNER JOIN Categories ON ReqMobile.CategoryId = Categories.CategoryId";
 
              }
      try {
                WebMail.SmtpServer = "10.139";
                WebMail.SmtpPort = 25;             
                WebMail.UserName = "[e-mail]";
                WebMail.From = "[e-mail]";
                WebMail.Password = "1234";
 
                // Send email
                WebMail.Send(to: "[e-mail]",
                subject: "Request Equipment from: " + @UsernameLog ,
                
              //body: "<p>Dear TSD Team,</p><p>I, (Mr./Mrs./Miss) "+ @UsernameLog+" would like to borrow from the Office:<p><b>Equipment"+ @CategoryM + "</b></p><p> I have received the aforementioned items in good working condition and will return them in their original intact state.</p><p> Should the items become damaged or lost, I am responsible for the cash value of such items<p>at the book value as at the time of the loss or damage, or per the Office’s policy as deemed appropriate by Technology System Department.</p><br><p>Best Regards,</p><p>"+ @UsernameLog+"</p><br><p>Computer name:"+ @ThisMachine+"</p><br>", 
              body: "<p>Dear TSD Team,</p><p>I, (Mr./Mrs./Miss) "+ @UsernameLog+" would like to borrow from the Office:<p><b>Equipment "+Request.Form["CatogoryId"].AsInt()+"</b>and expect to return date on "+Request.Form["DateReturn"]+"</p><p> I have received the aforementioned items in good working condition and will return them in their original intact state.</p><p> Should the items become damaged or lost, I am responsible for the cash value of such items<p>at the book value as at the time of the loss or damage, or per the Office’s policy as deemed appropriate by Technology System Department.</p><br><p>Best Regards,<br>"+ @UsernameLog+"</p><br><p>Computer name:"+ @ThisMachine+"<br>Date Request Equipment: "+Request.Form["DateReq"]+"<br>", 
                isBodyHtml: true
                 );
    }
            catch (Exception ex ) {
 
                  Response.Redirect ("error.cshtml");
            }     
         Response.Redirect ("RequestMobile.cshtml");
     }
}
 
    }

Answer 1

User-166373564 posted

Hi iamnung,

How it works in multi checkedbox IN CLAUSE and insert into Database (Category)

Could you please explain more details about your question, from your description, I'm not quite understanding your problem? 

Here is my code.... It works with insert into Database. I've followed this tutorial by Mike. Please help.

    if (IsPost)  {
        // Before processing anything, make sure that all user input is valid.
        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);
      foreach(var item in categories){
            String UserName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Substring(9);
            var db1 = Database.Open("TSD");
            var Status= "Pending";
             var commandText = @"INSERT INTO ReqMobile (UserName, OnBehalf, DateReq, DateReturn, CategoryId, Status_m, noted, StaffEmail, dateinsert) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8)";
             db1.Execute(commandText, UserName, @Request.Form["OnBehalf"], @Request.Form["DateReq"], @Request.Form["DateReturn"], @item.CategoryM, Status, @Request.Form["noted"],@StaffEmail, @dateinsert);
             
             
             commandText = @"SELECT UserName, OnBehalf, AuthorId, LastName + ', ' + FirstName As Author, CategoryM,
                 FROM ReqMobile
                 INNER JOIN Authors ON ReqMobile.AuthorId = Authors.AuthorId
                 INNER JOIN Categories ON ReqMobile.CategoryId = Categories.CategoryId";

According to your code, it seems that after post the data, you will select the related categories based on the selected CategoryId(through request["categoryId"]), then you will loop through the selected categories, and insert data into database. So, I'm not quite understanding what you want?

As for this multiple checkbox, I know you want to select multiple categoryid, as far as i know, if you selected multiple item, it will transfer them as an array, after you get the selected value, when save them into database, you could insert them one by one, the result like this:

productID	productname	category
1001	AAA	C1
1001	AAA	C2
1001	AAA	C3

Also, you could using some delimiter, like this:

productID	productname	category
1001	AAA	C1,C2,C3

Best regards,
Angie

Answer 2

User-2014067552 posted

Dear Angie,

I've 3 Tables 1. Authors 2. Categories 3. ReqMobile

My problem coding is when checkedbox and submitted the from, data insert into database on CategoryId as String and AuthorId not insert into database. Please help

Best regards,

Iamnung

Answer 3

User-2014067552 posted

Dear Angie,

I changed the coding as below. Its work for insert into database on AuthorId and CategoryId but It can be checked only one checkedbox. If i selected more one got the error " Conversion failed when converting the nvarchar value '1,2' to data type int." Please help. Thank you very much.

@{
    var db = Database.Open("TSD");
    var commandText = string.Empty;
        // Coumputer name
    String UsernameLog = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();
    String UserNamee = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Substring(9);
    var dt = DateTime.Now;
    String ThisMachine;
    ThisMachine = Server.MachineName;
    var dateinsert = DateTime.Now;
    var Userlogon = UserNamee;
    var Umail = Userlogon+"@test.com";
 
    var categories = db.Query("Select CategoryId, CategoryM FROM Categories");  
    var authors = db.Query("SELECT AuthorId, LastName + ', ' + FirstName AS AuthorName FROM Authors");
    // query categories table
         
    //if(IsPost){ 
        if (IsPost && 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);
 
    //
      foreach(var item in categories){
            String UserName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.Substring(9);
 
            var db1 = Database.Open("test");
            var Status = "Pending";
            commandText = @"INSERT INTO ReqEq (UserName, OnBehalf, DateReq, DateReturn, AuthorId, CategoryId, Status_m, noted, Umail, dateinsert) VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9)";
       db.Execute(commandText, UserName, @Request.Form["OnBehalf"], @Request.Form["DateReq"], @Request.Form["DateReturn"], @Request.Form["AuthorId"], @Request.Form["CategoryId"], @Status, @Request.Form["noted"], @Umail, @dateinsert);
       
    //db.Execute(commandText, UserName, OnBehalf, DateReq, DateReturn, authorId, categoryId, Status_m, noted, StaffEmail, dateinsert);
   }
   commandText = @"SELECT UserName, OnBehalf, UserEmail, LastName + ', ' + FirstName  As Author, CategoryM
                 FROM ReqEq
                 INNER JOIN Authors ON ReqEq.AuthorId = Authors.AuthorId
                 INNER JOIN Categories ON ReqEq.CategoryId = Categories.CategoryId";
                 //INNER JOIN ReqEq ON UserLogonid.ComLogonid = ReqEq.UserName";
 
 
  // var reqeq = db.Query(commandText);
  // var grid = new WebGrid(reqeq, canPage: false, canSort:false);
 
//Add email
   
  if(IsPost){
      //var item = "CategoryM";
      try {
                WebMail.SmtpServer = "10.139.162.8";
                WebMail.SmtpPort = 25;             
                WebMail.UserName = "[e-mail]";
                WebMail.From = "[e-mail]";
                WebMail.Password = "1234";
 
                // Send email
                WebMail.Send(to: "[e-mail]",
                subject: "Request Equipment from: " + @UsernameLog ,
                
              //body: "<p>Dear TSD Team,</p><p>I, (Mr./Mrs./Miss) "+ @UsernameLog+" would like to borrow from the Office:<p><b>Equipment"+ temp + "</b></p><p> I have received the aforementioned items in good working condition and will return them in their original intact state.</p><p> Should the items become damaged or lost, I am responsible for the cash value of such items<p>at the book value as at the time of the loss or damage, or per the Office’s policy as deemed appropriate by Technology System Department.</p><br><p>Best Regards,</p><p>"+ @UsernameLog+"</p><br><p>Computer name:"+ @ThisMachine+"</p><br>", 
              body: "<p>Dear TSD Team,</p><p>I, (Mr./Mrs./Miss) "+ @UsernameLog+" would like to borrow from the Office:<p><b>Equipment "+Request.Form["CatogoryId"].AsInt()+"</b>and expect to return date on "+Request.Form["DateReturn"]+"</p><p> I have received the aforementioned items in good working condition and will return them in their original intact state.</p><p> Should the items become damaged or lost, I am responsible for the cash value of such items<p>at the book value as at the time of the loss or damage, or per the Office’s policy as deemed appropriate by Technology System Department.</p><br><p>Best Regards,<br>"+ @UsernameLog+"</p><br><p>Computer name:"+ @ThisMachine+"<br>Date Request Equipment: "+Request.Form["DateReq"]+"<br>", 
                isBodyHtml: true
                 );
    }
            catch (Exception ex ) {
 
                  Response.Redirect ("error.cshtml");
            }     
         Response.Redirect ("test2RequestMobile.cshtml");
     }
    } }

Answer 4

User-2014067552 posted

Dear Angie,

If i would like to follow suggestion as the detail below. What would the coding should be? I would need your help. I'm very much appreciated your help in advance. 

productID	productname	category
1001	AAA	C1,C2,C3

Answer 5

User-166373564 posted

Hi iamnung,

Its work for insert into database on AuthorId and CategoryId but It can be checked only one checkedbox. If i selected more one got the error " Conversion failed when converting the nvarchar value '1,2' to data type int."

From the error message, it seems that the data type not match.

If you want to store multiple item into one field, the field data type should be nvarchar or varchar type, after you get all selected value, you could use string.join method to add delimiter, then insert the result (Like: "1,2") into database.

Then, when you want to use it, after query it from table, you could use string.split method to remove the delimiter, then convert them to Int type and use it to filter data.

Best regards,
Angie

Answer 6

User-2014067552 posted

Thank you for all your help. I really appreciate your support.