Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: VB6 always disconnected

    I made a program usaing VB using ADODB.Connection.Execute
    But sometimes, each time its executed, the connection is broken, then reconnected. this consumes a LOT of time when inserting a lot of fields.
    Can someone help me on this?

    The other, is I can't open any CSV file using ODBC. It always says that the disk is protected even though the disk is at HDD and there is no protection at all.

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi prd00,

    You need to change your logic to:

    Establish the ADO connection during the form_load event,

    Release the ADO connection during the form_unload event,

    And use an "ADODB.Command" to do the actual work.

    Dim lclCM as ADODB.Command
    Dim lclRows as long

    With lclCM
    .ActiveConnection = YourADOConnection
    .CommandText = YourQuery
    .CommandType = adCmdText
    .Execute lclRows
    End With

    lclRows will contain the number of rows affected by your query.

    You can find more details in the MSDN website under "converting RDO to ADO".

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Please post your code for the connection issue. What are you using the csv file for - are you trying to import it into a database ? Which database are you using ?

  4. #4
    Join Date
    Sep 2002
    Posts
    30
    Thx for help.

    I am using the CSV for import data from outside source into the database. I am just trying to use ODBC DSN file to connect. Right now I am using my own importing routines, but I plan to read the file using ODBC.
    Create New DSN, use Text object. That's all.

  5. #5
    Join Date
    Sep 2002
    Posts
    30
    Hi Bruce, it's not working.... Still the same....Okay.. this is my module...
    I am using class for database, with Connection object shared for the whole class. The connection is made with clsDB.Open <Database Name> not at initialize. There are 2 main command at this class, clsDB.Execute and clsDB.Retrieve.

    Code:
    Private connADO as ADODB.Connection
    Private Connected as Boolean
    
    Public Sub Open(DBName)
        cnString = "Provider=SQLOLEDB.1;Password=password;" & _
            "Persist Security Info=True;User ID=user;Initial Catalog=" & _ 
            "DBName & ";Data Source=SERVER"
        If Not Connected Then
            Set connADO = New ADODB.Connection
            connADO.Open cnString
            Connected = connADO.State = adStateOpen
        Else
            cnADO.Execute "USE " & DBName
        End If
    End sub
    
    Public Function Execute (SQLCmd) as integer
    Dim RecAffected As Integer, cmdADO As ADODB.Command
        Set cmdADO = New ADODB.Command
        With cmdADO
            .ActiveConnection = connADO
            .CommandText = SQLCmd
            .Execute RecAffected
        End With
        SQLExecute = RecAffected
    end sub
    
    Public Function Retrieve(SQLCmd) As ADODB.Recordset
    Dim rsADO As ADODB.Recordset
        
        Set rsADO = New ADODB.Recordset
        rsADO.Open SQLCmd, connADO
        Set SQLRetrieve = rsADO
    End Function
    
    Private Sub Class_Terminate()
        connADO.Close
    end Sub
    The sample Procedure to call them is like this
    Code:
    Public sub TestSub
    dim rsADO as ADODB.RecordSet, cDB as clsDB
        Set cDB = new clsDB
        set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
        cDB.Open "Data2002"
        for x = 0 to 1
           do While not rsADO.EOF
               cDB.Execute "INSERT INTO Temp (Fld1, Fld2, Fld3) VALUES (a1, " & _
               rsADO(0) & ", " & rsADO(1) & ")"
               rsADO.MoveNext
           loop
           cDB.Execute "DELETE FROM Sales WHERE ID in (SELECT Fld1 FROM TEMP)"
           cDB.Execute "INSERT INTO Sales SELECT * FROM TEMP"
           cDB.Execute "EXEC RecalcAll"
           set cDB = nothing
        Next
    end sub
    Now.. the problem lies at the last 3 command. Like this....
    1. Connect because of Open command
    2. Retrieve
    3. Doing Execute loop
    4. disconnect, reconnect, doing DELETE
    5. disconnect, reconnect, doing INSERT
    6. disconnect, reconnect, doing EXEC
    7. disconnect, reconnect, doing Retrieve
    8. Doing Execute loop
    9. disconnect, reconnect, doing DELETE
    10. disconnect, reconnect, doing INSERT
    11. disconnect, reconnect, doing EXEC

    At step 2,3, and 8 it is not disconnected.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    This might be a typo - but you are destroying the instance in the for next loop - it should be outside the for next loop. Why are you looping twice ? Make sure to destroy your recordset object as well. Also, how often is it disconnecting (every time) ? What is cnADO ? Shouldn't cDB.Open be called before you 'set rsADO' ?

  7. #7
    Join Date
    Sep 2002
    Posts
    30
    Oh.. yes... that's type.. it should be outside...
    The real one, it's not twice, it's as much as it needs to read all kind of data on the disk.

    Sorry... cnADO is supposed to be connADO... another typo...

    Yes... everytime... For example, if I have 6 files on the disk, then it is disconnected 6 times, at step 4,5,6,7,9,10,11 The only time its not disconnected is when it's looping inside do-loop

    Yes, cdb.open supposed be before set rsado... that's another typo... Sorry.. a lot of typo there...
    It's supposed to be like this:

    Code:
    Public sub TestSub
    dim rsADO as ADODB.RecordSet, cDB as clsDB
        Set cDB = new clsDB
        cDB.Open "Data2002"
        set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
        for x = 0 to 1
           do While not rsADO.EOF
               cDB.Execute "INSERT INTO Temp (Fld1, Fld2, Fld3) VALUES (a1, " & _
               rsADO(0) & ", " & rsADO(1) & ")"
               rsADO.MoveNext
           loop
           cDB.Execute "DELETE FROM Sales WHERE ID in (SELECT Fld1 FROM TEMP)"
           cDB.Execute "INSERT INTO Sales SELECT * FROM TEMP"
        Next
        cDB.Execute "EXEC RecalcAll"
        set cDB = nothing
    end sub
    The real code supposed to be:
    1. For-Next loop supposed to be for x=1 to < "*.csv" files count
    2. and typo-ed rsADO
    set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
    supposed to read records from CSV file using ODBC. Right now it's running using my own CSV routines, not with "Do Until rsADO.EOF".
    3. the csv filename will be the table name.
    Last edited by prd00; 12-15-02 at 05:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •