如何使用aspnet文本框和存储过程将数据插入sql [英] how to insert data to sql using aspnet textboxes and stored procedure

问题描述

我已经在sql server中创建了一个表，一个insert存储过程，一个带有5个用于编辑参数值的文本框的aspnet Webform，一个用于显示作用域标识的输出标识值的标签，另一个用于显示是否插入的标签从ExecuteNonQuery()命令的执行中插入的报告.
我不知道显示作用域标识函数返回的输出标识的代码，因此，请在检查我的代码后，向我提供将在我的标签中显示该标识的更正和aspnet代码.
我正在使用Web Developer Express 2010和SQL Server Express2008.
我的目标是使用存储过程将aspnet文本框数据插入表中.我还想在标签中显示存储过程中作用域标识函数返回的输出标识.
我已经尝试过几次下面的aspnet代码，但是似乎什么也没有发生.如果我在sql management studio窗口中使用存储过程，则可以正常工作.
代码如下:

sql server中的表名是Students，表定义如下:-

I have created a table in sql server, an insert stored procedure, a aspnet webform with 5 text boxes to edit the parameter values, a label to display the output identity value from scope identity and another label to display the inserted or not inserted report from the execution of the ExecuteNonQuery() command.
I don’t know the code to display the output identity returned by scope identity function, so please, after examining my code bellow, supply me the corrections and the aspnet code that will display the identity in my label.
I am using web developer express 2010 and sql server express 2008.
My goal is to insert aspnet text boxes data into the table using the stored procedure. I also want to display in a label the output identity returned by scope identity function in the stored procedure.
I have tried several times the aspnet code below, but nothing seems to be happening. If I use the stored procedure in a sql management studio window it just works fine.
The code is bellow:

The table name in sql server is Students and the table definition is as follows:-

      StudentID int IDENTITY(1,1) NOT NULL,
	LastName varchar(35) NOT NULL,
	MiddleName varchar(35) NOT NULL,
	FirstName varchar(35) NOT NULL,
	FothersCompleteName varchar(65) NOT NULL,
	MothersCompleteName varchar(65) NOT NULL,
<pre>
The stored procedure is as follow:
<pre>
CREATE PROCEDURE dbo.InsertStudentsNames
@LastName varchar(35),
@MiddleName varchar(35),
@FirstName varchar(35),
@FothersCompleteName varchar(65),
@MothersCompleteName varchar(65),
@IdentitY int OUTPUT
AS
BEGIN
INSERT Students (LastName, MiddleName, FirstName, FothersCompleteName, MothersCompleteName)
VALUES 
(@LastName, @MiddleName, @FirstName, @FothersCompleteName, @MothersCompleteName)
END
SELECT @Identity = SCOPE_IDENTITY();
RETURN @Identity

我在aspnet网络表单中定义了5个文本框，两个标签和一个按钮，如下所示:-

I have defined 5 textboxs, two labels and a button in aspnet webform as below:-

      <asp:TextBox ID="lastNameTextBox" runat="server" Width="232px">
      <asp:TextBox ID="middleNameTextBox" runat="server" Width="232px">
 <asp:TextBox ID="firstNameTextBox" runat="server" Width="232px">
        <asp:TextBox ID="fothersCompleteNameTextBox" runat="server" Width="232px">
        <asp:TextBox ID="mothersCompleteNameTextBox" runat="server" Width="232px">

<asp:Label ID="identityLabel" runat="server" Text="LastName:">
<asp:Label ID="insertReportLabel" runat="server" Text="LastName:">

<asp:Button ID="insertStudent" runat="server" onclick=" insertStudentButton_Click " 
            Text="insert" />

我不输入代码来显示数据库中的身份值，因为我不知道.请提供我.但是insertStudentButton_Click事件包含以下代码:

I did not put the code to display the identity value from the database because I don’t know it. Please supply me. But the insertStudentButton_Click event contains the following code:

protected void insertStudentButton_Click (object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=86.25.18.45\\SQLEXPRESS;Initial Catalog=Students;User ID=myusername;Password=mypassword");
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = " InsertStudentsNames ";
        
cmd.Parameters.Add("@LastName", SqlDbType.Int).Value = lastNameTextBox.Text.Trim();
cmd.Parameters.Add("@MiddleName", SqlDbType.Int).Value = middleNameTextBox.Text.Trim();
cmd.Parameters.Add("@FirstName", SqlDbType.Int).Value = firstNameTextBox.Text.Trim();
cmd.Parameters.Add("@FothersCompleteName", SqlDbType.Int).Value = fothersCompleteNameTextBox.Text.Trim();
cmd.Parameters.Add("@MothersCompleteName", SqlDbType.Int).Value = mothersCompleteNameTextBox.Text.Trim();

        cmd.Connection = con;
    
        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            insertReportLabel.Text = "Student inserted sucessfully";
            
        }
        catch
        {
            insertReportLabel.Text = "Student NOT inserted";
        }
        finally
        {
            con.Close();
            con.Dispose();
        }  
    }

当我单击按钮时，它会在标签中显示以下休憩文本:未插入学生.

When I click the button, it displays in the label the fallowing text: Student NOT inserted.

推荐答案

如果您发现了抛出的实际异常，它将为您带来更好的体验.知道问题出在哪里.

有一个空的catch块是没有意义的.您出于某种原因实施异常处理，而该原因并非程序流.

从好的方面来说，您正在使用存储的proc和参数化的sql命令.您远远领先于大多数在这里发帖的人.做得好.

If you caught the actual Exception being thrown it would give you a better idea what the problem is.

It is pointless to have an empty catch block. You implement exception handling for a reason, and that reason is not for program flow.

On the plus side you are using stored procs and parameterized sql commands. You are far ahead of most who post here. Well done.

这篇关于如何使用aspnet文本框和存储过程将数据插入sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！