Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

duplicate row values in same table?

Posted on 2005-03-15
7
Medium Priority
?
309 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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