General discussion

Locked

VB6 ADO code

By ebartholom ·
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.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VB6 ADO code

by roc180 In reply to VB6 ADO code

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

Collapse -

VB6 ADO code

by ebartholom In reply to VB6 ADO code

Poster rated this answer

Collapse -

VB6 ADO code

by ebartholom In reply to VB6 ADO code

This works just fine. Thanks for the help!

Collapse -

VB6 ADO code

by Cedric King In reply to VB6 ADO code

Think if it helps you, you should close this question. Thanks.

Collapse -

VB6 ADO code

by ebartholom In reply to VB6 ADO code

Poster rated this answer

Collapse -

VB6 ADO code

by Conrad In reply to VB6 ADO code

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


--Disraeli

Collapse -

VB6 ADO code

by ebartholom In reply to VB6 ADO code

Poster rated this answer

Collapse -

VB6 ADO code

by ebartholom In reply to VB6 ADO code

This question was closed by the author

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums