?
Solved

duplicate row values in same table?

Posted on 2005-03-15
7
Medium Priority
?
307 Views
Last Modified: 2008-02-26
what i am trying to do is copy all of the column values from the current row filtered in the dataview to a new row in the same datatable and change the primary key and a few other column values and store it as a new row and insert it into the same table.  When i do this with the code below i get a exception thrown saying that this row already exsists in this table even though the primary key("quote_id") is different.  Its right its the same row but different and i tested to make sure its changing the quote id and incrementing properly and all so im lost on this one :(  I was looking at the importrow method but that will only import to a different datatable. Any help will be greatly appreciated ty!

Public Function FormatQuoteId(ByVal CustId As String, ByVal NextQuoteNum As Integer) As String
        Dim myQuoteNum As String = NextQuoteNum.ToString + ""
        While myQuoteNum.Length < 3
            myQuoteNum = "0" & myQuoteNum
        End While
        myQuoteNum = CustId & "-" & myQuoteNum
        Return myQuoteNum
    End Function

Public Sub DuplicateQuote()
        Dim myQuoteIdx As Integer = BindingContext(myDs, "quotes").Position
        Dim myChild As Object = myQuoteDv.Item(myQuoteIdx).CreateChildView("quotes_to_customers_via_cust_id").Item(myQuoteIdx)
        Dim myCustID As String = cNull(myChild("cust_id"), "String")
        Dim myNextNum As Integer = cNull(myChild("next_quote_number"), "Integer")
        Dim myNew As DataRow = myDs.quotes.NewquotesRow
        Dim newQuoteId As String = FormatQuoteId(myCustID, myNextNum)
        myNew.BeginEdit()
        myNew = myQuoteDv.Item(myQuoteIdx).Row
        myNew("seq") = 0
        myNew("cust_id") = myCustID
        myNew("quote_id") = newQuoteId
        myNew("quote_date") = Date.Now.Today.ToShortDateString
        myNew("is_contract") = False
        myNew("total_building_cost_override") = myNull
        myNew("checked_out") = False
        myNew.EndEdit()
        myDs.quotes.Rows.Add(myNew)
        tmpFilter = "quote_id = '" & newQuoteId & "'"
        myChild("next_quote_number") = myNextNum + 1
    End Sub
0
Comment
Question by:rbirnesser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 

Author Comment

by:rbirnesser
ID: 13548372
i was goin to read the column values into an array and read then back into a row but i would think the solution would be easier then that by using the

Dim myNew As DataRow = myDs.quotes.NewquotesRow

Then

myNew = myQuoteDv.Item(myQuoteIdx).Row
0
 
LVL 14

Accepted Solution

by:
amyhxu earned 2000 total points
ID: 13552309
your problem is that by using: myNew = myQuoteDv.Item(myQuoteIdx).Row, it actually makes myNew reference to the row to be copied, so when you try to save the new row to database, it's saving the existing row, no matter how you've changed that row.
You should just create a new row and copy over each column value from the existing row that you don't want to change and assign different values to other columns you do want to change:

        Dim myNew As DataRow = myDs.quotes.NewquotesRow
               'Delete myNew = myQuoteDv.Item(myQuoteIdx).Row
        Dim newQuoteId As String = FormatQuoteId(myCustID, myNextNum)
        myNew = myQuoteDv.Item(myQuoteIdx).Row
        myNew("seq") = 0
        myNew("cust_id") = myCustID
        myNew("quote_id") = newQuoteId
        myNew("quote_date") = Date.Now.Today.ToShortDateString
        myNew("is_contract") = False
        myNew("total_building_cost_override") = myNull
        myNew("checked_out") = False
              'Copy other fields from the existing row
        myDs.quotes.Rows.Add(myNew)
0
 

Author Comment

by:rbirnesser
ID: 13554483
thats like over 100 columns :( no easier way to do it or a way to get around that exception ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rbirnesser
ID: 13554881
sorry its 200+ columns im trying to read them into a new row using a loop let ya know if it works
0
 

Author Comment

by:rbirnesser
ID: 13554931
       
        Dim myCol As DataColumnCollection
        Dim myNew As DataRow = myDs.quotes.NewquotesRow
        Dim x As Integer = 0
        MsgBox("before loop!")

        For Each myCol In myDs.quotes
            myNew.BeginEdit()
            myNew.Item(x) = myQuoteDv.Item(myQuoteIdx)(x)
            myNew.EndEdit()
            x = x + 1
        Next
        MsgBox("Total Columns: " & x)

I get a specified cast is not valid exception :(
0
 
LVL 14

Expert Comment

by:amyhxu
ID: 13555365
Dim myCol As DataColumn
0
 

Author Comment

by:rbirnesser
ID: 13555909
ok i got it to work using this code and i will still u the points b/c im a nice and you pointed me in the right direction :)
I like solving my own problems if i can lol, thanks for all ur help greatley appreciated :)

Public Sub DuplicateQuote()
        Dim myQuoteIdx As Integer = BindingContext(myDs, "quotes").Position
        Dim myChild As Object = myQuoteDv.Item(myQuoteIdx).CreateChildView("quotes_to_customers_via_cust_id").Item(myQuoteIdx)
        Dim myCustID As String = cNull(myChild("cust_id"), "String")
        Dim myNextNum As Integer = cNull(myChild("next_quote_number"), "Integer")

        Dim myCol As DataColumn
        Dim myNew As DataRow = myDs.quotes.NewquotesRow
        Dim curIdx As Integer

        For Each myCol In myDs.quotes.Columns
            myNew.BeginEdit()
            myNew(curIdx) = myQuoteDv.Item(myQuoteIdx).Row.Item(curIdx)
            myNew.EndEdit()
            curIdx += 1
        Next

        Dim newQuoteId As String = FormatQuoteId(myCustID, myNextNum)
        myNew.BeginEdit()
        myNew("seq") = 0
        myNew("cust_id") = myCustID
        myNew("quote_id") = newQuoteId
        myNew("quote_date") = Date.Now.Today.ToShortDateString
        myNew("is_contract") = False
        myNew("total_building_cost_override") = myNull
        myNew("checked_out") = False
        myNew.EndEdit()
        newRecord = True
        myDs.quotes.Rows.Add(myNew)
        InsertQuote()
        myQuoteDv.RowFilter = "quote_id = '" & newQuoteId & "'"
        myChild("next_quote_number") = myNextNum + 1
        newRecord = False
End Sub
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question