System.Data.Sqlclient.Sqlexception: Invalid Column Name 'Phone_Types_Phone_Type_Id'

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



Leave a reply



Submit