As you may see the above code I am very amateur but very enthusiastic to learn and develop my programming skills.
1- My question to you is how I can display the fields from the database using this code. I am trying to make this application to allow a user to pass three variables and run the query embedded in the code to bring the result form the database.
2-Is there anyway to make a connection other than the one I used in my code.
3-Is it possible to connect this code with crystal report

N.B I used fake user id and password to protect my database hope u understand that.

I really appreciate your time and priceless help.





Private Sub cmdCommand_Click()

Dim LettingNO As String
Dim ContractId As String
Dim CallNo As String
Dim adoConnection As ADODB.Connection
Dim adoRecordSet As ADODB.Recordset
Dim ConnectString As String
Dim mySQL As String

'create a new connection--
Set adoConnection = New ADODB.Connection

'create a new recordset object used to reference values from sql statement....
Set adoRecordSet = New ADODB.Recordset

'Purpose: Creates the connection string for Oracle
ConnectString = "Provider=OraOLEDB.Oracle;Persist Security Info=true;" & _
"Data Source=" & "datasource" & _
";User ID=" & "fakeid" & _
";Password=" & "fakeid"


'create SQL statement so the recordset object will reference to that...
mySQL = "SELECT DISTINCT Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS,"
mySQL = mySQL & "Q.PRICE,AVG(C.BIDPRICE),MIN(C.BIDPRICE)"
mySQL = mySQL & "FROM LETPROP L, PROJECT R, PROPPROJ J, PROPOSAL P, PROPITEM Q,"
mySQL = mySQL & "BIDLET B, BIDTABS C, ITEMLIST I,VENDOR V, BIDDERS D"
mySQL = mySQL & "WHERE L.Letting = B.Letting AND B.LETTING = C.LETTING,"
mySQL = mySQL & "AND P.CSPECYR = I.ISPECYR AND Q.PRPITEM = I.ITEM,"
mySQL = mySQL & "AND P.CONTID = L.LCONTID AND Q.CONTID = P.CONTID,"
mySQL = mySQL & "AND C.VENDOR = L.AVENDOR AND L.CALL = C.CALL,"
mySQL = mySQL & "AND Q.LINEFLAG = C.LINEFLAG AND Q.IPLINENO = C.IPLINENO,"
mySQL = mySQL & "AND R.PCN = J.PCN AND L.LETTING = D.LETTING,"
mySQL = mySQL & "AND C.LETTING = L.LETTING AND C.CALL = D.CALL,"
mySQL = mySQL & "AND C.LETTING = D.LETTING AND V.VENDOR = D.VENDOR,"
mySQL = mySQL & "AND L.LETSTAT = 'A'AND C.LINEFLAG = 'L',"
mySQL = mySQL & "AND L.LETTING =" & LettingNO
mySQL = mySQL & "AND P.CONTID =" & ContractId
mySQL = mySQL & "AND L.CALL =" & CallNo
mySQL = mySQL & "GROUP BY Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS,Q.PRICE, C.BIDPRICE"
mySQL = mySQL & "ORDER BY Q.IPLINENO"


'This will open the connecion String
adoConnection.Open ConnectString

'Before this adoRecordSet object make the refernce to the mySQL statement
'it will open the connecion using the connecion string object "adoConnecion"

adoRecordSet.Open mySQL, adoConnection

' 'List1.AddItem adoRecordSet!addend
' Set adoRecordSet = adoConnection.Execute("select distinct contid from addend where rownum < 10")
' Set adoRecordSet = adoConnection.Execute("mySQL")
' List1.AddItem adoRecordSet!contid
' List1.AddItem adoRecordSet!contid

End Sub