Inserting a DataSet into a Database table

ok figured that part out... and it is 0 as far as i can tell

int count = sda.Update(ds, "NamesTest");

int count = 0 when you mouse over it in debug mode

hee hee... scratch that... it equals 11

It's just a function returning a value like any other, so you simply assign it to a variable

Code:

---

int rowsAffected = myAdapter.Update(myTable);

---

or you can display it immediately

Code:

---

MessageBox.Show("Rows affected: " + myAdapter.Update(myTable).ToString());

---

Quote:

---

Originally Posted by jmcilhinney

Maybe you could try the same thing with two tables in the same database. If that works but it doesn't between databases then that must be the issue. I've never actually tried this myself but I can't see why there should be a problem.

---

I tried to transfer the same data with the same insert statement to an identical table named NamesTest2 in the same database and the data transfered flawlessly..... i'm confused now....

thanks

Well the number returned by Update is the number of rows affected, so if it returns 11 then that should mean that it's inserted 11 rows. If it is genuinely working within the same database but not between databases then I am confused. Like I said, never done it myself but I don't see why it shouldn't be possible.

The other option would be to create a DataTable and get the schema from the second database using FillSchema. You could then get the data from the first database with a DataReader and create the new rows in the DataTable yourself. Then you should be able to Update the new data because it should know that the table is from that same database. Seems like a workaround for a problem that shouldn't exist but necessity and invention.

well i appreciate the help... i'lll prolly asking more questions in the future... this project seems to keep getting bigger.

thanks a bunch

Code:

---

string Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\db1.mdb";
string Conn3 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\db3.mdb";
               
                private void button1_Click(object sender, System.EventArgs e)
                {
                        OleDbConnection xlConn = new OleDbConnection(Conn);
                        OleDbCommand selCMD = new OleDbCommand("Select * from NamesTest", xlConn);
                        OleDbDataAdapter sda = new OleDbDataAdapter();
                        sda.AcceptChangesDuringFill = false;
                        sda.SelectCommand = selCMD;
                        DataSet ds = new DataSet();
                        sda.Fill(ds, "NamesTest");
                        xlDataGrid.DataSource=ds.Tables["NamesTest"].DefaultView;
                        //xlDataGrid.DataBind();
                        //xlConn.Close();

                        OleDbConnection xlConn3 = new OleDbConnection(Conn3);
                        OleDbCommand insCmd = new OleDbCommand("INSERT INTO NamesTest (ID, FirstName, LastName) VALUES (?, ?, ?)", xlConn3);
                       
                        insCmd.Parameters.Add("@ID", OleDbType.Integer, 5, "ID");
                        insCmd.Parameters.Add("@FirstName", OleDbType.Char, 20, "FirstName");
                        insCmd.Parameters.Add("@LastName", OleDbType.Char, 20, "LastName");

                        sda.InsertCommand = insCmd;
                        xlConn.Open();
                        xlConn3.Open();
                        sda.Update(ds, "NamesTest");

---

This is wrong code! And hence will not do anything that you are trying to achieve. The correct and easier way of using the connection, datareader and dataset object in this particular scenario follows this order:
1. Open connection with database 1
2. Retrieve data into DataSet from this datasource
3. Close connection with database 1
and then
1. Open connection with database 2
2. Make a new copy of the earlier dataset. Make changes here. and call update with the second dataadapter.
3. Close connection with database 2
Look at your code above - I see both the connection.open calls at the end of the code just before the update call. How is the dataset going to fill up with data if the connection has not been initially opened?

Now, for the case where you would need to merge the data from the tables in these two datasets - you could use the merge() method with the dataset class. you could go ahead with that.

Quote:

---

Originally Posted by exterminator

Look at your code above - I see both the connection.open calls at the end of the code just before the update call. How is the dataset going to fill up with data if the connection has not been initially opened?

---

You'll note that I commented on the fact that these calls were unnecessary in a previous post. They make no difference to whether the data gets retrieved though, as the call to Fill is made several lines earlier, which implicitly opens and closes the associated connection. There is no call to Close the connection after the Update but this should not be an issue because the Update is not within a transaction so changes should be committed immediately.