Filter Help! - Datatypes

Hello,
I am having trouble using a filter due to incorrect data types. I am trying to eliminate items with a dollar value of 0 from the report. The dollar field that I am using comes from a SQL server database and the data type of the field is decimal(17,5). What value do I need to put in the value field?
I have tried = 0, = 0.0, and = 0.00000
Help!Check this thread:
http://groups.google.com/groups?threadm=OuLkPagWEHA.3012%40tk2msftngp13.phx.gbl
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||The data provider will return the field as System.Decimal. System.Decimal is
completely different than System.Double (i.e. =0.0) and therefore the
comparison fails.
Please try one of the following:
* change the filter expression to convert the decimal to a double:
=CDbl(...)
* or change the filter value to convert the constant into a decimal:
=CDec(0.0)
More details on the conversion functions are available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> Hello,
> I am having trouble using a filter due to incorrect data types. I am
trying to eliminate items with a dollar value of 0 from the report. The
dollar field that I am using comes from a SQL server database and the data
type of the field is decimal(17,5). What value do I need to put in the
value field?
> I have tried = 0, = 0.0, and = 0.00000
> Help!|||Thank you! Both of the options work great.
"Robert Bruckner [MSFT]" wrote:
> The data provider will return the field as System.Decimal. System.Decimal is
> completely different than System.Double (i.e. =0.0) and therefore the
> comparison fails.
> Please try one of the following:
> * change the filter expression to convert the decimal to a double:
> =CDbl(...)
> * or change the filter value to convert the constant into a decimal:
> =CDec(0.0)
> More details on the conversion functions are available at:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:1665CEF7-A11A-47FD-AB27-BE3A7AB35FFF@.microsoft.com...
> > Hello,
> >
> > I am having trouble using a filter due to incorrect data types. I am
> trying to eliminate items with a dollar value of 0 from the report. The
> dollar field that I am using comes from a SQL server database and the data
> type of the field is decimal(17,5). What value do I need to put in the
> value field?
> >
> > I have tried = 0, = 0.0, and = 0.00000
> >
> > Help!
>
>

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg asint

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg =(@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg,'@.jpg.jpg',

bulkcolumn fromOpenrowset(Bulk'D:\Data\Pics\@.jpg.jpg', Single_Blob)as'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROMOPENROWSET('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;','SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris