[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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