Hi,
This is my basic sql shape query:
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !
Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>
|||thanks, it was helpfull !
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Monday, March 19, 2012
Advanced Sql-Shape Query - Help
Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !SHAPE is not a TSQL language element. If I'm not mistaken, it is implemented
in ADO. I have a
feeling that you will get better assistance if you post to a ADO group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<doar123@.gmail.com> wrote in message news:1137102448.006011.123840@.g43g2000cwa.googlegroups
.com...
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !SHAPE is not a TSQL language element. If I'm not mistaken, it is implemented
in ADO. I have a
feeling that you will get better assistance if you post to a ADO group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<doar123@.gmail.com> wrote in message news:1137102448.006011.123840@.g43g2000cwa.googlegroups
.com...
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>
Advanced Sql-Shape Query - Help
Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !
Advanced Sql-Shape Query - Help
Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !
Advanced Sql-Shape Query - Help
Hi,
This is my basic sql shape query:
------------------
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
------------------
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !(doar123@.gmail.com) writes:
> This is my basic sql shape query:
> ------------------
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ------------------
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
If I get this right you want this in the SHAPE part:
SELECT * FROM tbl1
WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl2.field = tbl1.fiedl)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't know i can use the exists query - I will check it out and get
back to report.
thanks for your help !|||Well, It works like a charm !!!!
thank you !!!
Sunday, March 11, 2012
Advanced Join?
I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:
Table X
ID
-
2,1,4,
2,5,
1,
3,1,2,4,
ect...
whereas the ID field in Table Y contains one ID in each row EX:
Table Y
ID Color
- -
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...
Is there a way to join these two tables together? I need the output to be...
ID Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
ect...
Yes, but it will be messy and inefficient. And most likely, it will ignore any indexing.
You have violated a cardinal rule with ID columns. In order to efficiently link tables, they must be individual values -NOT simulated arrays of values. This is really is a bad design that will continue to haunt you throughout the project lifecycle.
SELECT
x.ID,
y.Color
FROM Table_X x
JOIN Table_Y x
ON ( ',' + x.ID ) LIKE ( ',' + y.ID + ',' )
There are some other methods as well, one of which would create a #Temp table, split apart the separate ID's in Table_X, and then JOIN that #Temp table with these two tables. Here is a link to that approach. And another.
You really 'shouldn't do that kind of 'trickery' with key values. The continual hassle with the data just isn't worth the 'buzz' gained from being so clever...
It's NOT an advanced JOIN, it is a very, very silly table design!
|||If you think this is "silly" you should see the rest of the database! The sad part about this whole thing is that our company purchased this application from a leading medical management software company for 1,$$$,$$$. Your example doesn't give me the output I was looking for but I will try the example from the link you provided on Monday when I get back to work. Thanks for your help.|||
I see that I left out an important piece of the code. My apologies.
SELECT
x.ID,
y.Color
FROM Table_X x
JOIN Table_Y x
ON ( ',' + x.ID ) LIKE ( '%,' + y.ID + ',%' )
That 'should' work a bit better.
I understand. Sometimes I just don't get how seemingly intelligent folks spend so much money for such poorly designed software. But it happens every day. And then we have to support it.
|||Your query gives me....
Table_X.ID Table_Y.Color
2,1,4, Blue
2,1,4, Green
2,1,4, Yellow
2,5, Blue
2,5, Orange
1, Green
3,1,2,4, Red
3,1,2,4, Green
3,1,2,4, Blue
3,1,2,4, Yellow
How do you get it to.?.?.?.
Table_X.ID Table_Y.Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
What you are now requesting is a function for the display tool. The client application 'should' be creating a string array to put those values together. Folks often forget, SQL Server excels at storing and retrieving data, not at creating displays. But so many application are being created by folks that really aren't very good programmers at all, so things like this get stuffed into SQL Server.
You can use one of the methods listed in these resources.
Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx
|||
Thanks for all your help!!!
Advanced Charting Controls
Can someone show me some advanced Charting examples
I want to embed a chart into a field in a table
I want the result of the count to show ontop
explain about the max - chart is not showing correct grading
thank
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com1. You can create a chart in a table - this chart will have the data
for within that table only. Try cutting and pasting the chart into a
cell once you have created it.
2. You can add values as labels on charts - counts can also be
displayed.
3. Not sure what you mean here - sorry.
Hope this helps.
On Jan 11, 1:20 am, jewel<jewelfi...@.gmail.com> wrote:
> Can someone show me some advanced Charting examples
> I want to embed a chart into a field in a table
> I want the result of the count to show ontop
> explain about the max - chart is not showing correct grading
> thanks
> Fromhttp://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-se...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
I want to embed a chart into a field in a table
I want the result of the count to show ontop
explain about the max - chart is not showing correct grading
thank
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com1. You can create a chart in a table - this chart will have the data
for within that table only. Try cutting and pasting the chart into a
cell once you have created it.
2. You can add values as labels on charts - counts can also be
displayed.
3. Not sure what you mean here - sorry.
Hope this helps.
On Jan 11, 1:20 am, jewel<jewelfi...@.gmail.com> wrote:
> Can someone show me some advanced Charting examples
> I want to embed a chart into a field in a table
> I want the result of the count to show ontop
> explain about the max - chart is not showing correct grading
> thanks
> Fromhttp://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-se...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
Thursday, March 8, 2012
ADSI Group Members and SQL
Does anyone have a good example of querying group members out of Active
Directory Group. The error seems to happen when the field I am querying has
multiple vales
This is what I have so far:
exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
'ADsDSOObject', 'adsdatasource'
select
convert(varchar(50), [Name]) as GroupName,
convert(varchar(500), member) as member
from openquery(xxx,
'select name, member
from ''LDAP://DC=domain,DC=com''
where objectClass = ''Group''')
This is the error message I am getting:
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
Could not convert the data value due to reasons other than sign mismatch or
overflow.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData returned
0x40eda: Data status returned from the provider: [COLUMN_NAME=member
STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
STATUS=DBSTATUS_S_OK]].
Thanks,
Kevin E.Hi,
The "member" attribute is multi-valued. ADO returns this as an array. I
assume the OLE/DB driver does as well. It can have no values, one, or many.
You should test your code with all 3 possibilities. I would have to
experiment using linkedserver.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:tvqdnWW6j--KbwjfRVn-jg@.centurytel.net...
> Does anyone have a good example of querying group members out of Active
> Directory Group. The error seems to happen when the field I am querying
has
> multiple vales
> This is what I have so far:
> exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
> 'ADsDSOObject', 'adsdatasource'
>
> select
> convert(varchar(50), [Name]) as GroupName,
> convert(varchar(500), member) as member
> from openquery(xxx,
> 'select name, member
> from ''LDAP://DC=domain,DC=com''
> where objectClass = ''Group''')
>
> This is the error message I am getting:
> Server: Msg 7346, Level 16, State 2, Line 1
> Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
> Could not convert the data value due to reasons other than sign mismatch
or
> overflow.
> OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData
returned
> 0x40eda: Data status returned from the provider: [COLUMN_NAME=member
> STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
> STATUS=DBSTATUS_S_OK]].
>
> Thanks,
> Kevin E.
>
>
Directory Group. The error seems to happen when the field I am querying has
multiple vales
This is what I have so far:
exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
'ADsDSOObject', 'adsdatasource'
select
convert(varchar(50), [Name]) as GroupName,
convert(varchar(500), member) as member
from openquery(xxx,
'select name, member
from ''LDAP://DC=domain,DC=com''
where objectClass = ''Group''')
This is the error message I am getting:
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
Could not convert the data value due to reasons other than sign mismatch or
overflow.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData returned
0x40eda: Data status returned from the provider: [COLUMN_NAME=member
STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
STATUS=DBSTATUS_S_OK]].
Thanks,
Kevin E.Hi,
The "member" attribute is multi-valued. ADO returns this as an array. I
assume the OLE/DB driver does as well. It can have no values, one, or many.
You should test your code with all 3 possibilities. I would have to
experiment using linkedserver.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"KevinE" <eckart_612@.hotmail.com> wrote in message
news:tvqdnWW6j--KbwjfRVn-jg@.centurytel.net...
> Does anyone have a good example of querying group members out of Active
> Directory Group. The error seems to happen when the field I am querying
has
> multiple vales
> This is what I have so far:
> exec sp_addlinkedserver 'xxx', 'Active Directory Services 2.5',
> 'ADsDSOObject', 'adsdatasource'
>
> select
> convert(varchar(50), [Name]) as GroupName,
> convert(varchar(500), member) as member
> from openquery(xxx,
> 'select name, member
> from ''LDAP://DC=domain,DC=com''
> where objectClass = ''Group''')
>
> This is the error message I am getting:
> Server: Msg 7346, Level 16, State 2, Line 1
> Could not get the data of the row from the OLE DB provider 'ADsDSOObject'.
> Could not convert the data value due to reasons other than sign mismatch
or
> overflow.
> OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IRowset::GetData
returned
> 0x40eda: Data status returned from the provider: [COLUMN_NAME=member
> STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=name
> STATUS=DBSTATUS_S_OK]].
>
> Thanks,
> Kevin E.
>
>
Tuesday, March 6, 2012
Adodb.field error 80020009 in my code
Hi everybody,
I really don't find the bug in my code. It returns Adodb.field error '80020009'. I tried various things but it don't works. Can U look at my code and find the bug....
-------------------
My connection object is 'dbconn'
My object query is 'rssqlselect_clients'
---------------------
<form name="monform">
<select name="listeA" onchange=changeliste()>
<option value=0>Choisit une liste</option>
<% rssqlselect_clients.moveFirst
while not rssqlselect_clients.eof %>
<% while not t > c %>
<option value=<%= t %>>Liste <%
response.write rssqlselect_clients("nomclient")
%> </option>
<%
t = t + 1
wend %>
<%rssqlselect_clients.moveNext
wend
rssqlselect_clients.close
Dbconn.close%>
------------
Thank U very much.
BaRRonWhat line of script is generating your error?
I really don't find the bug in my code. It returns Adodb.field error '80020009'. I tried various things but it don't works. Can U look at my code and find the bug....
-------------------
My connection object is 'dbconn'
My object query is 'rssqlselect_clients'
---------------------
<form name="monform">
<select name="listeA" onchange=changeliste()>
<option value=0>Choisit une liste</option>
<% rssqlselect_clients.moveFirst
while not rssqlselect_clients.eof %>
<% while not t > c %>
<option value=<%= t %>>Liste <%
response.write rssqlselect_clients("nomclient")
%> </option>
<%
t = t + 1
wend %>
<%rssqlselect_clients.moveNext
wend
rssqlselect_clients.close
Dbconn.close%>
------------
Thank U very much.
BaRRonWhat line of script is generating your error?
Sunday, February 19, 2012
ADO recordset field limitations
I am having trouble inserting a record into SQL using an ADO recordset.
Here's the line in question:
rs!ServerMessage = Data
It appears that when my data string is longer that 255 characters, it only
inserts the first 255 characters into the table and throws the rest away
without any errors. The SQL column is defined as varchar(5000).
Any ideas as to how I can get the full string into the database?
MS SQL 2000, VB6
Thanks,
StephenThree words: show more code.
Send us your DDL, your code, and then maybe we can see the problem.
ML|||The code below is collected from several different locations in the app.
Hopefully this is what you need.
Dim cn As ADODB.Connection
Dim provStr As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sqloledb"
provStr = " Provider=SQLOLEDB;Server=xxxxx;Database=
xxxxx;User
Id=xxxxx;Password = xxxxx"
cn.Open provStr
Set rs.ActiveConnection = cn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set rs.ActiveConnection = cn
rs.Open "Events", cn, , , adCmdTable
rs.AddNew
rs!ServerName = sName
rs!ServerIP = RemoteIP
rs!ServerMessage = Data
rs!DateTime = Date & " " & Time
rs.Update
If rs.State Then
rs.Close
End If|||Any special reason for not using stored procedures?
I don't see the Data variable being declared. Can it be that its length is
255 bytes?
Incidentally - 'Date & " " & Time' ? Please, use appropriate datatypes! The
Universe will be grateful...
ML|||I have verified that the Data variable does hold the complete string. But it
doesn't end up in the database.
>The Universe will be grateful...
Isn't that a little overboard?|||How are you determining that you only see 255 characters?
If you are doing it though query analyzer, be default each column will only
show 255 characters.
To change this, in QA Tools>Options, Results Tab, Maximum Characters per
Column
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>I am having trouble inserting a record into SQL using an ADO recordset.
> Here's the line in question:
> rs!ServerMessage = Data
> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters into the table and throws the rest away
> without any errors. The SQL column is defined as varchar(5000).
> Any ideas as to how I can get the full string into the database?
> MS SQL 2000, VB6
> Thanks,
> Stephen|||Luckily ML answered and not CELKO, then it would have been a right royal
chewing-out.
:)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C9556212-5C24-490A-81F4-C6EB84289192@.microsoft.com...
>I have verified that the Data variable does hold the complete string. But
>it
> doesn't end up in the database.
>
> Isn't that a little overboard?
>|||> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters
What does "appears" mean? What tool are you using to verify the length of
the data? Have you checked SELECT LEN(column) FROM table, and not just the
string result?|||That was exactly the problem. I increased the maximum characters, and saw al
l
of the information.
Thank you for taking the time to help me out.
"Mike Epprecht (SQL MVP)" wrote:
> How are you determining that you only see 255 characters?
> If you are doing it though query analyzer, be default each column will onl
y
> show 255 characters.
> To change this, in QA Tools>Options, Results Tab, Maximum Characters per
> Column
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>
>|||I ran the Len(column) as you suggested and many of the values returned were
well over 255 characters. Mike helped me out by suggesting increasing the
maximum characters in query analyzer. Then I was able to see all of the data
.
Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:
> What does "appears" mean? What tool are you using to verify the length of
> the data? Have you checked SELECT LEN(column) FROM table, and not just th
e
> string result?
>
>
Here's the line in question:
rs!ServerMessage = Data
It appears that when my data string is longer that 255 characters, it only
inserts the first 255 characters into the table and throws the rest away
without any errors. The SQL column is defined as varchar(5000).
Any ideas as to how I can get the full string into the database?
MS SQL 2000, VB6
Thanks,
StephenThree words: show more code.
Send us your DDL, your code, and then maybe we can see the problem.
ML|||The code below is collected from several different locations in the app.
Hopefully this is what you need.
Dim cn As ADODB.Connection
Dim provStr As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "sqloledb"
provStr = " Provider=SQLOLEDB;Server=xxxxx;Database=
xxxxx;User
Id=xxxxx;Password = xxxxx"
cn.Open provStr
Set rs.ActiveConnection = cn
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
Set rs.ActiveConnection = cn
rs.Open "Events", cn, , , adCmdTable
rs.AddNew
rs!ServerName = sName
rs!ServerIP = RemoteIP
rs!ServerMessage = Data
rs!DateTime = Date & " " & Time
rs.Update
If rs.State Then
rs.Close
End If|||Any special reason for not using stored procedures?
I don't see the Data variable being declared. Can it be that its length is
255 bytes?
Incidentally - 'Date & " " & Time' ? Please, use appropriate datatypes! The
Universe will be grateful...
ML|||I have verified that the Data variable does hold the complete string. But it
doesn't end up in the database.
>The Universe will be grateful...
Isn't that a little overboard?|||How are you determining that you only see 255 characters?
If you are doing it though query analyzer, be default each column will only
show 255 characters.
To change this, in QA Tools>Options, Results Tab, Maximum Characters per
Column
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>I am having trouble inserting a record into SQL using an ADO recordset.
> Here's the line in question:
> rs!ServerMessage = Data
> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters into the table and throws the rest away
> without any errors. The SQL column is defined as varchar(5000).
> Any ideas as to how I can get the full string into the database?
> MS SQL 2000, VB6
> Thanks,
> Stephen|||Luckily ML answered and not CELKO, then it would have been a right royal
chewing-out.
:)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C9556212-5C24-490A-81F4-C6EB84289192@.microsoft.com...
>I have verified that the Data variable does hold the complete string. But
>it
> doesn't end up in the database.
>
> Isn't that a little overboard?
>|||> It appears that when my data string is longer that 255 characters, it only
> inserts the first 255 characters
What does "appears" mean? What tool are you using to verify the length of
the data? Have you checked SELECT LEN(column) FROM table, and not just the
string result?|||That was exactly the problem. I increased the maximum characters, and saw al
l
of the information.
Thank you for taking the time to help me out.
"Mike Epprecht (SQL MVP)" wrote:
> How are you determining that you only see 255 characters?
> If you are doing it though query analyzer, be default each column will onl
y
> show 255 characters.
> To change this, in QA Tools>Options, Results Tab, Maximum Characters per
> Column
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:1FFC354E-2762-4D2D-BD3D-1F2DF1E81D5E@.microsoft.com...
>
>|||I ran the Len(column) as you suggested and many of the values returned were
well over 255 characters. Mike helped me out by suggesting increasing the
maximum characters in query analyzer. Then I was able to see all of the data
.
Thanks.
"Aaron Bertrand [SQL Server MVP]" wrote:
> What does "appears" mean? What tool are you using to verify the length of
> the data? Have you checked SELECT LEN(column) FROM table, and not just th
e
> string result?
>
>
Monday, February 13, 2012
ado 2.8 and sqlxml.... retrieve from field?
hello,
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbijaHello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlX
ml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.com...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work :)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbijaHello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlX
ml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.com...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work :)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
ado 2.8 and sqlxml.... retrieve from field?
hello,
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbija
Hello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlXml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.co m...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbija
Hello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlXml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.co m...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
ado 2.6 question
I am trying to do something very simple. I merely want to get a field from an SQL table. Here is my code. Everything works fine except when I look at the lRecords, I get -1, even though the iRc=0. Please tell me what is wrong?
iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);
iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);RecordCount returns always -1 when you implement a dynamic cursor.
Try to use a static one, or count the records by yourself using SELECT COUNT(*) from your_table WHERE your_condition
Originally posted by wooliewillie
I am trying to do something very simple. I merely want to get a field from an SQL table. Here is my code. Everything works fine except when I look at the lRecords, I get -1, even though the iRc=0. Please tell me what is wrong?
iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);
iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);|||Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
I noticed that you used an ODBC connection. Try to declare a connection object without using the ODBC layer. The connection object has a property "CursorType";you can choose between dynamic,static,Keyset and ForwardOnly (see ado documentation for more).
To count the records directly you can use the SELECT statement posted before (SELECT COUNT(*) as TotalRecords FROM your_table WHERE your_condition). It will return a recordset of one->examine the value of TotalRecords.
Originally posted by wooliewillie
Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
>>I noticed that you used an ODBC connection.
This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?|||To change the provider, simply replace MSDASQL with SQLOLEDB in your connection string, but I'd rather declare a connection object and use the object properties afterwards.
Originally posted by wooliewillie
>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
>>I noticed that you used an ODBC connection.
This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?
iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);
iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);RecordCount returns always -1 when you implement a dynamic cursor.
Try to use a static one, or count the records by yourself using SELECT COUNT(*) from your_table WHERE your_condition
Originally posted by wooliewillie
I am trying to do something very simple. I merely want to get a field from an SQL table. Here is my code. Everything works fine except when I look at the lRecords, I get -1, even though the iRc=0. Please tell me what is wrong?
iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);
iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);|||Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
I noticed that you used an ODBC connection. Try to declare a connection object without using the ODBC layer. The connection object has a property "CursorType";you can choose between dynamic,static,Keyset and ForwardOnly (see ado documentation for more).
To count the records directly you can use the SELECT statement posted before (SELECT COUNT(*) as TotalRecords FROM your_table WHERE your_condition). It will return a recordset of one->examine the value of TotalRecords.
Originally posted by wooliewillie
Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
>>I noticed that you used an ODBC connection.
This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?|||To change the provider, simply replace MSDASQL with SQLOLEDB in your connection string, but I'd rather declare a connection object and use the object properties afterwards.
Originally posted by wooliewillie
>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).
>>I noticed that you used an ODBC connection.
This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?
Subscribe to:
Posts (Atom)