Solved

updating a database from a datatable

Posted on 2007-03-26
10
231 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 4

Accepted Solution

by:
jtaylor8181 earned 400 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 400 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 100 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 400 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now