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!

[code=vbnet]
Public Sub InsertSvcReq(By Val 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.OleDbConn ection = DBConnect.GetCo nnection()

Dim SqlCMD As String = "Select count(RMA_No) as RMANO from RMA where RMA_No like '" & FormType & "%'"
Dim myCMD As New OleDb.OleDbComm and(SqlCMD, con)
Dim DataReader As OleDb.OleDbData Reader = myCMD.ExecuteRe ader()
DataReader.Read ()

Dim SvcNo As String = FormType & "/RCC/" & Now.ToString("y y") & "/" & format4digits(C Int(DataReader( "RMANO")) + 1)
DataReader.Clos e()

SqlCMD = "insert into RMA values('" & SvcNo & "','" & Now & "','" & CustName & "','" _
& CustAdd & "','" & CustState & "','" & CustPostcode & "','" & CustPhone & "','" _
& CustFax & "','" & CustEmail & "','" & CustCRS & "','" & CustPrvRMA & "')"

myCMD.CommandTe xt = SqlCMD
myCMD.ExecuteNo nQuery()

PdtDetails.Colu mns.Remove("No. ")
PdtDetails.Acce ptChanges()

For i As Integer = 0 To PdtDetails.Rows .Count - 1
PdtDetails.Rows (i)("RMA_No") = SvcNo
Next
PdtDetails.Acce ptChanges()

SqlCMD = "select * from RMA_Prod_Descri ption"

Dim DataAdapter As New OleDb.OleDbData Adapter(SqlCMD, con)Dim CmdBuilder As New OleDb.OleDbComm andBuilder(Data Adapter)
DataAdapter.Ins ertCommand = CmdBuilder.GetI nsertCommand

DataAdapter.Upd ate(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.Column s.Add("No.", Type.GetType("S ystem.Int16"))


dtSvcReq.Column s.Add("RMA_No", Type.GetType("S ystem.String"))
dtSvcReq.Column s.Add("Product_ Description", Type.GetType("S ystem.String"))
dtSvcReq.Column s.Add("Serial_N o", Type.GetType("S ystem.String"))
dtSvcReq.Column s.Add("DOP", Type.GetType("S ystem.String"))
dtSvcReq.Column s.Add("POP", Type.GetType("S ystem.String"))
dtSvcReq.Column s.Add("Phy_Dmg" , Type.GetType("S ystem.Boolean") )
dtSvcReq.Column s.Add("Store_Re place", Type.GetType("S ystem.Boolean") )
dtSvcReq.Column s.Add("Fault_De sc", Type.GetType("S ystem.String"))
dtSvcReq.Accept Changes()
Session("SvcReq Cart") = dtSvcReq
End Sub

Protected Sub btnAdd_Click(By Val sender As Object, ByVal e As System.EventArg s)
intSvcItem = Session("intSvc Item")
Dim tempDate As DateTime = txtDOP.Text

If intSvcItem < 5 Then
intSvcItem += 1

dtSvcReq = Session("SvcReq Cart")
drSvcReq = dtSvcReq.NewRow
drSvcReq("No.") = intSvcItem
drSvcReq("Produ ct_Description" ) = txtProduct.Text
drSvcReq("Seria l_No") = txtSN.Text
drSvcReq("DOP") = tempDate.ToStri ng("dd MMM yyyy")
drSvcReq("POP") = txtPOP.Text
drSvcReq("Phy_D mg") = ddlPhysical_Dmg .SelectedValue
drSvcReq("Store _Replace") = ddlStore_Replac e.SelectedValue
drSvcReq("Fault _Desc") = txtFaultDescrip tion.Text

dtSvcReq.Rows.A dd(drSvcReq)
dtSvcReq.Accept Changes()
Session("SvcReq Cart") = dtSvcReq
Session("intSvc Item") = intSvcItem
gvRMADetails.Da taSource = dtSvcReq
gvRMADetails.Da taBind()
Else
lblmsg.Text = "Reach maximum of 5 items per service request."
End If
End Sub
[/code]

[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.Accept Changes() in btnAdd_Click() and InsertSvcReq() methods.

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

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