Is there anyway to get the row number?

[chance]

I have this code:

DataRow row = docAttachTable.NewRow();
docAttachTable.Rows.Add(row);

What I want to know is the primary key number (it's auto-generated).
Is there anyway to do that?

thanks in advance,
chance.

 

[Nicholas Paldino [.NET/C# MVP]]

Chance,

Until you commit the row to the database, and reselect the id back from
the database, you won't be able to know.

That's one of the problems of using auto-generated ids, and it's because
of this that I stay away from them.

Hope this helps.

 

[OD]

Since the data are not sent to the database, the autoinc key is not
generated. So first you must post the data to the DB. Next you have to
query the DB to get the line and know the ID.. But of course, as you
don't know the ID, you can't query the DB to get it
So, there is mainly two ways to get out of this problem :

1) you add a string field in your table in which you put a new GUID
(using the framework it is simple to generate one), then you can query
the DB based on this GUID and you can know the ID... Of course it seems
a bit stupid to waste a string field for that purpose and also it is
not very smart to add a GUID if there is already an autoinc primary
key... But in some cases, this solution is not bad.
Another way to use this solution and avoid the GUID field is when your
table as a second possible primary key (a Candidate key). In this case
this is a very good way to select the record and read the autoinc and
this solution becomes the best and simplest one.

2) the most used solution is to avoid autoinc field... steps :

a) create a generator in your DB
b) create a stored procedure to return next value
c) optional : add a trigger to the table to insert a new value (from
the stored proc) when the field is null
d) when you wan programmaticaly set the ID yourself, you call the
stored procedure in your code and set the ID field as any other fields.

 

[Brooke]

Create a stored procedure on SQL Sever for your insert;

ALTER PROCEDURE INSERTFORMAT

@formatID INT OUTPUT,

@formatName VARCHAR(25),

@formatProgram VARCHAR(25)

AS

INSERT INTO FORMATS

(FORMATNAME,

FORMATPROGRAM,

FORMATUPDATEDBY)

VALUES (@formatName,

@formatProgram,

SUSER_SNAME())

SET @formatID = @@IDENTITY

RETURN



Call the store procedure from within your code using parameters;



//function to insert new record and return it's row identifier (autoinc)

public int InsertFormat(FormatsDetails fmt)

{

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand("InsertFormat", con);

cmd.CommandType = CommandType.StoredProcedure;



cmd.Parameters.Add(new SqlParameter("@formatName", SqlDbType.VarChar,
25));

cmd.Parameters["@formatName"].Value = fmt.FormatName;



cmd.Parameters.Add(new SqlParameter("@formatProgram",
SqlDbType.VarChar, 25));

cmd.Parameters["@formatProgram"].Value = fmt.FormatProgram;



cmd.Parameters.Add(new SqlParameter("@formatID", SqlDbType.Int, 4));

cmd.Parameters["@formatID"].Direction = ParameterDirection.Output;



try

{

con.Open();

cmd.ExecuteNonQuery();

return (int)cmd.Parameters["@formatID"].Value;

}

catch (SqlException err)

{

// add error handling code here

throw new ApplicationException("Data Insert Error");

}

finally

{

con.Close();

}

}