I can't get seem to get this adapter(asp.net/
vb) to update my msaccess db.
I am using an dataadapter to retrieve values from multiple tables using a stored query to fill a datatable (without a dataset) to bind to a datagrid. So far I can delete/add/update items in the datatable but when it's time to update the db(single table) I don't even get an error and my db does not show updated/deleted/new data. here is my code that retrieves and updates info from the db
:
Thank you for any help!!!!
dim dtPubTable as New DataTable("PubAuthors")
dim myAdapter as New OledbDataAdapter
sub filldgPubAuthors(pubID as integer)
Dim objConnection as new OledbConnection (ConfigurationSettings.AppSettings("StrConnection" ))
dim objCmd As New OleDbCommand()
Dim objParam As OleDbParameter
With objCmd
.Connection = objConnection
.CommandText = "[qryPubAuthorWeb]"
.CommandType = CommandType.StoredProcedure
objParam = .Parameters.Add("PublicationID", OleDbType.integer)
End With
objParam.Direction = ParameterDirection.Input
objParam.Value = pubID
Try
myAdapter = New OledbDataAdapter(objCmd)
addScema()
myAdapter.Fill(dtPubTable)
viewState("pubTable") = dtPUbTable
dgPubAuthors.dataSource = viewState("pubTable")
dgPubAuthors.databind()
Catch objError As Exception
labelError.Text = objError.Message
Finally
objConnection.Close()
end try
end sub
sub updateChild()
dtPubTable = cType(viewState("pubTable"),datatable)
dtPubTable.Columns.remove("Name")
dtPubTable.Columns.remove("PubRole")
dim strSQL as string = "SELECT tblPubAuthor.PubAuthorID, tblPubAuthor.PublicationID, tblPubAuthor.PubRoleID, tblPubAuthor.PersonID, tblPubAuthor.dlOrder, tblPubAuthor.Contribution " & _
"FROM tblPubAuthor " & _
"WHERE (((tblPubAuthor.PublicationID)=" & ViewState("hidPubID") & "));"
dim objCommand as new oledbCommand(strSQL,objConnection)
myAdapter.selectCommand = objCommand
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(myAdapter)
myAdapter.Update(dtPubTable)
dtPubTable.AcceptChanges()
end sub
sub addScema()
dim tempRow as DataRow
tempRow = dtPubTable.NewRow()
tempRow("PUblicationID") = 1
tempRow("dlOrder") = 1
tempRow("Name") = getAuthorName()
tempRow("PersonID") = session("wwuID")
tempRow("PubRole") = "Author/CoAuthor"
tempRow("PubRoleId") = 1
tempRow("Contribution") = 1
dtPUbTable.Rows.add(tempRow)
viewState("pubTable") = dtPUbTable
else
dtPubTable = cType(viewState("pubTable"),datatable)
end if
end sub