• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Working with SQL

How can I, without using controls, connect to an sql database and fetch data from there? I'm using vb enterprise edition version 5.0.
Please insert source code.
0
cybernide
Asked:
cybernide
1 Solution
 
dabelleiCommented:
Do you wanr to use ODBC DAO or connect true  an OCX to your SQL server?
0
 
cymbolicCommented:
First, you need to have the SQL CLient software installed on your machine.  This is available on the SQL Server install cd, and when installed on your amchine will install the ODBC drivers you need to use to communicate to SQL Server.

Then, you need to create a DSN using your 32bit ODBC driver manager software, icon found in your control panel to set up a DSN that points to your SQL Server and your database on the server.

With the install of the SQL CLient software, you will also get the enterprise manager, which will be the primary utility front end to SQL Server for you.

With the client software and ODBC Drivers installed, and a dsn created, and the appropriate security set up for your client logon on the SQL Server Database,you can procede to access your data using RDO as FOllows:

'Declare DataSources
Dim gEn                    As rdoEnvironment   'rdoEngine
Dim gCnTgt               As rdoConnection    'Target Database
Dim gSql as string
Dim Rs as RdoResultset
Dim Nxt as long

'Open a connection to your dsn
Set gCnTgt = gEn.OpenConnection(vbNullString, rdDriverPrompt, False)  ' this will present aprompt for your user to select the correct DSN

'create sql to get something
gSql ="select nxtnum from ctlnum where filnam = 'MyFile'"

'open the resultset
Set rs = gCnTgt.OpenResultset(gSql, rdOpenForwardOnly, rdConcurReadOnly)  'Will get your data per the SQL

if not rs.eof
 nxt=rs(0)  'gets first column of resultset in nxt, you can also say
 'nxt=rs!nxtnum or nxt=rs("nxtnum")
endif
rs.close
gcntgt.close
set gcntgt=nothing

end

'Now real code will have on error and your own version of an error handler.  I usually encapsulate the reads, and have a common error handler, like this:

Public Sub rsTgt(rs As rdoResultset)
On Error GoTo eRsTgt
Set rs = gCnTgt.OpenResultset(gSql, rdOpenForwardOnly, rdConcurReadOnly)
gSql = vbNullString
glReads = glReads + 1
Exit Sub
eRsTgt:
 LogErr
End Sub

'where I use a public gSql String, and call it using RsTgt(rs), where rs is an RdoResultSet.  Then I log all errors using an error file on disk (this is for a very long running migration file):

Public Function LogErr()
Dim x$, prt$, fi As Integer, i As Integer
Static iErrs As Long
Static idup As Long
    If rdoErrors.Count > 1 And Len(gSql) > 0 Then
        For i = 1 To rdoErrors.Count - 1
            x$ = rdoErrors(i).Description
            'trim the snot out of it so we just get readable messages
            While InStr(x$, "]") > 0  'gets rid of sql server header
             x$ = Mid$(x$, InStr(x$, "]") + 1)
            Wend
            While InStr(x$, ":") > 0  'gets rid of constraint name header
             x$ = Mid$(x$, InStr(x$, ":") + 1)
            Wend
            prt$ = vbCrLf & LTrim$(x$) & vbCrLf
        Next
        'look, who cares about dupes anyway, so dump them as errors
        If InStr(UCase$(prt$), "INSERT DUPLICATE") > 0 Then
         gCmnRec(gCntr).Dups = gCmnRec(gCntr).Dups + 1
         LogErr = 0
         Exit Function
        End If
        If prt$ = gLstErr$ Then
         prt$ = " " & gSql
         FMigWiz.txError = Format$(iErrs) + ": " + gLstErr$ + prt$
        Else
         If idup > 0 Then
          Print #fi, "(" + Format$(idup + 1) + ") " + gLstErr$
         End If
         idup = 0
         gLstErr$ = prt$
         prt$ = prt$ & " " & gSql
         FMigWiz.txError = Format$(iErrs) + ": " + prt$
         ' Display message box
         'MsgBox (prt$)
        End If
    Else
     prt$ = Err.Description & vbCrLf
    End If
    fi = FreeFile
    Open ErrLog$ For Append As fi
    Print #fi, prt$
    iErrs = iErrs + 1
    DoEvents
   
    'Determin Nature and Severity of Error
    LogErr = 0
    ' Comm Link, SQL Failure, or Invalid Object
    If InStr(prt$, "08S01") > 0 Or InStr(prt$, "S1000") > 0 Or InStr(UCase$(prt$), "OBJECT IS INVALID") > 0 Then
     prt$ = prt$ + vbCrLf + " Critical Error, Process Stopped!"
     Print #fi, prt$
     MsgBox prt$
     CloseDataSources
     End
    End If
    Close #fi

End Function
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now