.Net Editor using non-dbo schema

.Net Editor using non-dbo schema

nightmare6mnightmare6m Posts: 1Questions: 1Answers: 0

Hi there, I think this question has been asked, but so far it seems like the answer has been to use the dbo schema. In my case, that is not possible so I am hoping there is an answer.

I have a controller that looks like this:

var response = new Editor(db, "schemaName.tableName", "schemaName.TableName.Field_A_Name")
.Field(new Field("schemaName.TableName.Field_A_Name"))
.Field(new Field("schemaName.TableName.Field_B_Name"))
.Field(new Field("schemaName.TableName.Field_C_Name"))
.Field(new Field("schemaName.TableName.Field_D_Name"))
.Process(request)
.Data();

return Json(response);

This loads the data in correctly. However, When I try to insert a row, I get this response:
Cannot retrieve inserted id - no primary key was found.

Field_A_Name is the primary key of the table and is a varchar data type.

Do you have any suggestions on what I can do to resolve this?

Thank you

Answers

  • allanallan Posts: 49,474Questions: 1Answers: 7,275 Site admin

    Try dropping schemaName from everywhere but the second parameter for the new Editor() constructor - i.e.:

    var response = new Editor(db, "schemaName.tableName", "TableName.Field_A_Name")
    .Field(new Field("TableName.Field_A_Name"))
    .Field(new Field("TableName.Field_B_Name"))
    .Field(new Field("TableName.Field_C_Name"))
    .Field(new Field("TableName.Field_D_Name"))
    .Process(request)
    .Data();
    
    return Json(response);
    

    There is another option. Create a new database user which has schemaName defined as the default schema rather than dbo. That will let it work as expected.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    I followed your suggestion, it is still not working. Any ideas allan? Thanks!

  • colincolin Posts: 4,624Questions: 0Answers: 814

    Hi @clrncrao ,

    Did you try both the suggestions - the change to the syntax and the dropping of the table? The second should work, so I'm surprised it's not. Are you seeing errors?

    Cheers,

    Colin

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 14

    Hi @colin,

    I've tried the "Try dropping schemaName from everywhere but the second parameter for the new Editor() constructor - i.e.:" suggestion.
    The create new database user is not an option for my current project.

    Thanks

  • allanallan Posts: 49,474Questions: 1Answers: 7,275 Site admin

    Can you chance the default schema for the existing user? Unfortunately per schema support is not something the Editor .NET libraries have ready yet.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    Hi @allen,
    I've tried set the user's default the schema to the one that my table belongs to and it still doesn't work unfortunately. Everything works when the table is in the dbo schema but not in another schema.

    Thanks

  • allanallan Posts: 49,474Questions: 1Answers: 7,275 Site admin

    That's really odd - have you dropped the schema name from the list of fields when its setup like that and just used the column names (or table + columns if you are doing joins)?

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 22

    @allan I have it set up like this

    var response = new Editor(db, "schemaName.tableName", "TableName.Field_A_Name")
    .Field(new Field("TableName.Field_A_Name"))
    .Field(new Field("TableName.Field_B_Name"))
    .Field(new Field("TableName.Field_C_Name"))
    .Field(new Field("TableName.Field_D_Name"))
    .Process(request)
    .Data();

    return Json(response);

    I can update existing rows fine. I just can't add new rows.

  • colincolin Posts: 4,624Questions: 0Answers: 814

    Hi @clrncrao ,

    When you say you can't add new rows, are you seeing browser console or server-side errors? Are you able to link to your page?

    Cheers,

    Colin

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0
    edited March 22

    So going back to @allan earlier statement. Are you saying that this Data Tables .NET Library doesn't support any other schema beside dbo?

  • allanallan Posts: 49,474Questions: 1Answers: 7,275 Site admin

    That is currently correct, yes. I suspect the problem is here in the primary key detection.

    Allan

  • clrncraoclrncrao Posts: 7Questions: 1Answers: 0

    @allan

    Thanks allan. I'll take a look at the source code to see if I can do some modification to fit my solution.

Sign In or Register to comment.