Where is the Fill() method?

I am trying to add new rows to MyDataBase.mdb with the below code. My problem is that it does not identify the columns. It fails with the error message ¡°Name1 does not belong to Customers¡±. But it does so do Name2. I think it¡¯s locating the database and table as I can retrieve data with the open code using SQL Select.

Can anyone help Please

hemo

Dim connection As New OleDbConnection()

        Dim myadapter As OleDbDataAdapter

        Dim myDataSet As New DataSet

        Dim newrow As DataRow

        Dim MyTable As DataTable

                connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects2005\MyDataBase.mdb"

        myadapter = New OleDbDataAdapter("Customers", connection)

        MyTable = New DataTable("Customers")

        connection.Open()

        newrow = MyTable.NewRow()

        newrow("Name1") = TextBox1

        newrow("Name3") = TextBox2

        MyTable.Rows.Add(newrow)

        myadapter.Update(myTable)

        connection.Close()

BrunoYu

Thank you for your comment, sorry i have mislead you, my original code has since been modified, as below; While I think it¡¯s locating the database (although I¡¯m not sure) as it is not populating the database, it fails on the final row which i understand requires sqlCommandBuilder, but I¡¯m not sure.

Any help would be appreciated.

Hemo

Dim myAdapter As OleDbDataAdapter

Dim myDataset As New DataSet

Dim Mytable As New DataTable

Dim newrow As DataRow

Dim connection As New OleDbConnection()

connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects2005\MyDataBase.mdb"

myAdapter = New OleDbDataAdapter("Select * FROM Customers", connection)

connection.Open()

newrow = myDataset.Tables("Customers").NewRow()

myAdapter.Fill(Mytable)

newrow("Name1") = "Derick"

newrow("Name2") = "James"

' Mytable.Rows.Add(newrow)

myDataset.Tables("Customers").Rows.Add(newrow)

myAdapter.Update(myDataset, "Customers")

connection.Close()

I am still having problems with the above NewRow, DataTable method to population an Access Database, as my original question, which I would like to crack. Anyone who could help to solve this problem would be very much appreciated.

However, the below code, which may be of interest, does work, although somewhat convoluted.

hemo

Public Sub InsertRow()

¡® Assumes valid connection

connection.Open()

Try

Dim cmd As New OleDbCommand("Insert Into Test " & _

"(Name1,Name2,Name3)Values( , , )", connection)

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

connection.Close()

End Sub

Dim cmd As New OleDbCommand("Insert Into Test " & _ "(Name1,Name2,Name3)Values( , , )", connection) cmd.ExecuteNonQuery()

Thank you for you comments, i have tried your code but i get an error message ' Object reference not set to an instance of an object.' on code row; newrow = myDataset.Tables("Customers").NewRow() i have also experience this same error message on other code I have tried. Can you offer any help.

In answer to Jared Parsons MSFT query regarding the 2 table names, I decided to try another table within MyDatabase to eliminate any possible conflits.

hemo

I would follow article http://msdn2.microsoft.com/en-us/library/5ycd1034(VS.80).aspx

dim myDataset as new dataset should solve that error.

also wondering about the tablename myDataset.Tables(0) should also get you to the right table. 

Thank you for the MSDN link which is and will be a useful source, however i am still getting the same error using their example below, i.e. Object reference not set to an instance of an object. On the [newCustomersRow = DataSet1.Tables("Test").NewRow()]

A further error occurs if the DataRow is changed to ¡®New¡¯

hemo

Public rchConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects2005\MyDataBase.mdb"

Public connection As New OleDbConnection(rchConnect)

connection.Open()

Dim newCustomersRow As DataRow

newCustomersRow = DataSet1.Tables("Test").NewRow()

newCustomersRow("Name1") = "ALF"

newCustomersRow("Name2") = "Alfreds Futterkiste"

DataSet1.Tables("Test").Rows.Add(newCustomersRow)

connection.Close()

I have seen the error a couple times.

1. When you do not use "new" when creating a dataset.

dim ds as new dataset

2. When I did not make sure that the name of the table is really what I thought it was.

ds.tables(0).tableName = "test"

Thanks for the suggestion. I accept it¡¯s easy to mix up the databases, but with the problem I am experiencing with this procedure I have confirmed the connection is o.k. Unfortunately your other line of code returns an error ¡®Can¡¯t find table(0).

The ongoing problem with the [newCustomersRow = DataSet1.Tables("Test").NewRow()] Returning the error ¡®not set for the object¡¯, and suggesting using the ¡®New¡¯ key word, however if New is inserted into Dim dsNewRow As DataRow an error is shown ¡®not accessable as it¡¯s protected¡¯

Any help would be appreciated.

Desperate

hemo

Logically you have to do the following.

1. Create the dataset dim dataset as new dataset.

2. Fill the dataset with the connection.

3. Once you have the dataset filled you need to figure out if there was a table created. If ds.tables(0) does not exist you have to create the table. In most cases if you fill the dataset it will start at table(0) then add other tables. If table(0) is missing then you need to find either the table("name") or you need to find the table index from the ds.fill method or create a new table.

4. Name the table. ds.tables(0).tablename = "customer" it could be table(1) or something else either way you have to know the datasets table name or table index.

5. Once you know the table name then you can add rows to the table.

Thank you for your detailed analysis, although I believe all the procedures have been adhered to.The previous error ¡®Not set for the object¡¯ referring to: dsNewRow = ds.Tables("Customers").NewRow() still persist. Clearly, I need to research the subject further.

Thank you for your help.

hemo