none
in vb.net - save curency field to SQL RRS feed

  • Question

  • how is possible save in an update Query "ExecuteNonQuery" a field Like $21,345.00 to a numeric field in SQL

    it is making an error

      the string is like this

    PaySql = "Update IncomeTbl SET Payment = " & Me.PaidFld.Text & "

    Monday, April 1, 2019 5:48 PM

All replies

  • Hello,

    You should not be saving formatting in your database at all, instead when read back data format it then format it.

    Example, in the Order table there is a freight column setup for currency. To format it use

    FORMAT([column name],'C')

    which is done as shown below.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 1, 2019 5:59 PM
    Moderator
  • i tested a string 

    Update IncomeT SET   Payment = FORMAT($2,000.00,'C') WHERE (IncomeT.InvoiceID = 304214)

    to run in sql 

    it is making a error

    Please test this

    Monday, April 1, 2019 6:16 PM
  • i tested a string 

    Update IncomeT SET   Payment = FORMAT($2,000.00,'C') WHERE (IncomeT.InvoiceID = 304214)

    to run in sql 

    it is making a error

    Please test this

    This will not work on a string field, must be a numeric field/column.

    EDIT:

    For a string field you would first need to change it to a numeric in code then use VB.NET formatting e.g. C2 then take that string and save it to the table which is a lot of work and as mentioned in my first post is wrong.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, April 1, 2019 6:20 PM
    Moderator
  • the column i test it is format "money"

    and it is not working

    Please test it

    Thanks

    also

    in vb.net

    a dataset an not save

    .Item("charges") = Charges.Text

    the error:

    Input string was not in a correct format.Couldn't store <$2,000.00> in Charges Column.  Expected type is Decimal.'

    Monday, April 1, 2019 6:26 PM
  • First off you should be providing exceptions "It's not working" is not one of them, Im surprised Karen even gave you some pointers.

    You should also be using parameters.

    Finally, you test it! it not her program, put some effort in...wow...


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, April 1, 2019 6:32 PM
  • the column i test it is format "money"

    and it is not working

    Please test it

    Thanks

    also

    in vb.net

    a dataset an not save

    .Item("charges") = Charges.Text

    the error:

    Input string was not in a correct format.Couldn't store <$2,000.00> in Charges Column.  Expected type is Decimal.'

    It's not working because you are not listening to me, you store in this case the numeric value, zero formatting as a money field does not accept formatting.

    It's completely incorrect to even try and store formating as I've now indicated several times.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 1, 2019 6:36 PM
    Moderator
  • in a dataset i test it Like this

    .Item("charges") = Format(tCrg.Text, "C")

    Error Message:

    System.ArgumentException: 'Input string was not in a correct format.Couldn't store <C> in Charges Column.  Expected type is Decimal.'

    Monday, April 1, 2019 6:42 PM
  • in a dataset i test it Like this

    .Item("charges") = Format(tCrg.Text, "C")

    Error Message:

    System.ArgumentException: 'Input string was not in a correct format.Couldn't store <C> in Charges Column.  Expected type is Decimal.'

    Yes that is the correct error message, again you are attempting to set a string to a numeric field which will not work.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 1, 2019 6:49 PM
    Moderator
  • Perhaps this will make sense

    I read the table as per below using my BaseConnectionLibrary in my signature to connect to the database.

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Inherits BaseSqlServerConnections
        Public Sub New()
            DatabaseServer = ".\SQLEXPRESS"
            DefaultCatalog = "NorthWindAzure3"
        End Sub
        ''' <summary>
        ''' Read all Persons rows
        ''' </summary>
        ''' <returns></returns>
        Public Function Read() As DataTable
            Dim dt As New DataTable
    
            Dim selectStatement = "SELECT TOP 10 OrderID,Freight,ShipCountry FROM dbo.Orders;"
    
            Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                End Using
            End Using
    
            Return dt
    
        End Function
    
    End Class

    The DataGridView is setup where each column has it's DataMember set to the underlying column in the SELECT above and the freight column is formatted with C2.

    Note the data binding to show both formatted and unformatted data.

    The table is the same table as in my former reply, freight is type money, a decimal field would work too.

    So here it is with an update added in

    Public Class Form1
        Private bsOrders As New BindingSource
        Private ops As New DataOperations
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsOrders.DataSource = ops.Read
            DataGridView1.AutoGenerateColumns = False
            DataGridView1.DataSource = bsOrders
            freightTextBox.DataBindings.Add("Text", bsOrders, "Freight")
        End Sub
    
        Private Sub updateCurrentFreightButton_Click(sender As Object, e As EventArgs) Handles updateCurrentFreightButton.Click
            Dim row As DataRow = CType(bsOrders.Current, DataRowView).Row
            Dim orderId As Integer = row.Field(Of Integer)("OrderId")
            Dim freight As Decimal = row.Field(Of Decimal)("Freight")
    
            ops.Update(orderId, freight)
    
        End Sub
    End Class
    

    .

    Imports System.Data.SqlClient
    
    Public Class DataOperations
        Inherits BaseSqlServerConnections
        Public Sub New()
            DatabaseServer = ".\SQLEXPRESS"
            DefaultCatalog = "NorthWindAzure3"
        End Sub
        ''' <summary>
        ''' Read all Persons rows
        ''' </summary>
        ''' <returns></returns>
        Public Function Read() As DataTable
            Dim dt As New DataTable
    
            Dim selectStatement = "SELECT TOP 10 OrderID,Freight,ShipCountry FROM dbo.Orders;"
    
            Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                End Using
            End Using
    
            Return dt
    
        End Function
        Public Sub Update(orderId As Integer, freight As Decimal)
            Dim updateStatement = "UPDATE dbo.Orders SET Freight = @Freight WHERE dbo.Orders.OrderID = @OrderId;"
            Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = updateStatement}
                    cmd.Parameters.AddWithValue("@Freight", freight)
                    cmd.Parameters.AddWithValue("@OrderId", orderId)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, April 1, 2019 7:15 PM
    Moderator
  • Karen, I have to say you were gifted with an exceptional volume of patience.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, April 1, 2019 7:28 PM
  • Karen, I have to say you were gifted with an exceptional volume of patience.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thanks :-) My goal in this post is to get them to understand but so far I think they don't understand enough about databases else this would be resolved in the first post.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, April 1, 2019 7:55 PM
    Moderator