need assistance

squeakie
10-28-2004, 01:05 AM
:cool:
I need help with this!!!! i have a connection established and all. i am trying to run a query that only returns distinct titles.
Here's an example of how it would look in MS Access

SELECT Customers.ContactTitle, Count(Customers.ContactTitle) AS CountOfContactTitle
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.ContactTitle
ORDER BY Customers.ContactTitle;

However my problem is that I want to do the same similar thing in vb, although I want it to prompt the user to enter the first letter of the contactTitle in which to search for. Diplsay that information in Excel, and if there are ContactTitles that start with the same letter however they are different, the program should inform the user of the different types of contactTitles available. The user should choose the contactTitle of their choice, and then the program should end. All of this information is coming from a MS db, and the queries performed should be displayed in Excel.If anyone could help me in any way I would greatly appreciate it. Any advice would help.
If anyone know of any other way to do this please let me know.

Thanks
Squeakie

Dennis DVR
10-28-2004, 01:53 AM
The parameters statement can be ommited, I guess

PARAMETERS Param1 Text ( 255 );
SELECT Customers.ContactTitle, Count(Customers.ContactTitle) AS CountOfContactTitle
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID WHERE Customers.ContactTitle LIKE [Param1] & "%"
GROUP BY Customers.ContactTitle
ORDER BY Customers.ContactTitle;

we need to use the % instead of * since the query will be called from VB

To call that from VB you can either use a ADO recordset or ADO Command Object, but i prefer the "ADO Command object"

Using ADO Command Object

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample\db1.mdb"
cnn.CursorLocation = adUseClient 'without this default cursor location will be
'use, which is the the server side cursor, and the result would probably wrong
cnn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "Query5" 'is the name of the query in access
cmd.CommandType = adCmdStoredProc
'create the parameter
cmd.Parameters.Append cmd.CreateParameter("ClientTitle", adVarChar, adParamInput, 50, Text1.Text)
Set MSHFlexGrid1.DataSource = cmd.Execute 'pass the result to flexgrid control
Set cmd = Nothing
cnn.Close
Set cnn = Nothing


Using ADO Recordset

Dim rs As ADODB.Recordset
Dim strCnn As String
Set rs = New ADODB.Recordset
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample\db1.mdb"
rs.CursorLocation = adUseClient
rs.Open "Query5 '" & Text1.Text & "'", strCnn, adOpenDynamic, adLockReadOnly, adCmdStoredProc
Set MSHFlexGrid1.DataSource = rs
rs.Close
Set rs = Nothing

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum