Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problems with adding new rows 1

Status
Not open for further replies.
Fred48

Fred48

Programmer
Feb 23, 2004
62
US
Hi,

I need to code a function add rows to a dataset from a text file. I have not used the DataRow before and I am running into problems. I get the message "use the keyword new to create an object instance" when the statement, dsNewRow = dset.Tables("Table1").NewRow() is executed. When I add the new to Dim dsNewRow as New DataRow I get the message ,..is not accessible in sthe contextbecause it is protected".

I am at a lost as how to correct the code. Also, I do not know if the remaing code will work.



Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestMyCode.mdb;User Id=admin;Password=;")

Dim da As OleDb.OleDbDataAdapter
Dim dset As New DataSet
Dim dsNewRow As DataRow
Dim Sql As String
con.Open()


Sql = "SELECT * FROM Table1"
da = New OleDb.OleDbDataAdapter(Sql, con)
dset.DataSetName = "TestDataAccessMethods.mdb"
dsNewRow = dset.Tables("Table1").NewRow()


For i As Integer = 0 To 22000
dsNewRow.Item("fld1") = "hello everyone"
dsNewRow.Item("fld2") = "test it"
dset.Tables("Table1").Rows.Add(dsNewRow)
da.Update(dset, "Table1")
Next

con.Close()

con.Dispose()
 
Sort by date Sort by votes
Try this:
Code: 
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestMyCode.mdb;User Id=admin;Password=;")

        Dim da As OleDb.OleDbDataAdapter
        Dim dset As New DataSet
        Dim Sql As String
        con.Open()

        Sql = "SELECT * FROM Table1"
        da = New OleDb.OleDbDataAdapter(Sql, con)
        dset.DataSetName = "TestDataAccessMethods.mdb"
        Dim tbl As DataTable = dset.Tables("Table1")

        For i As Integer = 0 To 22000
            Dim dsNewRow As New Object
            dsNewRow.Item("fld1") = "hello everyone"
            dsNewRow.Item("fld2") = "test it"
            tbl.Rows.Add(dsNewRow)
        Next

        da.Update(dset, "Table1")

        con.Close()
        con.Dispose()

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
bother I forgot to fix something else about it because of the way I did it different.

Code: 
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestMyCode.mdb;User Id=admin;Password=;")

        Dim da As OleDb.OleDbDataAdapter
        Dim dset As New DataSet
        Dim Sql As String
        con.Open()

        Sql = "SELECT * FROM Table1"
        da = New OleDb.OleDbDataAdapter(Sql, con)
        dset.DataSetName = "TestDataAccessMethods.mdb"
        Dim tbl As DataTable = dset.Tables("Table1")

        For i As Integer = 0 To 22000
            [red]Dim dsNewRow(1) As Object
            dsNewRow(0) = "hello everyone"
            dsNewRow(1) = "test it"[/red]
            tbl.Rows.Add(dsNewRow)
        Next

        da.Update(dset, "Table1")

        con.Close()
        con.Dispose()

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Hi,

I tried both methods you suggested and got an exception both times.

The first suggestion when I typed in the code "dsNewRow." there was no "Item" from the list for dsNewRow object.


The second suggestion I got the exception,"No default member found for type 'Object'.
 
Upvote 0 Downvote
The second error you got on which line?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Hi,

Also, on the same line:

dsNewRow(0) = "hello everyone"
 
Upvote 0 Downvote
Please post all of your code exactly as it is now. dsNewRow(0) = "hello everyone" as it is writen if dsNewRow is an object I just don't know of any way that row itself could cause that type of error.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Hi,

sorry, so here is the code:

Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestDataAccessMethods.mdb;User Id=admin;Password=;")

Dim da As OleDb.OleDbDataAdapter
Dim dset As New DataSet
Dim Sql As String


con.Open()

Sql = "SELECT * FROM Table1"
da = New OleDb.OleDbDataAdapter(Sql, con)
dset.DataSetName = "TestDataAccessMethods.mdb"
Dim tbl As DataTable = dset.Tables("Table1")

For i As Integer = 0 To 22000
Dim dsNewRow As New Object
dsNewRow.Item("fld1") = "hello everyone"
dsNewRow.Item("fld2") = "test it"
tbl.Rows.Add(dsNewRow)
Next

da.Update(dset, "Table1")

con.Close()
con.Dispose()
 
Upvote 0 Downvote
Like I said that will not work the way I posted it so that was why I posed the second set of code. Object does not have .Item method. Originally I was going to give you code with "dsNewRow As DataRow", but I decided to do it with "dsNewRow(1) As Object" instead as it is just a little easier not to get messed up for something so simple IMO, but I didn't change everything correctly before I posted it. That is why the second code. If you use DataRow then you would have to do something more like this.


Something like this should work for using the DataRow instead.
Code: 
        For i As Integer = 0 To 22000
            [red]Dim dsNewRow As DataRow
            dsNewRow = tbl.NewRow[/red]
            dsNewRow.Item("fld1") = "hello everyone"
            dsNewRow.Item("fld2") = "test it"
            tbl.Rows.Add(dsNewRow)
        Next
A DataRow has to know to start with what fields are available.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
There are just so many ways to do the exact same thing I mess myself up sometimes.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Hi,

I am sorry about the mix up on the code, trying to do things to quick.

Here is the procedure of code with your last suggestion:


Private Sub btnAddRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddRow.Click

Dim StartTime As DateTime = DateTime.Now
Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestDataAccessMethods.mdb;User Id=admin;Password=;")

Dim da As OleDb.OleDbDataAdapter
Dim dset As New DataSet
Dim Sql As String
con.Open()

Sql = "SELECT * FROM Table1"
da = New OleDb.OleDbDataAdapter(Sql, con)
dset.DataSetName = "TestDataAccessMethods.mdb"
Dim tbl As DataTable = dset.Tables("Table1")

For i As Integer = 0 To 22000
Dim dsNewRow As DataRow
dsNewRow = tbl.NewRow
dsNewRow.Item("fld1") = "hello everyone"
dsNewRow.Item("fld2") = "test it"
tbl.Rows.Add(dsNewRow)
Next

da.Update(dset, "Table1")

con.Close()
con.Dispose()

Dim EndTime As DateTime = DateTime.Now
MessageBox.Show("Start: " & StartTime.ToLongTimeString & ControlChars.CrLf & "End: " & EndTime.ToLongTimeString)

End Sub

For line dsNewRow = tbl.NewRow there is the following message "Referenced object has a value of 'Nothing'." pointing to tbl.NewRow

 
Upvote 0 Downvote
Sorry, I got the impression you were leaving some things out on purpose. So rather than assume here is how your code should look.

Code: 
    Private Sub btnAddRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddRow.Click
        Dim StartTime As DateTime = DateTime.Now
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TestDataAccessMethods.mdb;")

        Dim da As OleDb.OleDbDataAdapter
        Dim dset As New DataSet
        Dim Sql As String

        dset.DataSetName = "TestDataAccessMethods" 'Avoid any characters other than "_" underscore in object names.
        Sql = "SELECT * FROM Table1" 'The name of a table in the TestDataAccessMethods.mdb

        con.Open()

        da = New OleDb.OleDbDataAdapter(Sql, con)
        da.Fill(dset, "Table1") 'Can be any name, but generally should be the same name as the table in TestDataAccessMethods.mdb
        Dim tbl As DataTable = dset.Tables("Table1")

        For i As Integer = 0 To 22000
            Dim dsNewRow As DataRow
            dsNewRow = tbl.NewRow
            dsNewRow.Item("fld1") = "hello everyone" 'Field fld1 must be a text or memo field
            dsNewRow.Item("fld2") = "test it" 'Field fld2 must be a text or memo field
            tbl.Rows.Add(dsNewRow)
        Next

        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        da.InsertCommand = cb.GetInsertCommand()

        da.Update(dset, "Table1")

        con.Close()
        con.Dispose()

        Dim EndTime As DateTime = DateTime.Now
        MessageBox.Show("Start:  " & StartTime.ToLongTimeString & ControlChars.CrLf & "End:  " & EndTime.ToLongTimeString)
    End Sub
You may get the error again. The error you are getting is a little off from what I would expect so lets just geting everything correct and go from there.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Hi,

Thanks for your help, the process works great. I am sorry for any inconivenace I may have given you.

Fred
 
Upvote 0 Downvote
np. I'm glad it all worked out.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
FILL RICHTEXTBOX WITH RECORDSET
Replies
4
Views
551
strongm
strongm
sal21
  • Locked
  • Question
send email with a free sever smpt api rest
Replies
3
Views
524
strongm
strongm
sal21
  • Locked
  • Question
Check if mobile number exists, with Api rest or other service. Possibile? 1
Replies
2
Views
566
sal21
sal21
2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
663
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
1K
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top