Question about Transact SQL - Database Discussion Boards

SqlDataReader : I need to assign retrieved value to C++ variable

Doug13-Oct-02 13:27

13-Oct-02 13:27

ADO.NET in C++
SqlDataReader

I can get the following to work, the result displaying on the screen:

Console::WriteLine(myReader->GetSqlValue(3));

What I really need to do is to assign the item retrieved to a C++ variable eg.

int halfhrSQL = myReader->GetSqlValue(3);

This gives the C2440 compile error: cannot convert from 'System::Object __gc *' to 'int'.

No matter what cast I use, I cannot get around the error.

Please help if you can.

Thanks, Doug (New Zealand)

Doug

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Nick Parker14-Oct-02 2:37

Nick Parker

14-Oct-02 2:37

Doug wrote:
This gives the C2440 compile error: cannot convert from 'System::Object __gc *' to 'int'.

Have you tried to convert the object instead of casting it? You might look into the Convert.ToInt16 method as it will accept an object as a paramerter.

HTH

Nick Parker

The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Doug15-Oct-02 12:15

Doug

15-Oct-02 12:15

Thanks Nick,

I have definitely got this working where the native C++ type is int and I am confident that I can now handle any numeric type.

What I am battling with is where the .NET value retrieved is a String (the SQL field is text).

I have managed to get the following to work:

String* busNumberSQL __gc[] = new String* __gc[97];
short halfhoursSQL = 0;
.
.
.
Console::WriteLine(myReader->GetString(0));
halfhoursSQL++;
busNumberSQL[halfhoursSQL] = Convert::ToString(myReader->GetValue(0));

However busNumberSQL is a managed array of type String*.

Now I need assign the value of busNumberSQL[halfhoursSQL] to a CString or native C++ string type. All I seem to end up with is the decimal representation of the hexidecimal memory address.

I have also tried:
Convert::ToChar(myReader->GetValue(0)) to assign one character
(Convert::ToString(myReader->GetValue(0)))->ToCharArray()

As I continue to try different things, do you have any idea how this may be done? I will let you know if I crack it.

(With the old ADO I was able to do this by doing a double cast: ---> _bstr_t --> LPCTSTR and then doing a simple assignment to a CString)

Thanks and regards

Doug

Doug

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Nick Parker15-Oct-02 16:38

Nick Parker

15-Oct-02 16:38

Doug wrote:
busNumberSQL[halfhoursSQL] = Convert::ToString(myReader->GetValue(0));

Can you do something like this:

busNumberSQL[halfhoursSQL] = myReader->get_Item("<code>field_name_here</code>")->ToString();

Nick Parker

The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill

Re: SqlDataReader : I need to assign retrieved value to C++ variable

Doug16-Oct-02 12:47

Doug

16-Oct-02 12:47

Hi Nick

Re: ADO.NET : SqlDataReader : I need to assign retrieved value to C++ variable

I have found the source of the problem expressed in the thread "ADO.NET : SqlDataReader : I need to assign retrieved value to C++ variable":
http://www.codeproject.com/script/comments/forums.asp?forumid=3785#xx309754xx

but do not yet know why it occurs or how I can fix it.

The problem arises with the /clr compiler setting.

Here is the code to illustrate this:

/////////////////////////////////////////////////////////

#include <stdafx.h>
#include <cstdlib>
#include "try_ADONET.h"

//#using <mscorlib.dll>
//#using <system.dll>
//#using <system.data.dll> // This is required for the ADO.NET Provider

using namespace std;
//using namespace System;

int Main()
{
CString busNumberSQL[2];
CString bus = "Hello";
busNumberSQL[1] = bus;

return 0;
}

////////////////////////////////////////////////////////

Without the /clr setting the array of CString busNumberSQL[2] is correctly contructed as is CString bus and the line

busNumberSQL[1] = bus;

works as expected. This can be seen by putting a breakpoint in at

return 0;

and seeing the variables in a Watch. Copy the expanded Watch details (Name, Value & Type) to an EXCEL sheet.
..................

Next, add the compiler switch /clr and rerun the code.

CString bus is constructed correctly BUT something weird happens in the construction of CString busNumberSQL[2].

Now,
busNumberSQL[1] = bus;

results in the address of bus as an integer being assigned to busNumberSQL[1], and look at the Watch details!!

Copy the expanded Watch details (Name, Value & Type) to an EXCEL sheet

Name: busNumberSQL; Value: {Length=2}; Type: ATL::CStringT<char,strtraitmfc<char,atl::chtraitscrt<char> > >[] ...(Note no dimension at end compared to the "no /clr" case.
Next Watch line: [0] 2083454756 __int32
Next Watch line: [1] 3103464 __int32

My conclusion is that busNumberSQL[2] is not an array of CString.

Of course, I need the /clr in order to use ADO.NET!

It seems as though a CString works, but a CString array for some (unknown) cannot even be defined.

Do you have any light for me? (I have not got into your previous post yet on Marshall)

Best regards

Doug.

(In case you are wondering I am very much a beginner with C++ and .NET)

Doug

msado15.dll

stevenson11-Oct-02 20:11

stevenson

11-Oct-02 20:11

what's wrong, tell me plz.

f:\projects center\task history\mission 8_13\hydrosrv\debug\msado15.tlh(530) : error C2011: 'ParameterDirectionEnum' : 'enum' type redefinition

Extreme programming. Do the No.1

Re: msado15.dll

Michael P Butler12-Oct-02 1:55

Michael P Butler

12-Oct-02 1:55

It looks like you are trying to #import the ADO stuff twice?

What does your #import line look like and where do you use it in your codd?

Michael

"I've died for a living in the movies and tv.
But the hardest thing I'll ever do is watch my leading ladies,
Kiss some other guy while I'm bandaging my knee."
-- The Unknown Stuntman

Question about Transact SQL

tongc11-Oct-02 6:38

tongc

11-Oct-02 6:38

Hi !

I'm totally new to Transact SQL. I've just been trying to familiarise myself with the below problem and struggling with it for last 6 hours without break.

Here is the schema

HOTEL (Hotel_No, Hotel_Name, Address)
GUEST (Guest_No, Guest_Name, Address)
ROOM (Room_No, Hotel_No, Type, Price)
BOOKING (Hotel_No, Guest_No, Date_From, Date_To, Room_No)

Where it asks what is the total income, for the month of July 2001, from occupied rooms at all the Sheridan Hotels.

How can i implement this in SQL? Or does any one have idea or hints.

Here is my attempmpt at it

<br />
DECLARE @NumberOfDay int<br />
<br />
IF (SELECT Hotel_Name FROM HOTEL WHERE Hotel_Name = 'Sheridan Hotel' and Address = 'Melbourne') = 'Sheridan Hotel'<br />
BEGIN<br />
   PRINT 'Sheridan Melbourne'<br />
   SELECT * FROM HOTEL h1, BOOKING b1, ROOM r1<br />
	WHERE h1.Hotel_Name = 'Sheridan Hotel' <br />
		and Address = 'Melbourne'<br />
		and b1.Date_From < '2001-Jul-01'<br />
		and b1.Date_To  in (Select DatePart(07,01)= true<br />
 <br />
END<br />
ELSE<br />
   PRINT 'Sheridan Sydney'<br />
<br />

I'm trying to program it in SQL in SQL Analyzer, but i have never experienced programming with this transact SQL before. In the first instance, it is like Pascal to me, (IF ..END IF, BEGIN)

Re: Question about Transact SQL

Richard Deeming11-Oct-02 7:10

Richard Deeming

11-Oct-02 7:10

This should help:

--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime
 
SELECT 
    --First of the month
    @StartMonth = CAST('1 July 2001' As smalldatetime), 
    --Day before the first of the next month
    @EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth)) 
 
SELECT
    --Hotel details
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address,
 
    --Total income per hotel
    SUM(Bookings.Income) As Income
 
FROM
    HOTEL INNER JOIN
    (
        --Calculate the income as Room price (per night) * # nights
        SELECT
            CASE
                --Booking started before the month
                WHEN Date_From < @StartMonth THEN
                    DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
 
                --Booking finished after the month
                WHEN Date_To > @EndMonth THEN
                    DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price

                --Booking was entirely within the month
                ELSE
                    DATEDIFF(day, Date_From, Date_To) * ROOM.Price
            END As Income
 
         FROM
            BOOKING INNER JOIN ROOM
            ON ROOM.Room_No = BOOKING.Room_No
 
        WHERE
        --Only want bookings that intersect the month
            Date_From < @EndMonth
        AND
            Date_To > @StartMonth
    )
    As Bookings
 
WHERE
--Only want bookings for the Sheridan hotels
    Hotel_Name = 'Sheridan Hotel'
 
GROUP BY
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address

Re: Question about Transact SQL

tongc11-Oct-02 13:48

tongc

11-Oct-02 13:48

Hi!

Your answer has resolved and swept away alot of mysterious things that were in my head! I highly appreciate your it.

Thank you very much

Regard,

Ps: it gives me error say near the "WHERE" keyword, but i am still attempting to fix it!

Server: Msg 156, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'WHERE'.

Re: Question about Transact SQL

Richard Deeming13-Oct-02 22:47

Richard Deeming

13-Oct-02 22:47

Whoops!

--Store the first and last day of the month (July 2001)
DECLARE @StartMonth smalldatetime, @EndMonth smalldatetime 
SELECT
     --First of the month
    @StartMonth = CAST('1 July 2001' As smalldatetime),
     --Day before the first of the next month
    @EndMonth = DATEADD(day, -1, DATEADD(month, 1, @StartMonth))
 
SELECT
    --Hotel details
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address,
 
    --Total income per hotel
    SUM(Bookings.Income) As Income
 
FROM
    HOTEL INNER JOIN
    (
        --Calculate the income as Room price (per night) * # nights
        SELECT
            ROOM.Hotel_No,
            CASE
                --Booking started before the month
                WHEN Date_From < @StartMonth THEN
                    DATEDIFF(day, @StartMonth, Date_To) * ROOM.Price
 
                --Booking finished after the month
                WHEN Date_To > @EndMonth THEN
                    DATEDIFF(day, Date_From, @EndMonth) * ROOM.Price
 
                --Booking was entirely within the month
                ELSE
                    DATEDIFF(day, Date_From, Date_To) * ROOM.Price
            END As Income
  
        FROM
            BOOKING INNER JOIN ROOM
            ON ROOM.Room_No = BOOKING.Room_No
 
        WHERE
        --Only want bookings that intersect the month
            Date_From < @EndMonth
        AND
            Date_To > @StartMonth
    )
    As Bookings
 
    ON Bookings.Hotel_No = HOTEL.Hotel_No
 
WHERE
--Only want bookings for the Sheridan hotels
    Hotel_Name = 'Sheridan Hotel'
 
GROUP BY
    HOTEL.Hotel_No,
    HOTEL.Hotel_Name,
    HOTEL.Address

SQL Suggestions.

Chris Meech11-Oct-02 4:24

Chris Meech

11-Oct-02 4:24

I have a view that joins a table back on itself like the following SQL

select  pm.portfolio_code master_code
       ,pp.portfolio_code part_code
from  portfolio pm
     ,portfolio pp
where pm.master_account_indicator = 1
  and pp.master_account = pm.portfolio_code
order by pm.portfolio_code, pp.portfolio_code

This produces some results like this

MASTER_CODE  PART_CODE
----------- ----------
         17          7
         17         12
         17         14
        227        226
        286        246
        286        287
        293         45
        293        440

Could someone suggest what has to be added so that a third colum gives me an incremented count of the master_code column. The result should be like this

MASTER_CODE  PART_CODE INC_COUNT
----------- ---------- ---------
         17          7         1
         17         12         2
         17         14         3
        227        226         1
        286        246         1
        286        287         2
        293         45         1
        293        440         2

I know how I can provide the data in some C++ code that will process the rows returned from the view, but I would really like to have the SQL do it for me.

Thanks for any suggestions.

Chris Meech

Re: SQL Suggestions.

Jon Hulatt11-Oct-02 5:52

Jon Hulatt

11-Oct-02 5:52

I think you can only do this with cursors and a temp table.

Signature space for rent. Apply by email to....

Re: SQL Suggestions.

Richard Deeming11-Oct-02 7:23

Richard Deeming

11-Oct-02 7:23

Assuming SQL Server 2000:

DECLARE @temp TABLE (
    MASTER_CODE int not null, 
    PART_CODE int not null, 
    INC_COUNT int not null DEFAULT 0
)
 
INSERT INTO 
    @temp (MASTER_CODE, PART_CODE)
SELECT 
    pm.portfolio_code, pp.portfolio_code 
FROM 
    portfolio As pm INNER JOIN portfolio As pp
    ON pp.master_account = pm.portfolio_code
WHERE 
    pm.master_account_indicator = 1
ORDER BY 
    pm.portfolio_code, pp.portfolio_code
 
DECLARE @Count int, @Previous int
SELECT @Count = 1, @Previous = Null
 
UPDATE @temp
SET
    @Count = INC_COUNT = CASE
        WHEN MASTER_CODE = @Previous THEN @Count + 1
        ELSE 1
    END,
    @Previous = MASTER_CODE
 
SELECT
    MASTER_CODE,
    PART_CODE,
    INC_COUNT
FROM
    @temp
ORDER BY
    MASTER_CODE,
    PART_CODE

Re: SQL Suggestions.

Chris Meech15-Oct-02 3:36

Chris Meech

15-Oct-02 3:36

Thanks for the suggestions. This morning, I finally got the view to do what I wanted. With the knowledge that the portfolio_code column of the portfolio table is a unique identifier for each row, I got the following SQL to run and do exactly what I was looking for.

select  pm.portfolio_code master_code
       ,pp.portfolio_code part_code
       ,( select count(c.portfolio_code) from portfolio c
          where c.portfolio_code <= pp.portfolio_code
            and c.master_account = pm.portfolio_code
        ) inc_count
from  portfolio pm
     ,portfolio pp
where pm.master_account_indicator = 1
  and pp.master_account = pm.portfolio_code
order by pm.portfolio_code, pp.portfolio_code

Essentially, as each row is returned, another query runs and returns the count of portfolio_code that is less than or equal to the value in the row being returned.

Chris Meech

Fast SELECT Count

Luca Leonardo Scorcia11-Oct-02 3:12

Luca Leonardo Scorcia

11-Oct-02 3:12

I always used the SELECT Count(*) AS nCount syntax to select the number of records which have the specified requirements. But yesterday, while giving a look to some example code, I found the SELECT Count(1) AS nCount syntax. Is this different from the first one? If yes, which one is the faster?

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

Re: Fast SELECT Count

Jon Hulatt11-Oct-02 3:42

Jon Hulatt

11-Oct-02 3:42

select count(1) is quicker, there is less data to shift about. compare the query plan for each one in query analyser and you'll see the point.

only really makes a difference on large-ish tables (100,000+ records or so)

Signature space for rent. Apply by email to....

Re: Fast SELECT Count

Daniel Turini11-Oct-02 3:52

Daniel Turini

11-Oct-02 3:52

What are you talking about? The SQL Server query optimizer will render both as the same!

My latest articles:
XOR tricks for RAID data protection
Win32 process suspend/resume tool

Re: Fast SELECT Count

Jon Hulatt11-Oct-02 4:23

Jon Hulatt

11-Oct-02 4:23

my mistake, i meant server trace.

there's not that much difference between the two forms though.

And, in sql server, the quickest way to get a rowcount of a whole table is to use sp_spaceused

Signature space for rent. Apply by email to....

Re: Fast SELECT Count

Luca Leonardo Scorcia11-Oct-02 6:55

Luca Leonardo Scorcia

11-Oct-02 6:55

What's this Query Analyser? Is it included in SQL Server? Because I'm using SQL on an Access DB via ADO... but if it's a standalone tool it could be very useful!!!

Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)

Re: Fast SELECT Count

Daniel Turini14-Oct-02 0:55

Daniel Turini

14-Oct-02 0:55

Oh, only in SQL Server, and it uses some nice SQL Server features, like showing the query plan, and so on...
If you go serious about database development, you should really consider using SQL Server. There's even a free version of SQL Server, named MSDE or SQL Desktop Engine. Try it, is nice...

My latest articles:
Desktop Bob - Instant CP notifications
XOR tricks for RAID data protection

Only 255 char?

Rickard Andersson²⁰10-Oct-02 21:30

Rickard Andersson²⁰

10-Oct-02 21:30

I have tried to store more than 255 chars in an Access 2000 database, but it isn't allowed!
How can I stor more than 255 chars in Access?
I beleive it's not possible!?

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Re: Only 255 char?

Mazdak10-Oct-02 22:01

Mazdak

10-Oct-02 22:01

Hi Rickard.

Rickard Andersson wrote:
I beleive it's not possible!?

No.Use Memo type.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Re: Only 255 char?

Rickard Andersson²⁰10-Oct-02 22:40

Rickard Andersson²⁰

10-Oct-02 22:40

Mazdak wrote:
Use Memo type

Okay, but how do I do that?
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no.... Frown | :(

Can you explain more?

Thank you very much!

Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!

Re: Only 255 char?

Nick Parker11-Oct-02 1:31

Nick Parker

11-Oct-02 1:31

Rickard Andersson wrote:
I opened my table in Access 200 in Design mode and checked if Memo type could be used for my field, but no....

The Memo field is listed under the data type section of your database.

Nick Parker

The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.