Storing Doubles in SQL

[Guest]

I can successfully read and write from/to sql using excel macros.

I have numbers in my worksheet (currency and large numbers and fractions)
that I so far handle using "double" in my macros.

a) My basic question is what is the best way to store these values in my sql
database. Should I define a "Double" or "Float" field in my database or
should I define a "string" in my sql database.

b) since the sql text sent to the server is all text is it best or
neccessary to convert and format the Double to a String before using in a sql
command ?

 

[Dave Patrick]

No, always use a data type that is equal. You can always use a type that is
greater as well but you're possibly wasting storage space.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Guest]

Thanks. So I'll use a Double definition in my sql database.

As to the second part of the question, should I convert the double to a
string and format as in :

dim s, sqlCMD as string
dim d as double

d = 5459.459
s = Format(d, "##,##0.00")

sqlCMD = "INSERT INTO tablex VALUES ('" & s & "')
' the above probably rounds off to 2 decimals


or is this valid ...

sqlCMD = "INSERT INTO tablex VALUES ('" & d & "')

No, always use a data type that is equal. You can always use a type that is
greater as well but you're possibly wasting storage space.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

I can successfully read and write from/to sql using excel macros.

I have numbers in my worksheet (currency and large numbers and fractions)
that I so far handle using "double" in my macros.

a) My basic question is what is the best way to store these values in my
sql
database. Should I define a "Double" or "Float" field in my database or
should I define a "string" in my sql database.

b) since the sql text sent to the server is all text is it best or
neccessary to convert and format the Double to a String before using in a
sql
command ?

 

[Dave Patrick]

You don't want the string delimiters. Do something like;

INSERT INTO Employees
(EmployeeID, FirstName, LastName)
VALUES (9999, N'John', N'Smith')


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Dave Patrick]

Should have been;

INSERT INTO Employees
(EmployeeID, FirstName, LastName)
VALUES (" & d & ", N'John', N'Smith')

Also it's pointless to format the value before storing it.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Thanks. So I'll use a Double definition in my sql database.

As to the second part of the question, should I convert the double to a
string and format as in :

dim s, sqlCMD as string
dim d as double

d = 5459.459
s = Format(d, "##,##0.00")

sqlCMD = "INSERT INTO tablex VALUES ('" & s & "')
' the above probably rounds off to 2 decimals


or is this valid ...

sqlCMD = "INSERT INTO tablex VALUES ('" & d & "')

No, always use a data type that is equal. You can always use a type that
is
greater as well but you're possibly wasting storage space.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

I can successfully read and write from/to sql using excel macros.

I have numbers in my worksheet (currency and large numbers and
fractions)
that I so far handle using "double" in my macros.

a) My basic question is what is the best way to store these values in
my
sql
database. Should I define a "Double" or "Float" field in my database
or
should I define a "string" in my sql database.

b) since the sql text sent to the server is all text is it best or
neccessary to convert and format the Double to a String before using in
a
sql
command ?

 

[Guest]

Thanks a lot for the advice Dave.

Should have been;

INSERT INTO Employees
(EmployeeID, FirstName, LastName)
VALUES (" & d & ", N'John', N'Smith')

Also it's pointless to format the value before storing it.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Thanks. So I'll use a Double definition in my sql database.

As to the second part of the question, should I convert the double to a
string and format as in :

dim s, sqlCMD as string
dim d as double

d = 5459.459
s = Format(d, "##,##0.00")

sqlCMD = "INSERT INTO tablex VALUES ('" & s & "')
' the above probably rounds off to 2 decimals


or is this valid ...

sqlCMD = "INSERT INTO tablex VALUES ('" & d & "')

No, always use a data type that is equal. You can always use a type that
is
greater as well but you're possibly wasting storage space.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
I can successfully read and write from/to sql using excel macros.

I have numbers in my worksheet (currency and large numbers and
fractions)
that I so far handle using "double" in my macros.

a) My basic question is what is the best way to store these values in
my
sql
database. Should I define a "Double" or "Float" field in my database
or
should I define a "string" in my sql database.

b) since the sql text sent to the server is all text is it best or
neccessary to convert and format the Double to a String before using in
a
sql
command ?

 

[Dave Patrick]

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect