Discussion Locked

VB6 ADO code

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am having trouble with a datacombo and ADO code command object:

I can successfully populate this datacombo using a dataenvironment and a parameterized recordset. However, I want to do the same thing using ADO code. I can populate a datacombo using ADO code if I don't use a command object and parameters, but I can't do it when I use the command object at run-time. I can't figure out why I can't do it at run-time!!

Here's the code,
Dim cn as adodb.connection
Dim rs2 as adodb.recordset
Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
Set rs2 = New ADODB.Recordset

'The boundtext of this datacombo is the EmployeeID
Zid = dtcEmpName.BoundText

'Connection establishes a connection to SQL Server
Connection

cmd.ActiveConnection = cn

'***This SQL statement executes fine if I use the dataenvironment and pass a parameter

cmd.CommandText = "SELECT tblEmployee.EmpName, tblJobCodeDsc.JobTitle, tblSupervisory.CostCtr FROM tblJobCodeDsc INNER JOIN tblSupervisory INNER JOIN tblEmployee ON tblEmployee.EmpID = tblSupervisory.AppraiseeID ON tblSupervisory.JobCode = tblJobCodeDsc.JobCode WHERE tblSupervisory.AppraiseeID = ? AND (tblSupervisory.AppraiserID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER) OR tblSupervisory.ReviewerID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER))"

cmd.CommandType = adCmdText

Set rs2 = cmd.Execute(Parameters:=Zid)

'***I get the desired results

Do Until rs2.EOF
Debug.Print rs2("JobTitle")
rs2.MoveNext
Loop

'***However, if I set the rowsource of the datacombo = rs2, I don't get any values in the datacombo

Set dtcJobTitle.RowSource = rs2
dtcJobTitle.ListField = "JobTitle"
dtcJobTitle.BoundColumn = "CostCtr"

'***I have successfully populated a datacombo using ADO code with a non-parameterized recordset - in other words, not using a command object in ADO code.

8 replies

by roc180

I believe that it can not retrive the rtecords when you execute the cmd object. Try this:

variable=Zid

cmd.CommandText = "SELECT tblEmployee.EmpName, tblJobCodeDsc.JobTitle, tblSupervisory.CostCtr FROM tblJobCodeDsc INNER JOIN tblSupervisory INNER JOIN tblEmployee ON tblEmployee.EmpID = tblSupervisory.AppraiseeID ON tblSupervisory.JobCode = tblJobCodeDsc.JobCode WHERE tblSupervisory.AppraiseeID = "& Variable &" AND (tblSupervisory.AppraiserID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER) OR tblSupervisory.ReviewerID = (SELECT tblEmployee.EmpID From tblEmployee WHERE NTUserName = SYSTEM_USER))"

cmd.CommandType = adCmdText

rs2.open cmd,,adopenkeyset
Set dtcJobTitle.RowSource = rs2

Notice I incorporated the variable(Zid) into the command object.text eliminating the need for a parameter. If this dosen't work let me know.

Good Luck

Neal Martinelli

by conrad

The more extensive a man's knowledge of what has been done, the greater will be his power of knowing what to do.

--Disraeli

This discussion is closed to new replies.