COLLATION in bulk insert

Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:

> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
[vbcol=seagreen]
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
>|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:

> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
>
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
[vbcol=seagreen]
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
>|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sampl
e
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:

> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
>
>

COLLATION in bulk insert

Hi,
I have a bulk insert statement:
BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
WITH (
DATAFILETYPE = 'WIDECHAR'
FIRSTROW = 1,
FIELDTERMINATOR = 'COL_SEP',
ROWTERMINATOR = 'ROW_SEP',
CODEPAGE = 'RAW')
Some fields that are inserted are encrypted using special characters
beyond the normal ascii character set. I have a database that has a
collation set to Arabic. When the collation is set to arabic the import
does not transfer some data across properly.
When I set the collation to latin on this database, the data comes
across correctly. Can someone help me to explain why this is the case?Hi
You don't say how the data file is created! Have you tried using code page
1256?
John
"Feldaspar" wrote:
> Hi,
> I have a bulk insert statement:
> BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> WITH (
> DATAFILETYPE = 'WIDECHAR'
> FIRSTROW = 1,
> FIELDTERMINATOR = 'COL_SEP',
> ROWTERMINATOR = 'ROW_SEP',
> CODEPAGE = 'RAW')
> Some fields that are inserted are encrypted using special characters
> beyond the normal ascii character set. I have a database that has a
> collation set to Arabic. When the collation is set to arabic the import
> does not transfer some data across properly.
> When I set the collation to latin on this database, the data comes
> across correctly. Can someone help me to explain why this is the case?
>|||Hi,
I have tried using codepage 1256 but to no luck. The data file is
created in code just by running through some old DBF files and
extracting there values and putting them in to a csv file.
John Bell wrote:
> Hi
> You don't say how the data file is created! Have you tried using code page
> 1256?
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have a bulk insert statement:
> >
> > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > WITH (
> > DATAFILETYPE = 'WIDECHAR'
> > FIRSTROW = 1,
> > FIELDTERMINATOR = 'COL_SEP',
> > ROWTERMINATOR = 'ROW_SEP',
> > CODEPAGE = 'RAW')
> >
> > Some fields that are inserted are encrypted using special characters
> > beyond the normal ascii character set. I have a database that has a
> > collation set to Arabic. When the collation is set to arabic the import
> > does not transfer some data across properly.
> >
> > When I set the collation to latin on this database, the data comes
> > across correctly. Can someone help me to explain why this is the case?
> >
> >|||Hi
Is the file created unicode? If not then you may wish to change the
Datafiletype. Also make sure that contents of the file are as expected for
the Arabic characters.
John
"Feldaspar" wrote:
> Hi,
> I have tried using codepage 1256 but to no luck. The data file is
> created in code just by running through some old DBF files and
> extracting there values and putting them in to a csv file.
>
> John Bell wrote:
> > Hi
> >
> > You don't say how the data file is created! Have you tried using code page
> > 1256?
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have a bulk insert statement:
> > >
> > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > WITH (
> > > DATAFILETYPE = 'WIDECHAR'
> > > FIRSTROW = 1,
> > > FIELDTERMINATOR = 'COL_SEP',
> > > ROWTERMINATOR = 'ROW_SEP',
> > > CODEPAGE = 'RAW')
> > >
> > > Some fields that are inserted are encrypted using special characters
> > > beyond the normal ascii character set. I have a database that has a
> > > collation set to Arabic. When the collation is set to arabic the import
> > > does not transfer some data across properly.
> > >
> > > When I set the collation to latin on this database, the data comes
> > > across correctly. Can someone help me to explain why this is the case?
> > >
> > >
>|||Hi,
The believe the datafile is created in whatever language is default on
the machine creating it. If it is created on an Arabic machine then it
will be arabic, if it is created on an english machine then it will be
english. So I believe it could be of all different types.
John Bell wrote:
> Hi
> Is the file created unicode? If not then you may wish to change the
> Datafiletype. Also make sure that contents of the file are as expected for
> the Arabic characters.
> John
> "Feldaspar" wrote:
> > Hi,
> >
> > I have tried using codepage 1256 but to no luck. The data file is
> > created in code just by running through some old DBF files and
> > extracting there values and putting them in to a csv file.
> >
> >
> > John Bell wrote:
> >
> > > Hi
> > >
> > > You don't say how the data file is created! Have you tried using code page
> > > 1256?
> > >
> > > John
> > >
> > > "Feldaspar" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a bulk insert statement:
> > > >
> > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > WITH (
> > > > DATAFILETYPE = 'WIDECHAR'
> > > > FIRSTROW = 1,
> > > > FIELDTERMINATOR = 'COL_SEP',
> > > > ROWTERMINATOR = 'ROW_SEP',
> > > > CODEPAGE = 'RAW')
> > > >
> > > > Some fields that are inserted are encrypted using special characters
> > > > beyond the normal ascii character set. I have a database that has a
> > > > collation set to Arabic. When the collation is set to arabic the import
> > > > does not transfer some data across properly.
> > > >
> > > > When I set the collation to latin on this database, the data comes
> > > > across correctly. Can someone help me to explain why this is the case?
> > > >
> > > >
> >
> >|||Hi
You may want to post DDL (see http://www.aspfaq.com/etiquette.asp?id=5006)
and an some example records that you have tried, You may want to cut this
down to a smaller size if necessary. Make sure that you have tried the sample
before posting. Some newsreaders (such as the one I am using) do no accept
attachments.
John
"Feldaspar" wrote:
> Hi,
> The believe the datafile is created in whatever language is default on
> the machine creating it. If it is created on an Arabic machine then it
> will be arabic, if it is created on an english machine then it will be
> english. So I believe it could be of all different types.
>
> John Bell wrote:
> > Hi
> >
> > Is the file created unicode? If not then you may wish to change the
> > Datafiletype. Also make sure that contents of the file are as expected for
> > the Arabic characters.
> >
> > John
> >
> > "Feldaspar" wrote:
> >
> > > Hi,
> > >
> > > I have tried using codepage 1256 but to no luck. The data file is
> > > created in code just by running through some old DBF files and
> > > extracting there values and putting them in to a csv file.
> > >
> > >
> > > John Bell wrote:
> > >
> > > > Hi
> > > >
> > > > You don't say how the data file is created! Have you tried using code page
> > > > 1256?
> > > >
> > > > John
> > > >
> > > > "Feldaspar" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a bulk insert statement:
> > > > >
> > > > > BULK INSERT DatabaseTMP_CONVERT FROM 'CONVERT.CSV'
> > > > > WITH (
> > > > > DATAFILETYPE = 'WIDECHAR'
> > > > > FIRSTROW = 1,
> > > > > FIELDTERMINATOR = 'COL_SEP',
> > > > > ROWTERMINATOR = 'ROW_SEP',
> > > > > CODEPAGE = 'RAW')
> > > > >
> > > > > Some fields that are inserted are encrypted using special characters
> > > > > beyond the normal ascii character set. I have a database that has a
> > > > > collation set to Arabic. When the collation is set to arabic the import
> > > > > does not transfer some data across properly.
> > > > >
> > > > > When I set the collation to latin on this database, the data comes
> > > > > across correctly. Can someone help me to explain why this is the case?
> > > > >
> > > > >
> > >
> > >
>sqlsql

Collation

Hi ,
I would know what is the simplest (and the more reliable) method to convert an entire db from a collation to another...
Thanks
:confused:The simplest way to do it is to script out the schema, edit the script to change the collation, then play the script into an empty database container and reload the data. For small (under 2 Gb) databases, this works fine.

-PatP|||Thanks ... But what about changing collation id from script?
I've already seen the possibility to change collation for columns table ...
Is there no solution to change also DB setting?

Bye :cool:|||ALTER DATABASE will allow you to change the collation, but read carefully to be sure that is what you want... I suspect that it won't be much help.

-PatP|||You can use "Alter database" to change the default collation, but this will only affect tables that are created after the change. You could face a lot of collation conflicts, if you are not careful. As for getting all of the columns in all of the tables in one shot, you would probably have to write a cursor to loop through each table and column with a type of (n)char, (n)varchar, or (n)text, and change them individually. Dose this help?|||So, When I change collation ID of columns table or db this setting will affect only on new data ...

If it's right there is something that I don't understand.

Sometime I've worked with several tables inside at the same db ... these table had different collations and JOIN statement between them caused an collation error, but after executing an UPDATE to collation ID of involded columns the problems was disappeared....Why? :confused: :confused:

Maybe is there some implicit conversion between semi-compatible collations or SQL doesn't raise (after upating ID collation) an error but the result of operation can be corrupted?

Thanks|||ALTER DATABASE will change the default collation used for new character columns (if you don't explicitly specify a collation). ALTER TABLE will change the collation of existing columns.

I don't know of any a "magic wand" that will retroactively change the collation of all your existing columns with a single action.

-PatPsqlsql

Collates - CP437 - ADO - Visual Basic

Hello, I am workink with a Database that has the Collate SQL_Latin1_General_CP437_CI_AS (Instance and Database-SQL2000), this convert the Database in a OEM Server.
I Can save Characters From 0 to 256 ASCII code CP 437 very well on the database (I import this Data with an DTS) but when i retrive the information with Visual Basic 6.0 with ADO some character > 127 are changed.
Did you know how I can fix It ?
I know that the problem is that Windows is ANSI and my SQL Server OEM, but .. a dontt know how yo fix it.
Thanks I sorry because my poor english. :(

Walter
ArgentinaThe easy answer is to do the transfer using Unicode (16 bit characters). This allows any character that Windows recognizes to pass through without problem.

If you've already got the file that was produced from the table using the 437 collation and want to import it into a server with a different collation, then things get a bit tricky, but they can still be done.

-PatP|||The problem is that I must work with the Hexadecimal code of the character, because I need to send this code (Hexa of ASCII) to a Printer (Special Printer).
All the program is around this printer and I need manipulate ASCII codes.
Thanks you :D

Walter|||Take the data from the table using the 437 collation in a Unicode (16 bit) text file. This format allows the easy transportation of any character that Windows supports, regardless of the character's collation or character set.

The Unicode format will preserve the exact character, although sometimes moving a character from one 8 bit character set to another will cause the hex value of the character to change even though it preserves the glyph (the printed form) of the character exactly. You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

-PatP|||You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

And Pat, it's not collation, it's a code page, and with BCP there are no "tricks" ;)|||A code page is definitely one way to implement part of a collation. I see a collation as a lot more complex and flexible than a code page.

I find it interesting that you suggested one of what I consider the tricks for using BCP (specifying a code page) and noted that you feel that BCP has no tricks in the same posting. I have a hard time understanding that apparent contradiction.

-PatP|||How can it be a contradiction if this is part of the syntax?

I see a collation as a lot more complex and flexible than a code page.One has nothing to do with the other, so do tell, - how do you see them? In blue?|||Gracias a Todos, pude resolver el problema.
En el string de conexion con el servidor SQL Server 2000 hay un parametro que se puede indicar para que al momento de recuperar los caracteres, el servidor no los modifique. Este parametro se llama Auto Translate.
Al poner Auto Translate = No los valores Hexadecimales de los caracteres que se guardan en la base permanecen intactos. Probe de Generar una Tabla con 2 columnas, la primera tiene un valor entero de 0 a 255 y la segunda es un caracter en el cual puse un codigo ASCII de 0 a 255, al tomarlo desde Visual Basic muestro el valor entero de 0 a 255, el caracter y luego con una funcion de visual muestro el valor Hexadecimal del caracter y vi muy felizmente que en los 255 casos permanecia igual.

>You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

Con respecto al DST, yo no tengo problemas con el DTS ya que al importar un archivo de texto, el mismo se importa exactamente con los cdigos exadecimales que este tiene.

>You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

Para mi es mucho mas importante preservar el codigo Hexadecimal porque este codigo es un caracter de control para la impresora, independientemente de como se vea visualmente en pantalla, la impresora interpreta el codigo Hexadecimal.

Now, I will try to translate all the text but.. its very hard.
Thanks to all of you, I can fix the problem.
There is a parameter In the String of conexion with the SQL Server 2000 that indicate that don`t change the Hexadecimal code when you retrieve the characters from the server. This parameters is call "Auto Translate"

I don`t have problem with the DTS. The DTS imports Ok the characters from the ASCII file.

For me, it`s more important the Hex value that the glymp value because the printer interpretes the Hex value, not the Glyp.

Sorry because my poor english, I cant translate all exactly but i think that the idea is the same.
Thanks !!!

Walter|||Based on how you've described your needs, my first choice would be to use BCP, using the -c trick that rdjabarov suggested earlier for both export and import. The -c parameter allows you to control exactly which Windows code page is used for the file, and as long as the code page/collation mapping operation supports the charcters you need going both ways, you should be fine.

My second choice would be to extract the data using a view, converting the column to VARBINARY to preserve the hex values exactly. This usually means that you need to import the output of that view into a scratch table, then convert the hexadecimal value back to a character value inside of SQL Server.

If you can extract the data directly from one server to another, using a linked server, that would be my next suggestion.

-PatP

cognos OLAP conversion to ms analysis service

Hello All,
I am new to this group. I am looking for a tool that will convert cognos
OLAp cubes into ms OLAP cubes. Anybody has an idea?
Thanks a lot
Angie..
Won't work using Replication - I'd repost in the Analysis Services/OLAP
groups....
Paul Ibison

Code page conversion shortcut?

Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?

I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.

Thoughts? suggestions?

-Clayton

In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.

To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.

I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.

|||

Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

Code page conversion shortcut?

Is there a simple way to avoid having to use a data conversion task to convert Unicode data (from DB2) to ANSI format (for insert to Oracle)?

I'm hoping that there is a way to set the code page property on the OLEDB driver so that it implicitly converts to the format I want instead of having to explicitly force the conversion in the package.

Thoughts? suggestions?

-Clayton

In the intervening month, the best method I've come up with is to use the SSIS wizard to build/rebuild the Data Flow. By default the wizard creates the Data Flow with the data conversion task in place, which saves a lot of time over performing the task manually.

To access the wizard, right click on the "SSIS Packages" node of the Solution Explorer and choose "SSIS Import and Export Wizard..." This will create a new package, but the data flow object can be cut from the new package and pasted into an existing one.

I did not find any way to tweak the code page settings on the source driver to cast to an ANSI code page.

|||

Try this: http://blogs.conchango.com/jamiethomson/archive/2005/10/25/2303.aspx

Code - Integer, Nothing vs Zero

The piece of code below attempts to convert minutes to a time string.
The problem I am having is if the value of Minutes is NULL, it treats it as
zero.
Has anyone got any suggestions ?
Thanks
Steve
---
Public Function MinutesToTime(ByVal Minutes AS Integer)
On Error GoTo errorhandler
Dim mins
Dim hrs
If IsNothing(Minutes) Then
MinutesToTime = ""
Else
hrs = Fix(Minutes / 60)
mins = Minutes - (hrs * 60)
If mins < 10 Then mins = "0" & mins
MinutesToTime = hrs & ":" & mins
End If
Exit Function
errorhandler:
MinutesToTime = "##:##"
End FunctionFirst off this is a function, you should be explicit on your return values.
You have two DIM's without specifing the type... then you have a check for
NULL (if nothing) you return a string... so which is it?
What happens if the minutes is actually 0 - what would you return? Would it
be an empty string or "00:00" ' If that is the case, then why not, for NULL
values, return "00:00" - the same as if you specified 0 minutes?
=-Chris
"SteveH" <SteveH@.discussions.microsoft.com> wrote in message
news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> The piece of code below attempts to convert minutes to a time string.
> The problem I am having is if the value of Minutes is NULL, it treats it
> as
> zero.
> Has anyone got any suggestions ?
> Thanks
> Steve
> ---
> Public Function MinutesToTime(ByVal Minutes AS Integer)
> On Error GoTo errorhandler
> Dim mins
> Dim hrs
> If IsNothing(Minutes) Then
> MinutesToTime = ""
> Else
> hrs = Fix(Minutes / 60)
> mins = Minutes - (hrs * 60)
> If mins < 10 Then mins = "0" & mins
> MinutesToTime = hrs & ":" & mins
> End If
> Exit Function
> errorhandler:
> MinutesToTime = "##:##"
> End Function
>|||Thanks Chris for your reply
I am wanting to pass in an integer representing minutes and return a string
that looks like a time as per the following examples.
67 returns "1:07"
0 returns "0:00"
NULL returns ""
My problems is that when NULL is passed in I am currently getting "0:00"
instead of "".
I have tried using IsNull function but I get a compilation error "Name
'IsNull' is not declared."
"Chris Conner" wrote:
> First off this is a function, you should be explicit on your return values.
> You have two DIM's without specifing the type... then you have a check for
> NULL (if nothing) you return a string... so which is it?
> What happens if the minutes is actually 0 - what would you return? Would it
> be an empty string or "00:00" ' If that is the case, then why not, for NULL
> values, return "00:00" - the same as if you specified 0 minutes?
> =-Chris
> "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > The piece of code below attempts to convert minutes to a time string.
> >
> > The problem I am having is if the value of Minutes is NULL, it treats it
> > as
> > zero.
> >
> > Has anyone got any suggestions ?
> >
> > Thanks
> > Steve
> > ---
> >
> > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > On Error GoTo errorhandler
> >
> > Dim mins
> > Dim hrs
> >
> > If IsNothing(Minutes) Then
> > MinutesToTime = ""
> > Else
> > hrs = Fix(Minutes / 60)
> > mins = Minutes - (hrs * 60)
> >
> > If mins < 10 Then mins = "0" & mins
> >
> > MinutesToTime = hrs & ":" & mins
> > End If
> >
> > Exit Function
> >
> > errorhandler:
> > MinutesToTime = "##:##"
> >
> > End Function
> >
>
>|||Reporting Services doesn't support 'code reuse'
so I would reccomend just doing this in Microsoft Access; it would be a
simple format string there; right?
-Aaron
SteveH wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >|||Finally found a solution and thought I would post it here for anyone who has
the same problem.
datatype needed to be Nullable(Of Integer) instead of Integer
Public Function MinutesToTime(ByVal Minutes As Nullabe(Of Integer)) AS
String
then use HasValue instead of IsNothing
If Minutes.HasValue Then
and Minutes.Value
"SteveH" wrote:
> Thanks Chris for your reply
> I am wanting to pass in an integer representing minutes and return a string
> that looks like a time as per the following examples.
> 67 returns "1:07"
> 0 returns "0:00"
> NULL returns ""
> My problems is that when NULL is passed in I am currently getting "0:00"
> instead of "".
> I have tried using IsNull function but I get a compilation error "Name
> 'IsNull' is not declared."
> "Chris Conner" wrote:
> > First off this is a function, you should be explicit on your return values.
> > You have two DIM's without specifing the type... then you have a check for
> > NULL (if nothing) you return a string... so which is it?
> > What happens if the minutes is actually 0 - what would you return? Would it
> > be an empty string or "00:00" ' If that is the case, then why not, for NULL
> > values, return "00:00" - the same as if you specified 0 minutes?
> >
> > =-Chris
> >
> > "SteveH" <SteveH@.discussions.microsoft.com> wrote in message
> > news:05257274-EDEA-438A-B6E5-054E122BC5C1@.microsoft.com...
> > > The piece of code below attempts to convert minutes to a time string.
> > >
> > > The problem I am having is if the value of Minutes is NULL, it treats it
> > > as
> > > zero.
> > >
> > > Has anyone got any suggestions ?
> > >
> > > Thanks
> > > Steve
> > > ---
> > >
> > > Public Function MinutesToTime(ByVal Minutes AS Integer)
> > > On Error GoTo errorhandler
> > >
> > > Dim mins
> > > Dim hrs
> > >
> > > If IsNothing(Minutes) Then
> > > MinutesToTime = ""
> > > Else
> > > hrs = Fix(Minutes / 60)
> > > mins = Minutes - (hrs * 60)
> > >
> > > If mins < 10 Then mins = "0" & mins
> > >
> > > MinutesToTime = hrs & ":" & mins
> > > End If
> > >
> > > Exit Function
> > >
> > > errorhandler:
> > > MinutesToTime = "##:##"
> > >
> > > End Function
> > >
> >
> >
> >