Smallmoney Conversion

[sachin_wal]

Hi,

I am trying to read from a smallmoney field from the database and assign it to a text box. Here is code.

.............

Dim DR As System.Data.SqlClient.SqlDataReader

Try

Conn1.Open()

DR = sqlComm.ExecuteReader

If DR.Read Then

residentName.Text = DR.Item("ResidentName")

residentAge.Text = DR.Item("ResidentAge")

.................

Dim wag As SqlTypes.SqlMoney = DR.GetSqlMoney("Wages")

Dim pen As SqlTypes.SqlMoney = DR.GetSqlMoney("Pension")

 

where Wages is of type smallmoney. I get the following error.

 

System.InvalidCastException: Cast from string "Wages" to type 'Integer' is not valid. ---> System.FormatException: Input string was not in a correct format. at Microsoft.VisualBasic.CompilerServices.DoubleType.Parse(String Value, NumberFormatInfo NumberFormat) at Microsoft.VisualBasic.CompilerServices.IntegerType.FromString(String Value).

Any pointers would be appreciated.

Regards,

Sachin.

[Nerseus]

Yes, you'll get the same error when using smallint. It's a pain, but if you REALLY want to use the smaller column size, you'll have to convert manually.

 

Use something like:

Dim wag As SqlTypes.SqlMoney = System.Convert.ToInt32(DR.GetSqlMoney("Wages"))

 

Or, convince your client/boss that smallmoney isn't going to save you but 4 bytes per row. At 1 million rows that's 4 mil bytes - 4 meg of hard drive.... not worth it if you ask me. We convinced our clients that smallint wasn't worth it using the same calculation (more or less).

 

-Nerseus

[sachin_wal]

hi,

Thanks for the suggestion. I made the following chnages and it worked fine.

employer.Text = DR.Item("Employer")

occupation.Text = DR.Item("Occupation")

wages.Text = DR.GetSqlMoney(21).ToString

Pension.Text = DR.GetSqlMoney(22).ToString

 

I just changed the fieldname("Wages") and changed it to the corresponding field number and it started working fine.Maybe i had done something wrong.

Regards,

Sachin.

[Nerseus]

Well your new code is simply calling ToString on the returned object which will ALWAYS work. In the original code you were assigning a specific variable type to the value, which needed converting. Using the ordinal position versus column name would have no impact on the original error.

 

Using the ordinal is probably slightly faster but less readable. That's about the only difference...

 

-Ner

[sachin_wal]

Re:

 

Hi,

I know that is does not make any difference and the ordinal position is just another way of accessing the fields.But when I used the same Tostring with value accessed Using "Wages" I got the same error message.

But if you see the original error message it says :

Cast from string "Wages" to type 'Integer' is not valid. So i guess when it tried to convert Wages into a ordinal value before reading from the table( I dont know if SQL does that) it resulted in the above error(Weird). I also double checked the name of the field(Wages).

Any idea why it worked that way?.

regards,

Sachin.

[Nerseus]

You'll get the error because .NET doesn't want to convert smallmoney to SqlMoney, apparently. With a conversion function you should be able to get around it.

 

-Nerseus

[Corinne]

I'm having a similar problem. In my database (sql 2000) the field "GoalValue" is defined as money. .GoalValue is an object I'm trying to fill which when I hover over it is defined as "Public poperty GoalValue() as System.Data.SqlTypes.SQLMoney. My question is how to convert e.Row.Cells("GoalValue").Value to SQLMoney? I can't get the syntax right. Thanks for any help.

 

Code so far:

 

If Not IsDBNull(e.Row.Cells("GoalValue").Value) Then

.GoalValue = e.Row.Cells("GoalValue").Value

.Month = e.Row.Cells("Month").Value

.Description = e.Row.Cells("Description").Value

.Year = e.Row.Cells("Year").Value

End If

[Joe Mamma]

I'm having a similar problem. In my database (sql 2000) the field "GoalValue" is defined as money. .GoalValue is an object I'm trying to fill which when I hover over it is defined as "Public poperty GoalValue() as System.Data.SqlTypes.SQLMoney. My question is how to convert e.Row.Cells("GoalValue").Value to SQLMoney? I can't get the syntax right. Thanks for any help.

 

 

just guessing, but this should work ( C# )

if ( e.Row.Cells("GoalValue").Value != null )
GoalValue = new SQLMoney(Convert.ToDecimal(e.Row.Cells("GoalValue").Value));