Solved

Check database fields using a method

Posted on 2004-09-29
43
173 Views
Last Modified: 2010-04-23
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.
0
Comment
Question by:Blitzman
  • 22
  • 19
43 Comments
 

Author Comment

by:Blitzman
ID: 12182075
Im checking to see  if it is the same as what is in the database
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12183306
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!
0
 

Author Comment

by:Blitzman
ID: 12183448
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.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12183751
How are you bring the data to your form?

Dataset or Datareader?
0
 

Author Comment

by:Blitzman
ID: 12183998
I have a function where it fills the data to the form using connection string.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12184143
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
0
 

Author Comment

by:Blitzman
ID: 12184461
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.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12184535
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?
0
 

Author Comment

by:Blitzman
ID: 12184758
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
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12185243
Do you have your textboxes binded to a dataset or datatable?
0
 

Author Comment

by:Blitzman
ID: 12185321
Neither. data is retreived from the server and stored in private variables within the order object.  Im using web forms with web textbox, etc.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12185391
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
0
 

Author Comment

by:Blitzman
ID: 12185705
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.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12189929
What is the reason you can't compare changes in txtInternStatusNote?

what values return note.Text?
0
 

Author Comment

by:Blitzman
ID: 12191183
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

0
 

Author Comment

by:Blitzman
ID: 12191200
The issue I work with doesn't require changes txtInternStatusNote.  it's telling us not to compare changes in txtInternStatusNote.
0
 

Author Comment

by:Blitzman
ID: 12191312
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
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12191396
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?
0
 

Author Comment

by:Blitzman
ID: 12191528
i dont need to use sqlcommand and sqldatareader because im using the class.  Im working with what i got already.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12191710
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?
0
 

Author Comment

by:Blitzman
ID: 12192296
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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Expert Comment

by:wguerram
ID: 12192328
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.
0
 

Author Comment

by:Blitzman
ID: 12192343
thats the problem im trying to figure out how to check all fields except internalstatus note
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192371
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
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192393
Can you post the code of your class and tell me wich are the fields you want to compare.
0
 

Author Comment

by:Blitzman
ID: 12192417
Can you show me the code for loops contols collection and condition the check fields only except internalstatus?
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192472
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.
0
 

Author Comment

by:Blitzman
ID: 12192536
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.  
0
 

Author Comment

by:Blitzman
ID: 12192562
Dim param_col As New Collection
        param_col.Add(New SqlParameter("@main_status_id", order.MainStatus))
        BindDropDownwithParams(ddlOrderStatus, "usp_GetOrderStatuses", param_col)
        FillOrderInfo()
0
 

Author Comment

by:Blitzman
ID: 12192576
BindDropDown2(ddlSalesman, "usp_GetActiveSalesmanList")
BindDropDown2(ddlDealer, "usp_GetActiveDealerList")
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192666
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
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192701
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
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12192713
In order this to work, the textbox need to have the same name as the fields.

like:

for field CustomerID would be txtCustomerID.
0
 

Author Comment

by:Blitzman
ID: 12192973
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
0
 

Author Comment

by:Blitzman
ID: 12193026
Sorry for giving you wrong answer.  im using dataset to get the data.
0
 

Author Comment

by:Blitzman
ID: 12193799
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?  
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12195818
sorry i was in long meeting, right now i have to leave.

tomorrow morning i will help you.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12199879

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.
0
 

Author Comment

by:Blitzman
ID: 12204530
Thank you for helping me but thats not the solution.  I apologize if the question was vague but I found the solution.
0
 
LVL 8

Expert Comment

by:wguerram
ID: 12217867
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.
0
 

Author Comment

by:Blitzman
ID: 12218022
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
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12259653
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

13 Experts available now in Live!

Get 1:1 Help Now