manually fill dataset but the changes aren't inserted

[Zarcom]

I have me a dataset which i fill with values but when I use the dataAdapters.Update command my new records aren't being inserted? I am not sure what I am doing wrong. I stepped through with the debugger and the dataset has the new row in it and the new rows status is "Added"
I am at a loss

Dim mydatarow As DataRow = mydataset.myTable.newrow

mydatarow("column&quot = value
mydatarow("column&quot = value
mydatarow("column&quot = value
mydatarow("column&quot = value

'add the new row to the dataset
mydataset.myTable.Rows.Add(mydatarow)

Try
'open the database
OleDbConnection1.Open()
'add the new records to the database
OleDbDataAdaptermyAdapter.Update(mydataset)
Catch

'error stuff here
Finally
OleDbConnection1.Close()
End Try



I seem to be missing something. Thanks for the help That'l do donkey, that'l do
Mark

 

[Craig0201]

Records are inserted using the Insert command, not the Update......

Craig

 

[jfrost10]

Mark/Craig: the dataadapter's Update is not the same as the UpdateCommand.

The dataadapter contains an InsertCommand, DeleteCommand, and an UpdateCommand. These are sql command templates to do each of the operations. However, when the dataadapter.update is called, it performs the following steps:

1. Determines the changes to the data set by checking each Datarow objects's RowState property (which can be Added, Deleted, Modified, Unchanged, or Detached).

2. Invokes the InsertCommand, Delete Command, or UpdateCommand to make the required changes

3. Resets teh updated DataRow objects RowState prpoerties to Unchanged

So Mark, you are doing it correctly. However, apparantly the Insert/Update/Delete commands are generated automatically ONLY if you create the dataadapter in Design mode (i.e. drag it onto your form as opposed to just in code), so its possible that you have no InsertCommand to run off of. When you step through your code, check and make sure your InsertCommand is there and it looks proper, that would be my first step.

D'Arcy

NOTE: this info can be found in MCAD Self Paced Training Kit: Developing Web Applications with VB.NET and C#

 

[jfrost10]

Mark:

Just tested out the theory about the dataadapter created by the designer and the dataadapter created in code, and the book was correct:

When you drag/drop to create one, it automatically sets all the settings for you. However, if you create it manually in your code, there is NOTHING set
i.e the designer-created da's InsertCommand.Commandtext property had a full sql statement. The newly code-created da's had Nothing.

D

 

[Zarcom]

I am sure that I have the jist of how it works. I did use the designer to create it. And I double checked the insert is there, everything is in place.

However, when I step through it, nothing happens. I set a watch to one of the rows rowstate and it stays on "added "doesn't go anywhere. No errors but nadda happens.

I am completely at a loss here. Any suggestions at all will help. I may just end up ditching the dataset and adapter all together though I don't want to. That'l do donkey, that'l do
Mark

 

[jfrost10]

Ah ok, sorry. Heh, silly of me to assume that just because i never use the designer to create my datasets/adapters, that thats how everyone does it


Hmmm...very interesting. The really weird thing is that you get no errors at all.

i'm guessing that this is saving it to an (blech) Access db?
You have that file set so that the asp.net user has permission to access and write to it, right?

D

 

[Zarcom]

mm the connection includes the username/password in the security file.

Thing is I just made a small app that fills a grid (same database different table) and the save works fine there. It's gotta be something funky with that table.
My select statment didn't use all of the columns, that shouldn't matter should it? That'l do donkey, that'l do
Mark

 

[Zarcom]

Verry interesting!

I just tried to drag that table onto my test app so as to make a data adapter and I got this.


The wizard detected the following problems when configuring he data adapter for "TableName"

Details:
* Generated SELECT statment

The data adapter cannot be configured for tables that have more than 100 columns.

Where the star is was one of those ! in an upside down yield sign icons.

I wasn't using all the columns before though and everything worked fine. Any ideas... anyone? That'l do donkey, that'l do
Mark

 

[jfrost10]

K, thats really weird then (it working in one app and not the other).

your select statement shouldn't matter that it didn't include all the columns, but maybe your insert/update statements are the issue (like, if you try to insert a record with only columns a, b, c, but the design-creted da/ds creates an insert statement with columns a, b, c, d, etc., maybe thats screwing something up?)

The REALLY frustrating thing is that there's NO error message!!!

D

 

[link9]

sounds to me like it's time for a little strategic de-normalization.

try to break that table into at least two smaller tables, ideally one that is more dynamic and changing (number of orders, etc...), and the other part which is more static in nature (such as mailing addresses, etc...)

I remember reading that there were problems w/ over 100 columns, so I guess in order to use this "automated" method, you'll have to make it less than that.

Otherwise, a manual update/insert/delete routine is going to be in order.

 

[jfrost10]

WOAH!

I posted my last one the same time you were posting yours Mark

You have a table with over 100 columns?!

How many columns are we talking about here?!

Paul's right: time to break that up a bit man.

How much license do you have as far as changing the architecture of your program (i.e. making it a bit more OO, normalizing the database, etc.)?

D

 

[Zarcom]

Sorry guys thats a no go. I agree with you though. That whole database is nutz, it's not normalized in the least. Thing is I am trying to insert records from my own new beautiful program into this legacy one. It's for customers who have it already.

Changing the database is not an option as all the clients would then need a new version of the old program.

I am not sure how many columns this thing actually has, but I only need 9 of them.

I am posting as I try to rebuild the dataAdapter.
...
Question:
I haven't filled the dataset just added two (in this case) new rows to it. Do I need to fill it first then add the rows?
....
working
....

Answer: Yes because otherwise the sequence autonumber field doesn't match.

After a rebuild it is now calling the insert and working as it should.

I am still confused as to why it didn't work before but thanks for your help anyway.
tchau That'l do donkey, that'l do
Mark