Is conversion from string to decimal needed and if so how should it be done?

Question

I am trying to write a code for correction of entries to my SQL Server database. I am a mechanical engineering student who has a programming class and I have never programmed before so I am not sure should I convert string to decimal and how. Last 2 rows contain 2 options I came up with. Second one is what I use for pure string, first one is a modification of formatting datetime.

This is my stored procedure:

ALTER PROCEDURE [dbo].[SP_RN_O_Ispravak]

@Br_RN_O bigint,
@Datum_O DateTime OUTPUT,
@Sifra_p int OUTPUT,
@Ime_P nvarchar (30) output,
@Prezime_P NVarChar(30) OUTPUT,
@Naziv_P nvarchar (50) output,
@Adresa_P nvarchar (50) OUTPUT,
@Telefon_P NVarChar(15) OUTPUT,
@Sifra_z int OUTPUT,
@Ime_Z nvarchar (30) output,
@Prezime_Z nvarchar (30) output,
@Sifra_kul nvarchar (3) OUTPUT,
@Naziv_Kul NVarChar(20) OUTPUT,
@Masa_O decimal (5,0) OUTPUT,
@Vlaga_O decimal (4,1) OUTPUT,
@Hek_Masa_O decimal (3,1) OUTPUT,
@Protein_O decimal (3,1) output,
@Cijena_O decimal (3,2) output
AS
SELECT @Br_RN_O=T_Otkup.Br_RN_O,
    @Datum_O=T_Otkup.Datum_O,
    @Sifra_p=T_Otkup.Sifra_p,
    @Sifra_z=T_Otkup.Sifra_z,
    @Sifra_kul=T_Otkup.Sifra_kul,
    @Masa_O=T_Otkup.Masa_O,
    @Vlaga_O=T_Otkup.Vlaga_O,
    @Hek_Masa_O=T_Otkup.Hek_Masa_O,
    @Protein_O=T_Otkup.Protein_O
FROM   T_Otkup 
WHERE  (T_Otkup.Br_RN_O = @Br_RN_O)

SELECT @Prezime_P=Prezime_P
FROM   T_Poljoprivrednik
WHERE  Sifra_P=@Sifra_p

SELECT @Prezime_z=Prezime_Z
FROM   T_Zaposlenik
WHERE  Sifra_Z=@Sifra_z

SELECT @Naziv_kul=Naziv_Kul
FROM   T_Kultura
WHERE  Sifra_Kul=@Sifra_kul
RETURN

This procedure is supposed to pull the data from the database and place it in textboxes shown in the image.visual of whati'm trying to make I managed to use the following code to convert decimal to string:

        Dim cijenao As SqlParameter = New SqlParameter("@Cijena_O", Data.SqlDbType.Decimal, 3, 2)
    cijenao.Direction = Data.ParameterDirection.Output
    cijenao.Value = Cijena_O.Text
    cmd.Parameters.Add(cijenao)

    Masa_O.Text = Format(masao.Value, "#####").ToString
    Vlaga_O.Text = Format(vlagao.Value, "###.#").ToString
    Hek_Masa_O.Text = Format(hmasao.Value, "##.#").ToString
    Protein_O.Text = Format(proto.Value, "##.#").ToString

However, it doesn't work for 2 decimal places like this:

Cijena_O.Text = Format(cijenao.Value, "#.##").ToString

I tried using the code posted by Mary, but it get the following message:

System.Data.SqlClient.SqlException: 'Procedure or function SP_RN_O_Ispravak has too many arguments specified.'

Answer 1

I've cleaned up this Sub, to properly scope the connection, and make sure the Connection and Command objects get disposed (via Using). It is always best to explicitly handle the data type conversions, such as using .ToString() on the .Value property of the parameters. Note I also parse the Long before assigning it to the input parameter (although you should Google the .TryParse() method and use that).

Protected Sub ISPRAVAK_NALOGA()

        Using conn As New SqlConnection(<your connection string here>)

            Using cmd As New SqlCommand("SP_RN_O_Ispravak", conn) With {.CommandType = CommandType.StoredProcedure}

                With cmd

                    .Parameters.Add("@Br_RN_O", SqlDbType.BigInt).Value = Long.Parse(Br_RN_O.Text)

                    .Parameters.Add("@Masa_O", Data.SqlDbType.Decimal, 5, 0)
                    .Parameters("@Masa_O").Direction = ParameterDirection.Output

                    .Parameters.Add("@Vlaga_O", Data.SqlDbType.Decimal, 4, 1)
                    .Parameters("@Vlaga_O").Direction = ParameterDirection.Output

                    conn.Open()
                    .ExecuteNonQuery()

                    Masa_O.Text = .Parameters("@Masa_O").Value.ToString
                    Vlaga_O.Text = .Parameters("@Vlaga_O").Value.ToString

                End With

            End Using

        End Using

    End Sub

Answer 2

As to your code...

Check the available overloads for the Constructors for the Parameter class. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter?view=netframework-4.8#constructors There are none that match your code. Dim masao As SqlParameter = New SqlParameter("@Masa_O", Data.SqlDbType.Decimal, 5, 0) The final parameter of the constructor with 4 parameters is a string holding the name of the source column.

A Bigint in Sql Server maps to and Int64 in .net. (A Long in vb.net) This is a good reference for mapping datatypes from Sql Server to .net. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings.

Now my code...

Declare the variable outside the using blocks.

The TryParse is a very clever method that not only test a string but fills the variable with the converted string when it succeeds. Return is functionly equivalent, in this case, to the vb.net specific Exit Sub . Return is often used in other languages (think C#).

Keep your databse objects local so you can control their closing and disposing. A Using block will do this for you even if there is an error. You don't need to create new variable for the parameters. They can be referred to by name in the Parameters collection. Set the values of maso an vlaga inside the Using block before the command is disposed.

After the database objects are duly discharges, we can set the values in the User Interface. Reguarding the .ToString method; N0 (the 0 is a zero) will give you a string containing the number with no decimal protion. The N stands for Number and the 0 is the number of decimal places. It adds commas to make the number easier to read and it will round as appropriate. See https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings for more details.

Ideally the database code and the UI code would be separated but that is for another day.

Protected Sub ISPRAVAK_NALOGA()
    Dim maso As Decimal
    Dim vlaga As Decimal
    Dim InputNumber As Long
    If Not Long.TryParse(Br_RN_O.Text, InputNumber) Then
        MessageBox.Show("Please enter a valid number.")
        Return
    End If

    Using conn As New SqlConnection("Your connection string")
        Using cmd As New SqlCommand("SP_RN_O_Ispravak", conn)
            cmd.CommandType = CommandType.StoredProcedure
            With cmd.Parameters
                .Add("@Br_RN_O", SqlDbType.BigInt).Value = InputNumber
                .Add("@Masa_O", Data.SqlDbType.Decimal)
                .Add("@Vlaga_O", Data.SqlDbType.Decimal)
            End With
            cmd.Parameters("@Vlaga_O").Direction = ParameterDirection.Output
            cmd.Parameters("@Masa_O").Direction = Data.ParameterDirection.Output
            conn.Open()
            cmd.ExecuteNonQuery()
            maso = CDec(cmd.Parameters("@Masa_O").Value)
            vlaga = CDec(cmd.Parameters("@Vlaga_O").Value)
        End Using
    End Using

    Masa_O.Text = maso.ToString("N0")
    Vlaga_O.Text = vlaga.ToString

End Sub

Answer 3

I am a bit unsure of what you intent. But if you want to know if you need to convert a number into a string before assigning the value to a textbox, then the answer is yes. You do need to convert it.

But there are a few things that you can do to display decimal values in a more readable way. For example, you can set the textbox customFormat to #########0.00 or ######,##0.00