VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Form1"
   ClientHeight    =   3090
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   4680
   LinkTopic       =   "Form1"
   ScaleHeight     =   3090
   ScaleWidth      =   4680
   StartUpPosition =   3  'Windows Default
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Dim oConn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Server As String
Dim Database As String


Private Sub Form_Load()
' Connection Strings
' Visual Basic Program Connection to Sequel Server
  
  Set oConn = New ADODB.Connection
  With oConn
    .CursorLocation = adUseClient
    .Open "Provider=SQLOLEDB.1;Server=MANIFEST2;Database=Northwind;UID=sa;PWD=;"
  End With
  Label2.Caption = "Connected..."

End Sub


Private Sub Form_Unload(Cancel As Integer)
'Close connection (recordset does not need to be closed - it now happens in the same sub's as it is opened)

  oConn.Close
  Set oConn = Nothing

End Sub

' Button that Runs the Actual Query
Private Sub Search_Click()
    
Dim strSql As String, strResults As String
Dim lngMaxRcs As Long, lngRcs As Long

'---- create the sql statement and print it to the immediates window
    strSql = "SELECT [First_Name] + '  '  +  [Last_Name] as FullName,  [Age] " _
           & "FROM [JoeFox] " _
           & "WHERE [First_Name] like '%" & txtSearch.Text & "%'"
    Debug.Print strSql
    
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText
    If rst.EOF Then
        txtResults.Text = "No Records Match the Criteria." & vbCrLf & "Please try again."
    Else
        rst.MoveLast
        rst.MoveFirst
        lngMaxRcs = rst.RecordCount
        For lngRcs = 1 To lngMaxRcs
            strResults = strResults & IIf(Len(strResults) > 0, vbCrLf, "") & rst("fullname") & " Age=" & rst("age")
            rst.MoveNext
        Next
        ' This is where the results are displayed
        txtResults.Text = strResults
        Label2.Caption = "Search Completed"
    End If
    ' Closes the Record set so that it dosent us up memory space
    rst.Close
    Set rst = Nothing

End Sub

Public Sub Command1_Click()

  Call AddUser(TextBox1.Text, TextBox2.Text, TextBox3.Text)

End Sub

Function AddUser(sFirstName As String, sLastName As String, SAge As String) As Boolean
    
Dim strSql As String
'---- create an sql statement that returns no data (as false <> true)
    strSql = "SELECT [First_Name], [Last_Name], [Age] " _
           & "FROM [JoeFox] " _
           & "WHERE false = true"
'---- Open the recordset
    Set rst = New ADODB.Recordset
    rst.Open strSql, oConn, 3, 3, 1 'static,optimistic,adCmdText

    AddUser = True
    With rst
        .AddNew 'adding new record
        .Fields("First_Name") = sFirstName
        .Fields("Last_Name") = sLastName
        .Fields("Age") = SAge
        .Update 'this updates the recordset
    End With
    Label2.Caption = "Recordset Updated..."

    ' Closes the Record set so that it dosent us up memory space
    rst.Close
    Set rst = Nothing

End Function