Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

VB6 ADO Return Records For Each Loop

Running a VB6 sql query and need to do something kind of crazy.

So I am running the following SQL query  SELECT username FROM MFUSERS WHERE USERNAME IN('test1', 'test2','test3')

I need it to create a string based off of what it has found.

So if it returns two rows and those two rows have TEST1 and TEST2 in them I need to concat to a string.

So for each record that it returns I would need it to build me a string as below example

Test1,test2

So delimited by comma

My Code Currentky

Sub Main()
    Dim finalvalue As String
    Dim database As New ADODB.Connection
Dim records As New ADODB.Recordset
Dim sql As String
Dim phones As String

database.ConnectionString="Provider=sqloledb; Data Source=192.168.2.121\EMMSDE;Initial Catalog=outlookreport; User Id=jsmith; Password=s5993153492;"
database.Open

sql = "SELECT * FROM OUTLOOKREPORT.DBO.MFUSERS WHERE USERNAME IN('" & AccountVariable("scriptrecip") & "'"
records.Open , sql, database, adLockReadOnly


For Each fld In records.Fields
finalvalue = finalvalue & records.Fields(0)
Next fld

End Sub

Open in new window

0
desiredforsome
Asked:
desiredforsome
  • 4
  • 3
  • 2
1 Solution
 
Hakan YılmazTechnical Office MEP EngineerCommented:
You have to iterate over records using records.MoveNext until end of recordset.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are currently doing a for each field instead of "for each record"

records.Open , sql, database, adLockReadOnly
while not records.EOF
finalvalue = finalvalue & records.Fields(0) & ","
records.movenext
wend

Open in new window

0
 
desiredforsomeAuthor Commented:
what is wend?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Hakan YılmazTechnical Office MEP EngineerCommented:
wend is end of while block
0
 
desiredforsomeAuthor Commented:
I think it could be possibly working however. It looks like my script is ignoring a part of it and it seems to happen after I open the DB. I cant figure this out.

The finalvalue = is not processing. Is there somethign I am doing wrong in my vb6?

Sub Main()
    Dim finalvalue As String
    Dim database As New ADODB.Connection
Dim records As New ADODB.Recordset
Dim sql As String
Dim phones As String

database.ConnectionString="Provider=sqloledb; Data Source=192.168.2.121\EMMSDE;Initial Catalog=outlookreport; User Id=jsmith; Password=s5993153492;"
database.Open

sql = "SELECT * FROM OUTLOOKREPORT.DBO.MFUSERS WHERE USERNAME IN('" & AccountVariable("scriptrecip") & "'"
records.Open , sql, database, adLockReadOnly


finalvalue = finalvalue & records.Fields(0) & ","
records.MoveNext

AccountVariable("test")=finalvalue
records.Close
database.Close


End Sub

Open in new window

0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
does it raise an error or give an empty string?

do you know structure of your database table? you may be reading wrong column.
0
 
desiredforsomeAuthor Commented:
looks like something possibly to do with data source name too long is what debug tells me hmmm.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
That error is about the connection string being incorrect...
0
 
desiredforsomeAuthor Commented:
I figured it out sorry lol. You WIN GUY!!! Final Code that worked below

Sub Main()

Dim finalvalue As String
Dim database As New ADODB.Connection
Dim records As New ADODB.Recordset
Dim sql As String
Dim phones As String
Dim totaloutput As String

database.ConnectionString="Provider=sqloledb; Data Source=192.168.2.121\EMMSDE;Initial Catalog=outlookreport; User Id=jsmith; Password=s5993153492;"
database.Open

sql = "SELECT * FROM OUTLOOKREPORT.DBO.MFUSERS WHERE USERNAME IN('" & AccountVariable("scriptrecip") & "')"
records.Open  sql, database, adLockReadOnly

While Not records.EOF

finalvalue = finalvalue & records.Fields(1) & ","

records.MoveNext
Wend
records.Close
database.Close

totaloutput = Left(finalvalue, Len(finalvalue) -1)

AccountVariable("test")=totaloutput




End Sub

Open in new window

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now