Insert data to sql server using stored procedure and aspnet textboxes

[Verygoodguy]

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 follow:

      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,

The stored procedure is as follow:

 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

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

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

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

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

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.

[hericles]

You have the columns types specified as int in your command parameters but as varchar in your table definition. I think the SqlDbType should be varchar.

You could always change your catch bracket to give you the real reason for the failure.

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

[Verygoodguy]

The replay helped me a lot because I can now insert my students to a student table.
But I can not see in my label the identity key returned buy scope identity from the stored procedure.
Can anyone help?
The overall code that is related to scope identity is

1. in stored procedure

   ...
   SELECT @Identity = SCOPE_IDENTITY();
   

2. In the button click event to insert the data - C#

   ...
   SqlParameter myParm = cmd.Parameters.Add("@Identity", SqlDbType.Int);
   myParm.Direction = ParameterDirection.ReturnValue;
   
   myParm = cmd.Parameters.Add("@Identity", SqlDbType.Int, 0, "StudentID");
   myParm.Direction = ParameterDirection.Output;
   ...
   try
       {
           con.Open();
           cmd.ExecuteNonQuery();
           storedPeocReportLabel.Text = "Inserted.";
           cmd.Parameters["@Identity"].Value = scopeIdentityReportLabel.Text;
   
   
       }
       catch (Exception ex)
       {
           scopeIdentityReportLabel.Text= ex.ToString();
       }
       finally
       {
           con.Close();
           con.Dispose();
       }  
   

This code reports no error, but it does not display my identity value returned from scope identity.
Also it displays "Inserted" in the 1st label of the ExecuteNonQuery method.
Any sugestions

[hericles]

Is this the line where you expect the scope identity tone displayed?

cmd.Parameters["@Identity"].Value = scopeIdentityReportLabel.Text;

Here you are setting a command parameter to be equal to that text box value. Seeing as you have already executed the cmd object I'm guessing this is in the wrong place.

[Verygoodguy]

Ya. I want the output parameter displayed in the following aspnet label control: "scopeIdentityReportLabel.Text".

[hericles]

If your stored procedure returns a value then you will need to catch it in a variable. If it returns an int you will need:

int identity = int.Parse(cmd.ExecuteNonQuery());
scopeIdentityReportLabel.Text = identity.ToString();

Or something close to that anyway. At the moment you aren't using anything to hold the return value form the stored procedure.

[Hidayat62]

con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "ClientInsert";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = txtID.Text.Trim();
            cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text.Trim();
            cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = txtcity.Text.Trim();
            cmd.ExecuteNonQuery();
            //It works try it
            //you can define diifrent datatypes then your original datatypes of table 

[nivabeath]

The given code samples is too long...please find the following link..shows how to use stored procedure asp.net with parameter values.

Stored Procedure with Parameter

Niva

[sheejo.apsalone]

very nice post