• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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?
0
wjshore
Asked:
wjshore
  • 4
  • 2
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now