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(orderConnection String, 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_UpdateN ote", 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.
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(orderConnection
note.Text = txtInternalStatusNote.Text
note.Save()
order.InternalStatusNoteID
these are the code in update save function:
param_col.Add(New SqlParameter("@note_id", _NoteID))
param_col.Add(New SqlParameter("@note_text",
RunStoredProc("usp_UpdateN
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.
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!
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!
ASKER
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.
it changed.
How are you bring the data to your form?
Dataset or Datareader?
Dataset or Datareader?
ASKER
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_UpdateN ote", param_col)
blnAnyChange = False
endif
and in the textbox.TextChanged event change the variable to true
dim blnAnyChange as boolena
Private Sub TextBox1_TextChanged(ByVal
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",
RunStoredProc("usp_UpdateN
blnAnyChange = False
endif
ASKER
Actually this code is in the command buttion called btnClickupdate:
Dim note As ispec_note
note = New ispec_note(orderConnection String, 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.
Dim note As ispec_note
note = New ispec_note(orderConnection
note.Text = txtInternalStatusNote.Text
note.Save()
order.InternalStatusNoteID
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?
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?
ASKER
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?
ASKER
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
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
ASKER
This is the almost correct code:
Public Function AnyChange() As Boolean
Dim ReturnValue As Boolean = False
Dim order As New ispec_order(orderConnectio nString, 115029)
Dim note As New ispec_note(orderConnection String, order.InternalStatusNoteID )
If String.Compare(txtInternal StatusNote .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.
Public Function AnyChange() As Boolean
Dim ReturnValue As Boolean = False
Dim order As New ispec_order(orderConnectio
Dim note As New ispec_note(orderConnection
If String.Compare(txtInternal
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?
what values return note.Text?
ASKER
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
ASKER
The issue I work with doesn't require changes txtInternStatusNote. it's telling us not to compare changes in txtInternStatusNote.
ASKER
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 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?
ASKER
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(orderConnectio nString, 115029)
Dim note As New ispec_note(orderConnection String, order.InternalStatusNoteID )
'Loop controls collection to compare values
End Function
Does this make sense to you?
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(orderConnectio
Dim note As New ispec_note(orderConnection
'Loop controls collection to compare values
End Function
Does this make sense to you?
ASKER
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.
i want to see the structure and based on that we will find a way to exclude the internalstatusNote.
ASKER
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(orderConnectio nString, 115029)
Dim note As New ispec_note(orderConnection String, 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
Dim ReturnValue As Boolean = False
Dim order As New ispec_order(orderConnectio
Dim note As New ispec_note(orderConnection
'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.
ASKER
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.
you told that already have a query, i want to see that.
ASKER
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.
ASKER
Dim param_col As New Collection
param_col.Add(New SqlParameter("@main_status _id", order.MainStatus))
BindDropDownwithParams(ddl OrderStatu s, "usp_GetOrderStatuses", param_col)
FillOrderInfo()
param_col.Add(New SqlParameter("@main_status
BindDropDownwithParams(ddl
FillOrderInfo()
ASKER
BindDropDown2(ddlSalesman, "usp_GetActiveSalesmanList ")
BindDropDown2(ddlDealer, "usp_GetActiveDealerList")
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
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
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.
like:
for field CustomerID would be txtCustomerID.
ASKER
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_SelectOrde r",
CommandType.StoredProcedur e, 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").ToSt ring(), Boolean)
_promo = CType(row("Promo"), Boolean)
_productionInfo.OrderID = _orderID
_productionInfo.RefreshDat a()
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_statu s_id", _orderStatusID))
param_col.Add(New SqlParameter("@canceled", _canceled))
param_col.Add(New SqlParameter("@promo", _promo))
RunStoredProc("usp_UpdateO rder", param_col)
_productionInfo.Save()
End Sub
Public Sub RefreshData()
Dim param_col As New Collection
param_col.Add(New SqlParameter("@order_id", _OrderID))
Dim ds As DataSet = GetDataSet("usp_SelectOrde
CommandType.StoredProcedur
Dim row As DataRow = ds.Tables(0).Rows(0)
_salesmanID = row("SalesmanID").ToString
_dealerID = row("DealerID").ToString()
_orderStatusID = CType(row("OrderStatusID")
_canceled = CType(row("Canceled").ToSt
_promo = CType(row("Promo"), Boolean)
_productionInfo.OrderID = _orderID
_productionInfo.RefreshDat
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
param_col.Add(New SqlParameter("@dealer_id",
param_col.Add(New SqlParameter("@order_statu
param_col.Add(New SqlParameter("@canceled", _canceled))
param_col.Add(New SqlParameter("@promo", _promo))
RunStoredProc("usp_UpdateO
_productionInfo.Save()
End Sub
ASKER
Sorry for giving you wrong answer. im using dataset to get the data.
ASKER
Public Sub RefreshData()
Dim param_col As New Collection
param_col.Add(New SqlParameter("@order_id", _OrderID))
Dim ds As DataSet = GetDataSet("usp_SelectOrde r",
CommandType.StoredProcedur e, 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").ToSt ring(), Boolean)
_promo = CType(row("Promo"), Boolean)
If Not row("InternalStatusNoteID" ).ToString () = String.Empty Then _
_internalStatusNoteID = CType(row("InternalStatusN oteID"), Long)
_productionInfo.OrderID = _orderID
_productionInfo.RefreshDat a()
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_statu s_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_UpdateO rder", param_col)
_productionInfo.Save()
End Sub
Do I make changes in this class?
Dim param_col As New Collection
param_col.Add(New SqlParameter("@order_id", _OrderID))
Dim ds As DataSet = GetDataSet("usp_SelectOrde
CommandType.StoredProcedur
Dim row As DataRow = ds.Tables(0).Rows(0)
_salesmanID = row("SalesmanID").ToString
_dealerID = row("DealerID").ToString()
_orderStatusID = CType(row("OrderStatusID")
_canceled = CType(row("Canceled").ToSt
_promo = CType(row("Promo"), Boolean)
If Not row("InternalStatusNoteID"
_internalStatusNoteID = CType(row("InternalStatusN
_productionInfo.OrderID = _orderID
_productionInfo.RefreshDat
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
param_col.Add(New SqlParameter("@dealer_id",
param_col.Add(New SqlParameter("@order_statu
param_col.Add(New SqlParameter("@canceled", _canceled))
param_col.Add(New SqlParameter("@promo", _promo))
param_col.Add(New SqlParameter("@int_status_
RunStoredProc("usp_UpdateO
_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.
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.
ASKER
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.
Just to see what were you looking for.
Probably because English is not my native language i ddin't understand you.
ASKER
Public Function AnyChange() As Boolean
Dim ReturnValue As Boolean
Dim order As New ispec_order(orderConnectio nString, 115029)
Dim note As New ispec_note(orderConnection String, order.ExternalStatusNoteID )
If order.SalesmanID = ddlSalesman.SelectedValue Then
TextBox1.Text = "True"
ElseIf String.Compare(txtExternal StatusNote .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.Selec tedValue, ispec_order.enumOrderStatu s) Then
ReturnValue = True
Else
ReturnValue = False
End If
End Function
Dim ReturnValue As Boolean
Dim order As New ispec_order(orderConnectio
Dim note As New ispec_note(orderConnection
If order.SalesmanID = ddlSalesman.SelectedValue Then
TextBox1.Text = "True"
ElseIf String.Compare(txtExternal
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.Selec
ReturnValue = True
Else
ReturnValue = False
End If
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER