MSSQL & VBASIC6 
Author Message
 MSSQL & VBASIC6

This should keep you busy for a while:

Option Explicit
Private Const DBCONNECT As String = "DSN=YOURDSN;UID=work1;PWD=password;"
Private cn As Connection
Private cat As ADOX.Catalog
Private sSQL As String

Private Sub cboTables_Click()
    Dim tbl As ADOX.Table
    Dim c As ADOX.Column
    Dim li As ListItem

    Set tbl = cat.Tables(cboTables.Text)

    lvCols.ListItems.Clear

    For Each c In tbl.Columns
        Set li = lvCols.ListItems.Add(, c.Name, c.Name)
        li.SubItems(1) = GetDataType(c.Type)
        li.SubItems(2) = CStr(c.Precision)
        li.SubItems(3) = CStr(c.NumericScale)
    Next
End Sub

Private Sub chkColSort_Click()
    If chkColSort = vbChecked Then
        lvCols.Sorted = True
    Else
        lvCols.Sorted = False
        cboTables_Click         ' reload data unsorted
    End If
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdViewSP_Click()
    Dim rs As Recordset

    Set rs = New Recordset

    rs.Open "select * from syscomments where id = object_id('" & cboSPs.Text
& "') order by number, colid2, colid", cn, adOpenDynamic, adLockReadOnly,
adCmdText
    sSQL = vbNullString

    Do Until rs.EOF = True
        sSQL = sSQL & rs!Text
        rs.MoveNext
    Loop

    rs.Close

    Form2.Show
End Sub

Private Sub Form_Load()
    Dim tbl As ADOX.Table
    Dim p As ADOX.Procedure
    Dim c As ADOX.Column
    Dim li As ListItem

    Set cn = New Connection
    cn.Open DBCONNECT
    Set cat = New Catalog
    Set cat.ActiveConnection = cn

    For Each tbl In cat.Tables
        If tbl.Type = "TABLE" Then
            cboTables.AddItem tbl.Name
        End If
    Next

    cboTables.Text = cboTables.List(0)

    For Each p In cat.Procedures
        cboSPs.AddItem p.Name
    Next

    cboSPs.Text = cboSPs.List(0)
    cboTables_Click
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set cat = Nothing

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.Close
        End If
        Set cn = Nothing
    End If
End Sub

Private Function GetDataType(lType As Long) As String
    Dim sDesc As String

    Select Case lType
        Case adTinyInt
            sDesc = "adTinyInt"
'            sDesc = "Exact numeric value, precision 3 scale 0"
        Case adSmallInt
            sDesc = "adSmallInt"
'            sDesc = "Exact numeric value, precision 5 scale 0"
        Case adInteger
            sDesc = "adInteger"
'            sDesc = "Exact numeric value, precision 10 scale 0"
        Case adBigInt
            sDesc = "adBigInt"
'            sDesc = "Exact numeric value, precision 19 scale 0"
        Case adUnsignedTinyInt
            sDesc = "adUnsignedTinyInt"
'            sDesc = "Unsigned version of adTinyInt"
        Case adUnsignedSmallInt
            sDesc = "adUnsignedSmallInt"
'            sDesc = "Unsigned version of adSmallInt"
        Case adUnsignedInt
            sDesc = "adUnsignedInt"
'            sDesc = "Unsigned version of adInteger"
        Case adUnsignedBigInt
            sDesc = "adUnsignedBigInt"
'            sDesc = "Unsigned version of adBigInt"
        Case adSingle
            sDesc = "adSingle"
'            sDesc = "Single precision floating point number"
        Case adDouble
            sDesc = "adDouble"
'            sDesc = "Double precision floating point number"
        Case adCurrency
            sDesc = "adCurrency"
'            sDesc = "Currency type"
        Case adDecimal
            sDesc = "adDecimal"
'            sDesc = "Variant decimal type"
        Case adNumeric
            sDesc = "adNumeric"
'            sDesc = "Numeric type"
        Case adBoolean
            sDesc = "adBoolean"
'            sDesc = "Variant Boolean type. 0 is false and ~0 is true"
        Case adUserDefined
            sDesc = "adUserDefined"
'            sDesc = "User-defined data type of variable length"
        Case adVariant
            sDesc = "adVariant"
'            sDesc = "Automation Variant"
        Case adGUID
            sDesc = "adGUID"
'            sDesc = "Globally Unique Identifier"
        Case adDate
            sDesc = "adDate"
'            sDesc = "Automation date"
        Case adDBDate
            sDesc = "adDBDate"
'            sDesc = "Database date data structure"
        Case adDBTime
            sDesc = "adDBTime"
'            sDesc = "Database time data structure"
        Case adDBTimeStamp
            sDesc = "adDBTimeStamp"
'            sDesc = "Database timestamp structure"
        Case adBSTR
            sDesc = "adBSTR"
'            sDesc = "Pointer to a BSTR"
        Case adChar
            sDesc = "adChar"
'            sDesc = "Fixed-length character string"
        Case adVarChar
            sDesc = "adVarChar"
'            sDesc = "Variable-length character string"
        Case adLongVarChar
            sDesc = "adLongVarChar"
'            sDesc = "Long variable-length character string"
        Case adWChar
            sDesc = "adWChar"
'            sDesc = "Wide fixed-length character string"
        Case adVarWChar
            sDesc = "adVarWChar"
'            sDesc = "Wide variable-length character string"
        Case adLongVarWChar
            sDesc = "adLongVarWChar"
'            sDesc = "Long, wide variable-length character string"
        Case adBinary
            sDesc = "adBinary"
'            sDesc = "Fixed-length binary data"
        Case adVarBinary
            sDesc = "adVarBinary"
'            sDesc = "Variable-length binary data"
        Case adLongVarBinary
            sDesc = "adLongVarBinary"
'            sDesc = "Long variable-length binary data"
        Case Else
            sDesc = "Unknown"
    End Select

    GetDataType = sDesc
End Function

' Do not delete this sub
Private Sub lvCols_OLEStartDrag(Data As MSComctlLib.DataObject,
AllowedEffects As Long)
    AllowedEffects = vbDropEffectCopy
End Sub

Public Property Get SQL() As String
    SQL = sSQL
End Property

Quote:

> I am looking to automate some SQL queries using Visual Basic. I would
> like to have a box that lists all the tables in a certain database, and
> once you select a table it can expand the list and show what fields are
> in that table. This would allow you to select what tables/fields you
> wanted to use for your query. Anyone have any idea what the syntax for
> something like this would look like? How would I pull that information
> from MS SQL Server 7? Thanks for your help in advance.

> Sent via Deja.com http://www.*-*-*.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 MSSQL & VBASIC6
I am looking to automate some SQL queries using Visual Basic. I would
like to have a box that lists all the tables in a certain database, and
once you select a table it can expand the list and show what fields are
in that table. This would allow you to select what tables/fields you
wanted to use for your query. Anyone have any idea what the syntax for
something like this would look like? How would I pull that information
from MS SQL Server 7? Thanks for your help in advance.

Sent via Deja.com http://www.deja.com/
Before you buy.



Thu, 06 Feb 2003 10:36:08 GMT  
 MSSQL & VBASIC6
You can also look at the openschema command

Mark

Quote:

> I am looking to automate some SQL queries using Visual Basic. I would
> like to have a box that lists all the tables in a certain database, and
> once you select a table it can expand the list and show what fields are
> in that table. This would allow you to select what tables/fields you
> wanted to use for your query. Anyone have any idea what the syntax for
> something like this would look like? How would I pull that information
> from MS SQL Server 7? Thanks for your help in advance.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 MSSQL & VBASIC6
There are a lot of visual query builders available with source code.  In
fact, I think one of the earlier versions of VB (maybe 5.0?) came with a
sample app that did just what you are trying to do (maybe it's still there
in vb6?).

        - Bob

Quote:

> I am looking to automate some SQL queries using Visual Basic. I would
> like to have a box that lists all the tables in a certain database, and
> once you select a table it can expand the list and show what fields are
> in that table. This would allow you to select what tables/fields you
> wanted to use for your query. Anyone have any idea what the syntax for
> something like this would look like? How would I pull that information
> from MS SQL Server 7? Thanks for your help in advance.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Besides Access & MSSql

2. Help with VB 4.0 & MSSQL 6.5

3. VB & MSSQL

4. vb & msSQL

5. VB, ADO & MSSQL for multilanguage

6. Date Type Access&MSSQL

7. VB, ADO & MSSQL for multilanguage

8. HELP!!!!!! Vbasic6

9. VBA to VBasic6.0

10. VBA to VBasic6.0

11. *&*&*&* Date math question *&*&*&*

12. VB & Oracle 8.0 & MSSQL

 

 
Powered by phpBB® Forum Software