System.Data.SqlClient.SqlException: Invalid column name 'phone_types_phone_type_id'
After doing quite a bit more research, it seems like I had a fairly unique issue. I attempted several of the fixes listed both on here and many other sites, but almost nothing seemed to fix the issue.
However, the solution I listed at the bottom of my original post seems to be working, and holding up well, so I believe it to be a fairly adequate solution to my problem.
To somewhat outline what was occurring, MVC EF was attempting to find a fk/pk relationship across two models, but since the column names across the models were different, it wasn't able to map them properly. If I were to trying to get all the emails from email_manager
by using the email_types
table, it wasn't an issue, but moving backwards, and grabbing the information from email_types
from email_manager
threw errors.
Since the column names between the two tables are different, EF tried to create a column to house the relationship, but since no such column existed, an error was thrown. To correct this, all that's necessary is to tell EF what the foreign key column actually is, and that is done by using [ForeignKey("email_type")]
above the collection that houses the parent model.
So for example, my new email_types
and email_manager
models were as follows:
[Table("employee.email_manager")]
public partial class email_manager
{
[Key]
public int email_id { get; set; }
public int employee_id { get; set; }
[Required]
[StringLength(255)]
public string email { get; set; }
public int email_type { get; set; }
[Column(TypeName = "date")]
public DateTime date_added { get; set; }
public bool deleted { get; set; }
[ForeignKey("email_type")]
public virtual email_types email_types { get; set; }
public virtual employees1 employees1 { get; set; }
}
[Table("employee.email_types")]
public partial class email_types
{
public email_types()
{
email_manager = new HashSet<email_manager>();
}
[Key]
public int email_type_id { get; set; }
[Required]
[StringLength(50)]
public string email_type_name { get; set; }
public virtual ICollection<email_manager> email_manager { get; set; }
}
System.Data.SqlClient.SqlException: 'Invalid column name 'music'.'
Assuming you actually have:
cmd.CommandText = "select*from ServiceP where ServiceName="+e.Item.Value.ToString() ;
and e.Item.Value
is "music"
, then what you need here is a parameter to hold the value to search for, i.e.
cmd.CommandText = "select*from ServiceP where ServiceName=@value";
cmd.Parameters.AddWithValue("@value", e.Item.Value.ToString());
Note that you can also throw away the cmd.ExecuteNonQuery();
- that does nothing useful here, and means you do the work twice.
System.Data.SqlClient.SqlException: Invalid column name 'Gender_id'
Genders_Id
and Programs_Id
are the default EF6 conventional names of FK columns for the one to many relationships introduced respectively by the Genders.Students
and Programs.Students
collection navigation properties.
Since your FK property / column names are not conventional, you should specify them by either [ForeinKey]
data annotations:
public class Programs
{
// ...
[ForeignKey("Program")]
public List<Students> Students { get; set; }
}
public class Genders
{
// ...
[ForeignKey("Gender")]
public List<Students> Students { get; set; }
}
or (my preferred) fluent API:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// ...
modelBuilder.Entity<Genders>()
.HasMany(e => e.Students)
.WithRequired()
.HasForeignKey(e => e.Gender);
modelBuilder.Entity<Programs>()
.HasMany(e => e.Students)
.WithRequired()
.HasForeignKey(e => e.Program);
}
System.Data.SqlClient.SqlException: 'Invalid column name '
The problem is that you are missing quotes. In any case the correct solution, is to use SQL parameters like:
SqlCommand command = new SqlCommand("SELECT rangemaster.code as ... where rangemaster.code = @param");
command.Parameters.Add("@param",SQlDbType.Nvarchar).Value = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
(Replication) System.Data.SqlClient.SqlException: 'Invalid column name'
Since you don't have some columns you have to try to change the select statement
Try this
SELECT MASV, HO, TEN, MALOP, DANGHIHOC FROM SINHVIEN
How do i fix this : System.Data.SqlClient.SqlException: Invalid column name 'TeamID'
You are correct in your comment that EF can't determine the relationships. One way to do it with annotations is with InverseProperty. Try:
[InverseProperty("Team")]
public virtual ICollection<PLUser> TeamUsers {get;set;}
and
[InverseProperty("TeamLeader")]
public virtual ICollection<Team> TeamsLeading { get; set; }
EDIT: You may have to play with it (been a while since I did it), but you may want to go something like this:
[InverseProperty("TeamsLeading")]
public virtual PLUser TeamLeader { get; set; }
public virtual ICollection<Scheme> TeamSchemes { get; set; }
[InverseProperty("Team")]
public virtual ICollection<PLUser> TeamUsers {get;set;}
Related Topics
How to Get All the Possible 3 Letter Permutations
Monodevelop Failure "Unknown Msbuild Failure" on Linux
How to Set PDF Paragraph or Font Line-Height with Itextsharp
Getters and Setters Are Bad Oo Design
String.Format Alternative in C++
Show Data in ASP.NET HTML Table
How to Change The Colour of The Line Below/Border of a Textbox (Entry)
In C# Wpf, Why Is My Tabcontrol's Selectionchanged Event Firing Too Often
How Set Value a Property Selector Expression<Func<T,Tresult>>
Should I Use Mkannotation, Mkannotationview or Mkpinannotation
How to Prevent Xss (Cross Site Scripting) Whilst Allowing HTML Input
How to Get Max Value of a Column Using Entity Framework
Convert JavaScript Regex to C#