SQL stored procedure to ASP recordset via VB dll

Results 1 to 5 of 5

Thread: SQL stored procedure to ASP recordset via VB dll

  1. #1
    Julie Watson Guest

    Default SQL stored procedure to ASP recordset via VB dll

    I’ve been working on this for some time and cannot seem to figure this out. I guess you can say I’m stumped. I’m an experienced VB programmer, but fairly new to SQL Server and Visual Interdev. I have the VS 6.0<BR><BR>I have created a basic table in SQL 7.0. It has a few fields for FirstName, LastName, HomePhone, CellPhone, and Pager. It also has an autoindex field as an integer. I created 4 stored procedures. One to return all records, one to return records filterd by last name, one to return a record by the index number, and one to update a record using the index. These work fine.<BR><BR>Next I created a VB dll. I eventually plan to put it in Transaction server, but for now it is registered on my local machine. I created a function to return records using the stored procedure that filters by last name. I also created a public property in which I store the last name I want to search for. Here is my VB dll code:<BR><BR>&#039local variable(s) to hold property value(s)<BR>Private mvarFilterName As String &#039local copy<BR>&#039local variable(s) to hold property value(s)<BR><BR>Public Function GetPhoneList() As String<BR> GetPhoneList = mvarFilterName & " returned"<BR>End Function<BR>Public Function GetARS() As ADODB.Recordset<BR>Dim dEnv As DataEnvironment1 &#039Our data environment<BR>Dim newStr As String<BR>Dim myset As New ADODB.Recordset<BR><BR>newStr = mvarFilterName<BR><BR>&#039Create an instance of the data environment<BR>Set dEnv = New DataEnvironment1<BR><BR>&#039Execute the data environment commane<BR>dEnv.dbo_usp_filteredlist newStr<BR><BR>Set myset = dEnv.rsdbo_usp_FilteredList<BR>mvarFilterName = myset!Firstname<BR>Set GetARS = myset<BR>dEnv.Connection1.Close<BR><BR><BR>End Function<BR>Public Function GetRS() As String<BR>Dim dEnv As DataEnvironment1 &#039Our data environment<BR>Dim newStr As String<BR><BR>newStr = mvarFilterName<BR><BR>&#039Create an instance of the data environment<BR>Set dEnv = New DataEnvironment1<BR><BR>&#039Execute the data environment commane<BR>dEnv.dbo_usp_filteredlist newStr<BR><BR>strfirstname = dEnv.rsdbo_usp_FilteredList(1)<BR>GetRS = strfirstname<BR>Set mvarReturnRS = dEnv.rsdbo_usp_FilteredList<BR>dEnv.Connection1.Cl ose<BR><BR><BR>End Function<BR>Public Property Let FilterName(ByVal vData As String)<BR>&#039used when assigning a value to the property, on the left side of an assignment.<BR>&#039Syntax: X.FilterName = 5<BR> mvarFilterName = vData<BR>End Property<BR><BR><BR>Public Property Get FilterName() As String<BR>&#039used when retrieving value of a property, on the right side of an assignment.<BR>&#039Syntax: Debug.Print X.FilterName<BR> FilterName = mvarFilterName<BR>End Property<BR><BR><BR><BR>Finally, I created the asp page in VI:<BR>&#060;%@ Language=VBScript &#037;&#062;<BR>&#060;HTML><BR>&#060;HEAD><BR>&#06 0;META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"><BR>&#060;/HEAD><BR>&#060;BODY><BR>&#060;OBJECT ID="EngPhoneObj" CLASSID="CLSID:F88DBC87-2FB8-11D3-B440-00801930319C"><BR>&#060;/OBJECT> <BR>&#060;INPUT TYPE="SUBMIT" NAME="btn1" VALUE="Click to test"><BR><BR>&#060;SCRIPT LANGUAGE=vbscript><BR>sub btn1_OnClick()<BR>dim reclist <BR><BR>set objEngPhone=document.all("EngPhoneObj")<BR>msgbox "it worked"<BR>objengphone.FilterName = "WATSON"<BR>MsgBox objengphone.FilterName<BR>MsgBox objengphone.GetPhoneList<BR>MsgBox objengphone.GetRS()<BR>msgbox "Phase 2"<BR><BR>****ERROR OCCURS RIGHT HERE****<BR><BR>set reclist=objengphone.GetARS<BR>reclist.open<BR>msgb ox "here we go"<BR>if reclist.eof then msgbox "eof" else msgbox reclist(1)<BR><BR>end sub<BR><BR>&#060;/SCRIPT><BR>&#060;P>&nbsp;&#060;/P><BR>&#060;/BODY><BR>&#060;/HTML><BR><BR><BR>It is all pretty straightforward, but I’m missing something. I think it is in how I am declaring the adodb.recordset. My goal is to get a recordset that I can work with in the ASP page. I get an error that says “The application requested an operation or an object with a reference to a closed or invalid connection object.”<BR><BR>I would appreciate any help or directions here. <BR><BR>My email is Julie.Watson@sdms.usa.xerox.com.<BR><BR>Thanks!<BR ><BR>Julie<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    86

    Default RE: SQL stored procedure to ASP recordset via VB dll

    Before you set your function to = new recordset you need to disconnect the recordset. That is: set myset.ActiveConnection = Nothing. You also have to make sure that the .CursorLocation of the myset recordset is adUseClient, before opening the recordset.<BR><BR>Hope this helps!!!

  3. #3
    Julie Watson Guest

    Default RE: SQL stored procedure to ASP recordset via VB dll

    Lee - I appreciate your help, but I&#039m still confused. I get the data into the VB recordset just fine. I just can&#039t get the recordset from the VB component to a recordset object in ASP. If I just have my VB component return a text field from the recordset it works great. Are you saying that I need to make these changes to the VB component or the ASP page?<BR><BR>Thanks, Julie

  4. #4
    Join Date
    Dec 1969
    Posts
    86

    Default RE: SQL stored procedure to ASP recordset via VB dll

    The error you are getting is telling you that the connection assosciated with the recordset (in VB) is closed. Therefore you need to disconnect the recordset before passing it from the function so that no reference to the connection is needed.<BR><BR>Note: If you are going to implement in MTS, disconnected recordsets are a must anyway.<BR><BR><BR>So, you need to make the changes to the VB Component.<BR><BR>This is your function code. I have added the line to show you how to disconnect the recordset and also explained the client-side cursor setting.<BR><BR><BR>Public Function GetARS() As ADODB.Recordset<BR>Dim dEnv As DataEnvironment1 &#039Our data environment<BR>Dim newStr As String<BR>Dim myset As New ADODB.Recordset<BR><BR>newStr = mvarFilterName<BR><BR>&#039Create an instance of the data environment<BR>Set dEnv = New DataEnvironment1<BR><BR>&#039Execute the data environment commane<BR>dEnv.dbo_usp_filteredlist newStr<BR><BR>Set myset = dEnv.rsdbo_usp_FilteredList<BR>mvarFilterName = myset!Firstname<BR><BR>&#039>>>>>> Add this Line<BR>Set myset.ActiveConnection = Nothing<BR><BR>Set GetARS = myset<BR>dEnv.Connection1.Close<BR><BR>End Function<BR><BR>In the DataEnvironment&#039s dbo_usp_filteredlist commands properties... (Right-click on the command in the DataEnv window)<BR><BR>Go to the Advanced Tab and check the "Cursor Location" is set to 3 - Use client-side cursors.<BR><BR>I hope this helps make it clearer.....


  5. #5
    Join Date
    Dec 1969
    Posts
    170

    Default Lee - thanks for your help!

    I finally got it to work. Hope I can return the favor some day.<BR><BR>Julie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •