Need help using DataAdapter RowUpdated event to get Sql Server identity

  • Thread starter Thread starter Aaron Prohaska
  • Start date Start date
A

Aaron Prohaska

Can someone please tell me why the following code doesn't work? I got
the example from
http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

I keep receiving an invalid cast exception where I execute
sqlCmd.ExecuteScalar() in the RowUpdated event.

Namespace
{
public class ClassName
{

private static SqlConnection sqlConn = new SqlConnection("server=na;
database=na; UID=na; Password=na");

public int Create()
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SELECT TOP 1 * FROM Bikes ORDER BY BID DESC";
sqlCmd.CommandType = CommandType.Text;
DataTable data = new DataTable("NewBike");
SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
sqlCmd.Connection = sqlConn;
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
try
{
adapter.Fill(data);
DataRow row = data.NewRow();
row[1] = this.wsSessionID;
row[2] = this.userID;
this.createDate = DateTime.Now;
row[3] = this.createDate;
row[4] = this.createDate;
this.lastUpdate = this.createDate;
for(int i = bkFirstPart; i < (bkNumParts + bkFirstPart - 1); i++)
{
row = bike[i - bkFirstPart, 0];
}
int count = bkNumParts + bkFirstPart;
row[count] = this.bikeStatus; count++;
row[count] = this.adjustedWeight; count++;
row[count] = this.buildDept; count++;
row[count] = this.buildAesthetic; count++;
row[count] = this.buildComments; count++;
row[count] = this.bikeName; count++;
row[count] = this.bikeMSRP; count++;
row[count] = this.bikeDiscount; count++;
row[count] = this.bikeImage; count++;
row[count] = this.createDate;
data.Rows.Add(row);
adapter.RowUpdated += new
SqlRowUpdatedEventHandler(GetBikeIDAfterRowUpdate);
adapter.Update(data);
this.bikeID = Convert.ToInt32(row[0]);
}
catch(Exception x)
{
x.ToString();
}
finally
{
sqlConn.Close();
sqlCmd.Dispose();
adapter.Dispose();
}
return this.bikeID;
}

private void GetBikeIDAfterRowUpdate(object sender,
SqlRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
{
int newID = 0;
SqlCommand sqlCmd = new SqlCommand("SELECT SCOPE_IDENTITY()",
sqlConn);
newID = (int)sqlCmd.ExecuteScalar();
e.Row[0] = newID;
sqlCmd.Dispose();
}
}
}
}

regards,

aaron
 
Back
Top