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
LVL 10
MaxOvrdrv2Asked:
Who is Participating?
 
grayeCommented:
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
0
 
MaxOvrdrv2Author Commented:
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
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
MaxOvrdrv2Author Commented:
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...
0
 
riyazthadCommented:
You need to add this line, I guess.

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(DA)

Thad
0
 
riyazthadCommented:
In your case use OleDbCommandBuilder and also remove line AcceptChanges()

Try

Thad
0
 
MaxOvrdrv2Author Commented:
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? :)
0
 
grayeCommented:
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!
0
 
riyazthadCommented:
You only need to create object of OleDbCommandBuilder by passing parameter as DataAdapter object. That will take care of updating you record.

0
 
riyazthadCommented:
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
0
 
vbturboCommented:
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
0
 
MaxOvrdrv2Author Commented:
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
0
 
MaxOvrdrv2Author Commented:
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
0
 
MaxOvrdrv2Author Commented:
you can take out the response.end and response.write lines... sorry about that...
0
 
MaxOvrdrv2Author Commented:
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!
0
 
MaxOvrdrv2Author Commented:
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!
0
 
MaxOvrdrv2Author Commented:
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
0
 
MaxOvrdrv2Author Commented:
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!
0
 
riyazthadCommented:
you bet

Thad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.