Convert SQL into VB6 Function

Hope this makes sense;  The following is a piece of SQL code.  
 
PARAMETERS [Enter MovieID:] Long;
SELECT DISTINCT m.[MovieID], m.[MovieName]
FROM Movies AS m INNER JOIN [SELECT [Actor1] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:] UNION SELECT [Actor2] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:] UNION SELECT [Actor3] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:] UNION SELECT [Actor4] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:] UNION SELECT [Actor5] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:]]. AS u ON m.[Actor1] = u.[Actor] OR m.[Actor2] = u.[Actor] OR m.[Actor3] = u.[Actor] OR m.[Actor4] = u.[Actor] OR m.[Actor5] = u.[Actor];

It is a parameter select query which asks for the value 'MovieID' and then performs the query.    I need to convert the code into a VB6 Function which requires the input value (a number representing MovieID) and then returns the list of movies - just like the above SQL.

Anybody know how to do this?
wjshoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SvenTech Lead Web-DevelopmentCommented:
Something like this?

Function GetMovieSQL(byVal MovieID)

GetMovieSQL = "SELECT DISTINCT m.[MovieID], m.[MovieName] FROM Movies AS m INNER JOIN [SELECT [Actor1] AS [Actor] FROM Movies WHERE [MovieID] = " & MovieID & " UNION SELECT [Actor2] AS [Actor] FROM Movies WHERE [MovieID] = " & MovieID & " UNION SELECT [Actor3] AS [Actor] FROM Movies WHERE [MovieID] = [Enter MovieID:] UNION SELECT [Actor4] AS [Actor] FROM Movies WHERE [MovieID] = " & MovieID  & " UNION SELECT [Actor5] AS [Actor] FROM Movies WHERE [MovieID] = " & MovieID & "] AS u ON m.[Actor1] = u.[Actor] OR m.[Actor2] = u.[Actor] OR m.[Actor3] = u.[Actor] OR m.[Actor4] = u.[Actor] OR m.[Actor5] = u.[Actor]"

End Function

And then the rest of the code has to be somthing klike this (in short):

1. Open a SQL connection
2. Open a recordset: rs.open GetMovieSQL(1014) ...
3. do whatever you want
4. Close recordset and connection
0
wjshoreAuthor Commented:
Thanks for trying.  Code won't compile.  Entering as MovieID as Call GetMovieSQL(13), where 13 is a valid ID, produces nothing.
0
SvenTech Lead Web-DevelopmentCommented:
"Call GetMovieSQL(13)" will not return anything!

Should be something like: "strSQL = GetMovieSQL(13)"

Then strSQL will be the SQL syntax to call by recordset.

You can check this with "MsgBox strSQL" or sth. like that.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

wjshoreAuthor Commented:
Darth:
Ease up on the exclamation points.  Makes you seem as though your light sabre's too short.  That said, it still appears we're not communicating.  Your function consists entirely of a string called GetMovieSQL.  How is it called?  You suggest another string called strSQL.  Can you please show all the code which allows the SQL to return a recordset with the input value of MovieID?   If I knew how, I wouldn't have asked.  Thanks,

Warren Shore
0
SvenTech Lead Web-DevelopmentCommented:
I thought you know the basics of programming some simple recordsets ;)

Dim objConn as ADODB.Connection
Dim objRs as  ADODB.Recordset
Dim ConnectionString as String

ConnectionString = "Data Provider=MSDASQL;Driver={SQL Server};Server=MySQLServerHostOrIP;UID=USER;PWD=PASSWORD;DATABASE=NAMEOFDATABASE"
   
Set objConn = New ADODB.Connection
   
    With objConn
        .Provider = "MSDataShape"
        .ConnectionTimeout = 15
        .CommandTimeout = 1200
        .Open ConnectionString
    End With

    Set objRs = New ADODB.Recordset
    objRs.Open GetMovieSQL(13), objConn, adOpenStatic, adLockOptimistic

    MsgBox objRs("MovieName")

    objRs.Close
    Set objRs = Nothing

objConn.Close
Set objConn = Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SvenTech Lead Web-DevelopmentCommented:
.ConnectionTimeout is the timeout to establish the connection to database.
.CommandTimeout is the timeout in seconds to call the sql statement.
Recordset will be opend ReadOnly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.