Add DataRow to MS Access database

Very green to this so bear with me, this is my code and when executing the Public Sub executes with no errors util it hits the last line of code "objDA_bdg_Accnt.Update(objDS_bdg_Accnt)".

I get the following exception "Update unable to find TableMapping['Table'] or DataTable 'Table'."
-----------------------------------------------------------------------------------

Public Sub Add_bdg_Accnt()

Dim objRow As DataRow

If txtActNum.Text = "" Then Exit Sub

objRow = objDS_bdg_Accnt.Tables("bdg_Accnt").NewRow()

objRow.Item("Act_Name") = txtActName.Text
objRow.Item("Act_Num") = txtActNum.Text
objRow.Item("Act_Owner") = txtActOwner.Text

objDS_bdg_Accnt.Tables("bdg_Accnt").Rows.Add(objRow)

objDA_bdg_Accnt.Update(objDS_bdg_Accnt)

End Sub

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

As an extra note I'm writing this code more or less out of a book and according to the book this should be it.

And if somebody feels up to it, could you explain what exactly happens as the code is executed. From what I gather the DataSet is populated and then can be updated and works as a temporary table. And not until the last line is run the actual DB is suppose to be updated.

I have successfully managed to pull a record from the DB and update it, but I just can't seem to add a new row. Also one last thing the table has 6 columns:

Act_Id = AutoNumber (Primary Key)
Bnk_Id = Number (Not Required)
Act_Name = Text (Required)
Act_Num = Text (Required)
Act_Owner = Text (Required)
Act_Value = Number (Not Required)

Thanx for listening

Claudio

Hi!

Try filling the DataAdapter with a schema before you fill it.

objDA_bdg_Accnt.Fillschema(ds, SchemaType.Source)

Done that already under my Form Load event.

Claudio

strange, I can se nothing wrong with what you've done....

What if you add the table name in the update row....

objDA_bdg_Accnt.Update(objDS_bdg_Accnt, "bdg_Accnt")

Well I have tried this as well but I do get different error:

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

Thats all it says.

Claudio

hehehehhe såg inte att du är från Sverige... Är fårn GBG själv fast bor/arbetar i Honolulu.

aha, coolt. =)

Låter ju helt ok och att jobba där, mycket brudar med basttjolar och kokosnötter som bh på kontoret eller? :)

Better keep talking english about you inquery though, so that others can join in and help if we are not able to figure something out, but mostly out of curtesy against all fellow developers. :)

What kind of database are you using, Oracle, Access, SQL?

Ok I solved the problem, but got another question. For it to insert a new row into the table it was required for me to specify all columns in my select statement in the DataAdapter.

Also all columns had to be populated with a value, even the AutoNumber column Act_Id. Like this:

objRow.Item("Act_Id") = TxtActId.Text
objRow.Item("Bnk_Id") = txtBankId.Text
objRow.Item("Act_Name") = txtActName.Text
objRow.Item("Act_Num") = txtActNum.Text
objRow.Item("Act_Owner") = txtActOwner.Text
objRow.Item("Act_Value") = txtActVal.Text

but I ofcourse what the Act_Id to be handled by a AutoNumber internaly by MS Access.

Any ideas?

Claudio

One idea might be trying to try use a typed dataset, where you can edit constraints and relations in the assembly and make sure the tables has the exact mapping as of them in the Access database. If you don't want to do that I think its not that hard to add those constraint through code, I'll see if I can dig something up.

Ok solved the AutoNumber issue as well, doesn't matter what information you pass to this column in the DataSet:

Dim shtActId As Short = 0

objRow.Item("Act_Id") = shtActId

it will still use the next AutoNumber in the DB. Like here I pass 0 as the value for this column but in the DB it inserted a new row with Act_Id 19.

Would still like a good ol explanation of the diffrent components expersially the DataAdapter, DataTable and DataSet. What exactly is going on when you populate them, and why this behaviour.

Thank you all for listening and to Athley for helping.

Claudio

Athley I thought I was doing a "typed dataset", please if you got a different way to have more control of whats in the table that would be nice to know.

Becuase personally I'm a Oracle DBA so I know how to search for data in a table but I find it hard using my SQL skills through VB.

Thnx again

Claudio

Check this out...

Thnx I will check it out when I get home...

Claudio