Site hosted by Angelfire.com: Build your free website today!
   
Structured Query Language (SQL) Example
The following is a sample SQL statement coded for my Virtual Placement Agency Project.  This is a sub procedure from the cCompanyDB class.  It opens and closes a connection to the Access database and updates or saves data to the database depending on whether the user choses to add or edit a company.
Public Sub Save(CompanyInfo As Variant)

Dim lstrSQL As String

Dim liCompanyID As Integer
Dim lstrCompanyName As String
Dim lstrPhone As String
Dim lstrStAddress As String
Dim lstrCity As String
Dim lstrStateProv As String
Dim lstrZipPostal As String
Dim lstrNotes As String

liCompanyID = CompanyInfo(0)
lstrCompanyName = CompanyInfo(1)
lstrPhone = CompanyInfo(2)
lstrStAddress = CompanyInfo(3)
lstrCity = CompanyInfo(4)
lstrZipPostal = CompanyInfo(5)
lstrStateProv = CompanyInfo(6)
lstrNotes = CompanyInfo(7)

'check for the AddMode variable

If liCompanyID = 0 Then    'adding a new record

     lstrSQL = "INSERT INTO tblCompanies (CompanyName, Phone, Street, City, StateProvince, ZipPostal, Notes)"
     lstrSQL = lstrSQL & "Values ("
     lstrSQL = lstrSQL & Chr(39) & lstrCompanyName & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrPhone & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrStAddress & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrCity & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrStateProv & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrZipPostal & Chr(39) & ", "
     lstrSQL = lstrSQL & Chr(39) & lstrNotes & Chr(39) & ")"

Else  'we are editing an existing record so build an update query/SQL statement

     lstrSQL = "UPDATE tblCompanies SET "
     lstrSQL = lstrSQL & "CompanyName=" & Chr(39) & lstrCompanyName & Chr(39) & ", "
     lstrSQL = lstrSQL & "Phone=" & Chr(39) & lstrPhone & Chr(39) & ", "
     lstrSQL = lstrSQL & "Street=" & Chr(39) & lstrStAddress & Chr(39) & ", "
     lstrSQL = lstrSQL & "City=" & Chr(39) & lstrCity & Chr(39) & ", "
     lstrSQL = lstrSQL & "StateProvince=" & Chr(39) & lstrStateProv & Chr(39) & ", "
     lstrSQL = lstrSQL & "ZipPostal=" & Chr(39) & lstrZipPostal & Chr(39) & ", "
     lstrSQL = lstrSQL & "Notes=" & Chr(39) & lstrNotes & Chr(39)
     lstrSQL = lstrSQL & "WHERE CompanyId= " & liCompanyID

End If

'open and close db and updata data
     mData.Connect
     mData.Update (lstrSQL)
     mData.Disconnect

End Sub

     




Essentials of eBusiness / Java / Visual Basic / DB2 / Peer Evaluations / Biography