Store procedure returning ID after insert.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Store procedure returning ID after insert.

Author

Topic

dealwi8me
Starting Member

13 Posts

Posted - 2009-08-29 : 14:33:58

Hello i have the following Stored Procedure fro inserting records in Northwind database. My problem is that it doesn't seems to work with my C# code.

SPROC:
CREATE PROCEDURE INSERT_ORDERS
(@CustomerID nchar(5),
@Freight money,
@OrdersID int output
)
AS

INSERT Orders (CustomerID,Freight)
VALUES(@CustomerID,@Freight)

SELECT @OrdersID=@@IDENTITY
GO


 private void saveToolStripButton_Click(object sender, EventArgs e)
        {
            string connectionString = @"Server=OP; Initial Catalog=Northwind; Integrated Security=True";
             using (SqlConnection conn = new SqlConnection(connectionString))
             {
                 SqlCommand command = new SqlCommand("INSERT_ORDERS", conn);
                 command.CommandType = CommandType.StoredProcedure;
                 
                 SqlParameter param = new SqlParameter("@OrderID", Convert.ToDecimal(orderID_txt.Text));
                 param.Direction = ParameterDirection.Output;


                 command.Parameters.Add("@CustomerID", SqlDbType.VarChar).Value = custID_txt.Text;
                 command.Parameters.Add("@Freight", SqlDbType.Money).Value = freight_txt.Text;

                 conn.Open();
                 int rows = command.ExecuteNonQuery();
                 orderID_txt.Text = ((int)param.Value).ToString();
                 conn.Close();
             }

        }