Windows DataSet sorta Inserts Access DB

[Qik3Coder]

I have a dataset that is tied to a Access database. If i run the select command it returns the rows from the table. If i run an insert statement, it appears to run. If I then run the select again the old and the new rows return.

When i close the app and reopen it the "new" row is gone.

here is my update code block:

        Dim xsdNames As New SampleDataSetTableAdapters.PNR_NAMESTableAdapter
        Dim dsNames As New SampleDataSet.PNR_NAMESDataTable()
        Dim intCounter As New Integer()
        xsdNames.Fill(dsNames)
        Try
            xsdNames.Connection.Open()
            [COLOR=green]'lvNames is a ListView[/color]
            For intCounter = 0 To lvNames.Items.Count() - 1
                Debug.Write("Ins->" & xsdNames.Connection.State() & " ")
                xsdNames.Insert("a", "1", _
                  lvNames.Items(intCounter).SubItems(1).Text, _
                  lvNames.Items(intCounter).SubItems(2).Text, _
                  lvNames.Items(intCounter).SubItems(3).Text, _ 
                  lvNames.Items(intCounter).Text, _
                  lvNames.Items(intCounter).SubItems(4).Text)
                Debug.Write(xsdNames.Connection.State() & "<-Ins ")
            Next
            [COLOR=green]'xsdNames.Connection.Close()[/color]
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try

-The answer to your problem may not be the answer to your question.

 

[RonRepp]

You must add your new rows to the dataset and then use the AcceptChanges command before you update the mdb.

See Help file under AcceptChanges.

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[Qik3Coder]

So...
Rather than
tableAdapter1.Insert(param1,param2)

I should
dataset1.add(row)
dataset1.AcceptChanges()
tableAdapter1.Update(dataset1)
??


-The answer to your problem may not be the answer to your question.

 

[jebenson]

Actually, if you call AcceptChanges before you call Update nothing will get saved to the database. The reason is that AcceptChanges sets the state of any modified rows to DataRowState.Unmodified, and removes any rows that have DataRowState.Deleted. Thus when you call Update after AcceptChanges the Update methods finds no changed or deleted rows, and so does nothing.

You don't need to call AcceptChanges at all when using Update, because Update implicitly calls AcceptChanges after the Update finishes.

So, you would do this:

dataset1.add(row)
tableAdapter1.Update(dataset1)

Note that you need an InsertCommand for the DataAdapter for this to work.

If you need any more info, please post back.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Qik3Coder]

xsdNames.Insert("a", "1", _
                  lvNames.Items(intCounter).SubItems(1).Text, _
                  lvNames.Items(intCounter).SubItems(2).Text, _
                  lvNames.Items(intCounter).SubItems(3).Text, _ 
                  lvNames.Items(intCounter).Text, _
                  lvNames.Items(intCounter).SubItems(4).Text)

I am trying to INSERT, for now, the statement returns true, and if i then do a read, the new row returns, but upon app close and re-open the "new" values are gone.

I am trying to figure out what i need to add after .INSERT() to get the rows to stick in the database.

Thank you,

-The answer to your problem may not be the answer to your question.