mvc html帮助器详解,使用HTML帮助器MVC在SQL Server数据库中创建记录

I want the user to type in all data that is displayed (the columns from the database), fill them in and submit. But I cant get my code to work, what have I done wrong here? I get the error:

System.Data.SqlClient.SqlException: Incorrect syntax near 'Contact'.

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

Create Contact

First name	Last name	Email	Phone
@Html.TextBoxFor(m => m.FirstName)	@Html.TextBoxFor(m => m.LastName)	@Html.TextBoxFor(m => m.Email)	@Html.TextBoxFor(m => m.Phone)

@Html.HiddenFor(m => m.Id)

@Html.HiddenFor(m => m.CompanyID)

@using (Html.BeginForm())

{

}

Controller:

using Project.Models;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Web.Mvc;

namespace Project.Controllers

{

public class HomeController : Controller

{

public ActionResult Index()

{

return View();

}

public ActionResult Create(List model)

{

string query = "INSERT INTO Contact";

using (var connection = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=Sublime;Integrated Security=True"))

{

using (var command = new SqlCommand(query, connection))

{

connection.Open();

using (var dr = command.ExecuteReader())

{

if (dr.HasRows)

{

while (dr.Read())

{

var contact = new Contact();

contact.Id = dr.GetInt32(dr.GetOrdinal("Id"));

contact.FirstName = dr.GetString(dr.GetOrdinal("FirstName"));

contact.LastName = dr.GetString(dr.GetOrdinal("LastName"));

contact.Email = dr.GetString(dr.GetOrdinal("Email"));

contact.Phone = dr.GetInt32(dr.GetOrdinal("Phone"));

contact.CompanyID = dr.GetInt32(dr.GetOrdinal("CompanyID"));

model.Add(contact);

}

}

}

}

}

return View(model);

}

}

}

I am not also sure if my TextBoxes are properly connected to my action...

Talk1:

There is a lot wrong with your code. You need to hit some textbooks and learn some basic T-SQL as well as ASP.NET MVC before you have any business posting on SO.

Talk2:

Talk3:

What's wrong with it specific?

Talk4:

Your form does not even contain any form controls (they are all outside the form tags. You have not even shown you POST method (just a GET method for Create, and the code in that makes no sense at all).

Talk5:

Can I actually get any useful advice that has some code difference?

Solutions1

You are using ExecuteReader, which is a method to get data, trying to perform an Insert.

You need to use ExecuteNonQuery instead; and the best practice is to add the data through parameters.

The second problem is that your INSERT command is not complete.

Then, a third problem is that you pass a list, so you need to iterate through the list, performing one INSERT per each element.

public ActionResult Create(List model)

{

string query = "INSERT INTO Contact (Id, FirstName, LastName, Email, "

+ "Phone, CompanyId) values (@Id,@FirstName,@LastName"

+ ",@Email,@Phone,@CompanyID)";

using (var connection = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=Sublime;Integrated Security=True"))

{

connection.Open();

foreach(Contact modelElement in model)

{

SqlCommand command = new SqlCommand(queryString, connection);

command.Parameters.AddWithValue("@Id", modelElement.Id);

command.Parameters.AddWithValue("@FirstName", modelElement.FirstName);

command.Parameters.AddWithValue("@LastName", modelElement.LastName);

command.Parameters.AddWithValue("@Email", modelElement.Email);

command.Parameters.AddWithValue("@Phone", modelElement.Phone);

command.Parameters.AddWithValue("@CompanyID", modelElement.CompanyID);

command.ExecuteNonQuery();

}

}

}

Talk1:

I tried your code, I get "System.NullReferenceException: object reference not set to an instance of an object"

Talk2:

Don't use ? as parameter placeholders for SQL Server - use named parameters like @Id, @FirstName etc. in your SQL query statement

Talk3:

Malphai, where did you get the exception? (line)