Thread: convert to a string

convert to a string

From

"Shavonne Marietta Wijesinghe"

Date:

06 March 2007, 12:42:54

Hello

i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a number 10

N_GEN (text) = 10

on error resume next
  Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
  If err <> 0 then 'If table not found
   GetFieldValue = "1"
  else
   GetFieldValue = oRs("N_GEN") + 1
  End If

But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to it??

Shavonne Wijesinghe
http://www.studioform.it

Re: convert to a string

From

"Bart Degryse"

Date:

06 March 2007, 12:54:59

I assume GetFieldValue is the name of a VB function or property get?

Try either

Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")

GetFieldValue = cint(oRs("N_GEN")) + 1

GetFieldValue = clng(oRs("N_GEN")) + 1

In the last two cases make sure that the function or property get returns an int or long

Also remember that if oRs("N_GEN") is NULL the sum will also be NULL

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-03-06 14:42 >>>
Hello

i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a number 10

N_GEN (text) = 10

But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to it??

Shavonne Wijesinghe
http://www.studioform.it

Re: convert to a string

From

"A. Kretschmer"

Date:

06 March 2007, 12:57:06

am  Tue, dem 06.03.2007, um 14:42:53 +0100 mailte Shavonne Marietta Wijesinghe folgendes:
> Hello
> 
> i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a
> number 10

Why do you store numbers as text?


> 
> N_GEN (text) = 10
>  
>  
> on error resume next
>   Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
>  
> But it doesn't add the 1 to my N_GEN

You can't add a number to a string.


> How can i set the Recordset to convert the string to a number and then add 1 to
> it??

You can try to cast it to int:

test=# select '10'::text;text
------10
(1 row)

test=*# select '10'::text::int;int4
------  10
(1 row)


In your Query, change 'SELECT N_GEN FROM' to 'SELECT N_GEN::int FROM'.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: convert to a string

From

"Shavonne Marietta Wijesinghe"

Date:

06 March 2007, 13:32:21

when i try the

set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")

it returns 1 and for the rest i doens't change. alsways 10

----- Original Message -----
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string

I assume GetFieldValue is the name of a VB function or property get?

Try either
  Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
or
   GetFieldValue = cint(oRs("N_GEN")) + 1
or
   GetFieldValue = clng(oRs("N_GEN")) + 1

In the last two cases make sure that the function or property get returns an int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-03-06 14:42 >>>
Hello

i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a number 10

N_GEN (text) = 10

on error resume next
  Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
  If err <> 0 then 'If table not found
   GetFieldValue = "1"
  else
   GetFieldValue = oRs("N_GEN") + 1
  End If

But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to it??

Shavonne Wijesinghe
http://www.studioform.it

Re: convert to a string

From

"Bart Degryse"

Date:

06 March 2007, 13:38:57

As Andreas already asked: do you really have a good reason to store numbers as text

If so, show us some more information: table definition, some table data, a complete (vb?) function/property get, ... anything that is directly involved.

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-03-06 15:32 >>>

when i try the

set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")

it returns 1 and for the rest i doens't change. alsways 10

----- Original Message -----
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string

I assume GetFieldValue is the name of a VB function or property get?

Try either
  Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
or
   GetFieldValue = cint(oRs("N_GEN")) + 1
or
   GetFieldValue = clng(oRs("N_GEN")) + 1

In the last two cases make sure that the function or property get returns an int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-03-06 14:42 >>>
Hello

i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a number 10

N_GEN (text) = 10

on error resume next
  Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
  If err <> 0 then 'If table not found
   GetFieldValue = "1"
  else
   GetFieldValue = oRs("N_GEN") + 1
  End If

But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to it??

Shavonne Wijesinghe
http://www.studioform.it

Re: convert to a string

From

"Shavonne Marietta Wijesinghe"

Date:

06 March 2007, 13:49:38

i figured it out

Set oRs = oConn.Execute("SELECT N_GEN FROM " & MyTable & " ORDER BY N_GEN::INT DESC")

thanks to everyone

----- Original Message -----
From: Shavonne Marietta Wijesinghe
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 3:32 PM
Subject: Re: [SQL] convert to a string

when i try the
set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
it returns 1 and for the rest i doens't change. alsways 10
:(
----- Original Message -----
From: Bart Degryse
To: pgsql-sql@postgresql.org
Sent: Tuesday, March 06, 2007 2:54 PM
Subject: Re: [SQL] convert to a string

I assume GetFieldValue is the name of a VB function or property get?

Try either
  Set oRs = oConn.Execute("SELECT N_GEN::int FROM MyTable ORDER BY N_GEN DESC")
or
   GetFieldValue = cint(oRs("N_GEN")) + 1
or
   GetFieldValue = clng(oRs("N_GEN")) + 1

In the last two cases make sure that the function or property get returns an int or long
Also remember that if oRs("N_GEN") is NULL the sum will also be NULL

>>> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> 2007-03-06 14:42 >>>
Hello

i have a column N_GEN in postgreSql defined as text. In this coloumn i insert a number 10

N_GEN (text) = 10

on error resume next
  Set oRs = oConn.Execute("SELECT N_GEN FROM MyTable ORDER BY N_GEN DESC")
  If err <> 0 then 'If table not found
   GetFieldValue = "1"
  else
   GetFieldValue = oRs("N_GEN") + 1
  End If

But it doesn't add the 1 to my N_GEN
How can i set the Recordset to convert the string to a number and then add 1 to it??

Shavonne Wijesinghe
http://www.studioform.it