Datagrid, ADODC, SQL Strings - Error Trapping Nightmare From H*LL!!

[PaulinKC]

Private Sub DataGrid3_Click()
On Error Goto DBErrorHandler

Dim strSQL As String
Dim ssql2 As String

'Check to see if we are going to display either the department or an individual employee

If indiv = False Then
'Department was selected
ssql2 = "SELECT DepartName FROM tblDepartInfo WHERE DepartNum =" & DataGrid3.Text

Set goGetdepNme = New ADODB.Connection
Set recGetDepNme = New ADODB.Recordset

goGetdepNme.CursorLocation = adUseClient
goGetdepNme.Open "Provider = Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;Data Source = F:\mydb.mdb"

recGetDepNme.Open ssql2, goGetdepNme, adOpenStatic, adLockOptimistic
txtDepartName.Text = recGetDepNme!departname & ""

strSQL = "SELECT EmpNum, EmployeeNme From tblEmployee WHERE DepartNum = " & DataGrid3.Text & " AND ActiveInactive=0"
txtDepartNum.Text = DataGrid3.Text
Adodc2.Enabled = True

Adodc2.RecordSource = strSQL
Adodc2.Refresh
Set DataGrid2.DataSource = Adodc2

ElseIf indiv = True Then
'Individual Employee Was Selected
ssql2 = "SELECT * FROM tblEmployee WHERE EmpNum =" & DataGrid3.Text 'Grabs the Employees Number from the Selected DataGrid3 Text

Set goGetdepNme = New ADODB.Connection
Set recGetDepNme = New ADODB.Recordset

goGetdepNme.CursorLocation = adUseClient
goGetdepNme.Open "Provider = Microsoft.Jet.OLEDB.4.0;Persist Security Info = False;Data Source = F:\mydb.mdb"

recGetDepNme.Open ssql2, goGetdepNme, adOpenStatic, adLockOptimistic
txtDepartName.Text = recGetDepNme!departname & ""
txtDepartNum.Text = recGetDepNme!departnum & ""
txtEmployeeNme.Text = recGetDepNme!EmployeeNme & ""
txtEmpNum.Text = recGetDepNme!empnum & ""

strSQL = "SELECT EmpNum, EmployeeNme From tblEmployee WHERE DepartNum = " & DataGrid3.Text & " AND ActiveInactive=0"
Adodc2.Enabled = False

End If

DBErrorHandler:

MsgBox err.description, err.number
resume next
exit sub

End Sub



Controls On The Form Are As Follows:

optDepartment 'Option Button For Selecting A Department
optEmployee 'Option Button For Selecting A Single Employee
Datagrid3 'Datagrid Control - Displays either Employee Number & Employee Name OR Department Number & Department Name
Datagrid2 'Datagrid Control - Displays only Employee Number & Employee Name IF a Department was Selected, other wise it's disabled if selecting a
'single employee
Datagrid1 'Datagrid Control - Displays the schedule for either the department or employee


Problem Description:

The code display above does not trap any errors, when a user selects the department name or employee name it gives a run time error and exits
the application. The user is supposed to select the DepartNum field if selecting a department or EmpNum field if selecting a individual
employee. If the user does not select a DepartNum or EmpNum, the ssql2 errors out and gives the following error message:

Run Time Error '-2147217904'(8004e10)':
No Value Given For One Or More Required Parameters

I know what this error is and what's causing, I just can't figure out how to tell the user that "Hey you selected a Department Name and not the
Department Number, try again" Or "Hey you selected an Employee Name and not the Employee Number, try again!"

 

[MarkSweetland]

The way you've defined the flow of the subroutine causes the msgbox to appear regardless of whether or not an error occurs. Plus the Resume Next in your error handler causes the next statement after the one that caused the error to try to execute. Since an error was detected on the previous step it is likely that the following statements are dependent and will also cause an error. I usually define only one exit point for the Sub and include any object release or mouse pointer reset. If an error was detected, I display the error message with a reference to the subroutine name (so I know where to look if there is a problem) and exit the routine altogether:


Sub SomeSubName
On Error Goto SomeSubErrorHandler
.
do something
.
ExitSomeSubName:
do cleanup
Exit Sub
SomeSubErrorHandler:
MsgBox "Error in SomeSubName: " & Err.Description
' or look for a particular Err.Number and display help,etc
Resume ExitSomeSubName
End Sub



Mark

 

[PaulinKC]

Ok so let me see if I understand you correctly. For the error handler I need to do the following:

ExitSubProc:
do cleanup
Exit Sub
DBErrorHandler:
MsgBox "There was an error in your selection and the returned result was: " & Err.Description
Resume ExitSubProc

End Sub

That should take care of the issues?

 

[MarkSweetland]

It will take care of the issue of displaying an error message when one occurs and gracefully exiting the ofending subroutine. Don't forget the "On error goto ... " at the start of the Sub.

It won't however, prevent the error from occuring. Since you know the problem occurs and why it happens, I would suggest resdesigning the available selections a user would have in order to make a "correct" selection. If the user can only make an employee number selection if the employee option button is selected, then only those valid selections should be acted upon. Or, prevalidate the selections to ensure they meet the criteria of the underlying table data before you make the DB call. I don't think you should wait until the execution of the database fetch routine and then a subsequent error to then inform the user that a bad selection was made. Just my opinion...




Mark

 

[PaulinKC]

Mark, I agree with what you're saying. I've experimented a little last night with the MsFlexgrid since it's not editable and you can force the user to select just a row and not any cell. The only problem with that was, I couldn't find a way to set the datasource of the MSFlexgrid to the MSRDC control at run-time. If I could figure that out, then that error message would disappear completely and I wouldn't have to worry about the user making an incorrect selection.

Is there a way to do that?

 

[MarkSweetland]

You can use the MSRDC control as a datasource for the MSFlexGrid by creating (if not already there) an ODBC Data Source Name (DSN) on your machine. Put the MSRDC control on a form with a Flexgrid. Select the MSRDC and set the datasourcename property to the ODBC DSN you've created, and the select statement you want to use in the SQL property. Select the flexgrid and set the datasource property to the name of the MSRDC. When you run the form, the flexgrid will be populated with the data from the SQL property of the MSRDC.

The drawback in using this control is that you'll have to set up the ODBC DSN on any client machine you want to run the program on.

You can use the flexgrid.MouseCol to determine the column the user has selected and proceed from there.



Mark