-
Dec 2nd, 2020, 04:31 PM
#1
Thread Starter
Fanatic Member
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!
-
Dec 2nd, 2020, 05:16 PM
#2
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 .
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 2nd, 2020, 05:21 PM
#3
Thread Starter
Fanatic Member
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!
-
Dec 2nd, 2020, 05:32 PM
#4
Re: ADODB Recordset not populating
What do you mean "works elsewhere"?
Where does it work?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 2nd, 2020, 06:07 PM
#5
Thread Starter
Fanatic Member
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!
-
Dec 2nd, 2020, 06:30 PM
#6
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 3rd, 2020, 09:02 AM
#7
Re: ADODB Recordset not populating
Originally Posted by ssabc
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
-
Dec 3rd, 2020, 10:15 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|