• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

DataSet Addnew problem

hi experts

 i include on top of form call
 Imports System.Data.OleDB

now i using dataset for connecting my mdb

DatabaseName = test.mdb
Tablename = mytable
field1= name  and field2 = address

here is code

    Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb")
    Dim da As New OleDbDataAdapter("SELECT * from mytable", Cn)
    Dim Ds As New DataSet
    da.Fill(Ds, "mytable")
    Dim myDataTable As DataTable = Ds.Tables(0)
    Dim tempRow As DataRow
   

'now here i able to get both field record in this loop
        For Each tempRow In myDataTable.Rows
            MsgBox(tempRow(0) + "--" + tempRow(1))
        Next

'now my first question can it is possible to store all rowvalue on sigle column in
'one array without assinging in loop
'suppose i have to store all name in table in one array variable called

'Dim AllName() as string
'and i assign AllName = tempRow(0) like this so all value of that column assing in my variable
'how to make this ?????

'my second question related to this is i am new in Dataset i need example for if i have to addnewrow
'what i have to do and same for Delete and Update method
'i had tried but i not able to make it .. it takes the value but not update the table

this is the code for that

    Dim tempRow1 As DataRow
    tempRow1 = myDataTable.NewRow()

    tempRow(0) = "Peter"
    tempRow(1) = "22, Main street"
    myDataTable.Rows.Add(tempRow1)
    Ds.AcceptChanges()
    myDataTable.AcceptChanges()
    da.Update(Ds)

    Cn.Close()

i tried this for delete also but not success

    Dim myrow As DataRow = myDataTable .row(0)
    myrow.Delete()
    myDataTable.AcceptChanges()

and same for Update but no idea how to make it

    myrow.beginnedit()
    myrow ("hkh") = hjkljkl
    mytable.acceptchanges()

my final queary is if i have to update whole Dataset at one time not each time updating row by row
how i make it ??

i am ready to give full point to solve my this queary i know in this there is some homework but
i have sortage of time due to this i appriciate eary answer

thanks in advance  
0
vsvb
Asked:
vsvb
  • 5
  • 4
1 Solution
 
amyhxuCommented:
just remove all the "AcceptChanges()", and you should see the database being updated.
remove these:
    Ds.AcceptChanges()
    myDataTable.AcceptChanges()
0
 
vsvbAuthor Commented:
still not work ??? :(
can you give me small example i will try that

0
 
amyhxuCommented:
Here you go:

    'Fill dataset
    Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb")
    Dim da As New OleDbDataAdapter("SELECT * from mytable", Cn)
    Dim Ds As New DataSet
    da.Fill(Ds, "mytable")

    'Read and assign values to an array
    Dim tempRow As DataRow
    Dim AllName(Ds.Tables("mytable").Rows.Count - 1) As String
    Dim i As Integer = 0
    For Each tempRow In Ds.Tables("mytable").Rows
        AllName(i) =  tempRow(0) + "--" + tempRow(1)
        i += 1
    Next

    'Read from the array
    For n As Integer = 0 To UBound(AllName) - 1
        Console.WriteLine(AllName(n))
    Next

    'Add a new row
    Dim tempRow1 As DataRow
    tempRow1 = Ds.Tables("mytable").NewRow()
    tempRow1(0) = "Peter"  ' Or tempRow1("Name") = "Peter
    tempRow1(1) = "22, Main street"  ' Or tempRow1("Address") = "22, Main street"
    Ds.Tables("mytable").Rows.Add(tempRow1)
    da.Update(Ds)

    'Delete the first row
    Dim myrow As DataRow = Ds.Tables("mytable").Rows(0)
    myrow.Delete()
    da.Update(Ds)

    'Update the first row
    Dim myrow1 As DataRow = Ds.Tables("mytable").Rows(0)
    myrow1("Name") = "John"
    myrow1("Address") = "100, Center Street"
    da.Update(Ds)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
vsvbAuthor Commented:
hi amyhxu thanks
but its gives error on
da.update(ds) line
and says

like this
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.

what to do ??
0
 
amyhxuCommented:
change all da.Update(ds) to:
    da.update(Ds.Tables("mytable")))
0
 
vsvbAuthor Commented:
still same error i am not getting why ???? :(
0
 
vsvbAuthor Commented:
can you please try this code i read in some Microsoft Help its maybe due to i have to define
primary key i did that also in table field Name is now my PK still this error is comes
at update time

Actually value is accepted by Dataset object .. when i print the value that add in debug window it shows
but make problem only time of updation in Database
0
 
amyhxuCommented:
   'Fill dataset
    Dim Cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb")
    Dim da As New OleDbDataAdapter("SELECT * from mytable", Cn)
    Dim myCB As OleDbCommandBuilder = New OleDbCommandBuilder(da)
    Dim Ds As New DataSet
    da.Fill(Ds, "mytable")

    'Read and assign values to an array
    Dim tempRow As DataRow
    Dim AllName(Ds.Tables("mytable").Rows.Count - 1) As String
    Dim i As Integer = 0
    For Each tempRow In Ds.Tables("mytable").Rows
        AllName(i) =  tempRow(0) + "--" + tempRow(1)
        i += 1
    Next

    'Read from the array
    For n As Integer = 0 To UBound(AllName) - 1
        Console.WriteLine(AllName(n))
    Next

    'Add a new row
    Dim tempRow1 As DataRow
    tempRow1 = Ds.Tables("mytable").NewRow()
    tempRow1(0) = "Peter"  ' Or tempRow1("Name") = "Peter
    tempRow1(1) = "22, Main street"  ' Or tempRow1("Address") = "22, Main street"
    Ds.Tables("mytable").Rows.Add(tempRow1)
    da.update(Ds.Tables("mytable"))

    'Delete the first row
    Dim myrow As DataRow = Ds.Tables("mytable").Rows(0)
    myrow.Delete()
    da.update(Ds.Tables("mytable"))

    'Update the first row
    Dim myrow1 As DataRow = Ds.Tables("mytable").Rows(0)
    myrow1("Name") = "John"
    myrow1("Address") = "100, Center Street"
    da.update(Ds.Tables("mytable"))

0
 
vsvbAuthor Commented:
tx amyhxu its works  without prob :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now