Error saving editor form after uploading files

dynasoft

Hi

I'm getting this error when clicking the save button on editor after loading files. Uploading works fine but saving does not. I'd be grateful for pointers on what the issue might be. I have followed all docs as best I could.

'The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.'

Here's my code:

• JS:

      var editor1 = new $.fn.dataTable.Editor({
  
          destroy: true,
          ajax: {
              url: '/CDRData/CRUDCDRDataAndFiles/',
              data: function ( d ) {
                  return $.extend( {}, d, {
                      intCdrFrmtSrc: intCdrFrmtSrc1,
                      intCdrFrmtTpe: intCdrFrmtTpe1,
                      strCdrFrmtNme: strCdrFrmtNme1,
                      strEditorState: strEditorState1
                  } );
              },
              type: 'POST',
              async: true,
              cache: false
          },
          table: '#tblCDRDataAndFilesTable',
          fields: [ {
                  label: '@(lblo.lblUploadedFile)*:',
                  name: 'UserFiles[].id',
                  type: 'uploadMany',
                  display: function ( fileId, counter ) {
                      return fileId ? GetEditorUploadFileRender(editor1.file( 'UserFiles', fileId ).WebPath, editor1.file( 'UserFiles', fileId ).FileName, 1, '@strFileIconWebPath') : null;
                  },
                  clearText: '@(lblo.lblClear)',
                  noFileText: '@(lblo.lblNoFile)'
              }
          ]
      });
  
      var dataTable = $('#tblCDRDataAndFilesTable').DataTable( {
  
          destroy: true,
          order: [[0, 'desc']],
          columnDefs: [
              { 'bVisible': false, 'targets': 0 }                    
          ],
          dom: 'Bfrtip',
          ajax: {
              url: '/CDRData/CRUDCDRDataAndFiles/',
              data: function ( d ) {
                  return $.extend( {}, d, {
                      intCdrFrmtSrc: intCdrFrmtSrc1,
                      intCdrFrmtTpe: intCdrFrmtTpe1,
                      strCdrFrmtNme: strCdrFrmtNme1,
                      strEditorState: strEditorState1
                  } );
              },
              type: 'GET',
              async: true,
              cache: false
          },
          columns: [
              { data: 'id' ,
                  className: 'text-left'
              },
              { data: "UserFileID",
                  render: function ( d ) {
                      return d.length ?
                          d.length+' image(s)' :
                          '@(lblo.lblNoFile)';
                  },
                  className: 'text-left',
                  defaultContent: '@(lblo.lblNoFile)',
                  title: '@(lblo.lblFile)'
              }
          ],
          select: true,
          buttons: [
              { extend: 'create', editor: editor1 },
              { extend: 'edit', editor: editor2 },
              { extend: 'remove', editor: editor2 }
          ]
      });
  

• Server:

  public ActionResult CRUDCDRDataAndFiles(int intCdrFrmtSrc, int intCdrFrmtTpe, string strCdrFrmtNme)
  {
      Editor editor = null;
  
      HttpRequest formData = System.Web.HttpContext.Current.Request;
  
      using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
      {
          editor = new Editor(db, "AutoCDRFiles").Model<CDRDataDBModel>();
          editor.Field(new Field("id")
              .Set(false)
          );
          editor.MJoin(new MJoin("UserFiles")
              .Link("UserFiles.id", "AutoCDRFiles.UserFileID")
              .Field(
                  new Field("id")
                      .SetFormatter(Format.IfEmpty(0))
                      .Upload(new Upload(strFolder + "__NAME____EXTN__")
                      .Db("UserFiles", "ID", new Dictionary<string, object>
                      {
                          {"WebPath", Upload.DbType.WebPath},
                          {"SystemPath", Upload.DbType.SystemPath},
                          {"FileName", Upload.DbType.FileName},
                          {"FileSize", Upload.DbType.FileSize}
                      })
                  )
              )
          );
          editor.TryCatch(false);
          editor.Debug(true);
          editor.Process(formData);
          editor.Data();
      }
  
      return Json(editor.Data(), JsonRequestBehavior.AllowGet);            
  }
  

• Model:

  public class CDRDataDBModel
  {
  public long id { get; set; }
  public long UserFileID { get; set; }
  }

allan

On mobile at the moment so just had a quick look at the code. Try matching the case - the Editor libraries are case sensitive, so use 'id' consistently rather than a mix of that and 'ID'.

Allan

dynasoft

Thanks I get the same error if I use small cap id in .Db("UserFiles", "id", new Dictionary<string, object>

allan

Could you send me the JSON response from the server when you get that error please? I see you have debugging enabled, so that will include the SQL Editor is generating which will be useful here.

Thanks,
Allan

dynasoft

Stacktrace:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>

allan

Try removing the TryCatch(false). We need to know what the SQL statement is that Editor is generating.

Thanks,
Allan

dynasoft

Please find the result below:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"The variable name \u0027@id\u0027 has already been declared. Variable names must be unique within a query batch or stored procedure.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":[{"Query":"DECLARE @T TABLE ( insert_id bigint ); INSERT INTO [AutoCDRFiles] ( [CDRSourceType], [CDRDataType], [CDRName], [CDRFilePath], [CDRFileName], [CDRFileMarked], [RatedStatus] ) OUTPUT INSERTED.ID as insert_id INTO @T VALUES ( @CDRSourceType, @CDRDataType, @CDRName, @CDRFilePath, @CDRFileName, @CDRFileMarked, @RatedStatus ); SELECT insert_id FROM @T","Bindings":[{"Name":"@CDRSourceType","Value":0,"Type":null},{"Name":"@CDRDataType","Value":0,"Type":null},{"Name":"@CDRName","Value":"DynasoftVoiceCDR","Type":null},{"Name":"@CDRFilePath","Value":"Z:\Dev\Projects\TeleFacturaCloud\Active\TeleFacturaCloud.Web\user\cdr\voicedata\DynasoftVoiceCDR\","Type":null},{"Name":"@CDRFileName","Value":null,"Type":null},{"Name":"@CDRFileMarked","Value":0,"Type":null},{"Name":"@RatedStatus","Value":0,"Type":null}]},{"Query":"INSERT INTO [UserFiles] ( [id], [id] ) VALUES ( @id, @id )","Bindings":[{"Name":"@id","Value":"10158","Type":null},{"Name":"@id","Value":111,"Type":null}]}],"cancelled":[]}

allan

Its this one:

    {
        "Query": "INSERT INTO  [UserFiles]  ( [id], [id] ) VALUES (  @id,  @id )",
        "Bindings": [{
            "Name": "@id",
            "Value": "10158",
            "Type": null
        }, {
            "Name": "@id",
            "Value": 111,
            "Type": null
        }]
    }

I don't understand why it is doing that I'm afraid. I'm going ot have to dig into that more and get back to you.

Allan

dynasoft

Thanks Allan

dynasoft

Hi Allan

Were you able to find the cause of the error above? Thanks

dynasoft

Hi

I'd like to progress with this. Kindly let me know

allan

Sorry - bit of a support backlog at the moment. I'll look into this later today.

Allan

allan

Apologies - I'm getting bogged down atm. I am going to look into it as soon as possible though!

dynasoft

Thanks

allan

its taken an embarrassingly long time to look at this - sorry!

I was reviewing your code to see if I could reproduce it here, but I spotted this - perhaps you could say if I'm correct or not:

        editor = new Editor(db, "AutoCDRFiles").Model<CDRDataDBModel>();
        editor.MJoin(new MJoin("UserFiles")
            .Link("UserFiles.id", "AutoCDRFiles.UserFileID")

That (I think) means that there can only ever be one UserFiles entry per AutoCDRFiles entry. i.e. it is one-to-one rather than one-to-many (it can't be one to many here since AutoCDRFiles.UserFileID needs to match the UserFiles.id file, which I presume is unique?

So I guess we need to consider, is one-to-one what you want? If so, don't use an Mjoin, use a left join.

If you do want a one-to-many connection there needs to be some relational way to show that in the db. Normally I just use a junction table, but you could use the AutoCDRFiles id in the UserFiles table as a reference (this is more tricky though since the file could be uploaded before the AutoCDRFiles row exists!

Or I could be totally misunderstanding!

Allan

dynasoft

Hi

Thanks.

I also got side tracked but Im back on this.

AutoCDRFiles.UserFileID is unique but not UserFiles.id as UserFiles can store id's from other tables. Will try with a left join.

dynasoft

Sorry I got that wrong. Its like this: UserFiles.id is unique of course.

dynasoft

hi

I have modified the code and added a leftjoin but get the same eror:

    editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
        .MJoin(new MJoin("UserFiles")
        .Link("UserFiles.id", "AutoCDRFiles.UserFileID")
        .Field(
            new Field("id")
                .SetFormatter(Format.IfEmpty(0))
                .Upload(new Upload(strFolder + "__NAME____EXTN__")
                .Db("UserFiles", "id", new Dictionary<string, object>
                {
                    {"WebPath", Upload.DbType.WebPath},
                    {"SystemPath", Upload.DbType.SystemPath},
                    {"FileName", Upload.DbType.FileName},
                    {"FileSize", Upload.DbType.FileSize}
                })
            )
        )
    );

Am I getting the leftjoin correctly?
Its one-to-one I want.

dynasoft

I'm getting the files uploaded and the files' data get saved to UserFiles table but not to AutoCDRFiles.

dynasoft

Have modified my code as per below:

editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
.Field(new Field("AutoCDRFiles.UserFileID")
.SetFormatter(Format.IfEmpty(0))
.Upload(new Upload(strFolder + "NAME____EXTN")
.Db("UserFiles", "id", new Dictionary<string, object>
{
{"WebPath", Upload.DbType.WebPath},
{"SystemPath", Upload.DbType.SystemPath},
{"FileName", Upload.DbType.FileName},
{"FileSize", Upload.DbType.FileSize}
})
)
);

Error: Unknown upload field name submitted

editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
.Field(new Field("AutoCDRFiles.id")
.SetFormatter(Format.IfEmpty(0))
.Upload(new Upload(strFolder + "NAME____EXTN")
.Db("UserFiles", "id", new Dictionary<string, object>
{
{"WebPath", Upload.DbType.WebPath},
{"SystemPath", Upload.DbType.SystemPath},
{"FileName", Upload.DbType.FileName},
{"FileSize", Upload.DbType.FileSize}
})
)
);

Error: Unknown upload field name submitted

There is no MJoin here but things trip up when I click the ok button on the modal form whereas with the MJoin I was able to upload the file(s) and even save their data to UserFils table but not autoCDRFiles. I'm not sure what the syntax should be for the new Field part if the above is the way to do it.

Grateful for any help.

allan

Unknown upload field name submitted suggests that your upload is called something other than AutoCDRFiles.id.

Allan

dynasoft

Hi

Thanks. Upload is unchaged from top of post:

fields: [ {
        label: '@(lblo.lblUploadedFile)*:',
        name: 'UserFiles[].id',
        type: 'uploadMany',
        display: function ( fileId, counter ) {
            return fileId ? GetEditorUploadFileRender(editor1.file( 'UserFiles', fileId ).WebPath, editor1.file( 'UserFiles', fileId ).FileName, 1, '@strFileIconWebPath') : null;
        },
        clearText: '@(lblo.lblClear)',
        noFileText: '@(lblo.lblNoFile)'
    }
]

Using this in server code:

                    editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
                        .Field(new Field("UserFiles.id")
                            .SetFormatter(Format.IfEmpty(0))
                            .Upload(new Upload(strFolder + "__NAME____EXTN__")
                            .Db("UserFiles", "id", new Dictionary<string, object>
                            {
                                {"WebPath", Upload.DbType.WebPath},
                                {"SystemPath", Upload.DbType.SystemPath},
                                {"FileName", Upload.DbType.FileName},
                                {"FileSize", Upload.DbType.FileSize}
                            })
                        )
                    );

where I use field named UserFiles.id still gives error Unknown upload field name submitted'

dynasoft

Not sure why this is such a problem: all I have is a table called autocdrfiles and a field called userfileid which stores the id from userfiles. each file uploaded should increment userfiles witha a new row and its id should get stored in a new row in autocdrfiles. Thats all.

dynasoft

Some debugging.

When I select the file to upload, it runs an insert successfully:

INSERT INTO [UserFiles] ( [WebPath], [SystemPath], [FileName], [FileSize] ) OUTPUT INSERTED.ID as insert_id INTO @T VALUES ( @WebPath, @SystemPath, @FileName, @FileSize ); SELECT insert_id FROM @T","Bindings":[{"Name":"@WebPath","Value":"-","Type":null},{"Name":"@SystemPath","Value":"-","Type":null},{"Name":"@FileName","Value":"ServiceCDR.txt","Type":null},{"Name":"@FileSize","Value":1538,"Type":null}]},{"Query":"UPDATE [UserFiles] SET [WebPath] = @WebPath, [SystemPath] = @SystemPath WHERE [id] = @where_0 ","Bindings":[{"Name":"@where_0","Value":"27","Type":null},{"Name":"@WebPath","Value":"\...","Type":null},{"Name":"@SystemPath","Value":"Z:\...","Type":null}]},{"Query":"SELECT [id] as \u0027id\u0027, [WebPath] as \u0027WebPath\u0027, [SystemPath] as \u0027SystemPath\u0027, [FileName] as \u0027FileName\u0027, [FileSize] as \u0027FileSize\u0027 FROM [UserFiles] WHERE [id] IN (@wherein1) ","Bindings":[{"Name":"@wherein1","Value":"27","Type":null}]}],"cancelled":[]}

When I click to save, it errors out on the insert into UserFiles and the insert into AutoCDRFiles does nothing:

INSERT INTO [AutoCDRFiles] ( [CDRSourceType], [CDRDataType], [CDRName], [CDRFilePath], [CDRFileName], [CDRFileMarked], [RatedStatus] ) OUTPUT INSERTED.ID as insert_id INTO @T VALUES ( @CDRSourceType, @CDRDataType, @CDRName, @CDRFilePath, @CDRFileName, @CDRFileMarked, @RatedStatus ); SELECT insert_id FROM @T","Bindings":[{"Name":"@CDRSourceType","Value":0,"Type":null},{"Name":"@CDRDataType","Value":1,"Type":null},{"Name":"@CDRName","Value":"xxxCDR","Type":null},{"Name":"@CDRFilePath","Value":"Z:\...","Type":null},{"Name":"@CDRFileName","Value":null,"Type":null},{"Name":"@CDRFileMarked","Value":0,"Type":null},{"Name":"@RatedStatus","Value":0,"Type":null}]},{"Query":"INSERT INTO [UserFiles] ( [id], [id] ) VALUES ( @id, @id )","Bindings":[{"Name":"@id","Value":"10153","Type":null},{"Name":"@id","Value":27,"Type":null}]}],"cancelled":[]}

dynasoft

Whole server code:

            using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
            {
                editor = new Editor(db, "AutoCDRFiles").Model<CDRDataDBModel>();
                editor.Field(new Field("AutoCDRFiles.id")
                    .Set(false)
                );

                if (strEditorState == "create")
                {
                    editor.Field(new Field("AutoCDRFiles.CDRSourceType") //.SetFormatter((val, data) => Array.Find(arrNVVM1, o => o.Text == Convert.ToString(val)).intValue)
                        .GetFormatter((val, host) => Array.Find(arrNVVM1, o => o.intValue == Convert.ToInt16(val)).Text)
                        .Validator((val, d, host) => CommonUtilities.ToString(val) == "-1" ? lblo.lblEnsureOptionSelected : null)
                        .SetValue(intCdrFrmtSrc)
                    );
                    editor.Field(new Field("AutoCDRFiles.CDRDataType")
                        .GetFormatter((val, host) => Array.Find(arrNVVM2, o => o.intValue == Convert.ToInt16(val)).Text)
                        .Validator((val, d, host) => CommonUtilities.ToString(val) == "-1" ? lblo.lblEnsureOptionSelected : null)
                        .SetValue(intCdrFrmtTpe)
                    );
                    editor.Field(new Field("AutoCDRFiles.CDRName")
                        .Validator((val, d, host) => CommonUtilities.ToString(val) == "default" ? lblo.lblEnsureOptionSelected : null)
                        .SetValue(strCdrFrmtNme)
                    );
                    editor.Field(new Field("AutoCDRFiles.CDRFilePath")
                        .SetValue(strFolder)
                    );
                    editor.Field(new Field("AutoCDRFiles.CDRFileName")
                        .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
                        .SetValue("")   //*HERE* - needs setting
                    );
                    editor.Field(new Field("AutoCDRFiles.CDRFileMarked")
                        .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                        .SetValue(0)
                    );
                    editor.Field(new Field("AutoCDRFiles.DateRated")
                        .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 1))
                        .SetValue(null)
                    );
                    editor.Field(new Field("AutoCDRFiles.DateFile")
                        .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 1))
                        .SetValue(CommonUtilities.GetFileCreateDate(strFolder, "")) //*HERE* - needs setting
                    );
                    editor.Field(new Field("AutoCDRFiles.RatedStatus")
                        .GetFormatter((val, host) => Array.Find(arrNVVM3, o => o.intValue == Convert.ToInt16(val)).Text)
                        .SetValue(0)
                    );

                    editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
                        .MJoin(new MJoin("UserFiles")
                        .Link("UserFiles.id", "AutoCDRFiles.UserFileID")
                        .Field(
                            new Field("id")
                                .SetFormatter(Format.IfEmpty(0))
                                .Upload(new Upload(strFolder + "__NAME____EXTN__")
                                .Db("UserFiles", "id", new Dictionary<string, object>
                                {
                                    {"WebPath", Upload.DbType.WebPath},
                                    {"SystemPath", Upload.DbType.SystemPath},
                                    {"FileName", Upload.DbType.FileName},
                                    {"FileSize", Upload.DbType.FileSize}
                                })
                                .Validator(Validation.FileExtensions(CommonUtilities.CorrectFileTypes(2), lblo.lblInvalidFileType))
                            )
                        )
                    );
                }
                else
                {
                    editor.Field(new Field("CDRSourceType") //.SetFormatter((val, data) => Array.Find(arrNVVM1, o => o.Text == Convert.ToString(val)).intValue)
                        .GetFormatter((val, host) => Array.Find(arrNVVM1, o => o.intValue == Convert.ToInt16(val)).Text)
                        .Set(false)
                    );
                    editor.Field(new Field("CDRDataType")
                        .GetFormatter((val, host) => Array.Find(arrNVVM2, o => o.intValue == Convert.ToInt16(val)).Text)
                        .Set(false)
                    );
                    editor.Field(new Field("CDRName")
                        .Validator((val, d, host) => CommonUtilities.ToString(val) == "default" ? lblo.lblEnsureOptionSelected : null)
                        .Set(false)
                    );
                    editor.Field(new Field("CDRFilePath")
                        .Set(false)
                    );
                    editor.Field(new Field("CDRFileName")
                        .Set(false)
                    );
                    editor.Field(new Field("CDRFileMarked")
                        .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                        .SetFormatter((val, data) => CommonUtilities.ToInt16(val) == -1 ? 0 : val)
                    );
                    editor.Field(new Field("DateRated")
                        .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 1))
                        .Set(false)
                    );
                    editor.Field(new Field("DateFile")
                        .GetFormatter((val, data) => CommonUtilities.ToDateTimeString(CommonUtilities.ToDateTime(val), 1))
                        .Set(false)
                    );
                    editor.Field(new Field("RatedStatus")
                        .GetFormatter((val, host) => Array.Find(arrNVVM3, o => o.intValue == Convert.ToInt16(val)).Text)
                        .Set(false)
                    );
                    editor.Field(new Field("UserFileID")
                        .Set(false)
                    );
                }
                if (intCdrFrmtSrc > -1) editor.Where("CDRSourceType", intCdrFrmtSrc);
                if (intCdrFrmtTpe > -1) editor.Where("CDRDataType", intCdrFrmtTpe);
                if (intCdrFrmtTpe > -1) editor.Where("CDRName", strCdrFrmtNme);
                editor.Debug(true);
                editor.Process(formData);
                editor.Data();

dynasoft

If I use this in the js code:

label: '@(lblo.lblUploadedFile)*:',
name: 'AutoCDRFiles[].UserFileID',
type: 'uploadMany',
display: function ( fileId, counter ) {
    return fileId ? GetEditorUploadFileRender(editor1.file( 'UserFiles', fileId ).WebPath, editor1.file( 'UserFiles', fileId ).FileName, 1, '@strFileIconWebPath') : null;
},
clearText: '@(lblo.lblClear)',
noFileText: '@(lblo.lblNoFile)'

And this in server code:

editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")
    .MJoin(new MJoin("AutoCDRFiles")
    .Link("UserFiles.id", "AutoCDRFiles.UserFileID")
    .Field(new Field("UserFileID")
        .SetFormatter(Format.IfEmpty(0))
        .Upload(new Upload(strFolder + "__NAME____EXTN__")
            .Db("UserFiles", "id", new Dictionary<string, object>
            {
                {"WebPath", Upload.DbType.WebPath},
                {"SystemPath", Upload.DbType.SystemPath},
                {"FileName", Upload.DbType.FileName},
                {"FileSize", Upload.DbType.FileSize}
            })                                    
        )
    )
);

I get when saving the form: 'Object reference not set to an instance of an object.'

dynasoft

Fyi, SetFormatter's don't get evaluated or hit if a break point is placed over them in server method CRUDCDRDataAndFiles after clicking save. These are reached in other parts of my code where i don't use uploadMany but simple single uploads.

allan

I get when saving the form: 'Object reference not set to an instance of an object.'

If you add .TryCatch(false) before the .Process() method call, does it tell you anything more specific about what is not an object? I don't actually see anything wrong with your code above - using AutoCDRFiles[].UserFileID looks correct there to me.

Thanks,
Allan

dynasoft

The thread 0x3aa4 has exited with code 0 (0x0).
Exception thrown: 'System.NullReferenceException' in DataTables-Editor-Server.dll
Object reference not set to an instance of an object.

dynasoft

System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=DataTables-Editor-Server
StackTrace:
at DataTables.MJoin.Insert(Editor editor, Object parentId, Dictionary`2 data) in /home/vagrant/DataTablesSrc/extensions/Editor-NET/DataTables-Editor-Server/MJoin.cs:line 510

allan

That suggests that this line is doesn't have _editor defined, but it is set by _Prepare from the passed in parameter.

Looking over your code, there is one thing that bothers me - you are using UserFiles as a left join and also an Mjoin. That's going to name clash in the JSON - I don't think it would cause the error you are seeing, but equally I don't actually know what it would do!

I don't see UserFiles being used as a left join field anywhere other than the LeftJoin call, so you could remove this line please:

editor.LeftJoin("UserFiles", "UserFiles.id", "=", "AutoCDRFiles.UserFileID")

If that doesn't solve it. Can you show me a few more lines of the stacktrace please?

Thanks,
Allan