Malphai Malphai - 3 months ago 28
C# Question

System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition

I want the user to type in all data that is displayed (the columns from the database), fill them in and submit. I cant get the Id and CompanyID to get auto generated either with

HTML.HiddenFor
element.


System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition..


Model:

namespace Project.Models
{
public class Contact
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public int Phone { get; set; }
public int CompanyID { get; set; }
}
}


View:

@model Project.Contact

<h2>Create Contact</h2>

@using (Html.BeginForm("Create", "Home", FormMethod.Post))
{
<table>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Phone</th>
</tr>
<tr>
<td>@Html.HiddenFor(m => m.Id)</td>
<td></td>
<td>@Html.TextBoxFor(m => m.FirstName)</td>
<td>@Html.TextBoxFor(m => m.LastName)</td>
<td>@Html.TextBoxFor(m => m.Email)</td>
<td>@Html.TextBoxFor(m => m.Phone)</td>
<td>@Html.HiddenFor(m => m.CompanyID)</td>
</tr>

</table>
<br />
<input type="submit" name="btn" class="btn-primary" value="Create" />
}


Controller:

using Arbetsprov_Sublime___Andre_Kordasti.Models;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Mvc;

namespace Arbetsprov_Sublime___Andre_Kordasti.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}

[HttpGet]
public ActionResult Create()
{
return View();
}

[HttpPost]
public ActionResult Create(Contact model)
{
var connection = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=Sublime;Integrated Security=True");
connection.Open();

var command = new SqlCommand("INSERT INTO Contact Values('" + model.Id + "','" + model.FirstName + " " + model.LastName + "','" + model.Email + " " + model.Phone + "','" + model.CompanyID + "')", connection);
command.ExecuteNonQuery();

return View();
}
}
}

Answer
var command = new SqlCommand("INSERT INTO Contact(Id,FirstName,LastName,Email,Phone,CompanyId) Values('" + model.Id + "','" + model.FirstName + " " + model.LastName + "','" + model.Email + " " + model.Phone + "','" + model.CompanyID + "')", connection);
command.ExecuteNonQuery();

if there is a column that has to be autoGenerated you have to tell the values which u are giving for which column they are so that sql then knows that the attributes which are not mentioned should be autoGenerated