0

I was working with SqlDataAdapter which is doing insert operation and should show identity column as output parameter in application.

Here is my code 

// created connection object
SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = new SqlCommand("GetPersons",con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

da.InsertCommand  = new SqlCommand("IPersons",con);
da.InsertCommand .CommandType = CommandType.StoredProcedure;
da.InsertCommand .Parameters.AddWithValue("@Name", txtname.Text);

// output param
SqlParameter param = new SqlParameter();
param.ParameterName = "@ID";
param.DbType = DbType.Int32;
param.Direction = ParameterDirection.Output;
da.InsertCommand.Parameters.Add(param);

DataSet ds = new DataSet();
da.Fill(ds);

string var = da.InsertCommand.Parameters["@ID"].Value.ToString();

..but in the last line I can't get value of the output parameter, it shows null value

This is the stored procedure:

Create Procedure [dbo].[IPersons]
     @Name varchar(255) = null,
     @ID int Out
AS
Begin
     insert into Persons values(@Name)

     select @ID = scope_Identity()
End

What am I missing from taking out output parameter?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
neeraj
  • 57
  • 1
  • 5
  • 9
  • You can't get the value of the output parameter after filling the Dataset because the `fill` method doesn't use the InsertCommand, it uses the SelectCommand. – Zohar Peled Apr 26 '15 at 11:49
  • You need the `ExecuteScalar` method if you want to retrieve any output parameter. – Youssef Lourayad Apr 26 '15 at 11:50
  • @Lourayad: Wrong. it's possible to use any execute method to get back an output parameter. this is not the problem here. neeraj: [Read this](http://stackoverflow.com/questions/1631054/using-sqldataadapter-to-insert-a-row) – Zohar Peled Apr 26 '15 at 11:55

0 Answers0