I cant 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 its time to update the db I dont 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
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 its time to update the db I dont 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
