Advertisement

08.19.2008 at 03:24PM PDT, ID: 23661440
[x]
Attachment Details

Using LINQ I need to get a row from a query into memory, modify it, and then write as a new row into the same data table

[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.3
Zones:

.NET, Microsoft Visual Basic.Net, Language Integrated Query - LINQ

Tags:

vb.net 2008, linq
Greetings;

I have an application that needs to be able to copy an existing row from a SQL table to a new row in the same table, but some of the data has to modified before it is written back to the SQL table.  I also need the automatically generated ID from the newly copied row.

For some reason, I cant seem to wrap my head around this one or find an example that actually does what I need to do without having to copy each column of the table manually (i.e.,  zork.CITY = addr2copy.CITY)  What I have generated here _does_ work, but it seems kludgy and I was hoping that there might be a better way to code it.

NOTE: The SQL database in on a SQL 2000 server, and I am not allowed to change the structure or relationships of the SQL databases, nor may I add new stored procedures or functions directly into the database.

Thanks!
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:

Imports System
Imports System.Data
Imports System.Data.Linq
Imports System.Data.Linq.Mapping
Imports System.Data.Linq.SqlClient
Imports System.Data.DataTableExtensions
Imports System.Reflection
Public Class Form1
	Private re7DataContext As New RE_Linq1DataContext
	Dim myRecord_ID As Integer = 0
	Dim myAddress_ID As Integer = 0
	Const automation = 449
	Const homePhone = 3667
	Const homeAddr = 4808
	Private Sub BTN_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Search.Click
		Dim CID_lookup = From records In re7DataContext.RECORDs Where records.CONSTITUENT_ID = IN_CID.Text Select records.ID
		Dim CAID_lookup = From CA In re7DataContext.CONSTIT_ADDRESSes Where CA.CONSTIT_ID = myRecord_ID And CA.PREFERRED = "-1" Select CA.ADDRESS_ID
		Dim ADDR_lookup = From ADDRs In re7DataContext.ADDRESSes Where ADDRs.ID = myAddress_ID Select ADDRs
 
		'set global record information
		myRecord_ID = CID_lookup.FirstOrDefault
		OUT_InfoBox.Text = "Global Values:" & vbCrLf & "----------------------------------------" & vbCrLf & "myRecord_ID: " & vbTab & myRecord_ID & vbCrLf
		myAddress_ID = CAID_lookup.FirstOrDefault
		OUT_InfoBox.Text = OUT_InfoBox.Text & "myAddress_ID: " & vbTab & myAddress_ID & vbCrLf
 
		'Copy the current preferred address to a new entry
		Dim addr2copy = ADDR_lookup.FirstOrDefault
		Dim zorkTable As Table(Of ADDRESS) = re7DataContext.GetTable(Of ADDRESS)()
		Dim zork As New ADDRESS
		zork.ADDRESS_BLOCK = addr2copy.ADDRESS_BLOCK
		zork.CARRIER_ROUTE = addr2copy.CARRIER_ROUTE
		zork.CITY = addr2copy.CITY
		zork.COUNTRY = addr2copy.COUNTRY
		zork.COUNTY = addr2copy.COUNTY
		zork.INFO_SOURCE = addr2copy.INFO_SOURCE
		zork.POST_CODE = addr2copy.POST_CODE
		zork.REGION = addr2copy.REGION
		zork.STATE = addr2copy.STATE
		zork.TYPE = addr2copy.TYPE
		zork.ACADEMY_ID = addr2copy.ACADEMY_ID
		zork.DPC = addr2copy.DPC
		zork.FULLADDRESS = addr2copy.FULLADDRESS
		zork.SYNCFULLADDRESS = addr2copy.SYNCFULLADDRESS
		zork.NZCITY = addr2copy.NZCITY
		zork.SUBURB = addr2copy.SUBURB
		zork.LOT = addr2copy.LOT
		zork.CERTIFICATION_DATA = addr2copy.CERTIFICATION_DATA
		zork.ACADEMY_DATE_LAST_CHANGED = addr2copy.ACADEMY_DATE_LAST_CHANGED
		zork.ACADEMY_LAST_CHANGED_BY = addr2copy.ACADEMY_LAST_CHANGED_BY
		zork.INTEGRATION_ID = addr2copy.INTEGRATION_ID
		zork.INTEGRATION_DATE_LAST_CHANGED = addr2copy.INTEGRATION_DATE_LAST_CHANGED
		zork.DPBARCODE = addr2copy.DPBARCODE
		zork.DATE_LAST_CHANGED = Now
		zork.LAST_CHANGED_BY = automation
		zorkTable.InsertOnSubmit(zork)
		re7DataContext.SubmitChanges()
		OUT_InfoBox.Text += "new address ID: " & vbTab & zork.ID & vbCrLf
	End Sub
End Class
Start your 7 day free trial to see this answer
Answered By: naspinski
Expert Since: 02/25/2008
Accepted Solutions: 450
Computer Expertise: Guru
Education: University of Minnesota, Master's Degree
naspinski has been an Expert for 10 months, during which he has posted 1776 comments and answered 450 questions. naspinski is just one of 2364 experts in the .NET Zone. 2 experts collaborated on this answer, which was graded an "A" by the asker.
 
 
20081119-EE-VQP-48 / EE_QW_2_20070628