data table question

[vbguy2008]

In the App I am writing for my wife, She wants to be able to change the number of servings in a recipe and have the program automatically adjust the amounts of the various ingredients she needs.

The amount of each ingredient in the original recipe is stored in a column of a data table that is then loaded into a data grid view for display.

So when the user changes the number of servings, can I somehow access the data stored in the data table and modify it before I reload the datagridview?

[jmcilhinney]

Presumably you have something like an Ingredient table with a Quantity column. You would simply loop through the Rows of the DataTable and multiply each Quantity value by the appropriate factor. There's no need for reloading anything. The table is already bound to the grid so any changes you make in the table will be automatically reflected in the grid.

[vbguy2008]

I do have a quantity column (its called Amount in the DB)

anyway, I just thought of something as I began to work on this feature of the program. I cant multiply a string.

Acceptable values for the Amount column are things like 1 or 0.25 or 1/8th. Right now the column is a nchar(10) in SQL Server.

What datatype could I change it to if I want to multiply 1/8th by say 5 and successfully get 5/8ths or multiply 0.25 by 5 and get 1 and 1/4th?

[Shaggy Hiker]

That's not going to be easy. You can't store "1/8" in a field as anything other than a string and have it show up right. You could store it as a decimal, but that has some obvious issues unless your wife reads decimals easily.

Another alternative would be to save the amount as two fields: Numerator and Denominator. An amount of 1/8 would have a 1 in the first field and an 8 in the second. An amount of 2 would have 2 in the first field and 1 in the second. Putting those back together to form your nice string would be a chore of a query, because you'd have to use something like a CASE statement to return just the numerator if the denominator is 1 (or it could be null, but it makes no significant difference), and return Numerator + '/' + Denominator for other cases. That can be done in SQL Server, I don't know if Access allows for CASE queries.

[techgnome]

carp... I have some code tucked away somewhere that takes fractions, converts them to decimal, and back again... If I can find it this weekend, I'll post it. It's going to be burried somewhere.

-tg

[vbguy2008]

I am trying to figure out the multiplication factor and I am having two problems

For the function I am using to do the division, it requires two doubles as inputs. The number of servings is typed into a textbox and is therefore a string. A string to a double conversion does not work it seems.

My second problem is that I am probably calling the wrong event but I dont know the right one to call. The event I am calling is firing more then just when I need it to.

here is the code I need some help with

Private Sub txtServes_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtServes.TextChanged
        Dim orginalvalue As Double 'orginal servings
        Dim newValue As Double

        If txtServes.Text = vbNullString Then
            Return
        Else
            newValue = CType(txtServes.Text, Double)
        End If


        Dim multiplicationFactor As Double

        orginalvalue = getDatabaseValue()
        multiplicationFactor = Math.IEEERemainder(newValue, orginalvalue)
        MsgBox(multiplicationFactor)




    End Sub
    Private Function getDatabaseValue() As Double
        Dim myDB As New DatabaseConnect
        Dim SQLstatement As String
        Dim temp As Decimal
        SQLstatement = "Select servings from dbo.recipe where recipe_id=@recipe_id"
        Using conn As New SqlClient.SqlConnection(myDB.getConnection)
            Try
                conn.Open()

            Catch ex As Exception
                MsgBox("Failed to open Database", MsgBoxStyle.Critical)
                Return 0
            End Try
            Try
                Using sqlcomm As New SqlClient.SqlCommand(SQLstatement, conn)
                    sqlcomm.Parameters.AddWithValue("@recipe_id", CurrentRecipe)
                    temp = sqlcomm.ExecuteScalar() ' database value is a real data type
                    getDatabaseValue = CType(temp, Double)
                End Using
            Catch ex As Exception
                MsgBox(ex.Message)
                Return 0
            End Try
            conn.Close()
        End Using
    End Function

[Shaggy Hiker]

The safe way to convert a string to a double is:

Double.TryParse(<string to convert>,<variable to hold the result>)

The method is different from other conversions, as it returns True if it worked, or False if the string could not be converted. You can't have text or spaces in the number for the conversion to work.