Designing INNER JOIN for Asp.net
Designing INNER JOIN for Asp.net
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
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.
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.:Allan
Many thanks