Introduction
Since the introduction of ADO.NET, we started to use a different concept in the development of database-driven applications, the disconnected environment. Most of you are already aware of this change, but this new development model brings some problems that need to be solved, in order to make our application work in a fully disconnected environment.
One of the problems I've found when I was developing an application using this approach was the method of updating relational data using a DataSet and a DataAdapter objects. If you update just one DataTable there is no big deal in creating the code for the DataAdapter, but if you work on more than one table, and these tables have a parent-child relationship, the update/insert process can be a little tricky, specially if the parent table has an IDENTITY/AutoNumber column. In this article, I'll show some techniques to workaround this problem, specially if you work with AutoNumber/IDENTITY columns in your database.
In the example I'll use a database with 2 tables, one that holds Order and one that holds OrderDetails. The OrderDetails have a foreign key relationship with the Order table in the OrderId column, that is an IDENTITY/AutoNumber column.
The article is divided in two parts, one that shows an implementation using an Access 2000 database, and another that using a SQL Server Database with stored procedures. Hope you enjoy this!
Creating the structure (Access 2000)
The first thing you need to do in order to complete our example is to create some basic variables to hold our DataSet and two DataAdapter objects (one for the parent and one for the child table). We are going to use the System.Data.OleDb namespace, since we are working with Access 2000.
DataSet oDS = new DataSet();
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=orders.mdb");
conn.Open();
OleDbDataAdapter oOrdersDataAdapter = new
OleDbDataAdapter(new OleDbCommand("SELECT * FROM Orders", conn));
OleDbCommandBuilder oOrdersCmdBuilder = new
OleDbCommandBuilder(oOrdersDataAdapter);
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";
OleDbDataAdapter oOrderDetailsDataAdapter = new
OleDbDataAdapter(new OleDbCommand("SELECT * FROM OrderDetails", conn));
OleDbCommandBuilder oOrderDetailsCmdBuilder = new
OleDbCommandBuilder(oOrderDetailsDataAdapter);
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);
pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";
In the previous code sample, we have created a connection to our database and two DataAdapter objects, one to update the parent table (Orders) and one to update the child table (OrderDetails). We have used the FillSchema method of the DataAdapter to create both DataTable objects in the DataSet, so that they have the same structure of the database tables (including the AutoNumber field).
We've also used the OleDbCommand builder to create the additional commands for both DataAdapter, so that we can submit our changes in the DataSets to the database later on.
Creating the relationship between the DataTables
Now we need to add a relationship between the two key columns of both tables. We'll do this by creating a new DataRelation object and attaching it to the Relations collection of our DataSet. The following code does exactly this.
oDS.Relations.Add(new DataRelation("ParentChild",
oDS.Tables["Orders"].Columns["OrderId"],
oDS.Tables["OrderDetails"].Columns["OrderId"]));
In the above code, we used the DataSet (oDS) to get a reference to both key columns of the DataTable objects, and created a DataRelation object, adding it to the Relations collection of the DataSet.
Inserting Data
Now that we have everything set, we need to insert the data into to the DataSet, prior to update the data in the Access database. We'll insert a new row in the Order table and a new row in the OrderDetails table.
DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);
DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;
oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);
Notice that we have used the SetParentRow method to create the relationship between the two rows. This is the most important part when you want to update to tables that have a relationship and a AutoNumber column.
Updating the DataSet
Now that we have the data inserted into the DataSet, its time to update the database.
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();
Solving the AutoNumber column issue
If you check the data in the database you'll notice that the rows inserted in the OrderDetails table have the OrderId column set to zero, and the inserted OrderId in the Orders table is set to one. This occurs due to some issues with Access 2000 and the DataAdapter object. When the DataAdapter object updates the data in the first table (Order) it does not return the generated AutoNumber column value, so the DataTable Orders in the DataSet stays with the value zero within it. In order to correct the problem, we need to map the RowUpdate event to update this information.
To map the RowUpdate event we'll create an event handler, and get the value of the new auto-generated number to save in the DataSet. You'll new to add this line of code after the creation of the oOrdersDataAdapter object.
oOrdersDataAdapter.RowUpdated += new
OleDbRowUpdatedEventHandler(OrdersDataAdapter_OnRowUpdate);
Now we need to create an EventHandler function to handle this RowUpdate event.
static void OrdersDataAdapter_OnRowUpdate(object sender,
OleDbRowUpdatedEventArgs e)
{
OleDbCommand oCmd = new OleDbCommand("SELECT @@IDENTITY"
e.Command.Connection);
e.Row["OrderId"] = oCmd.ExecuteScalar();
e.Row.AcceptChanges();
}
For each update in the OrdersDataAdapter, we'll call a new SQL command that will get the newly inserted AutoNumber column value. We'll do this by using the SELECT @@IDENTITY command. This command works only in Access 2000/2002, not in the prior versions. After the value update in the DataSet, we need to call the AcceptChanges method of the Row, in order to maintain this row in an "updated" state, and not in a "changed" state.
If you try to execute the code again you'll see that now the row in the OrderDetails table contains the correct value in the column.
Now we'll see how to target this same issue in SQL Server 2000. The method that I presented for Access database can be as well applied to SQL Server, but if you're working with stored procedures, there are other ways to do this.
Creating the structure (SQL Server 2000)
We'll start by creating the same structure that we used for Access 2000, but instead of creating the DataAdapter commands using the CommandBuilder, we'll create them by code, since we're going to use a SQL Server stored procedure to update the data.
DataSet oDS = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=.;
Initial Catalog=Orders;Integrated Security=SSPI");
conn.Open();
SqlDataAdapter oOrdersDataAdapter = new
SqlDataAdapter(new SqlCommand("SELECT * FROM Orders", conn));
oOrdersDataAdapter.InsertCommand = new
SqlCommand("proc_InsertOrder", conn);
SqlCommand cmdInsert = oOrdersDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].Direction = ParameterDirection.Output;
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@CustomerName",
SqlDbType.VarChar,50,"CustomerName"));
cmdInsert.Parameters.Add(new SqlParameter("@ShippingAddress",
SqlDbType.VarChar,50,"ShippingAddress"));
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";
SqlDataAdapter oOrderDetailsDataAdapter = new
SqlDataAdapter(new SqlCommand("SELECT * FROM OrderDetails", conn));
oOrderDetailsDataAdapter.InsertCommand = new
SqlCommand("proc_InsertOrderDetails", conn);
cmdInsert = oOrderDetailsDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductId", SqlDbType.Int));
cmdInsert.Parameters["@ProductId"].SourceColumn = "ProductId";
cmdInsert.Parameters.Add(new
SqlParameter("@ProductName", SqlDbType.VarChar,50,"ProductName"));
cmdInsert.Parameters.Add(new
SqlParameter("@UnitPrice", SqlDbType.Decimal));
cmdInsert.Parameters["@UnitPrice"].SourceColumn = "UnitPrice";
cmdInsert.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int ));
cmdInsert.Parameters["@Quantity"].SourceColumn = "Quantity";
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);
pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";
oDS.Relations.Add(new DataRelation("ParentChild",
oDS.Tables["Orders"].Columns["OrderId"],
oDS.Tables["OrderDetails"].Columns["OrderId"]));
In this piece of code we're manually creating a SqlCommand to do all the inserts in the database table through the DataAdapter. Each SqlCommand calls a stored procedure in the database that has the parameters structure equal to the table structure.
The most important thing here is the OrderId parameter of the first DataAdapter's command. This parameter has a different direction than the others. The parameter has an output direction and a source column mapped to the OrderId column of the DataTable. With this structure, after each execution, the stored procedure will return the value to this parameter, that will be copied to the OrderId source column. The OrderId parameter receives the @@IDENTITY inside the procedure, like the one below.
CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
@CustomerName varchar(50),
@ShippingAddress varchar(50)
)
AS
INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)
SELECT @OrderId=@@IDENTITY
Inserting the Data
Now that we set the entire structure, its time to insert the data. The process is exactly the same as we have done with the Access database, using the SetParentRow method to maintain the relationship and guarantee that the IDENTITY column will be copied to the child table (OrderDetails).
DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);
DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;
oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();
If you check the database you'll see that the OrderId column is updated with the correct IDENTITY column value.
| You must Sign In to use this message board. |
|
|
 |
|
|
 Hello,  pls am new to database programing,am trying to store data from eight(8)textboxes into a database and i dnt knw how to about it. am receiving data continuosly via serial port which are displayed on textboxes about 8 of them but i wnt to be storing these data also in a database which will be updated anytime data is received on the textboxes. 
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
I was having trouble adding a record to a Access 2000 database with an autonumber field. Your solution with the Row Update event handler was just what I needed.
Thanks for sharing!
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hello! I use it code, but it doesn't work correctly. The data stored in the database, but after the closing and opening, they are deleted. after I save the data, I open the database and see its, but after the opening of my application they do not exist. What would it be? Please help me. I don't know who else can i ask. Thanks
my code:
private void Companies_Load(object sender, EventArgs e)
{
conn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data2.mdb");
adapt = new OleDbDataAdapter("SELECT * FROM Company", conn);
ds = new DataSet();
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(adapt);
adapt.DeleteCommand = cmdBuilder.GetDeleteCommand();
adapt.UpdateCommand = cmdBuilder.GetDeleteCommand();
adapt.InsertCommand = cmdBuilder.GetInsertCommand();
adapt.Fill(ds, "Company");
companyDataGridView.DataSource = ds.Tables["Company"];
}
private void button1_Click(object sender, EventArgs e)
{
DataRow dr = ds.Tables["Company"].NewRow();
dr["ID"] = Int32.Parse(iDTextBox.Text);
dr["Address"] = addressTextBox.Text;
dr["BankDetails"] = bankDetailsTextBox.Text;
dr["Name"] = nameTextBox.Text;
dr["Email"] = emailTextBox.Text;
dr["Site"] = siteTextBox.Text;
ds.Tables["Company"].Rows.Add(dr);
try
{
adapt.Update(ds, "Company");
ds.AcceptChanges();
conn.Close();
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hi there,
I have a parent and child table both of them have an Auto Number field as a primary key.
Ie Order - OrderID PK
OrderDetails - OrderDetailID PK, OrderID FK
I can update the "order" table no problems. it create an Order ID (i.e 2000)
But Order detail table it gives errors "INSERT INTO statement".. when i look at the underlying row values, it is saving the order ID= 2000 aginst the OrderDetailID and OrderID in order Detail table.
Can anyone help me.
Thanks
Jay
|
| Sign In·View Thread·PermaLink | 1.00/5 |
|
|
|
|
|
|
Hi Ritter,
This is the one I searched for long time ; Thanks for this rare content.I want to do this with typed dataset.Is it possible ?.
I am having two tables : Offer and OfferItems.Both tables having Identity fields as primary keys Offer_ID and OfferItem_ID respectively. the field "OI_OfferID" in OfferItem table relates the parent column Offer_ID. I am using stored procedures and configured dataset with these tables with relationship. My SP for inserting Offer also returns Offer_ID as OUTPUT value.
While adding OfferItem row ,I am setting the relation with Offer table by SetParentRow () method .I am able see this relation until adapterOffer.update().after updating Offer table , new Offer_Id (say 5) created.If updating offerItem , it throws foreign key violated error (as it has 0 as OI_OfferID). Here the reference value is not updated.
How I Insert in this scenario?
Please rescue me .
With Regards,
Mathi
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hi Mathi,
I am experiencing the same problem. I also have a typed dataset with two tables bound by a foreign key relationship. My code looks like this: ('ds' is my data set)
var offerRow = ds.Offer.AddOfferRow (...);
var offerItemRow = ds.OfferItem.NewOfferItemRow ();
offerItemRow.OfferRow = offerRow; // calls SetParentRow
ds.OfferItem.AddOfferItemRow (offerItemRow);
// offerItemRow.OfferRow evaluates to the correct row.
new OfferDataTableAdapter ().Update (ds.Offer); // works fine
// offerItemRow.OfferRow now evaluates to null.
new OfferItemDataTableAdapter ().Update (ds.OfferItem);
// the above fails with a foreign key constraint violation.
t appears as if the the first Update breaks the relationship and the offerItemRow no longer has an OfferRow. offerItemRow.OfferID evaluates to -1, same as before the call to Update. Thus it is not a surprise that the second Update fails.
So my questions is, why does the first Update break the relationship? Does anyone know how to fix this?
Thanks a bunch,
Sergey
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hi Sergey,
I still struggling with this problem. Now, I am doing manual update.That is , In insert SP, we are returning the inserted record (select from table where id = SCOPE_IDENTITY).so, it fills the adapter with new record .Then, I get the parent Id and updating child record with parent Id. The problem is, it is not possible to do this more than one relationship (parent-child); if we have child for child table, it is too difficult to update those records.I knew, it is ugly of doing; But, don't know how to achieve this as per article  .
Thanks,
Mathi.
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
hi,
how can i use a stored procedure to delete a record.
secondly, i trying to fill a variable with the query "select sum(columnname) from tablename where columname2 = 'something'. How can this be done. Can it be done using a stored procedure and how? Thanks
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
i have a table called suppliers where their supplierIDs(which is a primary key for the table)is auto generated for them..problem is after they register how will i let them know their supplierID.please help
|
| Sign In·View Thread·PermaLink | 1.00/5 |
|
|
|
|
|
|
You have to concatenate two sql statements. One to insert the new record and the other to retrieve it. as shown below.
here is a sample:
"insert into tableName (column1,column2....)Values (value1,value2,......); select id from tableName where id= scope_identity"
last row inserted for that session is fetched by the where clause condition "SCOPE_IDENTITY"
Note the ";" after the insert statement it is very important
SPC Nigeria
Thanks.
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
Last week was a busy week for me getting ready for the release of a new customer website that went live today with glorious success
During the project I was using the new TableAdapters and typed DataSets at times to help with a bit of rapid application development! While using the TableAdapters and typed DataSets, I came across a ConstraintException Error I have seen reported in the MSDN Forums that I thought had to do with a misconfiguration of a SQL Server Table. Turns out that is not the case.
ConstraintException Was Unhandled - Column is Constrained to be Unique - Value is Present
The error happens when inserting records into a database table and might say something like:
“ConstraintException was unhandled. Column 'CustomerId' is constrained to be unique. Value '1' is already present.”
One might think SQL Server is returning this error, but it is actually the DataTable that throws the exception.
Customers Example
Let's take the example of a brand new table in SQL Server, called Customers. For simplicity, let's say we have 3 columns in the table, one being the PK CustomerId, which is an identity with an initial seed of 1 and an increment of 1.
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
I cannot get past this error. I get this error when trying to update the row on the dataset of the parent table. Isn't the PK (in this case 'ID') suppose to get updated with the stored procedure? Does anyone know what I am missing here? Thanks.
-Hector Cervantes
GIS Dude
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
I figured it out. I did not have the identity field set up correctly. I am using the SCOPE_IDENTITY() function instead of @@IDENTITY. Here is a copy of one of my procedures:
ALTER PROCEDURE [dbo].[proc_insertCall]
(@ID int output,
@SessionID varchar(36),
@DialedNumber varchar(10),
@ANI varchar(10),
@CPN varchar(10),
@CalledNumber varchar(10),
@DID varchar(10),
@TFN varchar(10),
@TotalTime varchar(10),
@IVRTime int,
@XferTime int,
@TTA int,
@CompCode char(1),
@EndState varchar(4),
@CallStart datetime,
@CallStop datetime,
@Lat float,
@Lon float,
@DialedNoANICount int,
@OrigTZ int,
@TermTZ int,
@NetworkID int,
@LocationID int,
@Zipcode varchar(10)
)
AS
INSERT INTO CallRecord (SessionID, DialedNumber, ANI, CPN, CalledNumber, DID, TFN, TotalTime, IVRTime, XferTime, TTA,
CompCode,
EndState,
CallStart,
CallStop,
Lat, Lon,
DialedNoANICount,
OrigTZ, TermTZ,
NetworkID, LocationID, Zipcode)
VALUES
(@SessionID, @DialedNumber, @ANI, @CPN, @CalledNumber, @DID, @TFN, @TotalTime, @IVRTime, @XferTime, @TTA,
@CompCode,
@EndState,
@CallStart,
@CallStop,
@Lat, @Lon,
@DialedNoANICount,
@OrigTZ, @TermTZ,
@NetworkID, @LocationID, @Zipcode)
SELECT SCOPE_IDENTITY()
-Hector Cervantes
GIS Dude
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
and another code
you will change ID not ParameterDirection , you will look like another column
but be sure it not null when you define.
ALTER PROCEDURE [dbo].[proc_InsertMobile]
( @ID nvarchar(50),@BrandName nvarchar(50)
)
AS
Begin
Insert Into mobilespec(
ID,BrandName
)
Values (@ID,
@BrandName
)
RETURN @@ROWCOUNT
End
Goodluck 
mai
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
|
|
|
This is a great code. but seems like you can use it only with stored procedure. I'm not using stored procedure but i'm directly using insertcommand with "Insert INTO" statement and adding the parameters. Now my problem is the ParentID in the child table are not getting updated and it shows 0 value. This is what i'm doing in code;
1. i'm creating datasets (I'm using Myadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey)
2. then i'm setting the relationship as explained in the example.
3. then i'm adding new parent row.
4. add new child row and set the parent row
5. for updates as i mentioned i'm not using stored procedure but i'm using insertcommand with "Insert into" statement along with parameters. Now with child dataset, i'm setting parameter.SourceVersion = DataRowVersion.Current
for the ParentID colum.
when i trigger update method, the parentid filed in child table is updated with 0 value.
can you tell me what am i doing wrong?
any lead in this matter would be higly appreciated.
thanks.
|
| Sign In·View Thread·PermaLink | 1.00/5 |
|
|
|
|
|
|
|
|
|
|
|
|
Hello ,
I have a question related to physical creation of a new dataTable in a given dataBase(SQL Server).
The Table to be created in the database is allready created in a dataSet,but it doesn't exist in dabase.
As input I have :
database name +
the conection string to the server +
dataSet containing the table to be inserted in the database
Could you provide few lines in order to make this happen ?I don't want to manualy create the table, which allready exists in memory dataset.
cosmin
|
| Sign In·View Thread·PermaLink | 1.25/5 |
|
|
|
|
|
|
While trying this guide, I found another prblem. It happens when the number generated from the database duplicates with the key already exists in the datatable. I'm thinking about giving a increment seed in the datatable, but it seems too dangerous and complicated. Do you have any idea??
There's a simple solution, it is you must immeditately update the database when you add a new row into the database.
-- modified at 13:59 Monday 29th May, 2006
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Your solution works cos autonumber in database starts from 1, and in dataset(datatable) it starts from 0 so there is no collision.
Maybe the best way is to set enforceconstrains to false before updating occurs. In this way you don't have to communicate with your database on each created row.
And always set AutoIncrement to true and AutoIncrementStep = 1
In older versions of VS there was chance of error if you fill your datatable and autonumber in database doesn't starts with 1 (let say 15) so you could create first 15 rows and on 16th you would get error while creating row. But this is solved in VS2005.
|
| Sign In·View Thread·PermaLink | 3.50/5 |
|
|
|
|
|
|
here is the problem
if you have database with 2 tables (ParentChild) you can fill dataset via dataadapter's select command to filter rows in relation order you gave.
dataadapter will create one big table with columns from both tables in that order.
But when you have dataset with 2 tables (in relation) how can you select(filter) rows from it.
There is no: dataset.select("filter_expression_for_both_tables")
there is only: dataset.tables("Parent").select("filter_expression")
I know there is datarow.getparent (or getchild) rows but is there any other way?
|
| Sign In·View Thread·PermaLink | 2.57/5 |
|
|
|
|
|
|
hi,
i am trying to insert recors that user enters at registration page of my application. i have taken help from this article but it is giving me errors like
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Line 108: customerDataAdapter.Update(ds,"Customer");
Line 109: loginDetailsDataAdapter.Update(ds,"LoginDetails");
Line 110: conn.Close();
Can any one help me out, what is wrong with this cod
My Code is
private void addRecords()
{
//Creating the Dataset object
DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\CPMS.mdb");
conn.Open();
//Creating DataTable 'Customer' in the DataSet and the CustomerDataAdapter
OleDbDataAdapter customerDataAdapter = new
OleDbDataAdapter(new OleDbCommand("SELECT * FROM Customer",conn));
customerDataAdapter.RowUpdated += new
OleDbRowUpdatedEventHandler(customerDataAdapter_OnRowUpdate);
OleDbCommandBuilder customerCmdBuilder = new OleDbCommandBuilder(customerDataAdapter);
customerDataAdapter.FillSchema(ds,SchemaType.Source);
DataTable cTable = ds.Tables["Table"];
cTable.TableName = "Customer";
//Creating DataTable 'LoginDetails' in the DataSet and the LoginDetailsDataAdapter
OleDbDataAdapter loginDetailsDataAdapter = new
OleDbDataAdapter(new OleDbCommand("SELECT * FROM LoginDetails",conn));
OleDbCommandBuilder loginDetailsCmdBuilder = new OleDbCommandBuilder(loginDetailsDataAdapter);
loginDetailsDataAdapter.FillSchema(ds,SchemaType.Source);
cTable = ds.Tables["Table"];
cTable.TableName = "LoginDetails";
// Creating relatioship b/w tables
ds.Relations.Add(new DataRelation("ParentChild",
ds.Tables["Customer"].Columns["CustomerID"],
ds.Tables["LoginDetails"].Columns["CustomerID"]));
//Inserting Data
DataRow customerRow = ds.Tables["Customer"].NewRow();
customerRow["EmailAddress"] = "TextEmailAdd.Text";
customerRow["FirstName"] = "TextFName.Text";
customerRow["LastName"] = "TextLName.Text";
customerRow["Address"] = "TextAddress.Text";
customerRow["PostalCode"] = "TextPCode.Text";
ds.Tables["Customer"].Rows.Add(customerRow);
DataRow loginDetailsRow = ds.Tables["LoginDetails"].NewRow();
loginDetailsRow["Login"] = "TextLogin.Text";
loginDetailsRow["Password"] = "TextPass.Value";
loginDetailsRow["Type"] = "C";
loginDetailsRow.SetParentRow(customerRow);
ds.Tables["LoginDetails"].Rows.Add(loginDetailsRow);
// updating database with new values using DataAdapter
customerDataAdapter.Update(ds,"Customer");
loginDetailsDataAdapter.Update(ds,"LoginDetails");
conn.Close();
}
static void customerDataAdapter_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
OleDbCommand cCmd = new OleDbCommand ("SELECT@@IDENTITY",e.Command.Connection);
e.Row["CustomerID"] = cCmd.ExecuteScalar();
e.Row.AcceptChanges();
}
salman rafiq
|
| Sign In·View Thread·PermaLink | |
|
|
|
|
|
|
Hi
I am facing similar issue with my code. I know where the problem lies; but do not know the solution.
In dataset with have datarows numbered 0 onwards. When say 1st record is inserted in the table - say with ID = 1, then RowUpdated event is fired. we try to assign 1 to the primary key ; but there already is a datarow with that ID. Hence the problem.
If you have already got the solution to the problem, please share it with me.
Thanks
~jyo
|
| Sign In·View Thread·PermaLink | 2.00/5 |
|
|
|
|
|
|
