Results 1 to 8 of 8

Thread: ADODB Recordset not populating

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    ADODB Recordset not populating

    Ok, this is driving me crazy because it works elsewhere!

    rs.count not even happening and prefsuppliername returning nothing.

    Code:
        Private Function M1_LookupPrefSupplierName(_pn As String) As String
            Dim connectionString As String = "Provider=sqloledb;Data Source=sql;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
    
            Dim cn As New ADODB.Connection
            Dim rs As New ADODB.Recordset
    
            For c As Integer = 0 To dgvParts.Rows.Count
                Dim sql As String = "SELECT [PartOrgReferences].[imzPartID], [Organizations].[cmoName] " &
                                    "FROM [M1_SU].[dbo].[PartOrgReferences] " &
                                    "INNER JOIN [Organizations] ON [PartOrgReferences].[imzOrganizationID] = [Organizations].[cmoOrganizationID] " &
                                    "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    
                ' Dim sql As String = "SELECT PartOrgReferences.imzPartID, Organizations.cmoName " &
                '                     "FROM  PartOrgReferences " &
                '                     "INNER JOIN Organizations ON PartOrgReferences.imzOrganizationID = Organizations.cmoOrganizationID " &
                '                     "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    
                'Try
                cn.Open(connectionString)
                rs.Open(sql, cn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockReadOnly)
                rs.MoveFirst()
                Do While Not rs.EOF
                    Dim prefsuppliername As String = rs.Fields("cmoName").Value
                    prefsuppliername = prefsuppliername.Trim
    
                    ' Only one part
                    ' rs.MoveNext()
    
                    Return prefsuppliername
                    rs.Close()
                    cn.Close()
                    Exit Do
    
                Loop
    
                'Catch
    
                'End Try
    
            Next
    
        End Function
    And this works directly in SQL:
    Code:
    SELECT        PartOrgReferences.imzPartID, Organizations.cmoName
    FROM            PartOrgReferences INNER JOIN
                             Organizations ON PartOrgReferences.imzOrganizationID = Organizations.cmoOrganizationID
    WHERE        (PartOrgReferences.imzPartID LIKE 'W900560%')
    Thanks!
    Last edited by ssabc; Dec 2nd, 2020 at 04:37 PM.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: ADODB Recordset not populating

    Hi.
    As a general notice if this is vb.net I would strongly advice to move to ADO.NET as this is so so deprecated.
    I can't really recall the last time I used ADODB so I can't really help but as a first step try to copy the SQL string to the SQL server and see if you have made any mistake .
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB Recordset not populating

    It works great in SQL Server, and the same logic on other tables. I'm wondering if there are some permissions issues that need to be opened up.
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: ADODB Recordset not populating

    What do you mean "works elsewhere"?
    Where does it work?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: ADODB Recordset not populating

    I have very similar logic in the same program. I have also tested the query in sql and it pulls what I am looking for.

    In the past, there have been access issues on certain tables, using Visual Studio only. I'm just guessing this could be a problem.

    One thing I do question is this, with all the references to the database and [dbo] syntax with brackets:
    Code:
    Dim sql As String = "SELECT [PartOrgReferences].[imzPartID], [Organizations].[cmoName] " &
                                    "FROM [M1_SU].[dbo].[PartOrgReferences] " &
                                    "INNER JOIN [Organizations] ON [PartOrgReferences].[imzOrganizationID] = [Organizations].[cmoOrganizationID] " &
                                    "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    Because this is all that is required in SQL Server:
    Code:
            Dim sql As String = "SELECT PartOrgReferences.imzPartID, Organizations.cmoName " &
                                "FROM  PartOrgReferences " &
                                "INNER JOIN Organizations ON PartOrgReferences.imzOrganizationID = Organizations.cmoOrganizationID " &
                                "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: ADODB Recordset not populating

    If you have similar logic then you can copy the SQL part over here.
    If it works then your issue is somewhere on the tables , if it doesn't then you issues is on the calling.
    I generally do not use brackets for non reserved words but it's not something I'm aware that causes issues.
    Also I can see that you are using [M1_SU].[dbo].[PartOrgReferences] . Is this a linked server you are trying to call? If yes then the second query seems problematic and you are missing a table on the link.
    Finally, try calling a top 1 from one table ONLY. Does it work?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: ADODB Recordset not populating

    Quote Originally Posted by ssabc View Post
    I have very similar logic in the same program. I have also tested the query in sql and it pulls what I am looking for.

    In the past, there have been access issues on certain tables, using Visual Studio only. I'm just guessing this could be a problem.

    One thing I do question is this, with all the references to the database and [dbo] syntax with brackets:
    Code:
    Dim sql As String = "SELECT [PartOrgReferences].[imzPartID], [Organizations].[cmoName] " &
                                    "FROM [M1_SU].[dbo].[PartOrgReferences] " &
                                    "INNER JOIN [Organizations] ON [PartOrgReferences].[imzOrganizationID] = [Organizations].[cmoOrganizationID] " &
                                    "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    Because this is all that is required in SQL Server:
    Code:
            Dim sql As String = "SELECT PartOrgReferences.imzPartID, Organizations.cmoName " &
                                "FROM  PartOrgReferences " &
                                "INNER JOIN Organizations ON PartOrgReferences.imzOrganizationID = Organizations.cmoOrganizationID " &
                                "WHERE (PartOrgReferences.imzPartID LIKE '" & _pn.Trim & "%') "
    If the one query works in SQL Server w/o the fully qualified name (FQN) ... then why are you using it in the VB code?
    Where did it all come from?


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: ADODB Recordset not populating

    IS M1_SU a database name? Or a server Name.... if dbname then it is fine that was if it is a linked server then you need more for the fully qualified name
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

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



Click Here to Expand Forum to Full Width