Microsoft KB Archive/294349

= How To Use ADO and SQL Server to Select Multiple Records by Unique Field =

Article ID: 294349

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 3
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 4
 * Microsoft Visual Basic 6.0 Enterprise Edition Service Pack 5
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q294349



SUMMARY
Sometimes the need arises to select one or more unique values from a single field in an application. For example, this is useful when you want to query a multiselect list box. The sample code in this article demonstrates how to return a recordset based on values that match a list of comma-delimited values that are sent to a SQL Server by using the IN clause.

Note that this method is the fastest way to return records based on multiple unique values. The SQL statement provides the minimum information that is needed to return the results. The records that are returned are only the records that are queried. You can further optimize the SQL statement to return only the fields that are needed.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: This sample code requires Visual Basic 6.0 and SQL Server 7.0 or later.  Create a Visual Basic Standard EXE project. Form1 is created by default. Add a reference to Microsoft ActiveX Data Objects version 2.1 or later. Add a Command button and ListBox control to Form1. Change the MultiSelect property for the ListBox control to 1 - Simple.  Add the following code to the form.

Note You must change User ID = and password = to the correct values before you run this code. Make sure that  has the appropriate permissions to perform this operation on the database. Private Sub Command1_Click Dim CNN As New ADODB.Connection Dim RS As ADODB.Recordset, FLD As ADODB.Field Dim Criteria As String, Cnt As Long ' This section builds a criteria string of one or more items ' that are selected in the ListBox. For more information, ' see the Visual Basic online help topic regarding the MultiSelect ' property. Criteria = &quot;&quot; For Cnt = 0 To List1.ListCount - 1 If List1.Selected(Cnt) Then If Len(Criteria) > 0 Then Criteria = Criteria & &quot;,&quot; Criteria = Criteria & &quot;'&quot; & List1.List(Cnt) & &quot;'&quot; End If   Next If Len(Criteria) = 0 Then MsgBox &quot;You must select at least one item.&quot;, vbInformation, &quot;Error&quot; Exit Sub End If   ' Open connection to SQL Server. You must change the Data Source value ' to the name of your SQL Server. Also, remember to provide the valid ' User ID and Password values for your SQL Server. CNN.Open &quot;Provider=SQLOLEDB;Data Source=kb;&quot; & _ &quot;Initial Catalog=pubs;User ID=;Password= ;&quot; ' Run the command, and return a recordset. Set RS = CNN.Execute(&quot;SELECT * FROM Authors WHERE au_lname IN (&quot; & Criteria & &quot;)&quot;) ' This section sends the recordset results to the Immediate window. Debug.Print &quot;Results&quot; & vbCrLf & &quot;---&quot; Do Until RS.EOF For Each FLD In RS.Fields Debug.Print FLD.Value, Next Debug.Print RS.MoveNext Loop

RS.Close CNN.Close End Sub

Private Sub Form_Load ' The following code adds a list of items that can be selected in a list box. List1.AddItem &quot;Dull&quot; List1.AddItem &quot;Green&quot; List1.AddItem &quot;White&quot; List1.AddItem &quot;Ringer&quot; List1.AddItem &quot;Stringer&quot; End Sub </li> Run the project. Notice that only records that match what is selected in the list box are returned in the Immediate window.</li></ol>

<div class="references_section">