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 connectionAsNew OleDbConnection()
Dim myadapterAs OleDbDataAdapter
Dim myDataSetAsNew DataSet
Dim newrowAs DataRow
Dim MyTableAs 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()
[4561 byte] By [
hemo] at [2007-12-28]
hemo,
The Fill() method of OLEDBDataAdapter can load data from the data ource into he DataSet. However, I can't find where you use the fill method. Otherwise, please try the InsertCommand of the OleDbDataAdapter.
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
I'd try:Dim myAdapter As OleDbDataAdapter
Dim myDataset As New DataSet
Dim newrow As DataRow
Dim connection As New OleDbConnection()
Dim myInsert as new OleDbCommand("INSERT INTO Customers...")
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects2005\MyDataBase.mdb"
myAdapter = New OleDbDataAdapter("Select * FROM Customers", connection)
myAdapter.Fill(myDataset)
newrow = myDataset.Tables("Customers").NewRow()
newrow("Name1") = "Derick"
newrow("Name2") = "James"
myDataset.Tables("Customers").Rows.Add(newrow)
myDataset.Tables("Customers").AcceptChanges()myAdapter.UpdateCommand = myInsert
myAdapter.Update()
Something along those lines should work. I didn't test this, but it should point you towards the right direction!Good luck!
Ethan
Dim cmd As New OleDbCommand("Insert Into Test " & _ "(Name1,Name2,Name3)Values(?,?,?)", connection) cmd.ExecuteNonQuery()
|
|
In your first example it looked like you were trying to insert into the "Customer" table. In this example you appear to be going for the "test" table. Could that be your issue?
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
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
