How to output ID no.?

Hi,

When I insert form details in the database table, I also have an id no. in the database, which auto generated.

I want to fetch i.e output the auto id no. so that I can use it for further insertions/other transactions. How to do that?

Re: How to output ID no.?

USe Output parameter and assign Scope_IDentity after insertion if you use stored proc.

For query:

strSQL = "INSERT INTO tablename (name) VALUES (@name);SELECT SCOPE_IDENTITY()"
SQLCommand.CommandText = strSQL
Id = SQLCommand.ExecuteScalar()

http://stackoverflow.com/questions/3913115/scope-identity-in-c-sharp-range

Re: How to output ID no.?

string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(query, conn))
  {
    cmd.Parameters.AddWithValue("", Category.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    cmd.CommandText = query2;
    ID = (int)cmd.ExecuteScalar();
  }
}

also refer to this link

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Re: How to output ID no.?

The problem is that since it is auto generated, I don't explicitly insert it in the database. Even in my SP, I have not used it in the parameters. Then how do you suggest me to add Output parameter?

Pl guide me...

Re: How to output ID no.?

You need not to insert in autogenerated column, when you insert other fields it is autogenerated and scope_identity will return the auto generated value.

For SP:

CREATE PROCEDURE dbo.DoSomething
(
    @var1 INT = NULL,
    @var2 INT = NULL,
    @var3 DATE = NULL
)
AS

BEGIN

    INSERT INTO atable
    (
        vara,
        varb,
        varc
    )
    VALUES
    (
        @var1,
        @var2,
        @var3
    )

   SELECT SCOPE_IDENTITY()

END

int result = 0;

/// create command
SqlCommand cmd = new SqlCommand("DoSomething", this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var1", thing.value1);
cmd.Parameters.AddWithValue("@var2", thing.value2);
cmd.Parameters.AddWithValue("@var3", thing.value3);


/// send data to db
result = (int)cmd.ExecuteScalar();

Re: How to output ID no.?

hai

http://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar

http://www.objectreference.net/post/SCOPE_IDENTITY()-return-the-id-from-the-database-on-insert.aspx

thank u

Re: How to output ID no.?

@urenjoy @sriramabi

Thanks!

One part is done. For the second part, pl tell me how to incorporate this Scope_Identity() into the code behind file, i.e where I insert the form values?

I have inserted all fields as parameters which go into a function where a SP is called.

Re: How to output ID no.?

coolpal9

@urenjoy @sriramabi

Thanks!

One part is done. For the second part, pl tell me how to incorporate this Scope_Identity() into the code behind file, i.e where I insert the form values?

I have inserted all fields as parameters which go into a function where a SP is called.

hey.i have one examble pls try this way..

its code behond side code

public int InsertBatchResponseStatus(string BatchId, int ResponseStatusCode, string LABatchId, string AddedBy, DateTime AddedDate)
        {
            using (SqlConnection con = new SqlConnection(connection))
            {
                SqlCommand cmd = new SqlCommand("[uspInsertBatchResponseStatus]", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@BatchId", SqlDbType.VarChar, (500)).Value = BatchId;
                cmd.Parameters.Add("@ResponseStatusCode", SqlDbType.Int).Value = ResponseStatusCode;
                cmd.Parameters.Add("@LABatchId", SqlDbType.VarChar, (500)).Value = LABatchId;
                cmd.Parameters.Add("@AddedBy", SqlDbType.VarChar).Value = AddedBy;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = AddedDate;
                con.Open();
                int DetailId = Convert.ToInt32(cmd.ExecuteScalar());
 return DetailId;
            }
        }

and here my Stored Procedure

CREATE PROCEDURE [dbo].[uspInsertBatchResponseStatus]                
(      
   @BatchId varchar(500),    
   @ResponseStatusCode int,    
   @LABatchId   varchar(500),                      
   @AddedBy varchar(200),    
   @AddedDate datetime      
)                
AS      
BEGIN              
SET NOCOUNT ON           
  INSERT INTO BatchDetails(BatchId,ResponseStatusCode,BatchQueryStatusCode,BatchStatusCode,DeleteBatchResponseCode,LABatchId,AddedBy,AddedDate)     
  VALUES(@BatchId,@ResponseStatusCode,1,1,2,@LABatchId,@AddedBy,@AddedDate)      
   SELECT SCOPE_IDENTITY() AS [@@DetailId];    
      
END

thank u

Re: How to output ID no.?

The way I insert the values into the database is:

InsertDetails= new InsertDetails(); //InsertDetails is a .cs file

bool ret = db.AddDetails(Batchid, ResponseStatusCode, LABatchId, AddedBy, AddedDate) //AddDetails() is a method in InsertDetails()

In the .cs file:

public bool AddDetails(string Batchid, string ResponseStatusCode, string LABatchId, string AddedBy, string AddedDate)
    {
        try
        {
            SqlParameter[] myparams = BuildSPParamsUnicode("Batchid, ResponseStatusCode, LABatchId, AddedBy, AddedDate", Batchid, ResponseStatusCode, LABatchId, AddedBy, AddedDate);
            Object obj = SqlHelper.ExecuteScalar(ConnStr, CommandType.StoredProcedure, "uspInsertBatchResponseStatus", myparams);
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

How shall I do that in this syntax?

Re: How to output ID no.?

try this way

Public Sub InsertAccountDetail(CompanyName As String, UserName As String, Password As String, EmailAddress As String, dtCreate As DateTime)
        Dim arParms() As SqlParameter = New SqlParameter(4) {}
        arParms(0) = New SqlParameter("@coName", SqlDbType.NVarChar, 12)
        arParms(0).Value = CompanyName
        arParms(1) = New SqlParameter("@username", SqlDbType.NVarChar, 12)
        arParms(1).Value = UserName
        arParms(2) = New SqlParameter("@Password", SqlDbType.NVarChar, 50)
        arParms(2).Value = Password
        arParms(3) = New SqlParameter("@EmailAddress", SqlDbType.NVarChar, 60)
        arParms(3).Value = EmailAddress
        arParms(4) = New SqlParameter("@dtCreate", SqlDbType.DateTime)
        arParms(4).Value = dtCreate
        SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "usp_Account_InsertAccountDetails", arParms)
    End Sub

thank u

Re: How to output ID no.?

I've done what I wanted!!

Thanks all those who helped me in this task!