Database work in VB6

I don't know if this really belongs in the Database forum, but seeing as its using old tech (VB6 & SQL 2000), here goes...

I need to connect to a SQL 2000 database hosted on a server on our corporate network, preferabbly using a DSN connection. From this database, I need to pull customer address details (query below) based on whatever was entered into the txtQuery textbox when the user clicks the cmdSubmit Command Button.

The results of this query then need to output to list box lstSearch.

Query:

Code:

---

SELECT title, initials, full_name, company_name, house, address, city, county, postcode,
customer FROM cust WHERE customer = **CONTENTS FROM TXTQUERY TEXTBOX**

---

All help appreciated!

Code:

---

"SELECT title, initials, full_name, company_name, house, address, city, county, postcode,
customer FROM cust WHERE customer = '" & txtQuery & "' Order By full_name"

---

VB Code:

Do Until rs.EOF
  lstSearch.AddItem rs!full_name ' or more
  rs.Movenext
Loop

Thanks, but how do I go about setting up a connection string & running the query?


Here's a little more info - As a web developer, here's what I did in PHP - I was originally doing this project on the web, but the remit changed.

Code:

---

$connection_string = 'DRIVER={SQL Server};SERVER=COSYFEET02;DATABASE=eluciddb_test';

$user = '******';
$pass = '******';

$connection = odbc_connect( $connection_string, $user, $pass );
$sqlquery="SELECT top 10 title, initials, full_name, house, address, city, county, postcode, customer, company_name FROM cust ORDER BY NEWID();";
$process=odbc_exec($connection, $sqlquery);

---

VB Code:

Public conSQL As ADODB.Connection
Public rs As ADODB.Recordset
 
      Set conSQL = New ADODB.Connection
      With conSQL
        .ConnectionTimeout = 30
        .Provider = strProvider  ' strProvider=SQLOLEDB.1
        .CursorLocation = adUseClient
        .ConnectionString = "Server=" & strServer & ";" & "User Id=" _
                            & strUserId & ";" & "Password=" _
                            & pwd & ";" & "Initial Catalog=" & strInitCat ' strServer= yourServer Initial Catalog=yourDatabase
        .Open
        '.Execute "Set Dateformat 'dmy'" ' used in Norway to force date format
      End With

VB Code:

Set rs = New ADODB.Recordset
  With rs
    .ActiveConnection = conSQL
    .CursorLocation = adUseServer ' adUseClient
    .LockType = adLockReadOnly ' adLockOptimistic
    .CursorType = adOpenForwardOnly ' adOpenDynamic
    .Open sqlTekst ' the above select statement
  End With

Good Stuff. One more thing, how do I call the connection from a DSN?

Is it simply just

Code:

---

Conn.Open "DSN NAME GOES HERE"

---

?

I sometimes forget and must take the shortcut through Adodc1's 'Use Connection String' to find the syntax:

Code:

---

Provider=MSDASQL.1;Persist Security Info=False;Data Source=(yourODBC)

---