• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

VBscript VB6.0 SQL

Hi I have a problem.. I must do this in vbscript because it´s in a software... but I can´f figure it all out.. I feel it´s more diffrent
whit vbscript then "vb.net"
Can I get som help...
I know that the SQL connection works.. I can  delete lines.. I can write new lines
Now I can get one singel value

But I want to get all rows values...

DATABAS

Column
ID      Names      Age
2      Kalle              22
3      Nils              34
5      Olof               34
12      Klase      40


What I want to do is add all this diffrent names into a listbox och textbox, combox dropdownbox... whatever..
the simples of it...

Whit this code I can get one single value...



Sub OnLButtonDown(Byval Item, Byval Flags, Byval x, Byval y)                 

Dim rs
Dim results
Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand

strConnectionString = "Provider=MSDASQL;DSN=TESTDB;UID=;PWD=;"
strSQL =  "Select Names from TEST_DB"

'MsgBox(strSQL)
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
'objCommand.Execute


Set rs = objCommand.Execute
results = rs.Fields(0).Value


MsgBox(results)


Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing 

Open in new window

0
kavvis
Asked:
kavvis
  • 3
  • 2
2 Solutions
 
Rainer JeschorCommented:
Hi,
you would need a recordset e.g.
Sub OnLButtonDown(Byval Item, Byval Flags, Byval x, Byval y)                 

Dim rs
Dim results
Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand

strConnectionString = "Provider=MSDASQL;DSN=TESTDB;UID=;PWD=;"
strSQL =  "Select Names from TEST_DB"

'MsgBox(strSQL)
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set rs = CreateObject("ADODB.Recordset")

rs.Open strSQL, objConnection

Do while Not rs.EOF
	results = results + rs.Fields("Names").Value + ";"
Loop
rs.Close
Set rs = Nothing
objConnection.Close
Set objConnection = Nothing 

MsgBox(results)

Open in new window


HTH
Rainer
0
 
aikimarkCommented:
Once you have instantiated your connection object, use it as a parameter in your recordset OPEN method.

Then you can iterate through the recordset rows with a do loop
Example:
objConnection.Open
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, objConnection
Do Until rs.EOF
   'do stuff with your recordset data.  You can access your (in this case) only column with
   'rs!Names
   'rs.Fields("Names")
   'rs.Fields(0)
   rs.MoveNext
Loop

Open in new window

0
 
Rainer JeschorCommented:
Ups, I forgot a
rs.MoveNext after line 22 (before the loop).
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
kavvisAuthor Commented:
Thank you! it all worked verry good.
Now I looking for how to add it to listbox or combox..
0
 
Rainer JeschorCommented:
Hi,
where do you want to have the combobox meaning what is the UI technology? ASP or HTML page?
Just create a new question, add this one as reference and we would all be looking forward to help you out.
Thanks.

KR
Rainer
0
 
aikimarkCommented:
listbox and combobox controls have an AddItem method
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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