ODBC + CDBVariant

Hi,

I'm using sql serser 2000, CRecordset, CDatabase, ODBC

How can I get ntext / nvarchar type field from my database?

maybe something like:

CDBVariant val;

rs.GetFieldValue("activity ",val);

CString activity = (*val.m_pstring).GetBuffer();

Thanks, Yael

I believe this is pretty much the same question as the post on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477058&SiteID=1

Did the response to that thread help you?

~Warren

Occurs in a field

Hi,

How do find out how many times a character occurs in a field thru an expression? Like the occurs() function in FoxPro

Thanks

Any suggestions?

Obtaining total of partial

Dear all,
I was wondering how do I for obtain the total for the field 'parcial' in the
same query, of course:
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) AS partial
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
ORDER BY MES,DIA
DDL:
CREATE TABLE [ABS_DiarioHisto] (
[DIA_CodigoApunte] [int] NOT NULL ,
[DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
NULL ,
[DIA_HoraEscritura] [datetime] NULL ,
[DIA_Sucursal] [int] NULL ,
[DIA_Puesto] [smallint] NULL ,
[DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaOper] [datetime] NULL ,
[DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaConta] [datetime] NULL ,
[DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_Importe] [float] NULL ,
[DIA_Predisp] [smallint] NULL ,
[DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_ClCajero] [smallint] NULL ,
[DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumHost] [int] NULL ,
[DIA_CodError] [int] NULL ,
[DIA_Anulada] [smallint] NULL ,
[DIA_TipoOper] [int] NULL ,
[DIA_NumOper] [int] NULL ,
[DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodEntidad] [int] NULL ,
[DIA_CodOficina] [int] NULL ,
[DIA_CasoEuro] [tinyint] NULL ,
[DIA_ImporteB] [float] NULL ,
[DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
Thanks in advance and best regards,
Enricsorry, as usual, I haven't enough patience...
select sum(total) from (
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
count(*) as total
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
) D
"Enric" wrote:

> Dear all,
> I was wondering how do I for obtain the total for the field 'parcial' in t
he
> same query, of course:
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> COUNT(*) AS partial
> FROM ABS_DIARIOHISTO
> WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
> ORDER BY MES,DIA
>
> DDL:
> CREATE TABLE [ABS_DiarioHisto] (
> [DIA_CodigoApunte] [int] NOT NULL ,
> [DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
> NULL ,
> [DIA_HoraEscritura] [datetime] NULL ,
> [DIA_Sucursal] [int] NULL ,
> [DIA_Puesto] [smallint] NULL ,
> [DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaOper] [datetime] NULL ,
> [DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaConta] [datetime] NULL ,
> [DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_Importe] [float] NULL ,
> [DIA_Predisp] [smallint] NULL ,
> [DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_ClCajero] [smallint] NULL ,
> [DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumHost] [int] NULL ,
> [DIA_CodError] [int] NULL ,
> [DIA_Anulada] [smallint] NULL ,
> [DIA_TipoOper] [int] NULL ,
> [DIA_NumOper] [int] NULL ,
> [DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodEntidad] [int] NULL ,
> [DIA_CodOficina] [int] NULL ,
> [DIA_CasoEuro] [tinyint] NULL ,
> [DIA_ImporteB] [float] NULL ,
> [DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> Thanks in advance and best regards,
> Enric|||Hi
This should be no different to
SELECT COUNT(*)
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) as totalDay, ( SELECT COUNT(*) FROM ABS_DIARIOHISTO WHERE
DIA_SUCURSAL = 81 ) AS TotalCount
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
John
"Enric" wrote:
> sorry, as usual, I haven't enough patience...
> select sum(total) from (
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> count(*) as total
> FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
> ) D
>
> "Enric" wrote:
>

Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )

Obtaining the time

dear all,
I'm looking for a better version of this stuff. I want to obtain the time of
a datetime field:
DuracionUlt is a datetime field.
SELECT DuracionUlt,
Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
+ ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
+ ':' +
Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
AND datediff(dd,duracionult,getdate()) = 1
This query returns:
2006-03-28 00:00:00.000 = 0:0:0:0
2006-03-28 00:00:42.000 = 0:0:42:0
Any comment/suggestion/thought/advice would be very appreciated,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)SELECT CONVERT(VARCHAR(30),GETDATE(),108)
select {fn extract(minute from getdate())}
Select {fn dayname( GetDate()) }
select {fn CURRENT_DATE()}
select {fn CURRENT_time()}
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
> dear all,
> I'm looking for a better version of this stuff. I want to obtain the time
> of
> a datetime field:
> DuracionUlt is a datetime field.
> SELECT DuracionUlt,
> Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
> + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
> Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
> + ':' +
> Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
> FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
> AND datediff(dd,duracionult,getdate()) = 1
> This query returns:
> 2006-03-28 00:00:00.000 = 0:0:0:0
> 2006-03-28 00:00:42.000 = 0:0:42:0
> Any comment/suggestion/thought/advice would be very appreciated,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> SELECT CONVERT(VARCHAR(30),GETDATE(),108)
> select {fn extract(minute from getdate())}
> Select {fn dayname( GetDate()) }
> select {fn CURRENT_DATE()}
> select {fn CURRENT_time()}
>
>
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
>
>

Obtaining count for identity

Is there a way to query a SQL server such that it will return the
current counter value of a field that increments automatically (an
identity field)? Using a MAX query might not necessarily give you the
correct information because it is possible that the high value records
were deleted. I checked the system tables and none of them appear to
have what I need. I suppose it would be possible to maintain a table
that lists all the incrementing columns, but ideally there would be a
way already built into SQL Server.

Thanks.On 15 Jun 2005 14:00:46 -0700, hartley_aaron@.hotmail.com wrote:

>Is there a way to query a SQL server such that it will return the
>current counter value of a field that increments automatically (an
>identity field)? Using a MAX query might not necessarily give you the
>correct information because it is possible that the high value records
>were deleted. I checked the system tables and none of them appear to
>have what I need. I suppose it would be possible to maintain a table
>that lists all the incrementing columns, but ideally there would be a
>way already built into SQL Server.
>Thanks.

Hi hartley_aaron,

Look up IDENT_CURRENT in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Look up IDENT_CURRENT or @.@.IDENTITY in Books Online.

HTH,
Stu|||It worked great.

Thank you both!

Object variable or With block variable not set

I was wondering if you have any knowledge of what I can be doing wrong.
Basically, here is what I want to do in SQL Reporting Services:
If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
value for this field: Fields!Opportunity_EstRev
Here is the syntax I am using:
=SUM(Fields!Opportunity_EstRev.Value
(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
When I run it, it returns an error: "Object variable or With block
variable not set."
I know independently that each separate syntax works because I have
them working in the same report:
=SUM(Fields!Opportunity_EstRev.Value)
=COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing))
When I try to combine them, they don't work. Logically, they make
sense to me combined, but I am sure I am messing up the syntax
somewhere. Would you have an idea? Any help would be greatly
appreciated
try:
=SUM(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_EstRev.Value, 0))
=COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",1, Nothing))
=SUM(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open", 1, 0))
-oj
<hendrix.craig@.gmail.com> wrote in message
news:1144162065.659636.72790@.j33g2000cwa.googlegro ups.com...
>I was wondering if you have any knowledge of what I can be doing wrong.
> Basically, here is what I want to do in SQL Reporting Services:
> If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
> value for this field: Fields!Opportunity_EstRev
> Here is the syntax I am using:
> =SUM(Fields!Opportunity_EstRev.Value
> (iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
> When I run it, it returns an error: "Object variable or With block
> variable not set."
> I know independently that each separate syntax works because I have
> them working in the same report:
> =SUM(Fields!Opportunity_EstRev.Value)
> =COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing))
> When I try to combine them, they don't work. Logically, they make
> sense to me combined, but I am sure I am messing up the syntax
> somewhere. Would you have an idea? Any help would be greatly
> appreciated
>

Object variable or With block variable not set

I was wondering if you have any knowledge of what I can be doing wrong.
Basically, here is what I want to do in SQL Reporting Services:
If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
value for this field: Fields!Opportunity_EstRev
Here is the syntax I am using:
=SUM(Fields!Opportunity_EstRev.Value
(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
When I run it, it returns an error: "Object variable or With block
variable not set."
I know independently that each separate syntax works because I have
them working in the same report:
=SUM(Fields!Opportunity_EstRev.Value)
=COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing))
When I try to combine them, they don't work. Logically, they make
sense to me combined, but I am sure I am messing up the syntax
somewhere. Would you have an idea? Any help would be greatly
appreciatedtry:
=SUM(iif(Fields!Opportunity_Won_Loss_Ope
n.Value = "Open",
Fields!Opportunity_EstRev.Value, 0))
=COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",1, Nothing))
=SUM(iif(Fields!Opportunity_Won_Loss_Ope
n.Value = "Open", 1, 0))
-oj
<hendrix.craig@.gmail.com> wrote in message
news:1144162065.659636.72790@.j33g2000cwa.googlegroups.com...
>I was wondering if you have any knowledge of what I can be doing wrong.
> Basically, here is what I want to do in SQL Reporting Services:
> If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
> value for this field: Fields!Opportunity_EstRev
> Here is the syntax I am using:
> =SUM(Fields!Opportunity_EstRev.Value
> (iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
> When I run it, it returns an error: "Object variable or With block
> variable not set."
> I know independently that each separate syntax works because I have
> them working in the same report:
> =SUM(Fields!Opportunity_EstRev.Value)
> =COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing))
> When I try to combine them, they don't work. Logically, they make
> sense to me combined, but I am sure I am messing up the syntax
> somewhere. Would you have an idea? Any help would be greatly
> appreciated
>