• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 900
  • Last Modified:

converting a string to decimal add a "D" at the end of the parsed decimal

I am using this code to convert a string from a textbox into a decimal to be stored in sql 2005 db.

'check for empty values otherwise catsing will trhow an exception
            If Me.Txt_CO2_CO2_km.Text.Trim <> "" Then
                CO2_Km = Decimal.Parse(Me.Txt_CO2_CO2_km.Text.Trim)
            Else
                CO2_Km = Nothing
            End If

for a stringlike  "0.1881"   I get 0.1881D

in my database the bumber is stored as Decimal(18,4)

I also tried CType(Me.Txt_CO2_CO2_miles.Text.Trim, Decimal)
same result.

How can I deleted that D ?
0
brillox
Asked:
brillox
  • 14
  • 11
1 Solution
 
SStoryCommented:
I don't think the D is actually part of the number. It is just shows up as the identifier. Where is you code to add it so SQL Server?  You should use a SQLCommand object, some of the datatypes from
System.Data.SqlTypes.SqlDecimal and a parameter query to say that it is to be SqlDecimal

Also See this:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23379036.html
0
 
brilloxAuthor Commented:
this is the code for sql

Public Function InsertData(ByVal TransportType As String, _
                               ByVal CO2Km As Decimal, _
                               ByVal CO2Miles As Decimal, _
                               ByVal AirFactor As Integer, _
                               ByVal Comments As String) As String
        SqlConn = New SqlConnection(GetConnString)
        Cmd = New SqlCommand

        With Cmd
            .Connection = SqlConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SP_CO2_Insert"
            .Parameters.AddWithValue("@Transport_Type", TransportType)
            .Parameters.AddWithValue("@CO2_km", CO2Km)
            .Parameters.AddWithValue("@CO2_miles", CO2Miles)
            .Parameters.AddWithValue("@Air_Factor", AirFactor)
            .Parameters.AddWithValue("@Comments", Comments)
        End With

        Try
            Da = New SqlDataAdapter(Cmd)

            If SqlConn.State = ConnectionState.Closed Then SqlConn.Open()

            Cmd.ExecuteNonQuery()
            Return "New Record Inserted"

        Catch ex As Exception
            Return ex.Message
        Finally
            SqlConn.Dispose()
            Cmd.Dispose()
        End Try


should I use this for the parameter ?
.Parameters.Add("@CO2_km",SqlDbType.Float).Value = CO2Km

the reason I now use float is because wjile waiting for the annswer I changed decimal to float.
However as per my second question (I am taking my chances here :-) ) what would you recommend for a value type 0.0000 or 00.0000 and so on..  Decimal or Float ?

0
 
brilloxAuthor Commented:
sorry.. I am confused...

forget for a moment the above post.

I get the "D" from my SELECT Stored procedure which is a simple


SELECT Lookup_CO2_Factors.* FROM Lookup_CO2_Factors

and I get the dataset to bind my gridview and textboxes from this code

Public Function GetData() As DataSet
        SqlConn = New SqlConnection(GetConnString)
        Cmd = New SqlCommand

        With Cmd
            .Connection = SqlConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "SP_CO2_Select"
        End With

        Try
            Da = New SqlDataAdapter(Cmd)
            Ds = New DataSet


            If SqlConn.State = ConnectionState.Closed Then SqlConn.Open()

            Da.Fill(Ds, "CO2Factors")

            Return Ds

        Catch ex As Exception
            Return Nothing
        Finally
            SqlConn.Dispose()
            Cmd.Dispose()
            Da.Dispose()
            Ds.Dispose()
        End Try

Should I format my gridview  cell and or my textboxes for decimal data ?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
brilloxAuthor Commented:
I tried to format the gridview cell from where I populate the textbox like this for float types
DataFormatString="{0:f4}" HtmlEncode="False"
I always get the "D" at the end. However I tried an insert and an update and the values are inserted fine in the database.

0
 
SStoryCommented:
When you pass an item as an object and not declared, it probably calls the .ToString method which would show the D at the end of it.

Yes you need to do something like:
        Dim s As New System.Data.SqlClient.SqlCommand
        Dim p As SqlClient.SqlParameter
        p = s.Parameters.Add("@CO2_km", SqlDbType.Decimal)
        p.Value = CO2KM

if CO2KM is a decimal.

Decimal is made for money and things where perfect decimal values are needed.  If you don't mind floating point's precision for your data, then it is fine to use a float.  Then you would change the SQlDBType as well.

I wrote a class to make the above code a lot simple to use. It went with the code for an article. It wasn't the primary focus of the article.

Visit: http://www.codeproject.com/KB/vb/vinheritdform.aspx
Download the code.  Use the clsParamBuilder class

Here's how easy it is to use, when building params:
A quote from my code:

   Dim pb As New clsParamBuilder(New SqlClient.SqlCommand(_
        "spAddNewOfficer"), CommandType.StoredProcedure)

      'add params

      pb.AddParam("@Badge", txtBadge.Text)
      pb.AddParam("@FirstName", txtFirstName.Text)
      pb.AddParam("@MidName", txtMidName.Text)
      pb.AddParam("@LastName", txtLastName.Text)
      pb.AddParam("@Password", txtPassword.Text)
      pb.AddParam("@UserType", cboUserType.Text)
      pb.AddParam("@Active", ckActive.Checked)


See how easy it is to add params and values with this?

0
 
SStoryCommented:
BTW, if you decided to go this way... I just realized you'd need to modify clsParamBuilder class to allow for specifying the data type.  I just thought I'd show you how you can do it simpler.
0
 
brilloxAuthor Commented:
When you pass an item as an object and not declared, it probably calls the .ToString method which would show the D at the end of it.

I do not understand your point ... ( due to my lack of knowledge...)

I get the "D" on SELECT non on insert or update. Data is stored just fine into the database, but on debug mode I get a "D" attached to the text boxes that display decimal types... then once inserted/updated, there is no trace of the "D" in the sql table records.

This is no longer an issue as I do not get problem in the application, however I would like to get rid of that "D" on select data as I do not like to leave starnge behaviour on my application.. so your help will be appreciated to nail down this issue
0
 
SStoryCommented:
Hmm.
I'm sorry, I am not sure why this is happening.  I tried some experiments here and didn't get that result.
0
 
brilloxAuthor Commented:
no worries...  I'll give you the points anyway for your time amd your effort..

Thanks again
0
 
SStoryCommented:
Thanks for the points. I'm sorry I couldn't help.

Here is one more thought. After the Dataset is built, look in the debugger at the table in it, row 1 and examine the types of columns and values given. Does the D show up there?  Or is it a problem in the grid.
0
 
brilloxAuthor Commented:
I will look at it tomorrow at my work... and I'll let you know, but it does not show up in the grid, the "D" comes in play after the textboxes are populated from the grid selected row. However I'll double check tomorow morning.
0
 
SStoryCommented:
Show me the code for populating from the grid to the textboxes.
If you do msgbox(textbox1.text) does it have a D?
0
 
brilloxAuthor Commented:
Here is the code

        Me.Txt_CO2_Factor_ID.Text = Dt.Rows(0).Item("CO2_Factor_ID").ToString
        Me.Txt_CO2_Transport_Type.Text = Dt.Rows(0).Item("Transport_Type").ToString
        Me.Txt_CO2_CO2_km.Text = Dt.Rows(0).Item("CO2_km").ToString
        Me.Txt_CO2_CO2_miles.Text = Dt.Rows(0).Item("CO2_miles").ToString
        Me.Txt_CO2_Air_Factor.Text = Dt.Rows(0).Item("Air_Factor").ToString
        Me.Txt_CO2_Comments.Text = Dt.Rows(0).Item("Comments").ToString

the values with the "D" are CO2_KM, CO2_Miles, Air_Factor

I also tried Decimal.parse(Me.Txt_CO2_CO2_km.Text = Dt.Rows(0).Item("CO2_km").ToString)

same result


0
 
SStoryCommented:
Try something like this:
cstr(val(Dt.Rows(0).Item("CO2_km")))
0
 
SStoryCommented:
I think the .ToString is where you problem comes from..but I am not positive. I wonder if getting at the value would be the solution.
0
 
brilloxAuthor Commented:
this is wird....

in the gridview the fornat is fine.. no "D" there...
then when I select a gridview orw to populate the textboxes, the "D" apperas....

So... from the dataset to the gridview is fine; it is from the gridview to the textboxes that the problem is...

this is what I use to get the value from the textbox to a variable

Dim CO2_Miles As Decimal = Nothing
CO2_Km = Decimal.Parse(Me.Txt_CO2_CO2_km.Text.Trim)

and this is the format I use in the gridview bound control to display the decimals
 <asp:BoundField DataField="CO2_miles" HeaderText="CO2 per Mile"
               DataFormatString="{0:f4}" HtmlEncode="False" />

I do not have any format option (I could not find any) for the textboxes


0
 
brilloxAuthor Commented:
sorry for all the typos...
0
 
brilloxAuthor Commented:
I am attaching a zip file with vb.net files and SQL scripts for you to reproduce the error on your machine (On bthe gridview selectedrow) while inserting or updating.
SQL.zip
0
 
SStoryCommented:
I wonder about this code in the AdminDataEntry part

Can you set a breakpoint or some how debug to tell me when you get
Dt.Rows(0).Item("CO2_Factor_ID")
what value is coming out? Does it have a D at this point?  Should it possibly be cast to a Decimal before set to the textbox at this point?
  Protected Sub Grd_CO2_Display_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Grd_CO2_Display.SelectedIndexChanged
 
        Dim Dt As New DataTable
        Dim Id As Integer = 0
 
        Id = Me.Grd_CO2_Display.SelectedDataKey.Value
        Dt = BLL.GetDataById(Id).Tables(0)
 
        Me.Txt_CO2_Factor_ID.Text = Dt.Rows(0).Item("CO2_Factor_ID")
        Me.Txt_CO2_Transport_Type.Text = Dt.Rows(0).Item("Transport_Type")
        Me.Txt_CO2_CO2_km.Text = Dt.Rows(0).Item("CO2_km")
        Me.Txt_CO2_CO2_miles.Text = Dt.Rows(0).Item("CO2_miles")
        Me.Txt_CO2_Air_Factor.Text = Dt.Rows(0).Item("Air_Factor")
        Me.Txt_CO2_Comments.Text = Dt.Rows(0).Item("Comments")
 
        Me.LabelMessage.Text = Nothing
 
    End Sub

Open in new window

0
 
brilloxAuthor Commented:
No at that point all the values are displayed as string (e.g."0.3345") without "D"s

the problem start here

Protected Sub ImgBtn_New_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImgBtn_New.Click

        Dim CO2_Km As Decimal = Nothing
        Dim CO2_Miles As Decimal = Nothing
        Dim Air_Factor As Decimal = Nothing
        Dim TransportType As String = String.Empty
        Dim New_Comment As String = String.Empty

        TransportType = Me.Txt_CO2_Transport_Type.Text
        New_Comment = Me.Txt_CO2_Comments.Text

        'check for empty values otherwise catsing will trhow an exception
        If Me.Txt_CO2_CO2_km.Text = "" Then
            CO2_Km = CType(Me.Txt_CO2_CO2_km.Text, Decimal)
        Else
            CO2_Km = Nothing
        End If

        If Me.Txt_CO2_CO2_miles.Text <> "" Then
            CO2_Miles = CType(Me.Txt_CO2_CO2_miles.Text, Decimal)
        Else
            CO2_Miles = Nothing
        End If

        If Me.Txt_CO2_Air_Factor.Text <> "" Then
            Air_Factor = CType(Me.Txt_CO2_Air_Factor.Text, Integer)

        Else
            Air_Factor = Nothing

        End If

        Me.LabelMessage.Text = BLL.InsertData _
        (TransportType, CO2_Km, CO2_Miles, Air_Factor, New_Comment)
        cleantextboxes()
        Me.Grd_CO2_Display.DataBind()


    End Sub

for example

 If Me.Txt_CO2_CO2_km.Text <> "" Then
            CO2_Km = CType(Me.Txt_CO2_CO2_km.Text, Decimal)
        Else
            CO2_Km = Nothing
        End If

will add the D as part of 0.3345 or whatever value the textbox has
0
 
SStoryCommented:
   Dim TransportType As String = String.Empty
        Dim New_Comment As String = String.Empty

        TransportType = Me.Txt_CO2_Transport_Type.Text
        New_Comment = Me.Txt_CO2_Comments.Text
The above are declared as strings. Do they cause problems when assigned to the label?

also,
"CO2_Km = CType(Me.Txt_CO2_CO2_km.Text, Decimal)"
What is Me in this context?  Is it the form?  
What is the value of me.Txt_CO2_CO2_km.Text before it is cast?
0
 
brilloxAuthor Commented:
ransportType = Me.Txt_CO2_Transport_Type.Text
        New_Comment = Me.Txt_CO2_Comments.Text
The above are declared as strings. Do they cause problems when assigned to the label?

I guess my naming system is confusing you a bit  :-)

yes, Me is related to the form, Txt is because is a textbox, CO2_Comments  is the same name of the field in the sql table, so in this case is a string (comments) nothing to do with the decimal CO2

The value of Me.Txt_CO2_CO2Km before the cast is "0.1212" then after the cast, CO2_Km value is 0.1212D

here is the D coming up !!

0
 
brilloxAuthor Commented:
I start to think of  a bug of Visual studio 2008 SP 1 :-)
0
 
SStoryCommented:
What are you doing to find the value of CO2_KM?  using code, or some sort of debugging watch?
I'm just trying to find somewhere that it is calling the tostring function or converting to string where unexpected.  Apart from this, by looking at your code, I have no idea why this would be occurring.
0
 
brilloxAuthor Commented:
debugging watch ... if I use response.write the values are just fine
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now