We help IT Professionals succeed at work.

How do I update a table based on a condition in an unrelated table in ADO

Clive Beaton
Clive Beaton asked
on
338 Views
Last Modified: 2013-11-26
I have inherited a system written in VB .NET.  I am not good with it or ADO.  I have a program that updates a field in a table conditional on the value of another field in the same table.  I understand it, but only just.  Snippet attached.
 
I need to change it conditional on the value of a field in an unrelated table.  In short, I want to see if a person exists in the Persons table (by their firstname and familyname) and if so, update the RegistrationExpiryDate, RegistrationStatus, and CurrentRegistrationKey fiels in the SysConfig table.

Any help will get my undying gratitude as well as the points.

Thanks in advance.
Dim objDs As DataSet
      Dim ObjDa As OleDbDataAdapter
      Dim objRow As DataRow
      Dim objTable As DataTable
      Dim objColumn As DataColumn 
      Dim MsgTitle As String = "Celebrant Assist Registration. "
      Dim sSQL As String = "Select * from SysConfig"
      Dim MySQL As String = _
          "UPDATE SysConfig SET RegistrationExpiryDate = #12/31/2020#, " & _
          "RegistrationStatus = 'Registered', " & _
          "CurrentRegistrationKey = '7B12-2F4A-FD8F-34B4' " & _
          "WHERE CurrentRegistrationRequest = '" & RequestNo & "'" 
      Dim MyConnection As New OleDb.OleDbConnection(ConnectStr) 
      Me.Show()
      Label1.Text = "Request Number: " & RequestNo
      objDs = New DataSet
      'Connect to database and specify sSQL  
      ObjDa = New OleDbDataAdapter(sSQL, ConnectStr) 
      Try
         ObjDa.Fill(objDs, "SysConfig")
         For Each objRow In objDs.Tables(0).Rows
            If objRow("CurrentRegistrationRequest").ToString = RequestNo Then
               Dim MyCommand As New OleDb.OleDbCommand(MySQL, MyConnection)
               MyConnection.Open()
               MyCommand.ExecuteNonQuery()
               MyConnection.Close()
               Label2.Text = "Celebrant Assist has been successfully registered."
               Exit For
            Else
               Label2.Text = "The Request Numbers do not Match. " & CrLf & "Please contact Celebrant Assist Support"
               Exit For
            End If
         Next 
         'Like Setting to Nothing
         objDs.Dispose()
         ObjDa.Dispose()
      Catch objException As Exception
         MsgBox("Registration Error.  Please contact Celebrant Assist Support. " & CrLf & _
         objException.ToString, , MsgTitle)
      End Try
   End Sub

Open in new window

Comment
Watch Question

J K

Commented:
can u pls spcify the fields of both the tbles?
Clive BeatonAccess Developer

Author

Commented:
Persons table; FirstName and LastName.

SysConfig table; RegistrationExpiryDate (date), RegistrationStatus (string), CurrentRegistrationKey (string)

I expect to enter the values of the name fields into the code and rebuild for each user.
J K

Commented:
Do you want something like this?

"Update a set a.RegistrationExpiryDate = #12/31/2020#, " & _
          "a.RegistrationStatus = 'Registered', " & _
          "a.CurrentRegistrationKey = '7B12-2F4A-FD8F-34B4' " & _
          " from SysConfig a INNER JOIN Person b on b.firstname='ABC' and b.lastname='XYZ' " &_
         " WHERE a.CurrentRegistrationRequest = '" & RequestNo & 
Clive BeatonAccess Developer

Author

Commented:
No, not quite.

I want to update SysConfig as you have it, above, only if a record exists in Persons with the names I supply.  The SysConfig and Persons tables are not related and have no common fields.  I don't want to test the CurrentRegistrationRequest.

In other words, if the person exists in the persons table then update the SysConfig table.  
Project Lead
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Clive BeatonAccess Developer

Author

Commented:
Perfect.  Just what I wanted.  Thank you.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.