Designing INNER JOIN for Asp.net

Designing INNER JOIN for Asp.net

dynasoftdynasoft Posts: 422Questions: 67Answers: 3
edited July 2019 in Free community support

Hi

I' trying to design an INNER JOIN and adapt the example given here https://datatables.net/forums/discussion/comment/116150/#Comment_116150 for MVC, but getting some errors. The INNER JOIN has to be done on CustomerVoiceCLI.id = CustomerVoiceCLIDetails.CustomerVoiceCLIIndex. I'd be grateful for some help. Many thanks.

  • Server:

            using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
            {
                editor = new Editor(db, "CustomerVoiceCLI").Model<CustomerSNsDBModel>();
                editor.Field(new Field("CustomerVoiceCLI.id")
                    .Options(new Options()
                        .Table("CustomerVoiceCLIDetails")
                        .Value("CustomerVoiceCLIDetails.CustomerVoiceCLIIndex")
                    )
                );
                editor.Field(new Field("CustomerVoiceCLI.CustID")
                    .Set(true)
                    .SetValue(lngContIdx)
                );
                editor.Field(new Field("CustomerVoiceCLI.CLI")
                    .SetFormatter(Format.IfEmpty(null))
                );
                editor.Where("CustomerVoiceCLI.CustID", lngContIdx);
                editor.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=");
                editor.TryCatch(false);
                editor.Debug(true);
                editor.Process(formData);
                editor.Data();
            }
    
  • Model:

    public class CustomerSNsDBModel
    {
    public class CustomerVoiceCLI
    {
    public long id { get; set; }
    public long CustID { get; set; }
    public string CLI { get; set; }
    }

    public class CustomerVoiceCLIDetails
    {
        public long id { get; set; }
        public long CustomerIndex { get; set; }
        public long CustomerVoiceCLIIndex { get; set; }
        public string CLIDetails { get; set; }
    }
    

    }
    }

  • Error:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=The multi-part identifier "CustomerVoiceCLIDetails.id" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CustomerIndex" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex" could not be bound.
The multi-part identifier "CustomerVoiceCLIDetails.CLIDetails" could not be bound.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>

Answers

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3
    edited July 2019
    • Server:
      using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
      {
      editor = new Editor(db, "CustomerVoiceCLI", "CustomerVoiceCLI.id")
      .Model<CustomerSNsDBModel.CustomerVoiceCLIDetails>("CustomerVoiceCLIDetails")
      .Model<CustomerSNsDBModel.CustomerVoiceCLI>("CustomerVoiceCLI");
      editor.Field(new Field("CustomerVoiceCLI.id")
      .Options(new Options()
      .Table("CustomerVoiceCLI")
      .Value("id")
      .Label("CLI")
      )
      );
      editor.Field(new Field("CustomerVoiceCLI.CustID")
      .Set(true)
      .SetValue(lngContIdx)
      );
      editor.Field(new Field("CustomerVoiceCLI.CLI")
      .SetFormatter(Format.IfEmpty(null))
      );
      editor.Where("CustomerVoiceCLI.CustID", lngContIdx);
      editor.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=");
      editor.TryCatch(false);
      editor.Debug(true);
      editor.Process(formData);
      editor.Data();
      }

    I'm now getting this:

    System.Data.SqlClient.SqlException
    HResult=0x80131904
    Message=The multi-part identifier "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex" could not be bound.
    Source=.Net SqlClient Data Provider
    StackTrace:
    <Cannot evaluate the exception stack trace>

    I'm just trying to make a simple IINER JOIN on CustomerVoiceCLI.id = CustomerVoiceCLIDetails.CustomerVoiceCLIIndex

    Thank you.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    The editor.Where() method is only used for simple literal comparisons. You need to use an anonymous function to be able to compare against another field in the db - see the docs here. e.g.:

    editor.Where(
      q => q.Where("CustomerVoiceCLI.id", "CustomerVoiceCLIDetails.CustomerVoiceCLIIndex", "=", false);
    );
    

    Allan

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Many thanks

This discussion has been closed.