Insert record into Paradox table

  • Thread starter Thread starter Robbie
  • Start date Start date
R

Robbie

OK, I'm trying to do something very simple...add a record to a
Paradox table, but for the life of me I can't seem to get it right.

Here is a code snippet:

----------------------------
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\MyDirectory;Extended Properties=Paradox 5.x;";
OleDbConnection connParadox = new OleDbConnection(connStr);

OleDbDataAdapter daParadox = new OleDbDataAdapter();
OleDbCommand selCmd = new OleDbCommand("SELECT * FROM
MASTER_TABLE",connParadox);
daParadox.SelectCommand = selCmd;

string insCommand = "insert into MASTER_TABLE (ID,CODE) VALUES
(9999,'99999')"; // This statement works using SQL Explorer in
Delphi.
OleDbCommand insCmd = new OleDbCommand(insCommand, connParadox);
daParadox.InsertCommand = insCmd;

//Fill the dataset.
System.Data.DataSet dsParadox = new System.Data.DataSet();
daParadox.Fill(dsParadox); // This works...I get records in the
DataSet.

....
// Update the table. // This doesn't work...compiles OK,
doesn't even raise an error, but no table update.
daParadox.Update(dsParadox);
dsParadox.AcceptChanges();

----------------------------

I've also used the SQL statment:
insert into MASTER_TABLE IN C:\\MyDirectory\\MASTER_TABLE.db paradox
(ID,CODE) VALUES (9999,'99999')
but I get the same thing...nothing.

Could someone tell me what the heck I'm doing wrong, or point me to
some code
sample I could examine.
 
Robbie,

Do you have any new rows added into the DataSet? If there is no tow state
change in dataset, daParadox.Update(dsParadox) does nothing. Instead of
hardcode the new values in Insert statement, you can use the parameterized
query and add a new row with expected data into dataset. Below is the sample
code to do this.

string insCommand = "insert into MASTER_TABLE (ID,CODE) VALUES (?, ?)";
OleDbCommand insCmd = new OleDbCommand(insCommand, connParadox);
insCmd.Parameters.Add("p1", OleDbType.Integer, 0, "ID");
insCmd.Parameters.Add("p2", OleDbType.VarChar, 40, "CODE");
daParadox.InsertCommand = insCmd;

//Fill the dataset
// ...

//Insert new rows
DataRow rowNew = dsParadox.Tables[0].NewRow();
rowNew["ID"] = 9999;
rowNew["CODE"] = "99999";
dsParadox.Tables[0].Add(rowNew);

//Update the dataset
daParadox.Update(dsParadox);

Thanks,
Kevin
 
Thanks for the response...

Well, It seems I got one step further, but now I get this
ugly message that reads:

"Operation must use an updatable query."

I've seen numerous posts saying that the table must have
update permissions and the connection string must reflect
this by having something like "User Id=Admin;Password=;" in it.
But I get the same error. I am using a single Paradox
table locally. I've used the Data Adapter Configuration Wizard
in the .NET IDE to let it construct a connection string for
me, however when I go to the "New Connection" dialog, select
"Microsoft Jet 4.0 OLE DB Provider" and enter all related
information, I get an error reading:

"Test Connection failed because of an error initializing
provider. Unrecognized database format."

FYI, my modified connection string is:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\MyDirectory;Extended Properties=Paradox 5.x;UserID=Admin;Password=";

Any ideas?
 
This error is from Jet DB. If you search MSDN.Microsoft.com you will find a
bunch posts about this error. Not sure which one is for your case, but they
may help you fix the issue.

Thanks,
Kevin

Robbie said:
Thanks for the response...

Well, It seems I got one step further, but now I get this
ugly message that reads:

"Operation must use an updatable query."

I've seen numerous posts saying that the table must have
update permissions and the connection string must reflect
this by having something like "User Id=Admin;Password=;" in it.
But I get the same error. I am using a single Paradox
table locally. I've used the Data Adapter Configuration Wizard
in the .NET IDE to let it construct a connection string for
me, however when I go to the "New Connection" dialog, select
"Microsoft Jet 4.0 OLE DB Provider" and enter all related
information, I get an error reading:

"Test Connection failed because of an error initializing
provider. Unrecognized database format."

FYI, my modified connection string is:

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\MyDirectory;Extended Properties=Paradox
5.x;UserID=Admin;Password=";

Any ideas?





MS said:
Robbie,

Do you have any new rows added into the DataSet? If there is no tow state
change in dataset, daParadox.Update(dsParadox) does nothing. Instead of
hardcode the new values in Insert statement, you can use the
parameterized
query and add a new row with expected data into dataset. Below is the
sample
code to do this.

string insCommand = "insert into MASTER_TABLE (ID,CODE) VALUES (?, ?)";
OleDbCommand insCmd = new OleDbCommand(insCommand, connParadox);
insCmd.Parameters.Add("p1", OleDbType.Integer, 0, "ID");
insCmd.Parameters.Add("p2", OleDbType.VarChar, 40, "CODE");
daParadox.InsertCommand = insCmd;

//Fill the dataset
// ...

//Insert new rows
DataRow rowNew = dsParadox.Tables[0].NewRow();
rowNew["ID"] = 9999;
rowNew["CODE"] = "99999";
dsParadox.Tables[0].Add(rowNew);

//Update the dataset
daParadox.Update(dsParadox);

Thanks,
Kevin
Click to expand...
Click to expand...
 
On 9 Sep 2004 07:09:52 -0700, (e-mail address removed) (Robbie) wrote:

¤ Thanks for the response...
¤
¤ Well, It seems I got one step further, but now I get this
¤ ugly message that reads:
¤
¤ "Operation must use an updatable query."
¤

This error is likely caused by the Paradox table not having a primary key index.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top