ODBC Driver (for SQL Server 2005) unable to retrive CRecorset''s fields values

I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.

State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index

I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.

Please help me!

Michael.

I am having the same exact problem! Does anyone have any input?

Jon

|||Me too.|||

I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.

ODBC Driver (for SQL Server 2005) unable to retrive CRecorset''s fields values

I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.

State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index

I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.

Please help me!

Michael.

I am having the same exact problem! Does anyone have any input?

Jon

|||Me too.|||

I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.

ODBC Driver (for SQL Server 2005) unable to retrive CRecorset's fields values

I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.

State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index

I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.

Please help me!

Michael.

I am having the same exact problem! Does anyone have any input?

Jon

|||Me too.

|||

I solved my problem. My situdation is: I use MFC wizard to generate the ODBC code and all the columns have been bound. I got the same error as your guys when I call GetFieldValue(). But since all columns have been bound, don't call GetFieldValue(), instead, directly access the member variable of the derived recordset class. Then problem disappeard.

sql

ODBC Driver (for SQL Server 2005) unable to retrive CRecorset's fields values

I am using MFC ODBC classes in my VC++ application for database accesss (on a SQL Server 2005 Database).
After opening a Recordset I have to use the CRecordset::GetFieldValue( LPCTSTR lpszName, CDBVariant& varValue, short nFieldType = DEFAULT_FIELD_TYPE ) method of the CRecordset object to obtain field values. The above method works fine with ODBC drivers for Jet but when I use ODBC for SQL Server it throws the following exception.

State:S1002,Native:0,Origin:[Microsoft][
ODBC SQL Server Driver] Invalid
Descriptor Index

I don't understand why the Microsoft SQL Server ODBC Driver is throwing this
exception.

Please help me!

Michael.

I am having the same exact problem! Does anyone have any input?

Jon

Obtaining values on datasource inserting event

Hi,

I want to be able to spot when the same employee name gets added to my grid. This is to ensure that I cannot not have the same firstname and lastname (i.e. cannot have 2 John Smiths).

It is kind of like spoting for duplicates but they are not PKs. I was hoping if there was a way you could identify the the feild values on the "inserting" event of the datasource so I could put some logic in. The reason for placing it there is because we have the e.cancel = true command.

Thanks in advance,

Jon

Since you are going to have to query the database to get this information, why not just use If Not Exists in your SQL?

|||

Mike,

Thanks for getting back so promptly. I understand what you are saying what does the SQL look like ? would you be able to provide a very basic example?

Just to give you a little more background, I am using a mixture of standard ASP controls and also using the RAD controls. Currently I focus on the controls to perform this kind of checking and would like a more generic solution at the Datasource level, this is so I dont need to worry about specific coding based on which control I use.

Thanks

|||

You would put it in a stored procedure:http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5

Note: there are some other ideas in that article. You can review those as well.

|||

Thanks, marked as answered

|||

You can create a procedure like below and bind it as the insert command for your sql datasource. Then you can execute the insert method in a try catch block and display the error message to the user:

create procedure pcheckDuplicates_insert(@.firstNamevarchar(50) ,@.lastNamevarchar(50))asbeginifexists (select 1from checkDuplicateswhere firstName = @.firstNameand lastName = @.lastName )beginraiserror ('A person with this name already exists',16,1)with nowaitreturnendelsebegininsert checkDuplicates ( firstName , lastName )values ( @.firstName , @.lastName )endreturnend

I don't think you'll need that inserting event of the datasource. You can directly write all your duplicate prevention logic in the procedure itself.

Hope this will help.

obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
DanCreate a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> >
> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
number of indexes.
> >
> > I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages
> > Removed). However I cannot seem to find a way to do this
> >
> > Can anyone help please?
> >
> > Thanks in advance
> >
> > Dan
> >
>|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
>> Create a table with the same structure as the DBCC returns and then use
> below technique:
>> INSERT theTable
>> EXEC('DBCC...')
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
>> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
>> > Hi All,
>> >
>> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> number of indexes.
>> >
>> > I wish to then manipulate the values from the result set (Pages
>> > Scanned,
> Pages Moved, Pages
>> > Removed). However I cannot seem to find a way to do this
>> >
>> > Can anyone help please?
>> >
>> > Thanks in advance
>> >
>> > Dan
>> >
>>
>|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> > You can't do that as the insert/exec creates a user transaction and
> > INDEXDEFRAG cannot run inside a user transaction. There's no way to
> > capture
> > the results from INDEXDEFRAG except using an external text file.
> >
> > Dan - what do you want to do with the values, out of interest?
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> >> Create a table with the same structure as the DBCC returns and then use
> > below technique:
> >>
> >> INSERT theTable
> >> EXEC('DBCC...')
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> >> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> >> > Hi All,
> >> >
> >> > I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a
> > number of indexes.
> >> >
> >> > I wish to then manipulate the values from the result set (Pages
> >> > Scanned,
> > Pages Moved, Pages
> >> > Removed). However I cannot seem to find a way to do this
> >> >
> >> > Can anyone help please?
> >> >
> >> > Thanks in advance
> >> >
> >> > Dan
> >> >
> >>
> >>
> >
> >
>

obtaining return values from DBCC INDEXDEFRAG in a sp

Hi All,
I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number
of indexes.
I wish to then manipulate the values from the result set (Pages Scanned,
Pages Moved, Pages Removed). However I cannot seem to find a way to do this
Can anyone help please?
Thanks in advance
Dan
Create a table with the same structure as the DBCC returns and then use below technique:
INSERT theTable
EXEC('DBCC...')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am writing a stored procedure that performs a DBCC INDEXDEFRAG on a number of indexes.
> I wish to then manipulate the values from the result set (Pages Scanned, Pages Moved, Pages
> Removed). However I cannot seem to find a way to do this
> Can anyone help please?
> Thanks in advance
> Dan
>
|||You can't do that as the insert/exec creates a user transaction and
INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
the results from INDEXDEFRAG except using an external text file.
Dan - what do you want to do with the values, out of interest?
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> Create a table with the same structure as the DBCC returns and then use
below technique:[vbcol=seagreen]
> INSERT theTable
> EXEC('DBCC...')
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
> news:uJ2XdA5qEHA.4044@.TK2MSFTNGP09.phx.gbl...
number of indexes.[vbcol=seagreen]
Pages Moved, Pages
>
|||Ah, thanks Paul. I didn't consider the transaction aspect of it...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> below technique:
> number of indexes.
> Pages Moved, Pages
>
|||Hi Paul,
I want to put them into a "User Friendly" report, that basically lists the
time, index defragged and the pages scanned, moved, etc.
Regards
Dan
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
> You can't do that as the insert/exec creates a user transaction and
> INDEXDEFRAG cannot run inside a user transaction. There's no way to
> capture
> the results from INDEXDEFRAG except using an external text file.
> Dan - what do you want to do with the values, out of interest?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uZVSYG5qEHA.2732@.TK2MSFTNGP09.phx.gbl...
> below technique:
> number of indexes.
> Pages Moved, Pages
>
|||I'm afraid the only way to do it is to output the results to a flat file and
then post-process.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <dan.parker@._nospam_pro-bel.com> wrote in message
news:uE0WCTFrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I want to put them into a "User Friendly" report, that basically lists the
> time, index defragged and the pages scanned, moved, etc.
> Regards
> Dan
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23GZ7H38qEHA.708@.tk2msftngp13.phx.gbl...
>

Obtain values from different tables

Table processes_user
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.