sqldataadapter

[DaveL]

Hello all,

Im inserting 100 rows into the database
i need the Identity column back.

Im not sure how to accomplish this...
Using SqlDataAdapter
Ive Tried samples that i found..but can't get them to work
except for 1 Row...Multiple rows identity column has 0 value


Does anybody know of any links i can read

Thanks
DaveL

 

[james]

See if this helps

http://www.kamath.com/tutorials/tut007_identity.asp

 

[DaveL]

Small Example of What is Happening
SqlCommandbuilder builds 100 plus Columns and when used does not work


SqlDataAdapter Ad = new SqlDataAdapter();
Ad.SelectCommand = new SqlCommand("Select top 0 * from MyTable With
(nolock)");
//use Sqlcommand builder
SqlCommandBuilder cmd = new SqlCommandBuilder(Ad);
Ad.SelectCommand.Connection=oConn;
Ad.InsertCommand = cmd.GetInsertcommand();

SqlDataSet ds = new SqlDataSet();

oConn.Open()
Ad.Fill(ds,"MyTable")
oConn.Close()
//at this point i have 0 rows/records
//i parse a Text file
//adding rows to this ds.Tables[0] mytable

//.....
//......
// looping through a text file and adding rows
//......
//......
//at this point i have 100 rows in the Table i want to insert them all and
have ds.tables[0] reflect the identity key
// add scope identity to the insert command
Ad.InsertCommand+="; Select Scope_identity() as MyIdentity";

oConn.Open()
Ad.Update(ds,"MyTable")
oConn.Close()

ds.AcceptChanges();
// does not work using Sqlcommandbuilder()

console.writeline(ds.tables[0]["MyIdentity") printed values = 0


//if i change the insertcommand to the below than it works
// the below reflects 100 new identities in the data set table[0]
// so im wondering, is there a switch in SqlCommandBuilder or somthing that
i am missing

//if i change the above Insert command to
Ad.InsertCommand.CommandText="Insert into MyTable (Field1>
values(@Field1);Select Scope_identity() as MyIdentity";
//
//
Ad.InsertCommand.Parameters.Add("@Field1", SqlDbType.Int, 0,"Field1");