Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
Solved
DataSet Fill from SQL Server then Update to OleDb (Access) in MC++
Posted on 2004-10-25
I am trying to retrieve some data from a disconnected SQl Server database via a DataSet and then Insert the contents of this dataSet into a local Access database.
The first part is working just fine. I'm using the following code:
String *ConnectionString = S"Data Source=notreally.here.com;
SqlConnection * conSql = new SqlConnection(ConnectionSt
SqlCommand *sqlSelect = new SqlCommand();
sqlSelect->CommandText = String::Concat(S"SELECT * FROM Posts WHERE ForumID=",
ForumID.ToString(), S" AND Sent>'", Since.ToString(), S"' ORDER BY Sent");
sqlSelect->Connection = conSql;
SqlDataAdapter *dataAdapter = new SqlDataAdapter();
dataAdapter->SelectCommand
DataSet *ds = new DataSet();
dataAdapter->Fill(ds);
The DataSet Fills up with the correct data. So next I create an OleDb DataAdapter to store in the local Access database like so:
OleDbConnection *oleConnection = new OleDbConnection(S"Provider
OleDbCommand *oleInsert = new OleDbCommand();
oleInsert->CommandText = S"INSERT INTO Posts VALUES (?,?,?,?)");
oleInsert->Connection = oleConnection;
oleInsert->Parameters->Add
oleInsert->Parameters->Add
oleInsert->Parameters->Add
oleInsert->Parameters->Add
OleDbDataAdapter *accessAdapter = new OleDbDataAdapter();
accessAdapter->InsertComma
accessAdapter->Update(ds);
When this executes, nothing happens. No error is generated but the Update method returns 0 and nothing is written to the database.
I have found that if I manually create a new DataSet and write in the appropriate columns and row values, that will Update just fine. So I think the problem lies within the DataSet itself and not OleDb code.
Can anyone suggest what is wrong, or am I going about this in the wrong way enitrely? Thanks.
The first part is working just fine. I'm using the following code:
String *ConnectionString = S"Data Source=notreally.here.com;
SqlConnection * conSql = new SqlConnection(ConnectionSt
SqlCommand *sqlSelect = new SqlCommand();
sqlSelect->CommandText = String::Concat(S"SELECT * FROM Posts WHERE ForumID=",
ForumID.ToString(), S" AND Sent>'", Since.ToString(), S"' ORDER BY Sent");
sqlSelect->Connection = conSql;
SqlDataAdapter *dataAdapter = new SqlDataAdapter();
dataAdapter->SelectCommand
DataSet *ds = new DataSet();
dataAdapter->Fill(ds);
The DataSet Fills up with the correct data. So next I create an OleDb DataAdapter to store in the local Access database like so:
OleDbConnection *oleConnection = new OleDbConnection(S"Provider
OleDbCommand *oleInsert = new OleDbCommand();
oleInsert->CommandText = S"INSERT INTO Posts VALUES (?,?,?,?)");
oleInsert->Connection = oleConnection;
oleInsert->Parameters->Add
oleInsert->Parameters->Add
oleInsert->Parameters->Add
oleInsert->Parameters->Add
OleDbDataAdapter *accessAdapter = new OleDbDataAdapter();
accessAdapter->InsertComma
accessAdapter->Update(ds);
When this executes, nothing happens. No error is generated but the Update method returns 0 and nothing is written to the database.
I have found that if I manually create a new DataSet and write in the appropriate columns and row values, that will Update just fine. So I think the problem lies within the DataSet itself and not OleDb code.
Can anyone suggest what is wrong, or am I going about this in the wrong way enitrely? Thanks.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
3 Comments
Hi grokuk,
A dataset has a number of versions of each data row. These are described by the System::Data::DataRowVersi
When you modify a row, the original version stays the same, but the proposed version shows the changes you have made. When you wish to update the database with the changes, the Update method will look to see if anything has changed in the dataset since it was last declared current. It determines if there are changes by looking at the proposed version of each row. When it sees the changes, it calls the appropriate command: UpdateCommand if the change requires an update statement; InsertCommand if the change requires a new row in the database; and the DeleteCommand if your changes deletes a row in the dataset and database.
This is half the story. The other half is that your Update, Delete, and Insert commands must use the parameters, and you must specify which datarow version the parameter is filled from.
I believe this is the problem with your example above. The parameters you have specified for the oleInsert command do not specify the row version to be filled from. Since your dataset does not appear to have any changes, nothing is going to be updated. If there were changes to reflect into your Access database, which version of the dataset's rows would the parameters fill from?
For your case, your oleInsert command might need to read like this example:
oleInsert->Parameters->Add
After you have completed the Update, if there have been any changes to the dataset, you need to "Accept" the changes so that a repeated call to Update does not try to send the changes again. This is done with the AcceptChanges method which can be called on the dataset object or the table object.
Hope that helps.
A dataset has a number of versions of each data row. These are described by the System::Data::DataRowVersi
When you modify a row, the original version stays the same, but the proposed version shows the changes you have made. When you wish to update the database with the changes, the Update method will look to see if anything has changed in the dataset since it was last declared current. It determines if there are changes by looking at the proposed version of each row. When it sees the changes, it calls the appropriate command: UpdateCommand if the change requires an update statement; InsertCommand if the change requires a new row in the database; and the DeleteCommand if your changes deletes a row in the dataset and database.
This is half the story. The other half is that your Update, Delete, and Insert commands must use the parameters, and you must specify which datarow version the parameter is filled from.
I believe this is the problem with your example above. The parameters you have specified for the oleInsert command do not specify the row version to be filled from. Since your dataset does not appear to have any changes, nothing is going to be updated. If there were changes to reflect into your Access database, which version of the dataset's rows would the parameters fill from?
For your case, your oleInsert command might need to read like this example:
oleInsert->Parameters->Add
After you have completed the Update, if there have been any changes to the dataset, you need to "Accept" the changes so that a repeated call to Update does not try to send the changes again. This is done with the AcceptChanges method which can be called on the dataset object or the table object.
Hope that helps.
Thanks javanic,
Your comments have helped me to see the problem. I can't actually add the DataRowVersion::Original parameter to the new OleDbParameter constructor as there isn't an overload that supports this combination.
However, I found that by setting:
dataAdapter->AcceptChanges
Just before the original Fill stops an AcceptChanges which is called automatically, thus leading to the version problem you've described. Adding this line fixes everything, and the OleDb part of the code now works without alteration.
I wouldn't have found this without your excellent comments. So, many thanks :)
Your comments have helped me to see the problem. I can't actually add the DataRowVersion::Original parameter to the new OleDbParameter constructor as there isn't an overload that supports this combination.
However, I found that by setting:
dataAdapter->AcceptChanges
Just before the original Fill stops an AcceptChanges which is called automatically, thus leading to the version problem you've described. Adding this line fixes everything, and the OleDb part of the code now works without alteration.
I wouldn't have found this without your excellent comments. So, many thanks :)
grokuk,
I should have checked the overloads before just typing it in. There is an overload that uses the datarow version enumeration; it looks like this:
[C++]
public: OleDbParameter(
String* parameterName,
OleDbType dbType,
int size,
ParameterDirection direction,
bool isNullable,
unsigned char precision,
unsigned char scale,
String* srcColumn,
DataRowVersion srcVersion,
Object* value
);
I believe the reason your AcceptChangesDuringFill is working is because in effect you have made your entire dataset to look like it is modified (as though the fill were actually inserting new rows into your dataset). If you only want to fill the dataset from the offline SQL database, and then ship the data to Access database, this should be ok. But if you want to use the dataset for anything (which is what makes the dataset so powerful), you may find that you can't keep track of changes properly.
Glad to have helped.
I should have checked the overloads before just typing it in. There is an overload that uses the datarow version enumeration; it looks like this:
[C++]
public: OleDbParameter(
String* parameterName,
OleDbType dbType,
int size,
ParameterDirection direction,
bool isNullable,
unsigned char precision,
unsigned char scale,
String* srcColumn,
DataRowVersion srcVersion,
Object* value
);
I believe the reason your AcceptChangesDuringFill is working is because in effect you have made your entire dataset to look like it is modified (as though the fill were actually inserting new rows into your dataset). If you only want to fill the dataset from the offline SQL database, and then ship the data to Access database, this should be ok. But if you want to use the dataset for anything (which is what makes the dataset so powerful), you may find that you can't keep track of changes properly.
Glad to have helped.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The following diagram presents a diamond class hierarchy:
As depicted, diamond inheritance denotes when two classes (e.g., CDerived1 and CDerived2), separately extending a common base class (e.g., CBase), are sub classed simultaneously by a fourt…
In Easy String Encryption Using CryptoAPI in C++ (http://www.experts-exchange.com/viewArticle.jsp?aid=1193) I described how to encrypt text and recommended that the encrypted text be stored as a series of hexadecimal digits -- because cyphertext may…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month14 days, 9 hours left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.