asked on

Update DB using DataSet Modifications

Hi,,

Basically, i am currently dumping a specific record from the DB(using a DataAdapter) into a DS... then i'm making changes to the DS manually based on the type of submission from the user, then i'm TRYING to update the database, for that one record, to match the new DS contents. But somehow it gives me an Unhandled Exception Error without further instructions except the stack trace... grrr... anyway... is there a way to update/modify an existing row just by dumping the modified DS into the DA and using Update() ??

Here's my code, it will give you a better idea of what i'm trying to do...

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Conn As New OleDbConnection(ConfigurationSettings.AppSettings("conInterDB"))
Dim DA As New OleDbDataAdapter("SELECT * FROM Requests WHERE RequestID=" & Request.Form("RequestID"), Conn)
Dim DS As New DataSet
Dim row As DataRow
DA.MissingSchemaAction = MissingSchemaAction.AddWithKey

DA.Fill(DS, "RS")

If DS.Tables(0).Rows.Count = 0 Then
ErrorMsg.Text = "<center><b>Error 5! Could not find specific record to save to!</b></center>"
DA = Nothing
DS = Nothing
Response.End()
End If

DS.AcceptChanges()

If Request.Form("ActionPerf") = "DevSave" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Saved in Log by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("WorkFiles") = CInt(DS.Tables(0).Rows(0).Item("WorkFiles")) + CInt(Request.Form("DevDocsSess"))
DS.Tables(0).Rows(0).Item("WorkTime") = CInt(DS.Tables(0).Rows(0).Item("WorkTime")) + CInt(Request.Form("DevTimeSess"))
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "WebSave" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Saved by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "Close" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Closed by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Closed"

ElseIf Request.Form("ActionPerf") = "Delete" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Deleted by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Deleted"

ElseIf Request.Form("ActionPerf") = "Assign" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Assigned to " & Request.Form("Assigning") & " by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebType") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSite") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "Publish" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Published by " & Session("UN") & " on " & Today.Date & "," & "Confirmation sent to client"
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Completed"

ElseIf Request.Form("ActionPerf") = "SendQA" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Sent to QA by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("OAT") = DS.Tables(0).Rows(0).Item("AssignedTo")
DS.Tables(0).Rows(0).Item("AssignedTo") = "QA"
DS.Tables(0).Rows(0).Item("Status") = "Quality Assurance"

ElseIf Request.Form("ActionPerf") = "QADone" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "QA Completed on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("AssignedTo") = "Webmaster"
DS.Tables(0).Rows(0).Item("Status") = "Publishing"
End If

DS.AcceptChanges()
DA.Update(DS)

'Put user code to initialize the page here

End Sub

graye

You need to get rid of the AcceptChanges()... it doesn't mean what you think it means

AcceptChanges means: Let's pretend that any changes that may have occured have already been saved manually by the user and therefore we should mark all of the rows as "unchanged".

So, when you run the Update() method, it scans the rows to find all of the rows that have changes... Obviously it won't find any, since AcceptChanges has erased all of the "markers" for the changes..

BTW: None of this should cause an exception... it just wouldn't save any changes

MaxOvrdrv2

ASKER

Ok... Removed the AcceptChanges() section... still getting the exact same error message... here's what it says with the stack and everything... if you can make sense of it and find the problem... points to you!

Server Error in '/WebLog' Application.
--------------------------------------------------------------------------------

Unable to validate data.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Unable to validate data.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): Unable to validate data.]
System.Web.Configuration.MachineKey.GetDecodedData(Byte[] buf, Byte[] modifier, Int32 start, Int32 length, Int32& dataLength)
System.Web.UI.LosFormatter.Deserialize(String input)

[HttpException (0x80004005): Authentication of viewstate failed. 1) If this is a cluster, edit <machineKey> configuration so all servers use the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster. 2) Viewstate can only be posted back to the same page. 3) The viewstate for this page might be corrupted.]
System.Web.UI.LosFormatter.Deserialize(String input)
System.Web.UI.Page.LoadPageStateFromPersistenceMedium()

[HttpException (0x80004005): Invalid_Viewstate
      Client IP: 127.0.0.1
      Port: 2886
      User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461; .NET CLR 1.1.4322)
      ViewState: dDwxNDMzMDc0NTk7Oz45k3IKQ7hUHFzqYPGVMv6ugjjoJw==
      Http-Referer: http://localhost/WebLog/Work.aspx
      Path: /WebLog/LogSave.aspx.]
System.Web.UI.Page.LoadPageStateFromPersistenceMedium()
System.Web.UI.Page.LoadPageViewState()
System.Web.UI.Page.ProcessRequestMain() +447

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

MaxOvrdrv2

ASKER

More info:

My DB has no fields that are required except the RequestID autonum, and that's already set since i'm only doing a modify...

Also... since this is kinda urgent, because i'm going on my frist day of vacation tomorrow... and i would like to at least get that part working before i leave, i will give 500 more points to whomever finds the solution to this...

riyazthad

You need to add this line, I guess.

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(DA)

Thad

riyazthad

In your case use OleDbCommandBuilder and also remove line AcceptChanges()

Try

Thad

MaxOvrdrv2

ASKER

ok, but then what do i do with the object? it would be pointless to just declare it for nothing now wouldn't it!? How do i use it? :)

graye

Oddly enough, it get's used "behind the scenes" The DataAdapter object (that gets passed as a parameter) is modified by the creation of the CommandBuilder...

Yeah, I know... not exactly elegant programming, but hey... that's how they wrote it!

riyazthad

You only need to create object of OleDbCommandBuilder by passing parameter as DataAdapter object. That will take care of updating you record.

riyazthad

if you want to learn more what is happening behind the scene

visit this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbConnectionClassTopic.asp

Read line just before update command in their example.

Thad

ASKER CERTIFIED SOLUTION

riyazthad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbturbo

Checkout this tutorial on working with dataset's. Gives you multiple posibilities with a minimum of code effort and code lines.

http://www.homeandlearn.co.uk/NET/nets12p9.html

And here from the begining

http://www.homeandlearn.co.uk/NET/nets12p4.html

Here is a small simplyfied ole code you can test out ! allthough if implemented in a larger solution then watch the scope for the commandbuilder

Dim pk(0) As DataColumn'define a primarykey collum
Dim cbSqlDataAdapter1 As OleDb.OleDbCommandBuilder 'define the command builder
Dim sId As Integer 'variabel to hold the found primarykey in the dataset
Dim sSql As String
Dim TmpStr As String
Dim oDatarow As DataRow
-----------------------------------------------------

m_cn.Open() 'open the connection
DataSet11.Clear()

'fill the dataset and setup the dataadapter
sSql = "SELECT Priority,Id,Firmname,Mailaddress,Fname,Lname,Katagori FROM tblname"
SqlDataAdapter1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
SqlDataAdapter1.Fill(DataSet11, "tblname")

DataSet11.Tables("tblname").Columns("Id").AutoIncrement = True
pk(0) = DataSet11.Tables("tblname").Columns("Id") '<--- the name collumname of your primary key
DataSet11.Tables("tblname").PrimaryKey = pk
m_cn.Close()

----------------------------------------------------
' do the UPDATE to the datasource ! all the modifications made to the dataset "insert , update , delete"

Public Sub updatetblname()
cbSqlDataAdapter1 = New OleDb.OleDbCommandBuilder(SqlDataAdapter1)
SqlDataAdapter1.Update(DataSet11, "tblname")

End Sub

--------------------------------------------------------

Example ...

'loop through the dataset collum (6) to see if a match is found to conditon = TmpStr

TmpStr = "some condition"

For Each oDatarow In DataSet11.Tables("tblname").Rows

If oDatarow.Item(6) = TmpStr Then
sId = oDatarow.Item(1)'the collum with primary
DataSet11.Tables("tblname").Rows.Find(sId)' find the particular row with that primary
oDatarow.Delete()
End If
Next

updatetblname()' call the update sub
----------------------------------------------------
vbturbo

MaxOvrdrv2

ASKER

Sorry guys, was on vacation... will test these out as per posting times... and let you know if it works... and give points accordingly if it does... ;)

Thanks!

M

MaxOvrdrv2

ASKER

Ok...

Still getting the same error... here's how my code looks now:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Conn As New OleDbConnection(ConfigurationSettings.AppSettings("conInterDB"))
Dim DA As New OleDbDataAdapter("SELECT * FROM Requests WHERE RequestID=" & Request.Form("RequestID"), Conn)
Dim DS As New DataSet
Dim row As DataRow
DA.MissingSchemaAction = MissingSchemaAction.AddWithKey

DA.Fill(DS, "RS")

If DS.Tables(0).Rows.Count = 0 Then
ErrorMsg.Text = "<center><b>Error 5! Could not find specific record to save to!</b></center>"
DA = Nothing
DS = Nothing
Response.End()
End If

Response.Write(DS.GetType)
Response.End()

If Request.Form("ActionPerf") = "DevSave" Then

DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Saved in Log by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("WorkFiles") = CInt(DS.Tables(0).Rows(0).Item("WorkFiles")) + CInt(Request.Form("DevDocsSess"))
DS.Tables(0).Rows(0).Item("WorkTime") = CInt(DS.Tables(0).Rows(0).Item("WorkTime")) + CInt(Request.Form("DevTimeSess"))
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "WebSave" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Saved by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "Close" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Closed by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Closed"

ElseIf Request.Form("ActionPerf") = "Delete" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Deleted by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Deleted"

ElseIf Request.Form("ActionPerf") = "Assign" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Assigned to " & Request.Form("Assigning") & " by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebType") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSite") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Working"

ElseIf Request.Form("ActionPerf") = "Publish" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Published by " & Session("UN") & " on " & Today.Date & "," & "Confirmation sent to client"
DS.Tables(0).Rows(0).Item("WebComments") = DS.Tables(0).Rows(0).Item("WebComments") & Request.Form("WebComments")
DS.Tables(0).Rows(0).Item("WebTypeID") = Request.Form("WebType")
DS.Tables(0).Rows(0).Item("WebSiteID") = Request.Form("WebSites")
DS.Tables(0).Rows(0).Item("AssignedTo") = Request.Form("Assigning")
DS.Tables(0).Rows(0).Item("Status") = "Completed"

ElseIf Request.Form("ActionPerf") = "SendQA" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "Sent to QA by " & Session("UN") & " on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("OAT") = DS.Tables(0).Rows(0).Item("AssignedTo")
DS.Tables(0).Rows(0).Item("AssignedTo") = "QA"
DS.Tables(0).Rows(0).Item("Status") = "Quality Assurance"

ElseIf Request.Form("ActionPerf") = "QADone" Then
DS.Tables(0).Rows(0).Item("History") = DS.Tables(0).Rows(0).Item("History") & "QA Completed on " & Today.Date & ","
DS.Tables(0).Rows(0).Item("DevComments") = DS.Tables(0).Rows(0).Item("DevComments") & Request.Form("DevComments")
DS.Tables(0).Rows(0).Item("AssignedTo") = "Webmaster"
DS.Tables(0).Rows(0).Item("Status") = "Publishing"
End If

Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DA)

'Put user code to initialize the page here

End Sub

MaxOvrdrv2

ASKER

you can take out the response.end and response.write lines... sorry about that...

MaxOvrdrv2

ASKER

Ok ppl,

I've been trying a few things, and even after commenting out the DS and DA section totally... and just using the ASPX file to try and output the request form... i'm getting that error... so there's something bigger than just this here... if anyone has a clue as to why i'm getting that error... please let me know...

Right now i have nothing in my code-behind... all i have is this in my HTML section:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="LogSave.aspx.vb" Inherits="WebLog.LogSave"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>LogSave</title>
            <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
            <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
            <meta name="vs_defaultClientScript" content="JavaScript">
            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:Label id="ErrorMsg" style="Z-INDEX: 101; LEFT: 272px; POSITION: absolute; TOP: 136px"
                        runat="server" Width="432px" Height="456px">Label</asp:Label>
            </form>
            <script language="javascript">
            alert("Action:<%=Request.Form("ActionPerf")%>");
            </script>
            <%
            response.write(Request.Form)
            %>
      </body>
</HTML>

And i'm still getting that unable to validate data error! Grr!

MaxOvrdrv2

ASKER

Ok... that was a web.config error... fixed it...

After updating my code, and as it stands in the last post... i get another error:

*-*-LAST PIECE OF CODE AS IT STANDS NOW-*-*
End If
DA.Update(DS)
Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DA)

*-*-ERROR-*-*
Update unable to find TableMapping['Table'] or DataTable 'Table'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Update unable to find TableMapping['Table'] or DataTable 'Table'.

Source Error:

Line 107: DS.Tables(0).Rows(0).Item("Status") = "Publishing"
Line 108: End If
Line 109: DA.Update(DS)
Line 110: Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(DA)
Line 111:

1000 points to anyone that gets this resolved!

MaxOvrdrv2

ASKER

Looks like nothing is working as it should... i added the table mappings to the DA... and i don't get any errors... but the Database never gets updated? GRRRR! Where is the good old days of:

RecordSet.Update

To fill in a friggin table in the Database huh? Can anyone tell me why we're actually take steps BACKWARDS with this .Net stuff?

Cheers

MaxOvrdrv2

MaxOvrdrv2

ASKER

Ok... finally got it to work... thanks to riyazthad's MSDN links... and some searching on my own... therefore... points go to him...

Thanks to all the others who tried to help...

Cheers!

riyazthad

you bet

Thad