hog

Cool......thx

In the code below where I have the first catch will the return false staement result in the Finally block not getting called? Or does the Finally block get called regardless?? Public Function Save() As Boolean Dim intX As Integer ' set internal error flag to false m_Error = False ' if validatedata returns false the data cannot be saved, user must recify If Not ValidateData() Then Return False End If ' if CalculateServiceDates returns false the data cannot be saved as service due dates are not created If Not CalculateServiceDates() Then Return False End If ' create a new command builder object based on this supplier objects data m_ocbContract = New System.Data.OleDb.OleDbCommandBuilder(m_odaContract) ' protect this section of code Try ' signal beginning of edit m_drContract.BeginEdit() ' write the private members to the corresponding data column m_drContract.Item("pr_number") = m_PRNumber m_drContract.Item("budget") = m_BudgetCode m_drContract.Item("dept") = m_DepartmentCode m_drContract.Item("po_number") = m_PONumber m_drContract.Item("dated") = m_Dated m_drContract.Item("Commencement") = m_Commencement m_drContract.Item("expires") = m_Expiry m_drContract.Item("contact") = m_Contact m_drContract.Item("contact_tel") = m_ContactTelephone m_drContract.Item("price") = m_Cost m_drContract.Item("frequency") = m_Frequency m_drContract.Item("order_line") = m_OrderLine m_drContract.Item("description") = m_Detail m_drContract.Item("internal") = m_Internal ' cycle through array and store service due dates in table For intX = 0 To m_Frequency - 1 m_drContract.Item("s" & intX) = m_ServiceDates(intX, 0) m_drContract.Item("s" & intX & "done") = m_ServiceDates(intX, 1) Next ' signal end of edit m_drContract.EndEdit() ' get the required update command for this data m_odaContract.UpdateCommand = m_ocbContract.GetUpdateCommand ' update the table in the database checking for concurrency errors while we are at it m_odaContract.Update(m_dsContract.Tables("tblContracts").GetChanges) ' signal to accept the changes m_dsContract.Tables("tblContracts").AcceptChanges() ' close the connection m_odaContract.UpdateCommand.Connection.Close() Catch objConcurrencyError As DBConcurrencyException ShowError("Location: Class Contract" & ControlChars.CrLf & ControlChars.CrLf & "Procedure: Save() As " & _ "Boolean" & ControlChars.CrLf & ControlChars.CrLf & "Error Text: " & "Another user has made " & _ "changes to this record since you opened it. You must " & ControlChars.CrLf & _ "reload the record and try again.") ' set internal error flag to true m_Error = True Return False Catch objException As Exception ShowError("Location: Class Contract" & ControlChars.CrLf & ControlChars.CrLf & _ "Procedure: Save() As Boolean" & ControlChars.CrLf & ControlChars.CrLf & _ "Error Text: " & objException.Message) ' set internal error flag to true m_Error = True Finally If m_odaContract.UpdateCommand.Connection.State.Open Then ' close the connection m_odaContract.UpdateCommand.Connection.Close() End If End Try ' return true to sigan data has been saved Return True End Function

Aha.....completely forgot about the Finally keyword.. Good one, thanks

Set the control's enabled property to False

This is the modification I'm making to all my code to fix the problem Nerseus pointed out: ' call function to calculate the total po cost If Not POTotal() Then If m_objConn.State.Open Then m_objConn.Close() End If Return End If

I put the brackets in exactly the same place as that genereated by Access. I removed the ; and changed False to 0

OK guys, thanks for this... Derek you say change it to SQLException, would this mean then that any other form of error may be missed? I tend to use Exception in all my try blocks as a sort of catch all. Am I in the wrong here then? Thx

Hi Derek, this is the norm for how I use the try/catch block. I have been using .NET for four months now, migrating from VBA and Access. My last real use of true VB was VB5 so VB.NET is a full of new stuff I'm learning.... So is this block of code OK or messy??? thx Public Sub New(ByVal strPONumber As String, ByVal lngSupplierID As Long) ' this constructor is used to create a contract object when the po and supplier name are known Dim intX As Integer ' set internal error flag to false m_Error = False ' create a connection using global connection string m_objConn = New System.Data.OleDb.OleDbConnection(gconnConnection) ' set-up the SQL which will return records for select PO number m_strSQL = "SELECT * FROM tblContracts WHERE tblContracts.po_number = '" & strPONumber & "' AND " & _ "tblContracts.Supplierid = " & lngSupplierID & " ORDER BY " & _ "order_line " ' create a new data adapter for the required data m_odaContract = New System.Data.OleDb.OleDbDataAdapter(m_strSQL, m_objConn) ' protect this section of code Try ' open the connection to the required database m_objConn.Open() ' fill the data table m_odaContract.Fill(m_dsContract, "tblContracts") ' set the data row object to the first row of the table, there will be only 1 as the supplier table ' only allows unique entries m_drContract = m_dsContract.Tables("tblContracts").Rows(0) ' assign values to private members, these will becomae available through each properties methods ' assign nothing if database field contains no data m_ContractID = IIf(IsDBNull(m_drContract.Item("contractid")), Nothing, m_drContract.Item("contractid")) m_SupplierID = IIf(IsDBNull(m_drContract.Item("supplierid")), Nothing, m_drContract.Item("supplierid")) m_PRNumber = IIf(IsDBNull(m_drContract.Item("pr_number")), Nothing, m_drContract.Item("pr_number")) m_BudgetCode = IIf(IsDBNull(m_drContract.Item("budget")), Nothing, m_drContract.Item("budget")) m_DepartmentCode = IIf(IsDBNull(m_drContract.Item("dept")), Nothing, m_drContract.Item("dept")) m_PONumber = IIf(IsDBNull(m_drContract.Item("po_number")), Nothing, m_drContract.Item("po_number")) m_Dated = IIf(IsDBNull(m_drContract.Item("dated")), Nothing, m_drContract.Item("dated")) m_Commencement = IIf(IsDBNull(m_drContract.Item("commencement")), Nothing, m_drContract.Item("commencement")) m_Expiry = IIf(IsDBNull(m_drContract.Item("expires")), Nothing, m_drContract.Item("expires")) m_Contact = IIf(IsDBNull(m_drContract.Item("contact")), Nothing, m_drContract.Item("contact")) m_ContactTelephone = IIf(IsDBNull(m_drContract.Item("contact_tel")), Nothing, m_drContract.Item("contact_tel")) m_OrderLine = IIf(IsDBNull(m_drContract.Item("order_line")), Nothing, m_drContract.Item("order_line")) m_Detail = IIf(IsDBNull(m_drContract.Item("description")), Nothing, m_drContract.Item("description")) m_Cost = IIf(IsDBNull(m_drContract.Item("price")), Nothing, m_drContract.Item("price")) m_Frequency = IIf(IsDBNull(m_drContract.Item("frequency")), Nothing, m_drContract.Item("frequency")) m_Active = IIf(IsDBNull(m_drContract.Item("active")), Nothing, m_drContract.Item("active")) m_Internal = IIf(IsDBNull(m_drContract.Item("internal")), True, m_drContract.Item("internal")) m_EquipmentID = IIf(IsDBNull(m_drContract.Item("equipmentid")), Nothing, m_drContract.Item("equipmentid")) m_RecordCount = IIf(IsDBNull(m_dsContract.Tables("tblContracts").Rows.Count), Nothing, m_dsContract.Tables("tblContracts").Rows.Count) ' resize the service due date array to the frequency value ReDim m_ServiceDates(m_Frequency, 2) ' cycle through table and store service due dates in array For intX = 0 To m_Frequency - 1 m_ServiceDates(intX, 0) = m_drContract.Item("s" & intX) m_ServiceDates(intX, 1) = m_drContract.Item("s" & intX & "done") Next ' call function to calculate the total po cost If Not POTotal() Then Return End If ' close connection to database m_objConn.Close() Catch objException As Exception ShowError("Location: Class Contract" & ControlChars.CrLf & ControlChars.CrLf & _ "Procedure: New(ByVal strPONumber As String, ByVal strSupplierID As Long)" & _ ControlChars.CrLf & ControlChars.CrLf & "Error Text: " & objException.Message) ' set internal error flag to true m_Error = True End Try End Sub

Hi, this is the generated sql I get when using the dataadapter query builder: SELECT tblJobs.jobdone, tblJobs.duedate, tblContracts.po_number, tblSuppliers.name, tblEquipment.model, tblEquipment.serial, tblEquipment.type FROM tblJobs INNER JOIN tblContracts ON tblJobs.contractid = tblContracts.contractid INNER JOIN tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid INNER JOIN tblSuppliers ON tblJobs.supplierid = tblSuppliers.supplierid WHERE (tblJobs.jobdone = 0) When I try to generate the dataset I get the error message Syntax error (missing operator) in query expression 'tblJobs.contractid=tblContracts.contractid INNER JOIN tblEquipment ON tblContracts.equipmentid=tblEquipment.equipmentid INNER JOIN tblSuppliers On tblJobs.supplierid=tblSuppliers.supplierid I have created the same query in Access which works fine using this sql: SELECT tblJobs.*, tblContracts.po_number, tblSuppliers.name, tblContracts.equipmentid, tblEquipment.serial, tblEquipment.model, tblEquipment.type FROM ((tblJobs INNER JOIN tblContracts ON tblJobs.contractid = tblContracts.contractid) INNER JOIN tblSuppliers ON tblContracts.Supplierid = tblSuppliers.supplierid) INNER JOIN tblEquipment ON tblContracts.equipmentid = tblEquipment.equipmentid WHERE (((tblJobs.jobdone)=False)); I have tried placing the brackets in the vb code but still get the same error. Any ideas?

Mmm point taken. I trust however where code attempts to access the database then it should be protected?

Mmm good observation Robby, I'll keep that one in mind :-)

OK thanks Derek, I'll look into you suggestion re the function calls. Can you enlighten me about why my try blocks are too big? I thought the idea was to protect blocks of code that could bomb? Surely you don't mean I should litter the block with multple try blocks, or is that how it is supposed to be done?? Thx

can you expand on you question, seems a bit vague to figure out what you are trying to achive?

Nerseus, maybe I used the wrong word, elegant? Perhaps I should have used efficient instead. As you see my code works fine, but the section where I get the result from the division then using a math method to obtain the integer portion without rounding seem messy to me. So what I'm asking is, is there a more efficient way of doing it or does the phase, "if ir ain't broke don't fix it" apply?

Just set the format of the field in your Access database table to short date format and no further work will be required by you.

This is what I use: Try Dim olNameSpace As Outlook.NameSpace Dim objOutlook As Outlook.Application Dim objNewMail As Outlook.MailItemClass ' create an outlook mail message objOutlook = New Outlook.Application() objOutlook.Session.Logon(NewSession:=False) olNameSpace = objOutlook.GetNamespace("MAPI") objNewMail = objOutlook.CreateItem(Outlook.OlItemType.olMailItem) ' format the body of the message strBody = gstrApplicationName & " has generated the following error: " & vbCrLf & vbCrLf & strBody & _ vbCrLf & vbCrLf & "Please contact this user as soon as possible" ' finalise message detail then send With objNewMail .To = gstrRecipient .Recipients.ResolveAll() .Subject = gstrSubject .Body = strBody .Send() End With Catch objException As Exception System.Windows.Forms.MessageBox.Show("An error has occured whilst attempting to send the email" & _ ControlChars.CrLf & ControlChars.CrLf & "If the problem persists " & _ "please contact the System Administrator", "pjhError.dll ~ Error", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error) End Try you need to reference Interop.Outlook.dll for this to work. [edit]Yes it is [vb][/vb][/edit]

when you create your data adapter you can use a query designer like that in Access but with less bells and whistles if you don't want to type the sql yourself

Thnx......

Ah, but it is in my code.....didn't realise you wanted all that with it sorry? By the way how do you get code to appear in the white neat boxes on this web page? Dim intX As Integer 'holds the number of weeks between services Dim intNumberOfWeeks As Integer ' stores date being calculated Dim dtmCalculatedDate As Date ' temporary storage of division result Dim dblTemp As Double ' set internal error flag to false m_Error = False ' protect this block Try ' resize the service date array to the selected frequency ReDim m_ServiceDates(m_Frequency, 2) ' calculate the number of weeks between commencement and expiry of contract and divide by frequency ' use floor method to obtain integer part without rounding up, which will result in the last service ' due date calculated being passed the expiry date dblTemp = CDbl(DateDiff(DateInterval.WeekOfYear, m_Commencement, m_Expiry, FirstDayOfWeek.Sunday) / m_Frequency) ' get the integer part intNumberOfWeeks = Math.Floor(dblTemp) ' seed calculated date dtmCalculatedDate = m_Commencement ' cycle through service date array For intX = 0 To m_Frequency - 1 ' calculate the next service due date, add the required number of weeks to the date supplied dtmCalculatedDate = DateAdd(DateInterval.WeekOfYear, intNumberOfWeeks, dtmCalculatedDate) ' if the date falls on either a Saturday or a Sunday then subtract 2 days to make it fall on a work day If dtmCalculatedDate.DayOfWeek = DayOfWeek.Saturday Or dtmCalculatedDate.DayOfWeek = DayOfWeek.Sunday Then dtmCalculatedDate = DateAdd(DateInterval.Weekday, -2, dtmCalculatedDate) End If ' store the date in the service date array and set serviced flag to false m_ServiceDates(intX, 0) = dtmCalculatedDate m_ServiceDates(intX, 1) = "False" Next Catch objException As Exception ShowError("Location: Class Contract" & ControlChars.CrLf & ControlChars.CrLf & _ "Procedure: CalculateServiceDates() As Boolean" & ControlChars.CrLf & _ ControlChars.CrLf & "Error Text: " & objException.Message) ' set internal error flag to true m_Error = True Return False End Try

If you hold your mouse over the blue line it tells you what the problem is. It tells you that it requires a constant value within the parenthesis

OK here you go... m_Commencement is the start date of a maintenance contract m_Expiry is the end date of the contract m_frequency is the service visit frequency My object calculates a proposed set of service dates which the user can accept or amend. So if the week difference is 52 and the frequency is 6 the result is 8.666666. This gets rounded up to 9 so my code calculates the dates at 9 week intervals between start and end, however the last date can end up being passed the end date of the contract. So my bit of code early gives me 8 which then ensures my dates stay within the start/end boundary. I look at the code though and think, yuk....must be a better way?

The way to find out is to try it. I haven't directly modified XML yet so have no knowledge of doing it that way, although I can't see why it wouldn't?

To obtain the unrounded integer part of the division below DateDiff(DateInterval.WeekOfYear, m_Commencement, m_Expiry, FirstDayOfWeek.Sunday) / m_Frequency) Which results in 8.666666 rounding up to 9 If there a more elegant way than this? dblTemp = CDbl(DateDiff(DateInterval.WeekOfYear, m_Commencement, m_Expiry, FirstDayOfWeek.Sunday) / m_Frequency) intNumberOfWeeks = Math.Floor(dblTemp) Thx:)

OK, not sure if this is the most effcient but this is how I got it to work as cystal is different than Access reporting. In report design I have a dataadapter and dataset with the required fields for the report. I use this to design the report to start with. When I use the report at runtime I point the reports recordsource to a dynamic dataset which has the same fields as the one used fr design time. When I am happy with the report the design time one is deleted as it is no longer needed. During development if I realise the dynamic dataset requires additional fields that will need to be on the report, I modify the design time one to gain access to the field objects to place on the form. Like I say not the most effcicient but it works for me and I have not had any info yet on a better way to do it?

What sort of email are you wanting to send, Outlook?, WEB?