One-to-many file upload with additional checkbox

One-to-many file upload with additional checkbox

ezdavisezdavis Posts: 15Questions: 2Answers: 0

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have a one to many file upload setup following your example using a link table. My image model looks like the following

public class ImagesModel : EditorModel
    {
        public System.Guid ImageID { get; set; }
        public byte[] Content { get; set; }
        public string ContentType { get; set; }
        public string Extension { get; set; }
        public string FileName { get; set; }
        public int FileSize { get; set; }
        public string MimeType { get; set; }
        public DateTime CreatedDate { get; set; }
        public bool IsDefault { get; set; }
    }

Basically, I have "Products" that can have many images. This is all setup and working fine with all images currently being uploaded as "IsDefault" false. However, I need a way to mark the "IsDefault". I want to have a checkbox next to the image and then a validator in my controller to only allow 1 image to be checked but I'm struggling on how to accomplish this. Any suggestions? Here's my editor

using (var db = new Database(dbType, dbConnection))
            {
                var response = new Editor(db, "Product.Product", "ProductID")
                    //.Model<ProductModel>()
                    .Field(new Field("ProductID"))
                    .Field(new Field("Product.ProductID").Set(false))
                    .Field(new Field("Product.Identifier").Set(false))
                    .Field(new Field("Product.Title").Set(false))
                    .MJoin(new MJoin("Product.Images")
                        .Link("Product.Product.ProductID", "Product.ImageRef.ProductID")
                        .Link("Product.Images.ImageID", "Product.ImageRef.ImageID")
                        .Model<ImagesModel>()
                        .Field(new Field("ImageID")
                            .Upload(new Upload()
                                .Db("Product.Images", "ImageID", new Dictionary<string, object>
                                {
                                    //{"web_path", Path.DirectorySeparatorChar+Path.Combine("uploads", "__ID____EXTN__")},
                                    {"ImageID", Upload.DbType.ReadOnly},
                                    {"Content", Upload.DbType.ContentBinary},
                                    {"ContentType", Upload.DbType.ContentType},
                                    {"Extension", Upload.DbType.Extn},
                                    {"FileName", Upload.DbType.FileName},
                                    {"FileSize", Upload.DbType.FileSize},
                                    {"MimeType", Upload.DbType.MimeType},
                                    {"CreatedDate", Upload.DbType.ReadOnly},
                                    {"IsDefault", Upload.DbType.ReadOnly}
                                })
                                .Validator(Validation.FileSize(500000, "Max file size is 500K."))
                                .Validator(Validation.FileExtensions(new[] { "jpg", "png", "jpeg" }, "Please upload an image."))
                                )
                        )
                    )
                    //.Debug(true).TryCatch(false)
                    .Process(Request)
                    .Data();

                return Json(response);
            }

Note: I have IsDefault setup as a bit in my database and defaults to 0.

Thanks for any help you can provide.

Answers

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Hi,

    With the values in the Dictionary for the third parameter to Db(), you don't just need to use the Upload.DbType enum - you can also use values or even a delegate. So for example:

    {"IsDefault", false}
    

    would write false into the IsDefault database field.

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    Sorry, just getting back to this

    So when I try as you suggest with false,true,0, or 1 I get the error -

    "Conversion failed when converting the nvarchar value '-' to data type bit."

    Once I get this error fixed how could I get the value of the checkbox. Please see my js below that shows how I display a checkbox by my image in the UploadMany (I have my own logic for displaying image by UID but the part I'm trying to get the value of is the checkbox). How could I get the value of this checkbox to be updated with my image?

    var editor; // use a global for the submit and return data rendering in the examples
    
    $(document).ready(function () {
        editor = new $.fn.dataTable.Editor({
            ajax: "/file/LoadAllImages",
            table: "#example",
            fields: [
                {
                    label: "Product ID:",
                    name: "Product.ProductID",
                    type: "readonly"
                },
                {
                label: "Part No:",
                    name: "Product.Identifier",
                    type: "readonly"
                },
                {
                label: "Title:",
                name: "Product.Title",
                type: "readonly"
                },
                {
                label: "Images:",
                name: "Images[].ImageID",
                type: "uploadMany",
                    display:
                        function (fileId, counter) {
                            return '<img src="http://localhost:44328/UID/' + editor.file('Product.Images', fileId).ImageID + editor.file('Product.Images', fileId).Extension + '"/> <input type="checkbox" id="' + editor.file('Product.Images', fileId).ImageID + '" name="' + editor.file('Product.Images', fileId).FileName + '" checked="' + editor.file('Product.Images', fileId).IsDefault + '">';
                    }
                    ,
                noFileText: 'No images'
            }
            ]
        });
    
        $('#example').DataTable({
            dom: "Bfrtip",
            ajax: {
                url: "/file/LoadAllImages",
                type: 'POST'
            },
            serverSide: "true",
            processing: "true",
            columns: [
                { data: "Product.ProductID" },
                { data: "Product.Identifier"},
                { data: "Product.Title" },
                {
                    data: "Images",
                    render: function (d) {
                        return d.length ?
                            d.length + ' image(s)' :
                            'No image';
                    },
                    title: "Image"
                }
            ],
            select: true,
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor }
            ]
        });
    });
    
  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Can you show me the schema for your table please?

    Thanks,
    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    I also have the Product.Product table but items are added to that separately. All I'm trying to do is add the ability to setup multiple images per product (Which works). And then mark one as the default image for the product.

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Many thanks. What version of the DataTables dll is it you are using? What should be happening is that the ReadOnly flag hits here and there is no write to the database on that column.

    But it looks like it might be getting down to line 687 for some reason and trying to set the column to be a dash, causing the error you are seeing.

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    2.0.4 is the version I'm using.

    It would make sense that it's hitting line 687 if I'm changing

    {"IsDefault", Upload.DbType.ReadOnly}
    

    to

    {"IsDefault", false}
    

    right?

    It's no longer a DbType. Should it be hitting completely separate logic for a value rather than a DbType?

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    You are spot on - it didn't quite click for me first thing this morning!

    The most obvious change is to simply remove those two q.Set calls, but there might be some who are depended upon this behaviour (it would need a default value in SQL for the target column). I could try setting it to null, but equally the column would need to allow null values.

    My inclination is to remove those two calls with Editor 2.1 (although I don't have a time scale for that yet). You could build the dlls from source with those lines removed, or I can send you over built ones if you prefer?

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    Yeah, if you could send me the built files I could try it out.

    But would I need to to use a delegate to get the value from my checkbox for IsDefault? Do you have an example of an upload that uses a delegate?

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    No you shouldn't need to use a delegate. The try/catch should handle cases where a non-delagate is given. However, you could use {"IsDefault", () => false} which is a simple lambda for it.

    The dll's are here.

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    Alright so I've tested it and I'm no longer getting an error but I don't think it's actually doing anything.

    I have my table defaulting the IsDefault to false in SQL.

    When I set

    {"IsDefault", true}
    

    the column is still set false. When I check my checkbox nothing happens there either it always stays false.

    When I set

    {"IsDefault", () => false}
    

    I get the error "Cannot convert lambda expression to type 'object' because it is not a delegate type"

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Could you possibly try:

    {"IsDefault", 1}
    

    please? That worked for me (using MySQL as the database with a boolean field).

    Sorry about the delegate - I'm to used to writing Javascript. It would need to be more along the lines of:

    (Func<Database, IFormFile, ojbect>)((d, f) => "1")},
    

    That isn't quite right though - there is a cast missing in the dll I sent you. Hopefully using the {"IsDefault", 1} will do the job for you though.

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    That didn't work for me either. Here are my 3 tables being used if you would like to create your own tables to try it.

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [Product].[Product](
        [ProductID] [int] IDENTITY(1,1) NOT NULL,
        [Identifier] [varchar](50) NOT NULL,
        [Title] [varchar](300) NOT NULL,
     CONSTRAINT [PK_Product_1] PRIMARY KEY CLUSTERED 
    (
        [ProductID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [Product].[Images](
        [ImageID] [uniqueidentifier] NOT NULL,
        [Content] [varbinary](max) NOT NULL,
        [ContentType] [varchar](50) NULL,
        [Extension] [varchar](50) NULL,
        [FileName] [nvarchar](50) NULL,
        [FileSize] [int] NULL,
        [MimeType] [nvarchar](50) NULL,
        [CreatedDate] [datetime] NULL,
        [IsDefault] [bit] NOT NULL,
     CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED 
    (
        [ImageID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    ALTER TABLE [Product].[Images] ADD  DEFAULT (newid()) FOR [ImageID]
    GO
    
    ALTER TABLE [Product].[Images] ADD  CONSTRAINT [DF_Images_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
    GO
    
    ALTER TABLE [Product].[Images] ADD  CONSTRAINT [DF_Images_IsDefault]  DEFAULT ((0)) FOR [IsDefault]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [Product].[ImageRef](
        [ImageID] [uniqueidentifier] NOT NULL,
        [ProductID] [int] NOT NULL,
     CONSTRAINT [PK_ImageRef_1] PRIMARY KEY CLUSTERED 
    (
        [ImageID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [Product].[ImageRef]  WITH CHECK ADD  CONSTRAINT [FK_ImageRef_Images] FOREIGN KEY([ImageID])
    REFERENCES [Product].[Images] ([ImageID])
    GO
    
    ALTER TABLE [Product].[ImageRef] CHECK CONSTRAINT [FK_ImageRef_Images]
    GO
    
    ALTER TABLE [Product].[ImageRef]  WITH CHECK ADD  CONSTRAINT [FK_ImageRef_Product] FOREIGN KEY([ProductID])
    REFERENCES [Product].[Product] ([ProductID])
    GO
    
    ALTER TABLE [Product].[ImageRef] CHECK CONSTRAINT [FK_ImageRef_Product]
    GO
    
  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    Hey Allan,

    Thanks for your help so far. Do you have any other suggestions on how to get this to work? I'm so close I just need to mark one as the default image somehow. I could even create another table to link to and then update somehow if that makes sense. Do you have any suggestions? Thanks.

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Could you comment back in the .Debug(true) and then from the response to the upload action, show me the JSON that the server is returning please? I'm surprised that {"IsDefault", 1} isn't working.

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    Here is the part that gets returned after I select the image. Looks like it uploads the image to the table when you select it and when you click update it does another call to update the ref table with the ID.

    {
        "draw": null,
        "data": [],
        "recordsTotal": null,
        "recordsFiltered": null,
        "error": null,
        "fieldErrors": [],
        "id": null,
        "meta": {},
        "options": {},
        "searchPanes": {
            "options": {}
        },
        "files": {
            "Product.Images": {
                "e4c8f4d7-dd0c-4287-a8d5-9c243bb48a63": {
                    "ImageID": "e4c8f4d7-dd0c-4287-a8d5-9c243bb48a63",
                    "ContentType": "image/jpeg",
                    "Extension": ".jpg",
                    "FileName": "Logo_2Color_tag.jpg",
                    "FileSize": 174001,
                    "MimeType": "image/jpeg",
                    "CreatedDate": "2021-10-06T09:32:03.29",
                    "IsDefault": false
                }
            }
        },
        "upload": {
            "id": "e4c8f4d7-dd0c-4287-a8d5-9c243bb48a63"
        },
        "debug": [{
                "Query": "DECLARE @T TABLE ( insert_id uniqueidentifier ); INSERT INTO  [Product].[Images]  ( [Content], [ContentType], [Extension], [FileName], [FileSize], [MimeType] ) OUTPUT INSERTED.ImageID as insert_id INTO @T VALUES (  @Content,  @ContentType,  @Extension,  @FileName,  @FileSize,  @MimeType ); SELECT insert_id FROM @T",
                "Bindings": [{
                        "Name": "@Content",
                        "Value": "LARGEIMAGEVALUEWASHEREBUTREMOVED",
                        "Type": null
                    }, {
                        "Name": "@ContentType",
                        "Value": "image/jpeg",
                        "Type": null
                    }, {
                        "Name": "@Extension",
                        "Value": ".jpg",
                        "Type": null
                    }, {
                        "Name": "@FileName",
                        "Value": "Logo_2Color_tag.jpg",
                        "Type": null
                    }, {
                        "Name": "@FileSize",
                        "Value": 174001,
                        "Type": null
                    }, {
                        "Name": "@MimeType",
                        "Value": "image/jpeg",
                        "Type": null
                    }
                ]
            }, {
                "Query": "SELECT  [ImageID] as 'ImageID', [ContentType] as 'ContentType', [Extension] as 'Extension', [FileName] as 'FileName', [FileSize] as 'FileSize', [MimeType] as 'MimeType', [CreatedDate] as 'CreatedDate', [IsDefault] as 'IsDefault' FROM  [Product].[Images] WHERE [ImageID] IN (@wherein1) ",
                "Bindings": [{
                        "Name": "@wherein1",
                        "Value": "e4c8f4d7-dd0c-4287-a8d5-9c243bb48a63",
                        "Type": null
                    }
                ]
            }
        ],
        "cancelled": []
    }
    

    NOTE: I removed the actual image from content value to make the json smaller.

    The one thing I notice is the first debug inserts the table without "CreatedDate" or "IsDefault"

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    I might have realised what the problem is - the DbType is an enum, which is causing to conflict with just passing in 1 for example.

    Let me get back to you on this tomorrow - I've got a plan...

    Allan

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Actually - I've just gone ahead and tried it out... :).

    Try:

    {"IsDefault", (Func<Database, IFormFile, dynamic>)((d, f) => 1)}
    

    with this dll.

    That does the job for me locally with SqlServer.

    We need to use the anonymous function after all since just using 1 would cause it to match against the DbType enum!

    Allan

  • ezdavisezdavis Posts: 15Questions: 2Answers: 0

    I get an error when trying to add the dll to my project. Can you send me a link like before that has the debug folder and the different net folders and dll's inside of it? Thanks

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    Sorry - it is actually a zip file, but I used the wrong extension. Here it is with the correct .zip extension.

    Allan

Sign In or Register to comment.