Problem adding new record.

[rpk2006]

I am using following code to Add New Record in a Table (Temp in this case). I want to save new record with this method i.e., non-SQL. It is not commiting changes. The "Update" statement has errors.

'Initialize Data Adapter and Data Table Objects
TempDA.Fill(TempDS, "Temp")
TempDT = TempDS.Tables("Temp") 'Temp is the name of table


'Store to Temporary Table
'Start Connection
If Cn.State = ConnectionState.Closed Then
Call StartConnection()
End If

TempNewRow = TempDT.NewRow()
TempNewRow("Pno") = NewPNo
TempNewRow("PLName") = tab1_namepl

'Add the new row
TempDT.Rows.Add(TempNewRow)

'Commit Changes
TempDS.AcceptChanges()

dsChanges = TempDS.GetChanges
TempDA.Update(dsChanges, "Temp")

'Increment Permit Number
NewPNo = NewPNo + 1
Cn.Close()

If TempDS.HasChanges(DataRowState.Added) Then
MsgBox("Record successfully saved.", MsgBoxStyle.OKOnly, "Success")
End If

Please also let me know which method for "Saving" record is fast (non-SQL).

 

[jebenson]

The problem is that you call .AcceptChanges before you call .Update. What .AcceptChanges does is exactly that - accepts all changes made to the dataset. So, any records you added will no longer show as DataRowState.Added, so when .Update is called it does not find any records to insert. The fix is simple - just put .AcceptChanges after the .Update call:

dsChanges = TempDS.GetChanges
TempDA.Update(dsChanges, "Temp")

'Commit Changes
TempDS.AcceptChanges()





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

 

[rpk2006]

I did as you suggested still the following error is displayed in the line with "Update Statement".

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

Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows.
----------------------

I am also giving the declarations below, please see if they are correct.

        Dim strTemp As String = "Select * from Temp"

        Dim cmdIns As New OleDb.OleDbCommand(strTemp, Cn)
        Dim TempDA As New OleDb.OleDbDataAdapter(cmdIns)


        Dim TempDS As DataSet = New DataSet("Temp")


        Dim dsChanges As New DataSet

        Dim TempDR As OleDb.OleDbDataReader
        Dim TempDT As DataTable = New DataTable("Temp")
        Dim TempNewRow As DataRow
        Dim TempQuery As String

 

[jebenson]

You need to assign an OleDbCommand to the DataAdapter's InsertCommand property. Here's a generic example, with a database table named Table1 with three fields (Field1, Field2 and Field3):

Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim conn As OleDbConnection
Dim cmd As OleDbCommand

'code here to set up connection, DataAdapter and Dataset
'I'm not putting the code because you seem to have this part


cmd = New OleDbCommand

With cmd
.Connection = conn
.CommandType = CommandType.Text
.Parameters.Add("@Field1", SqlDbType.Char, 11, "Field1")
.Parameters.Add("@Field2", SqlDbType.VarChar, 100, "Field2")
.Parameters.Add("@Field3", SqlDbType.VarChar, 100, "Field3")
.CommandText = ""Insert into Table1 (Field1, Field2, Field3) values (@Field1, @Field2, @Field3)""
End With

da.InsertCommand = cmd

'code here to make changes to the DataSet

da.Update(ds)

One thing I'm not sure about in your code is this:

dsChanges = TempDS.GetChanges
TempDA.Update(dsChanges, "Temp")

I'm not sure if the Update will work with this code, because dsChanges is not 'associated' with TempDA. That is, you didn't use TempDA to .Fill dsChanges, so I don't know if the update will work. But it might, so give your current code a try before making the changes below. If it doesn't, just skip using dsChanges and TempDT and .Update using TempDS directly, like so:

TempNewRow = TempDS.Tables("Temp").NewRow()
TempNewRow("Pno") = NewPNo
TempNewRow("PLName") = tab1_namepl

'Add the new row
TempDS.Tables("Temp").Rows.Add(TempNewRow)

[red]dsChanges = TempDS.GetChanges[/red] 'just remove this

TempDA.Update(TempDS, "Temp")


A decent overview of basic ADO .NET can be found at this link:

[URL unfurl="true"]http://www.devarticles.com/c/b/ADO.NET/[/url]

Look at the 'All You Need To Know About ADO.NET' articles (parts 1 & 2). They're kind of old, but they give a good solid overview of the basics. The other articles are helpful as well.

This is not to say that I won't continue to help you here, I just think that you will find this resource quite helpful...I sure did.

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

 

[rpk2006]

Thanks a lot.