1 / 17

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #8

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #8. Objectives. SQL Queries & Statements Useful SQL techniques DB Comparison. retrieve All records. SELECT * FROM Users Use Constants: Use inside Function:. Public Const GET_ALL_USERS = "Select * From Users".

javen
Download Presentation

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Enterprise Development Using Visual Basic 6.0 Autumn 2002Tirgul #8 ‘Tirgul’ # 8

  2. Objectives • SQL Queries & Statements • Useful SQL techniques • DB Comparison ‘Tirgul’ # 8

  3. retrieve All records • SELECT * FROM Users • Use Constants: • Use inside Function: PublicConst GET_ALL_USERS = "Select * From Users" PublicFunction GetAllUsers() As ADODB.Recordset '... Set RS = Connection.execute(GET_ALL_USERS) '... EndFunction ‘Tirgul’ # 8

  4. retrieve Some fields • SELECT UserID, UserName FROM Users • Use Constants: • Inside a function Public Const GET_USERS_FIELDS = "Select UserID, UserName” & _ “ From Users" Public Function GetUsersFields() As ADODB.Recordset '... Set RS = Connection.execute(GET_USERS_FIELDS) '... End Function ‘Tirgul’ # 8

  5. Conditions • SELECT UserID, UserName FROM Users WHERE UserName = 'James‘ • SELECT UserID, UserName FROM Users WHERE UserID < 10 • SELECT UserID, UserName FROM Users WHERE UserID < 10 OR UserName = 'James' ‘Tirgul’ # 8

  6. Conditions • Cannot build all string • Use inside Function: Public ConstGET_USER_BY_NAME = "SELECT UserID, UserName FROM Users WHERE UserName = '" Public Function GetUserByName(strUserName As String) As ADODB.Recordset '... Dim strQuery AsString strQuery = GET_USER_BY_NAME & strName & "'" Set RS = Connection.execute(strQuery) '... End Function ‘Tirgul’ # 8

  7. Sorting • SELECT * FROM Users ORDERBY UserName DESC • SELECT * FROM Users ORDERBY UserName • SELECT * FROM Users ORDERBY UserName ASC • SELECT * FROM Users ORDERBY UserName, UserID DESC ‘Tirgul’ # 8

  8. Sorting • Use Constants: • Use inside Function: PublicConst GET_USERS_ORDER_DESC = "SELECT * FROM Users ORDER BY UserName DESC" PublicFunction GetUserOrderDesc() As ADODB.Recordset '... Set RS = Connection.execute(GET_USERS_ORDER_DESC) '... EndFunction ‘Tirgul’ # 8

  9. Add records • INSERTINTO Users VALUES (10, 'James Crowley', 'PASSWORD') • INSERTINTO Users (UserName, UserID) VALUES ('James Crowley', 10) • INSERTINTO Users (UserName, Password) VALUES ('James Crowley', 'PASSWORD') ‘Tirgul’ # 8

  10. Add records Little an Evil.. • Use Constants: • Use inside Function: PublicConst INSERT_USER = "INSERT INTO Users (UserID,” & _ UserName)VALUES (‘" No Return value PublicFunction InsertUser(strUserID As String, _ strUserName As String) '... Dim strQuery AsString strQuery = INSERT_USER & strUserID & "'" & strUserName & "')" Set RS = Connection.execute(strQuery) '... EndFunction ‘Tirgul’ # 8

  11. Another option. • Use Array: PublicFunction InsertUser(UserDataArr) '... Dim strQuery AsString strQuery = INSERT_USER & UserDataArr(0) & "'" & UserDataArr(0) & "')" Connection.execute(strQuery) '... EndFunction PublicFunction InsertUser(UserDataArr) '... RS!UserID = UserDataArr(0) RS!UserName = UserDataArr(1) '... EndFunction ‘Tirgul’ # 8

  12. Updaterecords • UPDATE Users SET UserName = 'James Smith', Password = 'NEWPASSWORD' WHERE UserID = 10 • UPDATE Furniture SET Price = 10 WHERE ItemType = 'Cupboard' ‘Tirgul’ # 8

  13. Updaterecords • Use Constants: • Use inside Function: PublicConst UPDATE_USER = "UPDATE Users SET UserName = ‘" PublicFunction UpdateUser(strUserName As String) '... Dim strQuery AsString strQuery = UPDATE_USER & strUserName & "'" Connection.execute(strQuery) '... EndFunction ‘Tirgul’ # 8

  14. Delete a record • DELETEFROM Users WHERE UserID = 10 • DELETEFROM Furniture WHERE Price < 10 • TRUNCATETABLEauthors TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE ‘Tirgul’ # 8

  15. SQL Transaction statements • BEGIN TRANSACTION • COMMIT TRANSACTION • ROLLBACK TRANSACTION ‘Tirgul’ # 8

  16. Transaction Use PublicFunction Insert() AsBoolean Conn.BeginTrans 'Add 'Delete 'Update If Err.Number <> 0 Then Conn.RollBack Insert = False ExitFunction EndIf Conn.Commit EndFunction ‘Tirgul’ # 8

  17. Commercial Databases • Access • Simple yet useful • For a single/double work stations • Limit data size • MS SQL Server • Very common • Only Microsoft… • Ease of use • Time-to-market • Oracle • More “Commercial” • Known as a powerful tool • Multi platform – Win/Unix/Linux • IBM DB2 • More “Commercial” • Multi platform – Win/Unix/Linux • Useful for IBM family & tools ‘Tirgul’ # 8

More Related