dcsimg
Results 1 to 6 of 6

Thread: Looping through cells and returning a value from a SQL connection, fails

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Looping through cells and returning a value from a SQL connection, fails

    Hello

    I have an excel sheet where the user enters a bunch of parts numbers then a script matches each number
    in turn and returns another value from a SQL connection.

    This works fine the first time through the loop but on the second I receive a 3705 error.

    I'm pretty sure its due to not closing a connection or something but I can figure it out?

    Code:
    Sub Find_Costs()
    
    Dim RCount As Integer
    Dim Item As String
    Dim sqlStatement As String
    
    'Connect to DB to get cost of part
    
    'Declare variables'
            Set objMyConn = New ADODB.Connection
            Set objMyCmd = New ADODB.Command
            Set objMyRecordset = New ADODB.Recordset
    
    
    'Open Connection'
            objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=server1;Initial Catalog=FSPlanningOEMMR;User ID=SharePointDataReader;Password=####;"
            objMyConn.Open
                   
    
    'Start looping through the records
    
            Range("B20").Select
    
            Do Until ActiveCell.Offset(1, 0).Value = 
    
            Item = ActiveCell.Value
    
        
    'Set and Excecute SQL Command'
           Set objMyCmd.ActiveConnection = objMyConn
            objMyCmd.CommandText = "SELECT TOP 1000[RolledMaterialCost]FROM [FSPlanningOEMMR].[dbo].[ZFS_ItemMaterialCost] where ItemNumber =" & "'" & Item & "'"
            sqlStatement = "SELECT TOP 1[RolledMaterialCost]FROM [FSPlanningOEMMR].[dbo].[ZFS_ItemMaterialCost] where ItemNumber =" & "'" & Item & "'"
            objMyCmd.CommandType = adCmdText
      
            objMyCmd.Execute
                
    
    'Open Recordset'
            Set objMyRecordset.ActiveConnection = objMyConn             '<<<<<<fails here the second time round
            objMyRecordset.Open objMyCmd
    
    'Copy Data to Excel'
        
            ActiveCell.Offset(0, 5).Select
            ActiveCell.CopyFromRecordset (objMyRecordset)
            ActiveCell.Offset(1, -5).Select
                
    
    Loop
    
    
    End Sub
    Hope you can help

    Regards

    Geoff

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,899

    Re: Looping through cells and returning a value from a SQL connection, fails

    try
    Code:
    objMyRecordset.close
    after the copyfromrecordset

    you should try to avoid using select or active anything in your code and as far as possible only work with fully qualified ranges
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Re: Looping through cells and returning a value from a SQL connection, fails

    This now gives a 3707 error, Cannot change the ActiveConnection property of a recordset object which has a command object as its source"

  4. #4
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,596

    Re: Looping through cells and returning a value from a SQL connection, fails

    Why Open a recordset?
    The Execute method return a recordset itself:

    Code:
    Set objMyRecordset = objMyCmd.Execute
    Doesn't need to open objMyRecordset statment

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    3

    Re: Looping through cells and returning a value from a SQL connection, fails

    Thanks ,that fixed it

    My SQL statement only retrieves one record and is pasted into the correct cell when this runs

    Code:
    ActiveCell.CopyFromRecordset (objmyrecordset)
    Sometimes nothing is returned by the SQL statement but I cant seem to trap these occurrences?

    I was thinking something like

    Code:
    if objmyrecordset is empty then do this ,else do something else?
    Cheers

    Geoff

  6. #6
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,596

    Re: Looping through cells and returning a value from a SQL connection, fails

    Yes:

    Code:
    If objMyRecordset.Eof then
        Debug.Print "recordset is empty, do nothing"
    End If

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width