vb-oracle query

[kaustav]

hi,

i was using this code to access data from a table in oracle -

dim cn as new adodb.connection
dim rs as new adodb.recordset
dim str as string

str="select * from products"
cn.connectionstring ="dsn=aaa;uid=aaa;pwd=aaa"
cn.open
rs.open str,cn

now the problem is if that if the table doesnt exist, i get an error which is pretty much obvious. my question is how do i trap this error in VB. or may be how do i code to check whether a table exists in oracle or not. thanks

[GlenW]

Use ADOX Catalog

[kaustav]

hi glenn,

could you please elaborate a bit on this. thanks.

[GlenW]

Reference;
'Microsoft ADO Ext. 2.5 for DDl and Security'

Dim cnnCon As ADODB.Connection
Set cnnCon = New ADODB.Connection
cnnCon.Mode = adModeRead
Dim myCat As New ADOX.Catalog
    
cnnCon.Open dataText   
 
Set myCat.ActiveConnection = cnnCon
 
While (i < myCat.Tables.Count)
        If ((myCat.Tables(i).Name = "tblMyTable") _
        'Do whatever
        End If
        i = i + 1
Wend

Something like this will enable you to fing if a table exists

[Hack]

To error trap GlenW's code

Private Sub OpenCat()
On Error Goto ErrRtn
Dim cnnCon As ADODB.Connection
Set cnnCon = New ADODB.Connection
cnnCon.Mode = adModeRead
Dim myCat As New ADOX.Catalog
    
cnnCon.Open dataText   
 
Set myCat.ActiveConnection = cnnCon
 
While (i < myCat.Tables.Count)
        If ((myCat.Tables(i).Name = "tblMyTable") _
        'Do whatever
        End If
        i = i + 1
Wend
Exit Sub
ErrRtn:
Msgbox Err & " " & Error
End Sub

[GlenW]

Hack, you're just a bloody perfectionist!!

[Hack]

I wouldn't have touched your code had it not been for the fact that kaustav asked, in his original question, how do I error trap (well, maybe I would have anyway...I've just had too many dealings with runtime error that blow my users back to the desktop resulting in angry phone calls say "Hack, your stinkin' program don't work, FIX IT!!")