Problems with OleDbCommandBuilder

  • Thread starter Thread starter triffid
  • Start date Start date
T

triffid

Hi there,

I'm trying to update my database via an UPDATE from
OleDbCommandBuilder. This is the second time I've tried, and I'm still
not having any luck. I've almost certainly missed something obvious,
but having searched for quite a while, I don't know what it is(!)

The code is here (intentionally showing source rather than having
server run page):-
http://home.freeuk.com/misacham/temp/ASPX_COMMAND_BUILDER_UPDATE.aspx
and this is the Access DB I'm using:-
http://home.freeuk.com/misacham/temp/writable.mdb

(I considered pasting the whole longwinded thing here, but wasn't sure
that this would be a good idea.)

Here's what happens when I try to run it on my local Windows XP
machine ("Syntax error in UPDATE statement", apparently because UPDATE
statement is null):-
http://home.freeuk.com/misacham/temp/error.htm

It's clear that the UPDATE SQL isn't being created correctly. Would it
make more sense to attempt to write the UPDATE manually?

Any feedback would be appreciated, thanks.

- Triffid
 
Hi Triffid,

you can refer to the code below to update your database using the coommand
builder by generating the update command automatically.

protected void Button1_Click(object sender, EventArgs e)
{
string connstr = "provider = SQLOLEDB; data source =
in-ts-manish\\sqlexpress; integrated security = SSPI; database = pubs";
OleDbConnection myconn = new OleDbConnection(connstr);
// Create a dataset.
DataSet ds = new DataSet();
// Create dataadapters for each tables.
OleDbDataAdapter myadpt = new OleDbDataAdapter("select *
from dbo.authors", myconn);
myadpt.Fill(ds,"authors");

ds.Tables["authors"].Rows[1]["au_lname"] = "Singhal";
OleDbCommandBuilder scb = new OleDbCommandBuilder(myadpt);
myadpt.UpdateCommand = scb.GetUpdateCommand();
myadpt.Update(ds,"authors");

this.GridView1.DataSource = ds;
this.GridView1.DataBind();
}

Regards,
Manish
www.componentone.com
 
HiTriffid,

you can refer to the code below to update your database using the coommand
builder by generating the update command automatically.
Click to expand...

Thanks for the feedback; I adapted your code for my situation (using
my Access/Jet DB instead of SQL Server).

Although it overcame the problem I was having, unfortunately it then
ran into the exact same problem I had the last time I tried
OleDbCommandBuilder (now I remember...).

Specifically, it complained about syntax errors in the generated SQL.
Doing a Google on this problem, it seemed that it had something to do
with field names containing spaces. So I renamed all the fields in the
database, updated the code, and.... it seems to work.

Either OleDbCommandBuilder isn't particularly smart and requires
convoluted workarounds, or I've missed something, but I'm going to
look into this sometime.

Anyway, thanks for your help- at least I'm halfway there next time :)

- Triffid
 
Back
Top