HOWTO: Use Temporary Tables in SQL Server from Visual Basic 3.0

 [ODBC]
 ConnectionTimeout = 1  ; in seconds, default=600
				

  db1.close
  start = Timer
  While Timer < start + 2
  Wend
  FreeLocks
				

Dealing with a Bug in the ODBC Driver for SQL Server (SQLSRVR.DLL)

Setting Tab Stops by Using Dialog Box Base Units

   DlgWidthUnits = LOWORD(GetDialogBaseUnits()) / 4
				

Steps to Create the Example Program

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add three list boxes (List1, List2, and List3), one text box (Text1) and one command button (Command1) to the form. Size and placement will be set by the code in form load procedure.
3. Set the following properties at design time:

      Control   Property     Setting
      --------------------------------------------------
      Text1     MultiLine    True
      Text1     Scrollbars   3 - Both
      Command1  Caption      "Query Using Temp Tables"
   
   					

4. Add the following code to the Form Load event: NOTE: Some lines of code are wrapped to fit into the article space. Be sure to correct all lines so that each complete code statement is on one, single line.

      Sub Form_Load ()
   
         Dim fwidth As Integer, fheight As Integer
         ' Position and size the form regardless of screen resolution:
         Me.Move 0, 0, screen.Width, screen.Height * .89
   
         fwidth = Me.ScaleWidth
         fheight = Me.ScaleHeight
         ' Position and size the controls regardless of screen resolution:
         list1.Move 0, 0, fwidth / 3, fheight / 2
         list2.Move fwidth / 3, 0, fwidth / 3, fheight / 2
         list3.Move 2 * fwidth / 3, 0, fwidth / 3, fheight / 2
         text1.Move 0, list1.Height, fwidth
   
         ' Size and center the command button:
         ' Enter the following three lines as one, single line of code:
         command1.Move (fwidth - Me.TextWidth((command1.Caption))) / 2,
            list1.Height + text1.Height, Me.TextWidth((command1.Caption)),
            2 * Me.TextHeight((command1.Caption))
      End Sub
   
   					

5. Add the following code to the Command1_Click event:

      Sub Command1_Click ()
         Dim NL As String, sql As String
         ' Line labels are included on data operations to help locate the
         ' statements where errors may occur.
         On Error GoTo localerrhandler
         ' Define newline string:
         NL = Chr$(13) & Chr$(10)
         ' Open a connection to a SQL Server database:
         ' Enter the following two lines as one, single line of code:
         Set db1 =
            OpenDatabase("", 0, 0,"odbc;dsn=texas;database=pubs;uid=sa;pwd=;")
   
         ' Define first preliminary query, putting the result into temp table
         ' #sqltemp1. Return a list of all the titles with their associated
         ' title_id, price last name, and au_id fields. If the title has
         ' multiple authors, return only the first author listed in title
         ' author table:
         ' Enter the following two lines as one, single line of code:
         sql = "select titles.title, price, au_lname, authors.au_id,
            titles.title_id"
   
         sql = sql & " into #sqltemp1 "
         sql = sql & " from authors, titles, titleauthor, titleauthor"
         sql = sql & " where authors.au_id = titleauthor.au_id "
         sql = sql & " and titles.title_id=titleauthor.title_id"
         sql = sql & " and titleauthor.au_ord= 1"
   
         ' Create a snapshot to execute the action query:
      1001 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Create a snapshot to check the results of the query:
         sql = "select * from #sqltemp1"
      1002 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Call Sub procedure to fill list box:
         Fill_List list1
   
         ' Define second preliminary query, putting the result into temp table
         ' #sqltemp2. Return a list of all store names with their associated
         ' stor_address, title, title_id, price, ytd_sales fields, and show
         ' which had year-to-date sales greater than $10,000.00
         ' for particular titles.
   
         sql = "SELECT stor_name, stor_address, sales.title_id,"
         sql = sql & " title, ytd_sales, price"
         sql = sql & " into #sqltemp2 "
         sql = sql & " from sales, stores, titles"
         sql = sql & " where titles.title_id=sales.title_id"
         sql = sql & " and sales.stor_id=stores.stor_id"
         sql = sql & " and titles.ytd_sales > 10000.00"
   
         ' Create a snapshot to execute the action query:
      1003 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Create a snapshot to check the results of the query:
         sql = "select * from #sqltemp2"
      1004 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' Call Sub procedure to fill the list box:
         Fill_List list2
   
         ' Define a simple query that joins
         ' the two temp tables, #sqltemp1, #sqltemp2:
         sql = "select #sqltemp1.au_lname, #sqltemp2.ytd_sales,"
         sql = sql & " #sqltemp2.stor_name, #sqltemp2.title"
         sql = sql & " from #sqltemp1 , #sqltemp2"
         sql = sql & " where #sqltemp1.title_id= #sqltemp2.title_id"
   
         ' Create a snapshot to fetch the results of the query:
      1005 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' call the Sub procedure to fill the list box:
         Fill_List list3
   
         ' To demonstrate the fact that temp tables
         ' on the SQL Server only persist during the
         ' lifetime of an active session or connection,
         ' execute the following code, and then attempt
         ' to access the temp tables. See the text of
         ' this article for more info.
         '
         '   Set sn = Nothing
         '   1011 : db1.Close
         '   start = Timer
         '   While Timer < start + 2
         '   Wend
         '   FreeLocks
         '
         '   conn$ = "odbc;dsn=texas;database=pubs;uid=sa;pwd=;"
         '1006 : Set db1 = OpenDatabase("", 0, 0, conn$)
   
         ' Clean up temp tables in case you need to re-create
         ' them while this current connection is alive.
         ' If you close the connection, SQL Server will
         ' destroy the temp tables automatically
         sql = "drop table #sqltemp1"
      1007 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         sql = "drop table #sqltemp2"
      1008 : Set sn = db1.CreateSnapshot(sql, DB_SQLPASSTHROUGH)
   
         ' void the pointer
         Set sn = Nothing
      1009 : db1.Close
   
         Exit Sub
         localerrhandler:
         text1 = text1 & "Error number " & Err & " on line labeled " & Erl
         text1 = text1 & " " & Error$ & NL
         Resume Next
   
      End Sub
   
   					

6. Add the following code to the general declarations level of the form:

      Option Explicit
      ' Enter the following Declare statement as one, single line:
      Declare Function SendMessage Lib "User" (ByVal hWnd As Integer,
         ByVal wMsg As Integer, ByVal wParam As Integer,
         lparam As Any) As Long
      Declare Function GetDialogBaseUnits Lib "User" () As Long
   
      Const WM_USER = &H400
      Const LB_SETTABSTOPS = WM_USER + 19
      ' Add a horizontal scrollbar to the list boxes:
      Const LB_SETHORIZONTALEXTENT = (WM_USER + 21)
      Const DB_SQLPASSTHROUGH = 64
   
      Dim biggest_value() As Integer
      Dim tabstops() As Integer
      Dim db1 As database
      Dim sn As snapshot
   
      Function LOWORD (lparam As Long) As Integer
         LOWORD = CInt((lparam And &HFFFF&))
      End Function
   
      Sub Fill_List (l As Control)
         Dim i As Integer, listline$
         Dim retval&, DBWidthUnits As Single, fieldpiece As String
         Dim dlgratio As Single, accumpixels As Integer
         Dim TabSpace As Integer, charwidth_pixels  As Integer
         Const numchars = 6   'number of blank characters between columns
   
         ' This Sub accepts a list box control as a parameter
         ' and fills it with the contents of a global snapshot.
   
         ' Set tap stops in the list box to align the fields in the snapshot
         ' into columns by using dialog box units. Then add a horizontal
         ' scrollbar to the list box.
   
         ' The following formula gives DBWidthUnits given pixels
         ' DBWidthUnits= numpixels * 4/LOWORD(GetDialogBaseUnits())
         ' Rearranging the above formula gives pixels given DBWidthUnits
         ' numpixels= DBWidthUnits * LOWORD(GetDialogBaseUnits())/4
         ' For more efficient calculation, calculate a multiplier, based on
         ' the above formulas. Use it to convert pixels to DBWidthUnits:
         dlgratio = 4 / LOWORD(GetDialogBaseUnits())
   
         ' Return the DBunits for width:
         DBWidthUnits = LOWORD(GetDialogBaseUnits()) / 4
   
         ' One character averages 4 * DBWidthUnits in pixels:
         charwidth_pixels = numchars * 4 * DBWidthUnits
   
         ' Calculate the extra space between tabbed fields:
         TabSpace = charwidth_pixels * dlgratio
   
         ' Loop through and display the records:
         l.Clear
         ReDim biggest_value(0 To sn.Fields.Count - 1)
         ReDim tabstops(1 To sn.Fields.Count)
         While Not sn.EOF
            For i = 0 To sn.Fields.Count - 1
               ' In case of nulls in field, promote the NULL to empty string:
               fieldpiece = sn(i) & ""
   
               ' Enter the following two lines as one, single line:
               If Me.TextWidth(fieldpiece) / 
                  screen.TwipsPerPixelX > biggest_value(i) Then
   
                  ' Enter the following two lines as one, single line:
                  biggest_value(i) = Me.TextWidth(fieldpiece) / 
                     screen.TwipsPerPixelX
   
               End If
               listline$ = listline$ & fieldpiece & Chr$(9)
            Next i
            l.AddItem listline$
            listline$ = ""
            sn.MoveNext
         Wend
   
         For i = 0 To sn.Fields.Count - 1
            accumpixels = accumpixels + biggest_value(i)
            tabstops(i + 1) = accumpixels * dlgratio + (i + 1) * TabSpace
         Next i
   
         ' Send a message to l:
         retval& = SendMessage(l.hWnd, LB_SETTABSTOPS, i, tabstops(1))
   
         ' Add a horizontal scrollbar to single select list box.
         ' The message LB_SETHORIZONTALEXTENT expects the extent to be
         ' specified in pixels. Exploit the fact that the last tabstop
         ' element is not needed (tabs needed are one less than the number
         ' of fields to be tabbed. Store the accumulated length of the entire
         ' line in the last tab stop array element. The length is stored in
         ' DBWidthUnits. Convert it to pixels by dividing with the dlgratio
         ' calculated above.
         ' Enter the following two lines as one, single line of code:
         retval& = SendMessage(l.hWnd, LB_SETHORIZONTALEXTENT, tabstops(i) / 
            dlgratio, ByVal 0&)
      End Sub
   					

7. Save the project and run it. If you are using the SQL Server ODBC driver (SQLSRVR.DLL) that shipped with Visual Basic version 3.0, no error will appear in the text box. Look at the contents of the List1 and List2 boxes to see the contents of the temporary tables. Then look at the List3 box to see the results of the query based on the two temporary tables.