VB & Database, Query/Recordset problem on inner join with sql oledb provider

Query/Recordset problem on inner join with sql oledb provider

Author	Message
matt #1 / 4	Query/Recordset problem on inner join with sql oledb provider I have a query that populates an ADO recordset with an inner join between two tables with identical stuctures, representing two different sets of data. So, for example the tables are year and qtr: select * from [month] inner join [year] on [month].[employeeid] = [year].[employeeid] Now, let's say the the tables each have the field name TotalPay. When I try to reference rs![month].[TotalPay] or rs![year].[TotalPay] I receive the following error: "Error 3265: Item cannot be found in the collection corresponding to the requested name or ordinal." However, the problem is not bound to ADO explicitly, because I can access the the fields as shown above when I switch the provider to Jet 4. The problem is not within SQL Servers query engine, because if I run select [month].[TotalPay] ,[year].[TotalPay] from [month] inner join [year] on [month].[employeeid] = [year].[employeeid] in the SQL Server query analyzer I have no problems, as well I shouldn't. I was hoping there was something very obvious here that I am missing here. Thanks for any help, Matt
Mon, 10 Oct 2005 22:22:45 GMT

Val Mazu #2 / 4	Query/Recordset problem on inner join with sql oledb provider Hi, Did you try next kind of code rs.Fields("month.TotalPay").Value -- Val Mazur Microsoft MVP Quote: > I have a query that populates an ADO recordset with an inner join between > two tables with identical stuctures, representing two different sets of > data. > So, for example the tables are year and qtr: > select * from [month] inner join [year] on [month].[employeeid] = > [year].[employeeid] > Now, let's say the the tables each have the field name TotalPay. > When I try to reference rs![month].[TotalPay] or rs![year].[TotalPay] I > receive the following error: > "Error 3265: Item cannot be found in the collection corresponding to the > requested name or ordinal." > However, the problem is not bound to ADO explicitly, because I can access > the the fields as shown above when I switch the provider to Jet 4. The > problem is not within SQL Servers query engine, because if I run > select [month].[TotalPay] ,[year].[TotalPay] from [month] inner join [year] > on [month].[employeeid] = [year].[employeeid] > in the SQL Server query analyzer I have no problems, as well I shouldn't. > I was hoping there was something very obvious here that I am missing here. > Thanks for any help, > Matt
Tue, 11 Oct 2005 00:04:56 GMT

matt #3 / 4	Query/Recordset problem on inner join with sql oledb provider I actually produce the same error using that syntax, I appreciate the response though. Matt Quote: > Hi, > Did you try next kind of code > rs.Fields("month.TotalPay").Value > -- > Val Mazur > Microsoft MVP > > I have a query that populates an ADO recordset with an inner join between > > two tables with identical stuctures, representing two different sets of > > data. > > So, for example the tables are year and qtr: > > select * from [month] inner join [year] on [month].[employeeid] = > > [year].[employeeid] > > Now, let's say the the tables each have the field name TotalPay. > > When I try to reference rs![month].[TotalPay] or rs![year].[TotalPay] I > > receive the following error: > > "Error 3265: Item cannot be found in the collection corresponding to the > > requested name or ordinal." > > However, the problem is not bound to ADO explicitly, because I can access > > the the fields as shown above when I switch the provider to Jet 4. The > > problem is not within SQL Servers query engine, because if I run > > select [month].[TotalPay] ,[year].[TotalPay] from [month] inner join > [year] > > on [month].[employeeid] = [year].[employeeid] > > in the SQL Server query analyzer I have no problems, as well I shouldn't. > > I was hoping there was something very obvious here that I am missing here. > > Thanks for any help, > > Matt
Tue, 11 Oct 2005 01:18:22 GMT

Val Mazu #4 / 4	Query/Recordset problem on inner join with sql oledb provider Hi Matt, check which field name you have in your recordset for those duplicated fields. It could be that provider has changed them to something like Column1. You could check field name using next kind of code Debug.Print rs.Fields(10).Name -- Val Mazur Microsoft MVP Quote: > I actually produce the same error using that syntax, I appreciate the > response though. > Matt > > Hi, > > Did you try next kind of code > > rs.Fields("month.TotalPay").Value > > -- > > Val Mazur > > Microsoft MVP > > > I have a query that populates an ADO recordset with an inner join > between > > > two tables with identical stuctures, representing two different sets of > > > data. > > > So, for example the tables are year and qtr: > > > select * from [month] inner join [year] on [month].[employeeid] = > > > [year].[employeeid] > > > Now, let's say the the tables each have the field name TotalPay. > > > When I try to reference rs![month].[TotalPay] or rs![year].[TotalPay] I > > > receive the following error: > > > "Error 3265: Item cannot be found in the collection corresponding to the > > > requested name or ordinal." > > > However, the problem is not bound to ADO explicitly, because I can > access > > > the the fields as shown above when I switch the provider to Jet 4. The > > > problem is not within SQL Servers query engine, because if I run > > > select [month].[TotalPay] ,[year].[TotalPay] from [month] inner join > > [year] > > > on [month].[employeeid] = [year].[employeeid] > > > in the SQL Server query analyzer I have no problems, as well I > shouldn't. > > > I was hoping there was something very obvious here that I am missing > here. > > > Thanks for any help, > > > Matt
Tue, 11 Oct 2005 01:27:47 GMT

Page 1 of 1

[ 4 post ]