Link to home
Start Free TrialLog in
Avatar of Blitzman
Blitzman

asked on

Check database fields using a method

Hello,

I need help in checking database fields using a method.  I was thinking about using a boolean method.

my code is this:
' This is in a form
 Dim note As ispec_note
    note = New ispec_note(orderConnectionString, order.InternalStatusNoteID)
    note.Text = txtInternalStatusNote.Text
    note.Save()

    order.InternalStatusNoteID = note.NoteID

these are the code in update save function:

    param_col.Add(New SqlParameter("@note_id", _NoteID))
    param_col.Add(New SqlParameter("@note_text", NoteText))
    RunStoredProc("usp_UpdateNote", param_col)

It's calling the save function from class and also the save function is calling the other function called updatesave function that has the parameters.

So i need to figure out what method i should create to check the database field.  Im planning to create a method in a form than putting in class.
Avatar of Blitzman
Blitzman

ASKER

Im checking to see  if it is the same as what is in the database
Can post an example of what you are trying to accomplish.

I don't know what do you mean by figure out what method i should create to check the database field.

You want your method to automatically get the fields name and it corresponding values:

the form have to know FieldName in some way?

UPDATE FieldName = Value

is this what you are asking for?

sorry for not understanding!
What I'm trying to say is we need a routine to check against the database.  If something has changed then trigger the change or let us know that
it changed.
How are you bring the data to your form?

Dataset or Datareader?
I have a function where it fills the data to the form using connection string.
You can do something, add a boolean variable to the form that indicates if the form data has changed:

and in the textbox.TextChanged event change the variable to true

dim blnAnyChange as boolena

Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged, TextBox3.TextChanged
     blnAnyChange = True
End Sub

you can add all your textboxes.TextChanged to just one eventhandler separating them with  a comma like the example.

this way you will know if something changed.

if blnAnyChange then
   param_col.Add(New SqlParameter("@note_id", _NoteID))
    param_col.Add(New SqlParameter("@note_text", NoteText))
    RunStoredProc("usp_UpdateNote", param_col)

   blnAnyChange = False
endif
Actually this code is in the command buttion called btnClickupdate:

    Dim note As ispec_note
    note = New ispec_note(orderConnectionString, order.InternalStatusNoteID)
    note.Text = txtInternalStatusNote.Text
    note.Save()

    order.InternalStatusNoteID = note.NoteID


The changes in txtInternalStatusNote should trigger changes to the status Enum which is called
Public Enum enumOrderStatus As Long
    NewOrder = 0
    Confirmed = 1
    UnderReview = 2
    NeedsChange = 3
    PleaseConfirm = 4
    Locked = 5
    InProduction = 6
    CompletedDelayed = 7
    ReadytoShip = 8
    ShipDelayed = 9
    StockBoat = 10
    Shipped = 11
    Received = 12

I was wondering if you can instead of boolean variable how about making a boolean method.  I am planning to use boolean method.
You can use a boolean method, but what are the reason you want a boolean method instedad?

Do you have some other criteria for the need of a method?

where you want to place the method in a class or form?

or you want a method take the info you have in your textboxes and compare those values against the info in the database table? and return a boolean indicating if are different?
I want it to place in the form for now and yeh a the function will do is check all text box values against the database and compare for changes.  if one single field has been changed, the function should return true else if no changes are made at all return false
Do you have your textboxes binded to a dataset or datatable?
Neither. data is retreived from the server and stored in private variables within the order object.  Im using web forms with web textbox, etc.
This would be one way to do it:  

Public function AnyChange() as Boolean

dim cmd as new sqlcommand("Select CustomerID, CustomerName from Customers WHERE CustomerID = '001'")
dim dr as sqldatareader = cmd.ExecuteReader()

if dr("CustomerID") <> me.txtCustomerID.Text then
    Return True
end if

if dr("CustomerName") <> me.txtCustomerName.Text then
    Return True
end if

end function
This is the almost correct code:

Public Function AnyChange() As Boolean
    Dim ReturnValue As Boolean = False
    Dim order As New ispec_order(orderConnectionString, 115029)
    Dim note As New ispec_note(orderConnectionString, order.InternalStatusNoteID)
    If String.Compare(txtInternalStatusNote.Text, note.Text) = 0 Then
      ReturnValue = False
    Else
      ReturnValue = True
    End If

  End Function

But the thing is that we're not checking for changes in txtInternStatusNote but we're checking for everything else that is changed.
What is the reason you can't compare changes in txtInternStatusNote?

what values return note.Text?
Note.Text is the actually text of a given note.  all notes are stored in the "Notes" table.  The Orders table is never loaded down with acutal notes.  Instead the notes are stored in the notes table and referenced by their ID

The issue I work with doesn't require changes txtInternStatusNote.  it's telling us not to compare changes in txtInternStatusNote.
Need to create a function that returns a boolean.  that function must verify that all fields have or have not been changed.  with the exception of the internalnotes field
But can't you just exclude the comparison of the InternalNotes?


I modified the function i posted and deleted the comparison of the CustomerID.

Why can't you do this?

Public function AnyChange() as Boolean

   dim cmd as new sqlcommand("Select CustomerID, CustomerName from Customers WHERE CustomerID = '001'")
   dim dr as sqldatareader = cmd.ExecuteReader()

   if dr("CustomerName") <> me.txtCustomerName.Text then
       Return True
   end if

end function


or you don't want to make it this way?
i dont need to use sqlcommand and sqldatareader because im using the class.  Im working with what i got already.
But the think is that the class needs to know the name of the textbox that correspond to the fields in your class right?

You can modify your method in order to receive the webpage as parameter:

and you can name the TextBoxes exactly the field you want to match it to.

then loop through the Page.Controls collection to find the textbox matching the name of your fields

and compare the values if they are different.

Public function AnyChange(Page as Object) as Boolean
    Dim ReturnValue As Boolean = False
    Dim order As New ispec_order(orderConnectionString, 115029)
    Dim note As New ispec_note(orderConnectionString, order.InternalStatusNoteID)

    'Loop controls collection to compare values

  End Function


Does this make sense to you?
I understand this but i just want to check all fields on the form except internalstatusNote.  Theres more objects on the form but i just want to check all fields on the form except internalstatusNote.
Ok, Now that you understand this can you post your code to check all fields.

i want to see the structure and based on that we will find a way to exclude the internalstatusNote.
thats the problem im trying to figure out how to check all fields except internalstatus note
Public function AnyChange(Page as Object) as Boolean
    Dim ReturnValue As Boolean = False
    Dim order As New ispec_order(orderConnectionString, 115029)
    Dim note As New ispec_note(orderConnectionString, order.InternalStatusNoteID)

    'Loop controls collection to compare values

     Here you can place a condition, check the fields only if they are not internalstatus
   

  End Function
Can you post the code of your class and tell me wich are the fields you want to compare.
Can you show me the code for loops contols collection and condition the check fields only except internalstatus?
I need to see the structure of your class, to see how are you polling the data into your class.

you told that already have a query, i want to see that.
All fields, basically we dont need to go into enterprise manager to check the tables and stuff like that. just a code to check all fields.  just give me an example and ill just manually put the fieldsname myself.  
Dim param_col As New Collection
        param_col.Add(New SqlParameter("@main_status_id", order.MainStatus))
        BindDropDownwithParams(ddlOrderStatus, "usp_GetOrderStatuses", param_col)
        FillOrderInfo()
BindDropDown2(ddlSalesman, "usp_GetActiveSalesmanList")
BindDropDown2(ddlDealer, "usp_GetActiveDealerList")
Public function AnyChange(Page as Object) as Boolean
   dim blnAnyChange as boolean
   dim cmd as new sqlcommand("Select CustomerID, CustomerName from Customers WHERE CustomerID = '001'")
   dim dr as sqldatareader = cmd.ExecuteReader()

   dim c as WebControl
   dim i as integer
   with dr
      for i = 0 to dr.FieldCount - 1
           c = GetControlByName(Page, "txt" & dr.GetName(i))
           if not isnothing(c) then
               if c.Text <> .Item(i) then
                     blnAnyChange = True
                    exit for
               end if
           end if
      next
   end with

   return blnAnyChange
end function

'Function to search a control by name
Private Function GetControlByName(Page as Object, ControlName as string) as WebControl
      dim c as WebControl
     dim blnFound as boolean

      for each c in Page.Controls
           if c.ID = ControlName then
                 blnFound = True
                 exit for
            end if
      next

     if blnFound then
         Return c
      else
         Return nothing
     end if
end functtion
I forgot, here you can check if its is internalstatus

Public function AnyChange(Page as Object) as Boolean
   dim blnAnyChange as boolean
   dim cmd as new sqlcommand("Select CustomerID, CustomerName from Customers WHERE CustomerID = '001'")
   dim dr as sqldatareader = cmd.ExecuteReader()

   dim c as WebControl
   dim i as integer
   with dr
      for i = 0 to dr.FieldCount - 1
           c = GetControlByName(Page, "txt" & .GetName(i))
           if not isnothing(c) then

               'Verify you are not comparing internalstatus field

               if c.Text <> .Item(i) and .GetName(i) <> 'internalstatus' then
                     blnAnyChange = True
                    exit for
               end if
           end if
      next
   end with

   return blnAnyChange
end function

'Function to search a control by name
Private Function GetControlByName(Page as Object, ControlName as string) as WebControl
      dim c as WebControl
     dim blnFound as boolean

      for each c in Page.Controls
           if c.ID = ControlName then
                 blnFound = True
                 exit for
            end if
      next

     if blnFound then
         Return c
      else
         Return nothing
     end if
end functtion
In order this to work, the textbox need to have the same name as the fields.

like:

for field CustomerID would be txtCustomerID.
SOrry wrong class. Heres a class called orders. It has two functions. Refresh function and Save function.

Public Sub RefreshData()
    Dim param_col As New Collection

    param_col.Add(New SqlParameter("@order_id", _OrderID))

    Dim ds As DataSet = GetDataSet("usp_SelectOrder",

    CommandType.StoredProcedure, param_col)
    Dim row As DataRow = ds.Tables(0).Rows(0)

_salesmanID = row("SalesmanID").ToString()
_dealerID = row("DealerID").ToString()
_orderStatusID = CType(row("OrderStatusID"), Long)
_canceled = CType(row("Canceled").ToString(), Boolean)
_promo = CType(row("Promo"), Boolean)
_productionInfo.OrderID = _orderID
    _productionInfo.RefreshData()
  End Sub

Public Sub Save()
    Dim param_col As New Collection

    param_col.Add(New SqlParameter("@order_id", _OrderID))


param_col.Add(New SqlParameter("@salesman_id", _salesmanID))
param_col.Add(New SqlParameter("@dealer_id", _dealerID))
param_col.Add(New SqlParameter("@order_status_id", _orderStatusID))
param_col.Add(New SqlParameter("@canceled", _canceled))
param_col.Add(New SqlParameter("@promo", _promo))

RunStoredProc("usp_UpdateOrder", param_col)

    _productionInfo.Save()
End Sub
Sorry for giving you wrong answer.  im using dataset to get the data.
Public Sub RefreshData()
    Dim param_col As New Collection

    param_col.Add(New SqlParameter("@order_id", _OrderID))

    Dim ds As DataSet = GetDataSet("usp_SelectOrder",

    CommandType.StoredProcedure, param_col)
    Dim row As DataRow = ds.Tables(0).Rows(0)

_salesmanID = row("SalesmanID").ToString()
_dealerID = row("DealerID").ToString()
_orderStatusID = CType(row("OrderStatusID"), Long)
_canceled = CType(row("Canceled").ToString(), Boolean)
_promo = CType(row("Promo"), Boolean)

If Not row("InternalStatusNoteID").ToString() = String.Empty Then _
      _internalStatusNoteID = CType(row("InternalStatusNoteID"), Long)

_productionInfo.OrderID = _orderID
    _productionInfo.RefreshData()
  End Sub

Public Sub Save()
    Dim param_col As New Collection

    param_col.Add(New SqlParameter("@order_id", _OrderID))


param_col.Add(New SqlParameter("@salesman_id", _salesmanID))
param_col.Add(New SqlParameter("@dealer_id", _dealerID))
param_col.Add(New SqlParameter("@order_status_id", _orderStatusID))
param_col.Add(New SqlParameter("@canceled", _canceled))
param_col.Add(New SqlParameter("@promo", _promo))
param_col.Add(New SqlParameter("@int_status_note_id", _internalStatusNoteID))

RunStoredProc("usp_UpdateOrder", param_col)

    _productionInfo.Save()
End Sub

Do I make changes in this class?  
sorry i was in long meeting, right now i have to leave.

tomorrow morning i will help you.

Public function AnyChange(Page as Object) as Boolean

   'Check if some field was changed, if changed return true      
   if salesmanID <> Page.txtSalesManID then
        return true
   end if

   if _dealerID <> Page.txtDealerID then
        return true
   end if

   'and so on


   return false
end function


so when you call the method you would pass the webpage as parameter.
Thank you for helping me but thats not the solution.  I apologize if the question was vague but I found the solution.
Can you post the code you used to solve your problem.

Just to see what were you looking for.

Probably because English is not my native language i ddin't understand you.
Public Function AnyChange() As Boolean
    Dim ReturnValue As Boolean
    Dim order As New ispec_order(orderConnectionString, 115029)
    Dim note As New ispec_note(orderConnectionString, order.ExternalStatusNoteID)
    If order.SalesmanID = ddlSalesman.SelectedValue Then
      TextBox1.Text = "True"
    ElseIf String.Compare(txtExternalStatusNote.Text, note.Text) <> 0 Then
      ReturnValue = True
    ElseIf order.DealerID = ddlDealer.SelectedValue Then
      ReturnValue = True
    ElseIf order.Canceled = chkCancel.Checked Then
      ReturnValue = True
    ElseIf order.Promo = chkPromo.Checked Then
      ReturnValue = True
    ElseIf order.OrderStatus = CType(ddlOrderStatus.SelectedValue, ispec_order.enumOrderStatus) Then
      ReturnValue = True
    Else
      ReturnValue = False
    End If

  End Function
ASKER CERTIFIED SOLUTION
Avatar of ee_ai_construct
ee_ai_construct
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial