| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing Doubles in SQL
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 ? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
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 "naive14" wrote: 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 ? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
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 & "') "Dave Patrick" wrote: 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 "naive14" wrote: 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 ? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
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 "naive14" wrote: 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 & "') |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
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 "naive14" wrote: 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 & "') "Dave Patrick" wrote: 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 "naive14" wrote: 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 ? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
Thanks a lot for the advice Dave.
"Dave Patrick" wrote: 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 "naive14" wrote: 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 & "') "Dave Patrick" wrote: 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 "naive14" wrote: 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 ? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing Doubles in SQL
You're welcome.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "naive14" wrote: Thanks a lot for the advice Dave. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i get rid of doubles from my database? | Excel Discussion (Misc queries) | |||
| Summarizing doubles | Excel Worksheet Functions | |||
| Sorting doubles | Excel Discussion (Misc queries) | |||
| How do I set up a Doubles Tennis roster for 12 players? | New Users to Excel | |||
| Comparing Doubles | Excel Programming | |||
Hybrid Mode
