Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

updating a database from a datatable

Posted on 2007-03-26
10
Medium Priority
?
244 Views
Last Modified: 2010-04-23
I have 2 datatables with data

datatable1

UserIdent (string)
UserName (string)
Field1  (string)
Field2 (datetime)
Field2 (string)

datatable2

UserIdent  string
Date1   datetime
UserData string


and 2 tables in the SQL database

table1
______________
UserID  int
UserIdent (string)
UserName (string)
Field1  (string)
Field2 (datetime)
Field2 (string)

table2
________________
UserID  int
Date1   datetime
UserData string

I need to go through each row of the datatables, check if it exists in the database and if it is - update existing record, if does not exist - insert the record.

Could someone give me an example of the best way to do that?
0
Comment
Question by:YZlat
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 4

Accepted Solution

by:
jtaylor8181 earned 1600 total points
ID: 18794198
you can do something like this if you have bounded you datatable by way of a dataadapter.  first create a dataview and link it to the datatable.

Dim m_da as dataadapter
Dim m_dt as datatable
Dim m_dv as dataview
'Assuming that you have already bounded your dataview to your datatable and datatable by way of  
'dataadpter

'Create a method that will accept the values used to check to see if a record exist in your database
'If it exsist then you can just update the database, else insert a new record

Private Sub AddRecord(byval userId as string, byval userName as string, byval field1 as string, _
byval field2 as datatime, byval field3 as string)
   'Create a Rowfilter for the dataview  
   Dim filter as string = "UserIdent = '" & userID & "' AND UserName = '" & userName & "'"
   m_dv.Rowfilter = filter  

   'create the datarowview and check to see if the record exsist with the rowfilter
   Dim dr As DataRowView
   
  'If the m_dv.Count is greater then 0 then that means a record exsist, so just update the record
  'If the m_dv.Count is equal to 0 then the record does not exist to insert a new record.
   If m_dv.Count() > 0 Then
       dr = Me.m_dv.Item(0)
       dr.BeginEdit()
   Else
       dr = Me.m_dv.AddNew()
       dr.Item("UserIdent") = userID
       dr.Item("UserName") = userName
       dr.Item("field1") = field1
       dr.Item("field2") = field2
       dr.Item("field3") = field3
   End If

   dr.EndEdit()
   'Make sure that you set the rowfilter back to nothing
   m_dv.RowFilter = ""

   'Update the database with the dataadapter
   m_da.Update(m_dt)
End Sub
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18794447
What are the Unique keys?  Can it be assumed that UserIdent is Unique and the same value for UserIdent in datatable1, datatable2 (in the app) and table1 (in the database) identify the "same" record?  And then that UserID in table1 and table2 in the database identify the "same" record?  So that the string UserIdent in one of the app's datatable's links directly to table1 in the database and the integer UserID in that provides the link to table2 in the database?

Roger
0
 
LVL 35

Author Comment

by:YZlat
ID: 18794673
Rger, your assumptions are correct.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 34

Expert Comment

by:Sancler
ID: 18795148
It's going to be a few hours - maybe tomorrow - before I can come back to this.  If, in the meantime, someone comes up with an answer, OK by me.  If not, I'll look at it then.  Two other bits of info might be helpful.  First, what version of .NET - 2, or earlier?  Second, what sorts of numbers are we talking about?  E.g. will there be lots more records in the database than might appear in the app?  Are we talking 10s or 100s or 1000s in the app?

Roger
0
 
LVL 35

Author Comment

by:YZlat
ID: 18795388
jtaylor8181, what do I do in case of update?

Thanks Roger!
0
 
LVL 4

Expert Comment

by:jtaylor8181
ID: 18795515
you would change the if statement to this::

  If m_dv.Count() > 0 Then
       'This part updates the record
       dr = Me.m_dv.Item(0)
       dr.BeginEdit()
       dr.Item("UserIdent") = userID
       dr.Item("UserName") = userName
       dr.Item("field1") = field1
       dr.Item("field2") = field2
       dr.Item("field3") = field3
   Else
       'This part inserts a new record
       dr = Me.m_dv.AddNew()
       dr.Item("UserIdent") = userID
       dr.Item("UserName") = userName
       dr.Item("field1") = field1
       dr.Item("field2") = field2
       dr.Item("field3") = field3
   End If
0
 
LVL 4

Assisted Solution

by:jtaylor8181
jtaylor8181 earned 1600 total points
ID: 18795734
Actually it would be this in this example:

If m_dv.Count() > 0 Then
       'This part updates the record
       dr = Me.m_dv.Item(0)
       dr.BeginEdit()
       dr.Item("field1") = field1
       dr.Item("field2") = field2
       dr.Item("field3") = field3
   Else
       'This part inserts a new record
       dr = Me.m_dv.AddNew()
       dr.Item("UserIdent") = userID
       dr.Item("UserName") = userName
       dr.Item("field1") = field1
       dr.Item("field2") = field2
       dr.Item("field3") = field3
   End If

You wouldn't update the UserIdent and UserName because those are the rowfilters so you would only need to update the other fields if necessary.
0
 
LVL 34

Assisted Solution

by:Sancler
Sancler earned 400 total points
ID: 18798560
You didn't answer my question about numbers of records and the balance of numbers as between the database and your datatables.  That could be a significant factor as in some scenarios the best way to go might be to bring all the database records into a datatable in the app and work with that.  But, unless the figures point severely the other way, I would do this with stand alone commands.

I would have one command to get a record from the database for each record in the app.  If that returned anything I would know that the record had to be updated: if it returned nothing I would know that a record had to be inserted.  I would then have two sets of commands - one for updating, one for inserting - each comprising two commands - one to deal with table1 and one to deal with table2.

I'm assuming - I didn't ask - that the table1/table2 relationship is one-to-one.  And that for each record in datatable1 there is exactly one matching record in datatable2.

What follows is intended to illustrate the approach.  Also, I didn't ask whether this was OleDb or SQL: if it's the former then parameter placeholders in the following would need replacing with ?.

  Dim sqlGet As String = "SELECT userID FROM table1 WHERE UserIdent = @UserIdent"
  Dim cmdGet As New SQLCommand(sqlGet, myConnection)
  cmdGet.Parameters.Add("@UserIdent", String)

  Dim sqlT1Update As String = "UPDATE table1 ... WHERE UserID = @UserID"
  Dim cmdT1Update As New SQLCommand(sqlT1Update, myConnection)
  'Add parameters ... including
  cmdcmdT1Update.Parameters.Add("@UserID", Integer)
 
  Dim sqlT2Update As String = "UPDATE table2 ... WHERE UserID = @UserID"
  Dim cmdT1Update As New SQLCommand(sqlT2Update, myConnection)
  'Add parameters ... including
  cmdcmdT1Update.Parameters.Add("@UserID", Integer)

  Dim sqlT1Insert As String = "INSERT INTO table1 ..."
  Dim cmdT1Insert As New SQLCommand(sqlT1Insert, myConnection)
  'Add parameters

  Dim sqlT2Insert As String = "INSERT INTO table2 ..."
  Dim cmdT2Insert As New SQLCommand(sqlT2Insert, myConnection)
  'Add parameters

  myConnection.Open
  Dim result As Object
  Dim thisID As Integer
  Dim dr2() As DataRow
  For Each dr1 As DataRow In datatable1.Rows
    dr2 = datatable2.Select("UserIdent = '" & dr1("UserIdent") & "'")
    cmdGet.Parameters("@UserIdent").Value = dr1("UserIdent")
    result = cmdGet.ExecuteScalar
    If result Is Nothing Then
       'record does not exist
       'use insert command
       'filling parameters from dr1 and dr2(0)
    Else
       'record does exist
       'use update command
       'filling parameters from dr1 and dr2(0)
       thisID = CInt(result)
       'and with thisID as Value for @UserID parameter
    End If
  Next
  myConnection.Close

Finally (and fairly obviously) the code has been typed "onto the screen", so it's untested.

Roger

PS ... and I would adopt Ajtaylor8181's last comment.  You would reflect that in the CommandText and Parameters for your Update commands.
0
 
LVL 35

Author Comment

by:YZlat
ID: 18801480
jtaylor, I get an error

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

in this line

m_da.Update(m_dt)
      
0
 
LVL 4

Assisted Solution

by:jtaylor8181
jtaylor8181 earned 1600 total points
ID: 18802079
that means that you database is not connected to your datatable or that there is a problem with the UPDATE command when you created your SQL connections and command.  If you used a dataadapter to fill the datatable, then that means you were able to successfully use the SELECT command so your UPDATE command is the problem.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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