C# 如何将数据从 ASP.Net 插入到 MS SQL Server？

How to insert data from ASP.Net to MS SQL Server?

提问by Behnaz Mardanzadeh

This question has been asked for several times here. I read posted questions but I still have problem. I'm trying to insert values from ASP.Net form to SQL Server. I created a sample website to work on inserting data into Sql table. SQL Database's name is "TestDatabaseDB" which has one table called "Person". Person table has 4 columns. They are ID, FirstName, LastName, NationalID. The type of ID is "int". I set "Is Identity:Yes". So SQL will assign an ID to each inserted record. It just doesn't work. When I click the button nothing happens. It must insert data into database table or clears the textboxes at least but it doesn't. I tried

这个问题在这里被问过好几次了。我阅读了发布的问题，但我仍然有问题。我正在尝试将值从 ASP.Net 表单插入到 SQL Server。我创建了一个示例网站来将数据插入到 Sql 表中。SQL 数据库的名称是“TestDatabaseDB”，其中有一个表称为“Person”。Person 表有 4 列。它们是 ID、名字、姓氏、国民 ID。ID 的类型是“int”。我设置了“是身份：是”。所以SQL会为每条插入的记录分配一个ID。它只是不起作用。当我单击按钮时，什么也没有发生。它必须至少将数据插入数据库表或清除文本框，但事实并非如此。我试过

SqlConnection conn= new SqlConnection(@"Data source=.\SQLEXPRESS; AttachDBFilename=""|DataDirectory|\TestWebSiteDB.mdf""; integrated user=true; User Instance=true") 

It didn't work. So I changed that into:

它没有用。所以我把它改成：

SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");

Didn't make any difference. Here is my code:

没有任何区别。这是我的代码：

using System;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnRegister_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");
        SqlCommand insert = new SqlCommand("insert into Person(FirstName, LastName, NationalID) values(@Name, @Surname, @ID)" ,conn);
        insert.Parameters.AddWithValue("@Name", txtboxName.Text);
        insert.Parameters.AddWithValue("@Surname", txtboxFamilyName.Text);
        insert.Parameters.AddWithValue("@ID", txtboxNationalCode.Text);
        try
        {
            conn.Open();
            insert.ExecuteNonQuery();
        }
        catch
        {
            LMsg.Text="Error when saving on database";
            conn.Close();
        }
        txtboxName.Text="";
        txtboxFamilyName.Text = "";
        txtboxNationalCode.Text = "";
    }
}

Any help would be appreciated.

任何帮助，将不胜感激。

回答by ????

You need to track what error you are getting as follows. Because it is not possible to help you without the actual error.

您需要跟踪您遇到的错误如下。因为如果没有实际错误，就不可能为您提供帮助。

catch(Exception ex)
    {
        LMsg.Text=ex.Message;
    }

Also you need to use finally in your code for closing connection rather than closing it into the catch block.

此外，您需要在代码中使用 finally 来关闭连接，而不是将其关闭到 catch 块中。

finaly
{
   conn.Close();
}

回答by jdev

check your connection string. in conn.open() you get exception ?

检查您的连接字符串。在 conn.open() 你得到异常？

To create a data connection to a SQL Server database

创建到 SQL Server 数据库的数据连接

In Server Explorer/Database Explorer click Connect to Database.

在服务器资源管理器/数据库资源管理器中单击连接到数据库。

In the Choose Data Source dialog box, select Microsoft SQL Server, and then click OK.

在“选择数据源”对话框中，选择 Microsoft SQL Server，然后单击“确定”。

If the Add Connection dialog box opens, and the Data source is not Microsoft SQL Server, click Change to open the Choose/Change Data Source dialog box

如果“添加连接”对话框打开，并且数据源不是 Microsoft SQL Server，请单击“更改”打开“选择/更改数据源”对话框

. For more information, see Choose/Change Data Source Dialog Box. Select a server name from the drop-down list, or type the name of the server where the database you want to access is located.

. 有关详细信息，请参阅选择/更改数据源对话框。从下拉列表中选择一个服务器名称，或键入要访问的数据库所在的服务器的名称。

Based on the requirements of your database or application, select either Windows Authentication or use a specific user name and password to log on to the SQL Server (SQL Server Authentication).

根据您的数据库或应用程序的要求，选择 Windows 身份验证或使用特定用户名和密码登录 SQL Server（SQL Server 身份验证）。

For more information, see Add/Modify Connection (Microsoft SQL Server).

有关详细信息，请参阅添加/修改连接 (Microsoft SQL Server)。

Select the database you want to connect to from the drop-down list.

从下拉列表中选择要连接的数据库。

Click OK.

单击确定。

then copy generated connection string to your program. when you install sql server the server name and the setting you choose for installing .affect your connection string.

然后将生成的连接字符串复制到您的程序中。当您安装 sql server 时，您选择的服务器名称和设置会影响您的连接字符串。

for more inforamtion on installing sql server see the installing sql server express edition

有关安装 sql server 的更多信息，请参阅 安装 sql server express edition

and also check this for connecting asp.net application to sql server Asp.net connection to SQL Server

并检查这个以将 asp.net 应用程序连接到 sql server Asp.net connection to SQL Server

回答by user3548409

Actually the list view has a default method call onsorting. It will automatically sort the list view. onsorting will call the function as below.The function no need put any statement.

实际上列表视图有一个默认的方法调用onsorting。它将自动对列表视图进行排序。onsorting 将调用如下函数。该函数不需要放置任何语句。

protected void Sorting(object sender,ListViewSortEventArgs e)
{

}

For the link button in list view u just simply put the tag like that:

对于列表视图中的链接按钮，您只需简单地放置这样的标签：

<asp:ListView ID="DisplayContent" runat="server" onSorting="Sorting">
<asp:LinkButton ID="Name" runat="server" CommandName="Sorting" CommandArgument="Name" Text="Name" />

回答by balakhial

Try this:

尝试这个：

protected void Register_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DB_Users.mdf;Integrated Security=True");
    SqlCommand insert = new SqlCommand("insert into tbl_users(name, username, password,email) values(@name, @username, @password,@email)", conn);
    insert.Parameters.AddWithValue("@name", txtname.Text);
    insert.Parameters.AddWithValue("@username", txtusername.Text);
    insert.Parameters.AddWithValue("@password", txtpassword.Text);
    insert.Parameters.AddWithValue("@email", txtemail.Text);
    try
    {
        conn.Open();
        insert.ExecuteNonQuery();
        lbl_msg.Text = "Register done !";
      //lbl_msg.Text = "??? ??? ?? ?????? ????? ??";
    }
    catch (Exception ex)
    {
        lbl_msg.Text = "Error: "+ex.Message;
      //lbl_msg.Text = "??? ?? ?????? ?? ?????? ????";
        conn.Close();
    }
}

It works for me.

这个对我有用。

回答by HaMi

Try following steps:

尝试以下步骤：

Step1: Instead of putting you query in your C# file, declare a stored procedure for it like bellow:

步骤 1：不要将查询放在 C# 文件中，而是为它声明一个存储过程，如下所示：

CREATE PROCEDURE [dbo].[ADD_PERSON_SP]
    /*Type of this variables should be their column types*/
    @firstName varchar(MAX),
    @lastName varchar(MAX),
    @nationalID varchar(MAX)
AS
BEGIN
    INSERT INTO [dbo].[Person] (FirstName, LastName, NationalID)
           VALUES (@firstName,@lastName,@nationalID)
END 

Step2: Using Stored Procedure where you need:

步骤 2：在需要的地方使用存储过程：

SqlConnection con = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand("ADD_PERSON_SP", con);
com.Parameters.AddWithValue("@firstName", txtboxName.Text);
com.Parameters.AddWithValue("@lastName", txtboxFamilyName.Text);
com.Parameters.AddWithValue("@nationalID", txtboxNationalCode.Text);
com.CommandType = CommandType.StoredProcedure;
try
{
    con.Open();
    com.ExecuteNonQuery();
}
catch (Exception)
{
    throw;
}
finally
{
    if (con.State == ConnectionState.Open)
        con.Close();
}

Keep in mind to:

请记住：

1. Do not put db related stuffs in your C# files
2. Try to choose better names for your variables(specially for controls)
3. Thismay help you with connectionString

1. 不要将数据库相关的东西放在你的 C# 文件中
2. 尝试为变量选择更好的名称（特别是控件）
3. 这可能会帮助您处理 connectionString

I hope that help

我希望有帮助

回答by ruken aslan

protected void btnRegister_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=. ; Database=TestWebSiteDB; Integrated Security=true");
    SqlDataAdapter dap = new SqlDataAdapter("insert into Person(FirstName, LastName, NationalID) values(@Name, @Surname, @ID)", con);
    dap.InsertCommand(txtboxName.Text, txtboxFamilyName.Text,txtboxNationalCode.Text);

    txtboxName.Text="";
    txtboxFamilyName.Text = "";
    txtboxNationalCode.Text = "";
}