Problem to update a database using DataSet

[Lourenço Teodoro]

I developed a program that updates the database using a DataSet. I am
testing it against an Access database. If I get the data from the database
using the SQL statement without a WHERE clause, I am able to change data in
the DataSet and use the Update command in my DataAdapter. However, when I
specify any WHERE clause, I get the following error message when I call the
update command:

OleDbCommand.Prepare method requires all parameters to have an explicitly
set type.

Below is the code that I am using to update a specific row:

tb = ds.Tables[0];
rows = tb.Rows;
rows[0][0] = "Test";
adapter.Update(ds)

ds = DataSet
tb = DataTable
rows = DataRowCollection

I would appreciate any help on this matter.

Lourenço.

 

[William Ryan eMVP]

I'd need to see the sql statment and the parameters collection of the
command object (for the Update).

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Lourenço Teodoro]

William,

First of all thank you very much for your help. I could identify why the
problem was happening. When using a SELECT statement with a WHERE clause I
was using parameters in the Command class. When trying to update, the
DataAdapter tries to use the parameters in there, but I had only added
parameters for the fields in the WHERE clause of my SELECT statement.

I did not show my code because it has a lot of other stuff that would make
this message too big; however after identifying the problem, I wrote a
simple application that reproduces the problem. The code is listed below,
please let me know if you see any problem with what I did.

connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Documents and Settings\\Lourenco\\My Documents\\Test.mdb;Persist
Security Info=False");
command = new OleDbCommand("SELECT [Date], [StringA] FROM ADOExample WHERE
StringA=@Value0", (OleDbConnection)connection);
String StringA = "d";
OleDbParameter param = new OleDbParameter("@Value0", StringA);
command.Parameters.Add(param);
adapter = new OleDbDataAdapter(command);
builder = new OleDbCommandBuilder(adapter);
dataset = new DataSet();
adapter.Fill(dataset);
command.Parameters.Clear(); //This was the change that fixed the problem, I
just cleared the parameter list
DataRowCollection rows = dataset.Tables[0].Rows;
if(rows.Count > 0)
{
rows[0]["StringA"] = "test";
adapter.Update(dataset);
}

Regards,
Lourenço.

I'd need to see the sql statment and the parameters collection of the
command object (for the Update).

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

I developed a program that updates the database using a DataSet. I am
testing it against an Access database. If I get the data from the database
using the SQL statement without a WHERE clause, I am able to change data in
the DataSet and use the Update command in my DataAdapter. However, when I
specify any WHERE clause, I get the following error message when I call the
update command:

OleDbCommand.Prepare method requires all parameters to have an explicitly
set type.

Below is the code that I am using to update a specific row:

tb = ds.Tables[0];
rows = tb.Rows;
rows[0][0] = "Test";
adapter.Update(ds)

ds = DataSet
tb = DataTable
rows = DataRowCollection

I would appreciate any help on this matter.

Lourenço.