OleDBDataAdapter.Update does not work

[kendrick82]

Hi, I am developing a web application and facing a difficulty in inserting the new data in a Datatable into the MS Access databse. The below method is able to execute without any error message but the data is not insert into the database. Please assists. Need to solve this urgently. Thanks!

Public Sub InsertSvcReq(ByVal FormType As String, ByVal CustName As String, _ 
ByVal CustAdd As String, ByVal CustState As String, _
ByVal CustPostcode As String, ByVal CustPhone As String, _ 
ByVal CustFax As String, ByVal CustEmail As String, _
ByVal CustCRS As String, ByVal CustPrvRMA As String, _ 
ByVal PdtDetails As DataTable) 
 
Dim con As OleDb.OleDbConnection = DBConnect.GetConnection()
 
Dim SqlCMD As String = "Select count(RMA_No) as RMANO from RMA where RMA_No like '" & FormType & "%'"
Dim myCMD As New OleDb.OleDbCommand(SqlCMD, con)
Dim DataReader As OleDb.OleDbDataReader = myCMD.ExecuteReader() 
DataReader.Read()
 
Dim SvcNo As String = FormType & "/RCC/" & Now.ToString("yy") & "/" & format4digits(CInt(DataReader("RMANO")) + 1) 
DataReader.Close()
 
SqlCMD = "insert into RMA values('" & SvcNo & "','" & Now & "','" & CustName & "','" _
& CustAdd & "','" & CustState & "','" & CustPostcode & "','" & CustPhone & "','" _ 
& CustFax & "','" & CustEmail & "','" & CustCRS & "','" & CustPrvRMA & "')"
 
myCMD.CommandText = SqlCMD
myCMD.ExecuteNonQuery()
 
PdtDetails.Columns.Remove("No.") 
PdtDetails.AcceptChanges()
 
For i As Integer = 0 To PdtDetails.Rows.Count - 1 
PdtDetails.Rows(i)("RMA_No") = SvcNo
Next
PdtDetails.AcceptChanges()
 
SqlCMD = "select * from RMA_Prod_Description"
 
Dim DataAdapter As New OleDb.OleDbDataAdapter(SqlCMD, con)Dim CmdBuilder As New OleDb.OleDbCommandBuilder(DataAdapter) 
DataAdapter.InsertCommand = CmdBuilder.GetInsertCommand
 
DataAdapter.Update(PdtDetails)
 
End Sub
 
 
PS: The table I am working is manually created using the following code before passing it to the above method
 
Protected Sub CreateTable()
        dtSvcReq.Columns.Add("No.", Type.GetType("System.Int16"))
 
 
        dtSvcReq.Columns.Add("RMA_No", Type.GetType("System.String"))
        dtSvcReq.Columns.Add("Product_Description", Type.GetType("System.String"))
        dtSvcReq.Columns.Add("Serial_No", Type.GetType("System.String"))
        dtSvcReq.Columns.Add("DOP", Type.GetType("System.String"))
        dtSvcReq.Columns.Add("POP", Type.GetType("System.String"))
        dtSvcReq.Columns.Add("Phy_Dmg", Type.GetType("System.Boolean"))
        dtSvcReq.Columns.Add("Store_Replace", Type.GetType("System.Boolean"))
        dtSvcReq.Columns.Add("Fault_Desc", Type.GetType("System.String"))
        dtSvcReq.AcceptChanges()
        Session("SvcReqCart") = dtSvcReq
    End Sub
 
    Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        intSvcItem = Session("intSvcItem")
        Dim tempDate As DateTime = txtDOP.Text
 
        If intSvcItem < 5 Then
            intSvcItem += 1
 
            dtSvcReq = Session("SvcReqCart")
            drSvcReq = dtSvcReq.NewRow
            drSvcReq("No.") = intSvcItem
            drSvcReq("Product_Description") = txtProduct.Text
            drSvcReq("Serial_No") = txtSN.Text
            drSvcReq("DOP") = tempDate.ToString("dd MMM yyyy")
            drSvcReq("POP") = txtPOP.Text
            drSvcReq("Phy_Dmg") = ddlPhysical_Dmg.SelectedValue
            drSvcReq("Store_Replace") = ddlStore_Replace.SelectedValue
            drSvcReq("Fault_Desc") = txtFaultDescription.Text
 
            dtSvcReq.Rows.Add(drSvcReq)
            dtSvcReq.AcceptChanges()
            Session("SvcReqCart") = dtSvcReq
            Session("intSvcItem") = intSvcItem
            gvRMADetails.DataSource = dtSvcReq
            gvRMADetails.DataBind()
        Else
            lblmsg.Text = "Reach maximum of 5 items per service request."
        End If
    End Sub
 

[Plater]

I think the .Update() function looks for an UPDATE command that would be supplied to the datatable/dataset.
I see you created an INSERT function, but I did not see an UPDATE function.
Maybe that has to do with it?

[kendrick82]

Hi Plater, sorry for the late reply and thanks for the advice. :)

I had solved the problem few minutes ago. It was caused by the dtSvcReq.AcceptChanges() in btnAdd_Click() and InsertSvcReq() methods.

Once I removed all the dtSvcReq.AcceptChanges(), it is able to work properly.

Cause:
When calling dtSvcReq.AcceptChanges() before DataAdapter.Update(PdtDetails), the Rowstate of dtSvcReq will become Unchange and it will not update to the database.