Making a SQL Call from a Batch Handler

Ernest Noa

Is it possible to make a SQL call from a batch handler? I've got a line of code that looks like this.

Using conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection

This and the subsequent lines compile, but they do not work. When I run them I get a ‘stored procedure not found’. The stored procedure I am calling does work, I feel like the issue is there really is no OpenAppDBConnection I am grabbing here.

Thanks,

Ernie

Ernest Noa

I got around my initial problem. I am not sure why the syntax is a little different but in an import handler this works:

Using conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection

In a batch handler, I need to change GetRequestContext to RequestContext like this.

Using conn As SqlConnection = Me.RequestContext.OpenAppDBConnection()

With that tiny change out of the way I am able to call the database as expected. My next problem is permissions \\ security related on the new stored procedure I want to call. If I deploy the procedure through TSQL then the procedure is not even found in my code, although I know it exists. If I deploy it through loadspec it shows up just fine but complains I do not have rights to run the procedure.

I have reached out to someone responsible for permissions where I work. I am wondering does this require GRANT EXECUTE on the stored procedure at the database level or is Blackbaud somehow managing this. Things seem to work better when I operate using Blackbaud objects \\ and wrapers. I guess I will find out soon!

Ernest Noa

Continuing to make lots of progress with my project. I have my data call moved into calls, that can be called from multiple batch handlers or import handlers. It needs to be tidied up but it works.

I also have a bulk edit form. Essentially an edit form with two nested collection controls. I can call the class, and make the data call from this form, but it's causing major problems. I do not know where to make my call. I want to make my DB call and refresh one subfield in my collection on my edit form if an only if the user has changed another collection field.

The user changes CONSTITUENTGUID field, then ALT ID Field is auto refreshed on the collection record.

If I place this in the field changed event handler, the data refresh call is called with every keystroke (I think) and the page just spins and becomes non-responsive.

If I place this call in the validated event handler, it works, but only if validation fails.

Seems to me I want to place this call in a field or row loses or changes focus, but I don't see a handler like this.

Most of my goals have been accomplished, I just can figure this last bit out.

Here's the working code, that I am trying to add to the correct place.

For Each row In GROUPMEMBERS.Value
If row.Selected = True Then
Try
Dim myCONSTITUENTID As New Guid
myCONSTITUENTID = Guid.Parse(row.MEMBERID.Value.ToString)

If String.IsNullOrEmpty(myCONSTITUENTID.ToString) = False Then
row.LOOKUPID.Enabled = True
Dim conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection() 'Me.RequestContext.OpenAppDBConnection()
Dim myUSRConstituent As New usr_myConstituent(myCONSTITUENTID, conn)

row.LOOKUPID.Value = myUSRConstituent.LOOKUPID
row.LOOKUPID.Enabled = False
End If
Catch
End Try

End If
Next

Ernest Noa

Looking at some other posts about accessing collection fields I was able to move this around and getting running. Here's my revised code.

Private Sub _groupmembers_ActiveFieldChanged(sender As Object, e As ActiveFieldChangedEventArgs) Handles _groupmembers.ActiveFieldChanged
For Each row In GROUPMEMBERS.Value
If row.IsDirty = True Then
Try

Dim myCONSTITUENTID As New Guid
myCONSTITUENTID = Guid.Parse(row.MEMBERID.Value.ToString)

If String.IsNullOrEmpty(myCONSTITUENTID.ToString) = False Then
Dim conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection()
Dim myUSRConstituent As New usr_myConstituent(myCONSTITUENTID, conn)

row.LOOKUPID.Value = myUSRConstituent.LOOKUPID
row.ISDECEASED.Value = myUSRConstituent.ISDECEASED
End If
Catch
End Try
End If

Next
End Sub

Chris Whisenhunt

Set the following property at the top of your sproc spec.

GrantServiceRolePermission="true"

Ernest Noa

That got it done. Thank you!!!