Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Visual Basic





See Related Forum Messages: Follow the Links Below to View Complete Thread

ADO Parameter FAQ For Single Quotes
I"m trying to use the FAQ FAQ709-1526 to get around the single quote issue.  I have a VB6 frontend with an Access 2000 backend. However, in the
CODEadoComm.CreateParameter("....

Syntax To Pass A Single Record As A Parameter
Is it possible to pass a single record (of a recordset) as a parameter of a subroutine. What would be the syntax?

Example:

Private Sub FooBar(myRecord as Record)
With myRecord
Msgbox !Name
End Sub


Private Sub Main
'Establish recordset called rsMain
Call FooBar(rsMain)
End Sub


Thanks for any suggestions,
Jeff

[out] Parameter Doesn't Return Value To VB
Hello, everybody.
I have very strange problem, please help me.

I use DLL component in ASP script wrotten in C++
one of the method is:
DoSomething([out] long* lVal1, [out] long* lVal2)

it work fine, if client wrotten in C++, but VB or ASP client doesn't receive
value back.
I tried to use retval attribute for lVal2 and it works, but I can't use this
attr for both arguments.

Code in ASP:

Dim lVal1
Dim lVal2
'initialize by anything:
lVal1=5
lVal2=10

Set obj= CreateObject("Myproj.MyInt.1")
obj.DoSomething(lVal1, lVal2)

I receive my initial values, although DoSomething in object of C++ write
other values.

May be it is stupid question, but I lost whole day playing with it, and
didn't successed
What problem of [out] in ASP or VB?

Any help appreciated.
Alex Grudsky
Alex.Grudsky@tabs.co.il

FYI: Using Split To Return A Single Element
Here's a neat little trick. Really, I never thought to do it
before today, but it makes sense that it works.

Lets say you have a string, or a series of strings, and you want
to split them, lets say at "", but you only need the n'th element
from the split. Instead of setting the whole split return value to a variant, {thus returning ALL the elements}, do this instead:


VB Code:
'Lets say we only need the Element in pos 1Private Sub Command2_Click()Dim MyExStr As StringDim MyOutStr As StringMyExStr = "c:DemoStringElement"MyOutStr = Split(MyExStr, "", -1, vbTextCompare)(1) 'Tag your Index to the SplitMsgBox MyOutStrEnd Sub


{Of course, if the ubound of split falls short of the n'th element,
You'd have to through in an Error Handler.}
Its a trivial peice of code, but It could be handy.

Does anybody Else have any VB Tricks?

-Lou

XML Xpath To Return Value Of Single Node?

Hi,

does anyone have a function that if passed in the tag name i'm searching for.  ie  getNode("<Help>") that it will return the value of Help in the XML file?

Many thanks,
JD

Urgent!!!Return All Or One Using A Numeric Parameter
I want to be able to return an ID or All IDs(if in the user interface the All option is selected), it seems that i can only return an specified ID, is there a wildcard for returning all numeric values. The # does not work, i get an type mismatch error...Help Fast!

Parameter Not Return From Stored Procedure
Hi

I put my code vb as:

with cmd
..
..
.Parameters.Append .CreateParameter("@myout", adChar,adParamOutput, 1, myvar)

end with

inside the procedure It put 3 or 5 or other value, but inside vb It return 0 (ZERO)

what happened, It only work when I put
myvar = cmd.parameters(("@myout")

Output Parameter Wont Return! Really Strange
Anyone could help me with this? Im working with SQL Server 2000 and VB 6.0.
The output parameters wont suffer any modification. Here´s the code of the program:

Set Comand = New ADODB.Command

With Comand
.ActiveConnection = cnn1
.CommandText = "cpCargaUsos"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Compania", adSmallInt, adParamInput, 2, 10)
.Parameters.Append .CreateParameter("@Cobertura", adVarChar, adParamInput, 2, "C ")
.Parameters.Append .CreateParameter("@Zona", adSmallInt, adParamInput, 2, 1)
.Parameters.Append .CreateParameter("@Codigo_INFOAUTO", adBSTR, adParamInput, 5, "32233")
.Parameters.Append .CreateParameter("@Comercial", adInteger, adParamOutput, 4, Comercial)
.Parameters.Append .CreateParameter("@Particular", adInteger, adParamOutput, 4, Particular)
.Parameters.Append .CreateParameter("@Taxi", adInteger, adParamOutput, 4, Taxi)
.Parameters.Append .CreateParameter("@Remise", adInteger, adParamOutput, 4, Remise)
End With

Comand.Execute



And the StoredP:


CREATE PROCEDURE cpCargaUsos
@Compania smallint, @Cobertura nvarchar(2), @Zona smallint, @Codigo_INFOAUTO nvarchar(5),
@Comercial int OUTPUT,
@Particular int OUTPUT,
@Taxi int OUTPUT,
@Remise int OUTPUT
as

if exists (select * from usospormodelo where modelo = @codigo_INFOAUTO)
BEGIN
set @Comercial = (select comercial from usospormodelo where modelo = @codigo_INFOAUTO)
set @Particular = (select particular from usospormodelo where modelo = @codigo_INFOAUTO)
set @Taxi = (select taxi from usospormodelo where modelo = @codigo_INFOAUTO)
set @Remise = (select remise from usospormodelo where modelo = @codigo_INFOAUTO)
select * from usospormodelo where modelo = @codigo_INFOAUTO
END


Im passing the parameters as constants so it enters the IF... I know it works on ASP, couse my collegue has tested it. Any ideas? HELP! TNKS.

Return Output Parameter From Stored Procedure.
I have a problem getting an output parameter from a stored procedure in VB6.

I have declared the output parameter as below:

Set pRegId = .CreateParameter("@NonStandardReplyID", adInteger, adParamOutput, , mudtProps.NonStandardReplyID)
.Parameters.Append pRegId

And then i execute the SP as below reading the result set returned by the SP into my ADODB.Recordset object SaveToDatabse. And then i need to pick up the value of the output parameter pRegId. See below:

Set SaveToDatabse = .Execute
mudtProps.NonStandardReplyID = Val(pRegId)

-This doesn't return a value for pRegId

The SP is definetly returning the output parameter (i have checked via SQL profiler what is executed and returned by the SP), but I the value of pRegId is returned as an empty string back in the VB code above, UNLESS i execute the ADODB.Command without reading it back into my recordset. I.e. as below:

.Execute
mudtProps.NonStandardReplyID = Val(pRegId)

-This DOES return a value for pRegId!!

Now I would have concluded that the ADODB.Command object does not allow you to both read in a result set into a recordset object AND pick up the value returned by an output parmeter at the same time - except that i have done exactly this with seemingly identical code and SP output in another class within my application!!! As seen below:

1.declare the output param:
Set pRegId = .CreateParameter("@RegistrationID", adInteger, adParamOutput, , mudtProps.RegistrationID)
.Parameters.Append pRegId

2.Execute the sp and read in the output parm value:
Set SaveToDatabse = .Execute
mudtProps.RegistrationID = Val(pRegId)

--THIS DOES WORK!!

any help here would be appreciated

Creating Parameter Of A Return Value Of A Stored Procedure
Hi all,

How can i create a parameter of a return value of a stored procedure? Thanks!

Visual Basic 6 Return Parameter From Form
Is there a way to return a value from a form in Visual Basic 6?

Search By Worksheet And Range To Return Single Cell
Can anyone please help ? I don't know where to start.

I have to create a form (preferably web to share over an intranet) to search by worksheet name and then by x-range and y-range and give the single cell value as an answer. eg. Excel speadsheet no. 1 :

Syd Mel Per <- ex
Syd 1 2 3
Mel 4 5 6 <- dest
Per 7 8 9

The form should ask ex city and destination city. As above, if it is going from Mel to Per the it should return 8. The input fields should be dropdowns preferably filled from the range. There are a few spreadsheets that are the same, and the selection criteria for them is in the name of the sheet eg. 20%, 30%, 40% which is another dropdown on the form.

How do I do this? Should I use ASP.NET or XML and should I use the script editor for Excel XP or Visual Studio .NET?

Thanks.

Unable To Return Parameter From Stored Procedure Called By VB App To The App
I need to return a value that is a record count/query result that is run at the end of a stored procedure
called by my VB application.
This code does not return parameters from the stored procedure:
DoEvents
    'Set up stored procedure command object
    Set cmdSP = New Command
    With cmdSP
        .CommandText = "sp_My_Stored_Procedure"
        .CommandType = adCmdStoredProc
        .ActiveConnection = connSQL
        .CommandTimeout = 600
        Set rstResults = .Execute
        rstResults.Close
    End With
    Set rstResults = Nothing
    Set cmdSP = Nothing
    Unload Me

I need to return a string with a numeric recored count (ie. result
of a SQL query at the end of the stored procedure in SQL server) to the VB application.
This code does not return anything.
Can anyone help please?

Date Parameter In Recordset.
Hi!
I need to find a recordset with a date parameter. I got this far:

Dim db As Database
Dim Rs As Recordset
Dim DateFom As Date
Dim strInfoRs As String

DateFrom = #1/28/2001#

'this next line works

strInfoRs = " SELECT Bookings.From, Bookings.To From Bookings WHERE Bookings.From >#1 28 2001#"

'this next line does not

strInfoRs = " SELECT Bookings.From, Bookings.To From Bookings WHERE Bookings.From >DateFrom"

Any ideas please about how I can do this?


Thanks

Passing An ADO.Recordset In Parameter
I have a memory leak problem with an application.
The application is taking more in more memory from the system for each item he have to deal with.

I checked for each thing I set in my application and I set them to nothing after the utilisation.

But before I set some recordset to nothing, I use them in different subs and functions, passing them byref. Is there a problem with that ?

I really don't know what to check.. I checked everything I can think of.. if you have a suggestion, post it :P

P.S. I can't attach my project so plz don't ask.

Recordset Parameter Error
Hey!

How do I solve this Error:

No value given for one or more required parameters.

oRs.Open "SELECT DISTINCT ISBN, Title, SellingPrice, Subject FROM SAStock ORDER BY Title WHERE (Country = (?)) AND (Phase = & (?)) OR Subject = & (?)) OR (Title = & (?)) OR (ISBN = & (?))", oConn, adOpenStatic

How do send Parameters using a RecordSet?

Delete Single Recordset They Choose...
Hey how would I go about this:

I have a form that has contacts. It has their name, address, email, phone, and all that. It also has one of those tacky looking data controls of which they use to navigate the recordsets. How would I be able to make a delete button that when they click it, it finds out which recordset they are on and deletes that whole recordset? I can make it delete just fine but it deletes the first one, then the second, ETC. but not the one they are currently viewing.

Single Record Vs Recordset Access
Hello!
I am creating a VB6/MSSQL application, and I am relatively new to both.

In my research on ADO access, all the examples I have seen show a "Select * from <tblname>" without any "Where" clause to subset the data into a Recordset.

Am I mistaken, or can I allow a user to subset the data based on form values, and then record lock them for update so I get no collisions with other users?

Is a Recordset my best option, or should I more safely and successfully only Select/Where the single record I want to update?

Any ideas, or pointers to source are welcome!

Thanks in advance!

Don

Recordset As Parameter (type Mismatch)
Here is a simple project with a reference to the Microsoft ActiveX Data Objects Recordsets 2.5 library.

Why the type mismatch when function a is called.

Private Sub Command1_Click()
Dim rs As Recordset
Set rs = New Recordset
a (rs) 'here is where the type mismatch occurs
End Sub

Function a(rs As Recordset)
MsgBox "a"
End Function

Getting An Output Parameter And A Recordset From A Proc
Does anyone know how to return both an output parameter to a VB6 app using Ado?

I have been trying to do this from a single storedproc, but it seems that I can either get the recordset or the parameter. Not both.

Code looks something like...

    Dim cnlane As ADODB.Connection
    Dim rs As Recordset
    Dim cmd As ADODB.Command
    
    Set cnlane = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    
    cnlane.Open "provider=sqloledb;server=(local);integrated security=sspi;database=lanemessages"

    With cmd
        .CommandText = "dbo.procGetMessages"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@plazahassome", adInteger, adParamOutput)
        .ActiveConnection = cnlane
         Set rs = .Execute
         MsgBox .Parameters("@plazahassome").Value
    End With
    
    cnlane.Close


If I omit Set rs = .Execute then the parameter is returned, if not, no parameter....

I have also tried
rs.ActiveCommand.parameters("@plazahassome").value but this also fails..

Any help is appreciated.


Rob


Concerning Opening Recordset Of Parameter Query
Hi there,
Would appreciate some help with a bit of code that will open the recordset of a parameter query that needs a text entry (persons name from combobox) to run.

cheers
Simon

Parameter Query To Recordset In Access 95 With VB6.0
I developed a VB application with Access 95(DAO3.51). Since the application was bounded with Access 95, there's an update issue. I am not able to convert from QueryDef to Recordset in VB 6.0 with Access 95 because I can't put Parameter query into Access 95. I want to avoid updating from DAO 3.5 to ADO or .NET. Any ideas?

Delete Single Record In After Search Using ADO Recordset
Hi,

I am novice using the ADO recordset to retrieve same records from table, say (id = 123) 2 times of the same record into a datagrid. When i want to delete, will delete both same ID at same time... But i want to delete one by one

This coding first to search:
rs.Find ("ID = '" & Trim(rs2.Fields("ID")) & "'")

Plz help and thanks

paul

Return A RECORDSET...
I was just wondering that is it possible for a function to return a recordset and if yes then how would I use it.

I understand that this how the function will look like.


VB Code:
Private Function MatchingEntry(MainID as Long) as ADODB.RecordSet    MatchingEntry = ??? 'what should I write here to return the recordset I get from the MainIDEnd Function


Hope I am making it clear enough to understand.

Cheers.

Return Recordset From VB
Hi

I am new to vb. i am calling a vb class from asp page

I want to get 20 records by calling a vb function.I know that I need to use a list. How to write a function in VB??

how to access this from asp page??

Thanks
sk5567

Need Help DLL Return Recordset
I just wrote a simple function in DLL to return a recordset to ASP page. But when the asp page call the function, the dll throw an error on the line that I set the return recordset. Here is the sample code. (The error number that return is 0.!!)

*** VB ***
Public Function fn_getUser(rsUser As Variant) As String
    
    Dim ConnObj As ADODB.Connection
    Dim cm As ADODB.Command
    Dim rs As ADODB.Recordset
    
    On Error GoTo Errfn_getUser
    
    Set ConnObj = New ADODB.Connection
    Set cm = New ADODB.Command
    Set rs = New ADODB.Recordset
    
    ConnObj.ConnectionString = sConnStr 'Constanct value
    ConnObj.Open
    
    With cm
        .ActiveConnection = ConnObj
        .CommandText = "sp_getUser"
        .CommandType = 4
    End With
    rs.CursorLocation = adUseClient
    rs.Open cm, , adOpenForwardOnly, adLockReadOnly
    
    'This is where the problem is. The stored procedure always returns records, but the return record never get set. Moreover it return an error 0 here.
    Set rsUser = rs
    
    ConnObj.Close
    Set cm = Nothing
    Set rsGroup = Nothing
    Set ConnObj = Nothing
    
Errfn_getUser:
    fn_getUser = Err.Number & ": " & Err.Description
End Function
'******************

'***ASP Page********
dim obj, rsUser, msg
set obj=server.CreateObject ("VBCOM_General.IAdmin")
set rsUser= server.CreateObject("ADODB.recordset")
msg = obj.jobscanCache(rsUser)

response.write msg    
'******************

'****browser display**
0:
'*****************

If i try to display data from the recordset

response.write rsUser("name")

The page will return an error say the recordset is close.

I wonder that I did any wrong on the dll code for not. Anyone have any idea.. Thank you..


Return A Recordset
can u ppl plz tell me how to return a recordset object from a function in a module to the calling sub procedure

How To Return Recordset?
I am trying to write a COM object that is used to access a database and perform any necessary logic before returning a recordset to vbscript on an asp page. The problem I encountered is that I don't know what return type to use for the ADO recordset.

While I am writing the COM component in ATL, I am including the post here, because I thought that this group might have a better idea of the return type that I would be looking for in the vbscript.

Thanks in advance.

Parameter Based Recordset In Crystal Report
hi,

I am using crystal 8.5 with VB6 and MS Access. I have made a .rpt file and using a recordset i am able to see the report populated with those records from recordset properly.
Now i want to make use of parameter fields so that user will be prompted for it and once he selects / enters the parameter the report should be populated with those records only.The user will be asked for 2 parameters - 1) Date and 2)Number and then the recordset should be based on these 2 parameters . I have created these 2 parameters in .rpt file but dont know how to use them in my "SELECT" query ... Any help ?

Thnx.

ADO Recordset As Procedure Parameter? (Visual Basic 6)
Hello,

I am a Visual Basic programmer. While working with master-detail forms I need to use SQL Server 2000 Procedure for child recordset to improving the performace. Is there any option for passing a ADO recordset as parameter.

Thanks in advance
Kiran


Aum Namah Shivaya

One Form Showing Data From Three Tables Using Single Recordset
I need to display data on a form from a matching record (based upon month) from two tables from the same database. I believe that one recordset can do this using a SELECT...INNER JOIN statement. This is my code so far:

Dim RS1 As Recordset
Set DB1 = DBEngine.Workspaces(0).OpenDatabase("C:PROGRAM1.MDB")
Set RS1 = DB1.OpenRecordset("INCOME", dbOpenDynaset)

Select INCOME.month, EXPENSES.month
from INCOME inner join EXPENSES on INCOME.MONTH = EXPENSES.MONTH

Am I on the right path? What should my code look like to accomplish this task?

Return Recordset In A Function
I am trying to pass a record set back thru the function (e.i. tempvar = getusers(1)). I want a Variant/Object/Recordset but i am getting a Variant/Object/Fields. Here is my code...what am i doing wrong.

Code:

Public Function GetUsers(Optional sid) As Recordset
Set t = New ADODB.Recordset
If IsNumeric(sid) Then
t.Open "select * from [user] where site = " & sid, cs
Else
t.Open "select * from [user]", cs
End If
Set GetUsers = t
t.Close
End Function

Return A Recordset From A Function
I am trying to write a function that will return a recordset:

Property Get QueryResult() As Recordset

Dim ado as New Recordset

'Other declarations and execution of the query here

QueryResult = ado

End Property


The line 'QueryResult = ado' gives me an error: 'Inalid use of Property'. Can someone tell me how I can pass this recordset as a variable ?

Thanks

Is It Possible To Return A Recordset Using ADO 2.5 And Oracle 7.34
Pretty much the subject says it all. Just wondering if it is possible to return a recordset from a stored procedure using ADO 2.5 and Oracle 7.34. If anyone could provide a small snippet of the VB syntax to call the storedproc that would be great! Thanks for the help!

Running Parameter Query On A Recordset With Multiple Records
I am trying to run a query on a recordset with 300 records. the 300 records are customercodes, the customercodes are parameters in a query that appends data from one table to another. The code i am using is below but it only runs the query on the first customercode in the recordset ignoring all others
With adoRec

Do Until .EOF

run query here

.MoveNext

Loop
End With

can anyone suggest why this wont work and how I could make it work? Any thoughts would be apreciated
Thanks

ADO: Output Parameter Value From Stored Proc Recordset Results

ADO: Output Parameter value from Stored Proc Recordset Results

Hi,

I am working with ADO to retrieve recordsets from SYBASE using stored procedures. The stored procedures also return an output parameter.

The problem I am having is that the value of the output parameter is not being exposed until after the recordset is cycled through and closed, however I need the output parameter value to handle the recordset as I loop through it. This used to work fine with RDO but not with ADO for some reason.

Any ideas on work arounds or fixes?

thanks,
Barry

---------------------

Copy Recordset Into New Excel Doc, And Run Macro From Another File With Parameter
Hi All VB/VBA experts....

i need to do this project...
my client is needing reports in excel, rather than crystal report or else, because maybe they want to manipulate or do something with the reports..

but the problem is recordset copied to excel don't look nice(still need to be arrange and formatted).
the solution is maybe by running macros(from another excel files which already created first) to the new document. also the macros can having parameters like date, row number, text, etc.

but im still blank with those kind of proccess..
maybe some one could give me an example how to do that..


especially on "Creating Parameters in Macro" saving it, and then "Call The Macros and implement it on the new excel document"


thanks alot..

regards.

Capture SP Return Value & ADODB.Recordset
I have a sp:

Code:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = '_test' AND type = 'P')
DROP PROCEDURE _test
GO
CREATE PROCEDURE _test
AS
SELECT '<?xml version="1.0"?>
<root>
</root>'

return 1
GO
and an vb6 client:

Code:
Dim g_strConn As String
Dim cmd As ADODB.Command
Dim objRst As ADODB.Recordset
Dim prm0 As ADODB.Parameter

g_strConn = "Provider=SQLOLEDB;Password=" & _
"password" & ";User ID=" & _
"sa" & ";Initial Catalog=" & _
"db" & ";Data Source=" & _
"pc" & ";Network Library=DBMSSOCN;"

Set cmd = New ADODB.Command

cmd.CommandTimeout = 3000
cmd.CommandText = "_test"
cmd.CommandType = adCmdStoredProc


Set prm0 = cmd.CreateParameter("RETURN", adInteger, adParamReturnValue)
cmd.Parameters.Append prm0

cmd.ActiveConnection = g_strConn
Set objRst = cmd.Execute

If Not objRst.EOF Or Not objRst.BOF Then
'Move to the first record
'objRst.MoveFirst '--> hit erorr if uncomment this

'Lets move through the records one at a time until we reach the last record
'and print out the values of each field
'Do

MsgBox CStr(objRst.Fields(0))
'objRst.MoveNext
'Loop Until objRst.EOF = True

objRst.Close

lRet = cmd.Parameters("RETURN").Value

Set objRst = Nothing
Set cmd = Nothing
End If

MsgBox lRet
Why I cannot use "'objRst.MoveFirst" if my sp might return more than one row? I get:
"Rowset position cannot be restarted."

Recordset Object Does Not Return All Records
Hi,

I have this code which seems to work nicely for loading an Access Database and displaying the contents in a List Box for processing :

Code:
Option Explicit

Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset 'this is the recordset
Private ptrPos As Integer
Private newPos As Integer


Private Sub Form_Load()

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Program FilesShipDBShip Database.mdb;" & _
"Mode=Read;Persist Security Info=False"
cn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient

rs.Open "SELECT * FROM ShipDatabase", cn, adOpenKeyset, adLockPessimistic, adCmdText

rs.Sort = "ShipName"
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF = True
lstShipName.AddItem rs!ShipName
cboIMO.AddItem rs!IMONum
rs.MoveNext
Loop

rs.MoveFirst
lstShipName.Text = rs!ShipName

FillFields

End Sub

My problem is that my Access database has 603 records, but the code above returns and displays the first 600 (The recordcount method confirms only 600 records are seen).

Any ideas on why the records over 600 do not appear?

Thanks for any help
Dave


Edit by Moderator:
Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

Return ADODB.Recordset From A Vb Function
i need 2 return a ADODB.Recordset from a function.....

if i do it like any other variable type then i get a error "object required"

can any one tell me how can i do this???

SQL Problem: Incorrect Recordset Return
I need to create an SQL Statement that will Determine the Tax, base from the Gross Income entered by the user. (I am using MSAccess) The data coming from TaxTable is like this:

GrossStart --- GrossEnd---- Tax
0 ---------- 200.99------- 5.00
201---------500.99-------10.00
600---------999.99-------15.00
.. .. ..
All the fields (GrossStart, GrossEnd and Tax) are Double
The user will input the Gross Income to the textbox txtGrossIncome

My SQL Statement is this:
SELECT Tax FROM TaxTable WHERE ('" & CDbl(txtGrossIncome) & "' BETWEEN CDbl(GrossStart) AND CDbl(GrossEnd))

The returned recordset (rs!Tax) is not valid

Please help Me ..

Return A Recordset From Data Tier?
Hi. This is my first post, so please bear with me...

I have a VB6.0 app that uses a SQL Server 2000 database. I've tried to tier the app with separate UI, business, and data tiers.

Here is the problem:

A function(LoadDefaultView) in the business tier calls a function(GetDefaultView) in the data tier to retreive a recordset from the database.

LoadDefaultView does get the recordset without any problems BUT how do I pass the recordset back to the business tier function(LoadDefaultView) AND close the database connection at the data tier?

*The error I get is at the end of this function where I set the function = to the recordset and then close the connection. Back in the business tier function I get an error, something to the effect that" this operation can't be performed when the object is closed"..


Thanks for your time


Code:
Public Function GetDefaultView(ByVal sDataSource As String, ByVal sDatabase As String, _
ByVal sUserID As String, ByVal sUserPwd As String) As ADODB.Recordset

'PURPOSE: To return a default set of records to populate the MSFlexGrid so the user has
' something to look at when the app loads.
'IN/OUT : none.

On Error GoTo ErrorTrap

Dim oRSet As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command

Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
Set oRSet = New ADODB.Recordset

'Establish database oConnection
modDBSQL.EstablishConnection oConn, sDataSource, sDatabase, sUserID, sUserPwd

'set command properties
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "sp_GetDefaultView"

oRSet.Open oCmd, , adOpenStatic, adLockReadOnly

If oRSet.RecordCount > 0 Then
Set GetDefaultView = oRSet
modDBSQL.CloseConnection oConn
End If

Set oCmd = Nothing
Exit Function

ErrorTrap:

err.raise err.number

Set oRSet = Nothing
modDBSQL.CloseConnection oConn

End Function

ProgressBar While Waiting For Recordset Return
Hi,

How do you make a progress bar while waiting for recordset return?


VB Code:
SQL = "Select......Set rs = cn.OpenResultset(SQL, rdOpenKeyset, rdConcurRowVer)ProgressBar1.Min=0Progressbar1.Max=rs.RowCountProgressBar1=0......

Get Return Value And Recordset From Stored Proc
I am trying to return a value to my vb app. When I run it in query analyzer I can get the return value and the recordset, but when I try in my vb app I get an empty value.

This is my stored proc:

CREATE PROCEDURE sp_FindUser
            @UserName varChar(30)
    AS

    DECLARE     @Found int

    SELECT
            UserName,
            [Password],
            PremScreen,
            AdminScreen,
            AdminUser

    FROM        tblFJ_Users

    WHERE    UserName = @UserName

    IF @@ROWCOUNT > 0
        BEGIN
        SELECT @Found = 1
        --PRINT @Found
        RETURN @Found
        END

    ELSE
        BEGIN
        SELECT @Found = 0
        --PRINT @Found
        RETURN @Found
        END


This is the vb code calling the procedure

Public Sub chkUser()

Dim rsChkUser As New ADODB.Recordset
Dim params As ADODB.Parameters

On Error GoTo Err_ChkUser

    Set cmd.ActiveConnection = FJames_ADOCONNECT
    
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "sp_FindUser"
    End With
    
    Set params = cmd.Parameters

    params.Append cmd.CreateParameter("Found", adInteger, adParamReturnValue, , -1)
    params.Append cmd.CreateParameter("User", adVarChar, adParamInput, 30, m_strChkUser)

    Set rsChkUser = cmd.Execute
    'cmd.Execute
    Debug.Print "Param value = " & cmd.Parameters("Found").Value
    
    If cmd("Found").Value = 0 Then
        MsgBox "User not found"
    ElseIf cmd("Found").Value = 1 Then
        m_strChkUser = rsChkUser!UserName
        m_strPassword = rsChkUser!Password
        m_strAdminUser = rsChkUser!AdminUser
        m_strAdScreen = rsChkUser!AdminScreen
        m_strPremScreen = rsChkUser!PremScreen
        
End If

Set cmd = Nothing

    
Exit Sub

Err_ChkUser:
    Call ErrorTrap("clsLogin!ChkUser", True)
    
End Sub

If I change the following lines so that the Set line is not called I get the return value but not the recordset
    'Set rsChkUser = cmd.Execute
    cmd.Execute

Has anyone got any ideas

Thanks

Dave

How To Return A ADODB.Recordset Variable
what is the syntax for returning a recordset variable?
i got a function as

Quote:





Function QueryDB(sSqlStatement As String) As ADODB.Recordset
Dim RecordsetDB As ADODB.Recordset
Dim ConDB As ADODB.Connection
Dim sCol1 as String
Dim sCol2 as String

Set ConDB = New ADODB.Connection
Set RecordsetDB = New ADODB.Recordset

ConDB.CursorLocation = adUseClient
ConDB.Open GetConnection

RecordsetDB.Open sSqlStatement, ConDB, adOpenStatic, dLockOptimistic

With RecordsetDB
If (.BOF And .EOF) Then
MsgBox "no record found"
Else
Do Until .EOF
sCol1 = .Fields("Col1")
sCol2 = .Fields("Col2")
.MoveNext
Loop
End If
End With

RecordsetDB.Close
ConDB.Close
Set RecordsetDB = Nothing
Set ConDB = Nothing

End Function






i plan to call this funciton and returning a recordset, so that i can take the recordset and loop in others function. i dun want direct loop in this function.
i try to direct equal but error occur

Quote:





QueryDB = RecordsetDB





how can i return the recordset when this function is called?

Return Recordset Form Sql Server [SOLVED]
Hi

How do I read the result of the following stored procedure using ADO in VB


CREATE PROCEDURE Test
AS

declare @Temp table (num int, sname varchar(100))
insert into @temp values(1, '001')
insert into @temp values(2, '002')
insert into @temp values(3, '003')
select * from @Temp
GO



Thanx in advance

What Is The Syntax To Return Recordset For Report's Recordsource
How to set the report's recordsource in VB by the recordset returned from the stored procedures?

In form's open form event I have
sqlstr = "SELECT * FROM tblBag_results WHERE sampling_data_id = " & sampling_id & " Order by bag_num"
Me.RecordSource = sqlstr

I need something like
report's open report event

With add_bag_results
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "spSampling_add_bag_results"
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@lotnum", adInteger, adParamInput, 4, lot_n)
Set rs_add_bag_results = .Execute
End With
''*****Need help here
report's recordsource = rs_add_bag_results

MSHflexgrid Problem When Recordset Return Null
 Hi All,

  I have problem with MSHFlexgrid ,if the recordset is null then the cursor not perform anything i.e
if recordset return null then click or double click events on the mshflexgrid its not performing any action,
i have to close the form and open it again.

below is my code

vstr = " select * from emp"
Set adoRS = New ADODB.Recordset
adoRS.Open vstr, db, , adLockOptimistic
Set MSHflexgrid.DataSource = adoRS
adoRS.Close
Set adoRS = Nothing

Thanks in advance,
I really appreciate if any help.

Exec Stored Procedure To Return ADODB.Recordset
I created a stored procedure in SQL that outputs a record. I need
to have a function in vb exec the sp and return a recordset to use
in my program.
Stored procedure...

Code:
IF EXISTS(SELECT Name FROM SysObjects
WHERE Name = 'sp_RRGetTask' AND Type = 'P')
DROP PROCEDURE sp_RRGetTask
GO

CREATE PROCEDURE sp_RRGetTask
@Task_No nvarchar(8) OUTPUT
AS
SELECT *
FROM Tasks
WHERE Task_No = @Task_No
GO
Function in vb to execute it.

VB Code:
Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String) As ADODB.Recordset'<RR 09/16/2003 - VB/OUTLOOK GURU>    On Error GoTo No_Bugs        Dim oCM As ADODB.Command    Dim lRecs As Long        Set oCM = New ADODB.Command    oCM.ActiveConnection = oCnnS    oCM.CommandType = adCmdStoredProc    oCM.CommandText = sp_Name        'NEED TO CHANGE FROM HERE???    oCM.Execute lRecs    If lRecs > 0 Then        gbExec_SPrs = True    Else        gbExec_SPrs = False    End If    Exit Function    No_Bugs:        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName    End Function
This is my code for executing stored procedures that don't return anything.
How can I complete it to return an ADODB.Recordset?

Copyright © 2005-08 www.BigResource.com, All rights reserved