how to sum number value to database field ?

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

33 Replies - 10150 Views - Last Post: 08 October 2011 - 06:26 PM Rate Topic: -----

#1 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

how to sum number value to database field ?

Posted 30 September 2011 - 07:22 AM

Hi,
how to sum the number value(textbox) to my database field ?

the red section doesn't sum the value(textbox) to my data field.

    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click

        Dim sql As String
        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim Result As Integer
        Dim qtty As Integer
        Dim a As Integer = 0


        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Or TextBox7.Text = "" Then
            MsgBox("Please fill in the all field!")
            TextBox1.Focus()
            default2()

        Else

            sql1 = "Select count(Itemcode) from inventory Where Itemcode = @Itemcode"
            Dim com2 As OleDbCommand = New OleDbCommand(sql1, con4)
            com2.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
            Result = CInt(com2.ExecuteScalar)

            If Result > 0 Then

                [color="#FF0000"]qtty = Trim(ds4.Tables(0).Rows(a).Item("Additional")) + Val(TextBox3.Text)[/color]

                sql3 = "Update inventory set Description = @Description ,Additional = @Additional Where Itemcode = @Itemcode"
                Dim com3 As OleDbCommand = New OleDbCommand(sql3, con4)
                com3.Parameters.AddWithValue("@Description", TextBox2.Text)
                com3.Parameters.AddWithValue("@Additional", CInt(qtty))
                com3.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com3.ExecuteNonQuery()
                MsgBox("Updated Successfully")
                ConnectInventory()
            End If

            sql = "Insert into Receive(Itemcode,Description,Qty,DateEntry,ReceiveBy,Gin,Invoice) Values (@Itemcode ,@Description ,@Qty ,@DateEntry ,@ReceiveBy ,@Gin ,@Invoice)"
            Dim com As OleDbCommand = New OleDbCommand(sql, con2)
            com.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
            com.Parameters.AddWithValue("@Description", TextBox2.Text)
            com.Parameters.AddWithValue("@Qty", CInt(TextBox3.Text))
            com.Parameters.AddWithValue("@DateEntry", TextBox4.Text)
            com.Parameters.AddWithValue("@ReceiveBy", TextBox5.Text)
            com.Parameters.AddWithValue("@Gin", CInt(TextBox6.Text))
            com.Parameters.AddWithValue("@Invice", CInt(TextBox7.Text))

            com.ExecuteNonQuery()
            MsgBox("New Record Add Successfully")
            Connect2()

            sql2 = "Insert into Inventory(Itemcode,Description,additional) Values (@Itemcode ,@Description ,@additional)"
            Dim com1 As OleDbCommand = New OleDbCommand(sql2, con4)
            com1.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
            com1.Parameters.AddWithValue("@Description", TextBox2.Text)
            com1.Parameters.AddWithValue("@Additional", CInt(TextBox3.Text))

            com1.ExecuteNonQuery()

        End If

    End Sub



anyone help appreciated.


Is This A Good Question/Topic? 0
  • +

Replies To: how to sum number value to database field ?

#2 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: how to sum number value to database field ?

Posted 30 September 2011 - 08:49 AM

I would advise (IMO):

  • Turn Option Strict On
  • Use at the very least Parse (TryParse is a better choice) instead of Val() - better would be to even use a NumberUpDown control on the form for the quantity box.


Why option strict? You will see all the places that implicit conversions are possibly going to cause you issues.
Why NumberUpdDown control? You can set the minimum and maximum numbers, set the default value, and it's value is already numerical (Double).
Why TryParse or Parse? You are saying in your code that "this may not be a number, but I am going to check and then handle it when it isn't" which is (imo) better than the Val() mehtod.

Also while you Trim() the Additional value from the database you don't cast or convert it, once again relying on the implicit conversion.

Another thing MsgBox - replace with MessageBox.Show() since this is the method that is actually getting called anyways.

Not trying to be too over critical. :)
Was This Post Helpful? 0
  • +
  • -

#3 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 75
  • View blog
  • Posts: 323
  • Joined: 12-January 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 09:29 AM

View Postdemausdauth, on 30 September 2011 - 08:49 AM, said:

Not trying to be too over critical. :)


I wouldn't call it over critical, I would have mentioned the same things had I caught it before you. The TryParse avoids expensive exceptions and exception handling, the msgbox is per the Miscrosoft suggest standard practices, as for Option Strict...I have never used it, but now that someone has explained it, I may start.

EDIT: Additionally, if you use tryparse to get the integer values from your textboxes, you can do something like this:
Dim t1 ,t3, t6, t7 as Integer

If Not Integer.TryParse(Textbox1.Text, t1) or Textbox2.Text="" or Not Integer.TryParse(Textbox3.Text, t3) or Textbox4.Text="" or Textbox5.Text="" or Not Integer.TryParse(Textbox6.Text, t6) or Not Integer.TryParse(Textbox7.Text, t7) then

     MsgBox("Please fill in all the fields with appropriate values!")
     Textbox1.Focus()
     default2()
Else
     '...code here
End If



Now you don't have to worry about casting during the sql statement, making it much easier to read.

Another thing I could suggest to make it more readable is to use this:

With com.Parameters
     .AddWithValue("stuff", val)
     '....the rest of your com.Parameters
End With



this will reduce the amount of typing you have to do and make it easier to read.

This post has been edited by Psyguy: 30 September 2011 - 09:41 AM

Was This Post Helpful? 0
  • +
  • -

#4 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 181
  • View blog
  • Posts: 659
  • Joined: 03-February 10

Re: how to sum number value to database field ?

Posted 30 September 2011 - 11:07 AM

@Psyguy

Quoted from Get Dot Net Code - Option Strict On and another good reference Option Explicit and Option Strict.

Quote

Although Visual Basic .NET allows you to perform implicit type conversions and late binding, you should avoid these practices. Implicit type conversions and late binding may lead to severe performance problems, runtime errors, code that is difficult to read and maintain, and sub-standard programming practices. Many VB.NET professional programmers believe that leaving OPTION STRICT OFF by default was perhaps Microsoft's worst decision in the VB.NET implementation. Use OPTION STRICT ON.

Note: It is interesting to notice that the C# compiler automatically performs the same type checking that the VB.NET compiler only performs if OPTION STRICT is set to ON in a VB.NET project. C# is a new language and there is no legacy C# code to be upgraded to C#.NET. Because of this the dangerous option of turning strict type checking off is not a part of the C# language.

What does OPTION STRICT ON do? It forces a strong typing system in your code. It restricts implicit data type conversions to only widening conversions. This explicitly disallows any data type conversions in which data loss would occur and any conversion between numeric types and strings. It verifies that functions have return types and that all paths return a value. Some specific things OPTION STRICT ON forces to happen are:
  • Finds undeclared variables;
  • Ensures you do not accidentally use late binding. Late binding can still be done when absolutely necessary.
  • Prevents data loss when one variable is assigned to another that has less precision or capacity;
  • Provides compile-time notification when bad type conversions are tried.

Was This Post Helpful? 1
  • +
  • -

#5 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 75
  • View blog
  • Posts: 323
  • Joined: 12-January 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 12:00 PM

Thanks for the info!
Was This Post Helpful? 0
  • +
  • -

#6 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 75
  • View blog
  • Posts: 323
  • Joined: 12-January 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 12:00 PM

Thanks for the info!
Was This Post Helpful? 0
  • +
  • -

#7 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 06:27 PM

Hi,
thank for all of you advise and info,im still new in vb.net,my problem is the number value on textbox3 cannot sum in my database 'additional' field? i know this code cause the problem,but i don't know how to slove it.

this code:
qtty = (ds4.Tables(0).Rows(a).Item("Additional") + Val(TextBox3.Text)


anyone help appreciated.
Was This Post Helpful? 0
  • +
  • -

#8 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 75
  • View blog
  • Posts: 323
  • Joined: 12-January 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 10:40 PM

View Posthwkong1688, on 30 September 2011 - 06:27 PM, said:

Hi,
thank for all of you advise and info,im still new in vb.net,my problem is the number value on textbox3 cannot sum in my database 'additional' field? i know this code cause the problem,but i don't know how to slove it.

this code:
qtty = (ds4.Tables(0).Rows(a).Item("Additional") + Val(TextBox3.Text)


anyone help appreciated.


Is the problem with the left side of the + or the right side? Val is a dangerous method to use if you are unsure of what is going to be in Textbox3. Personally, I prefer to use either TryParse or Parse because, even though Parse throws an exception if you feed it something that is not the desired data type, it will always tell you if you have a problem. Val will not do that, it will just throw a value into your result that may or may not make any sense.

Here is what I propose:
'first, make your Textbox3.text into an actual number
Dim tbVal as Double
Double.TryParse(Textbox3.text, tbVal)
'then try and add it to the database value
qtty = ds4.Tables(0).Rows(a).Item("Additional") + tbVal


With this situation, you can easily set some break points on the "qtty =..." line and figure out what each side of the plus sign holds. If you have a problem, this will be the best way to find it. Another thing you may want to check is how the database stores the value ds4.Tables(0).Rows(a).Item("Additional"). If it is stored as something other than the datatype you are looking for, you may run into problems when trying to add it to another number. You may want to just add another variable and assign the database value to it to make sure.

Edit: you also have one extra "(" in your call to the dataset table.

This post has been edited by Psyguy: 30 September 2011 - 10:41 PM

Was This Post Helpful? 0
  • +
  • -

#9 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 30 September 2011 - 11:43 PM

Psyguy, thank for reply, i follow your propose but the number not accurate in the data field.

(eg.user key 50 in textbox3 when i click save button the 50 will updated to the exists 'additional'
field value.If 'additional field value current is 50 then will become 100)

see attachment.

my code:
 Dim sql As String
        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim Result As Integer
        Dim qtty As Integer
        Dim a As Integer = 0
        Dim t1, t3, t6, t7 As Integer

        If Not Integer.TryParse(TextBox1.Text, t1) Or TextBox2.Text = "" Or Not Integer.TryParse(TextBox3.Text, t3) Or TextBox4.Text = "" Or TextBox5.Text = "" Or Not Integer.TryParse(TextBox6.Text, t6) Or Not Integer.TryParse(TextBox7.Text, t7) Then
            MessageBox.Show("Please fill in the all field!")
            TextBox1.Focus()
            default2()

        Else

            sql1 = "Select count(Itemcode) from inventory Where Itemcode = @Itemcode"
            Dim com2 As OleDbCommand = New OleDbCommand(sql1, con4)
            com2.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
            Result = CInt(com2.ExecuteScalar)
           
            If Result > 0 Then

                Dim tbval As Double
                Double.TryParse(TextBox3.Text, tbval)
                qtty = ds4.Tables(0).Rows(a).Item("Additional") + tbval

                sql3 = "Update inventory set Description = @Description ,Additional = @Additional Where Itemcode = @Itemcode"
                Dim com3 As OleDbCommand = New OleDbCommand(sql3, con4)
                com3.Parameters.AddWithValue("@Description", TextBox2.Text)
                com3.Parameters.AddWithValue("@Additional", CInt(qtty))
                com3.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com3.ExecuteNonQuery()
                MsgBox("Updated Successfully")
                ConnectInventory()

            Else

                sql = "Insert into Receive(Itemcode,Description,Qty,DateEntry,ReceiveBy,Gin,Invoice) Values (@Itemcode ,@Description ,@Qty ,@DateEntry ,@ReceiveBy ,@Gin ,@Invoice)"
                Dim com As OleDbCommand = New OleDbCommand(sql, con2)
                com.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com.Parameters.AddWithValue("@Description", TextBox2.Text)
                com.Parameters.AddWithValue("@Qty", CInt(TextBox3.Text))
                com.Parameters.AddWithValue("@DateEntry", TextBox4.Text)
                com.Parameters.AddWithValue("@ReceiveBy", TextBox5.Text)
                com.Parameters.AddWithValue("@Gin", CInt(TextBox6.Text))
                com.Parameters.AddWithValue("@Invice", CInt(TextBox7.Text))

                com.ExecuteNonQuery()
                MsgBox("New Record Add Successfully")
                Connect2()

                sql2 = "Insert into Inventory(Itemcode,Description,additional) Values (@Itemcode ,@Description ,@additional)"
                Dim com1 As OleDbCommand = New OleDbCommand(sql2, con4)
                com1.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com1.Parameters.AddWithValue("@Description", TextBox2.Text)
                com1.Parameters.AddWithValue("@Additional", CInt(TextBox3.Text))

                com1.ExecuteNonQuery()

            End If
        End If


Was This Post Helpful? 0
  • +
  • -

#10 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 01 October 2011 - 03:50 AM

I think maybe this code cause the problem,because the number no accurate,actually i only want to add the number value who ever user key the number from textbox3 will add to the additional data field,anyone can help me to solve this problem.

your help will much appreciated.

im using access database.
data type for additional field is integer.


Dim tbval As Double
Double.TryParse(TextBox3.Text, tbval)
qtty = ds4.Tables(0).Rows(a).Item("Additional") + tbval


Was This Post Helpful? 0
  • +
  • -

#11 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1820
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: how to sum number value to database field ?

Posted 01 October 2011 - 04:29 AM

If I get you correctly, you need to add the summation of all column values with the value entered?
If so just get the sum of all column using executeScalar() and the add the value. Something like:
 query = select SUM(Additional) from inventory" 'Assumes your column is of numeric type
Dim com2 As OleDbCommand = New OleDbCommand(query, con4)
dim x As Double = Double.Parse(com2.ExecuteScalar())
Dim sum As Double = x + Double.Parse(TextBox3.Text)

Is this what you want?
Was This Post Helpful? 0
  • +
  • -

#12 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 01 October 2011 - 06:18 AM

smohd,thank for reply,sorry i english not so well maybe you can't get want i mean.
i want is just key number from textbox3 then will add/sum to the additional field.

you can see refer to attachment is more clear.

Attached Image
Was This Post Helpful? 0
  • +
  • -

#13 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 01 October 2011 - 06:52 AM

it's work!! thank you so much psyguy and all of you guy.
you propose to me on #8 working fine. just now i write to the wrong line,
now everything is working fine.
Was This Post Helpful? 0
  • +
  • -

#14 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 75
  • View blog
  • Posts: 323
  • Joined: 12-January 11

Re: how to sum number value to database field ?

Posted 01 October 2011 - 12:26 PM

Glad i could help.
Was This Post Helpful? 0
  • +
  • -

#15 hwkong1688  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 30-September 11

Re: how to sum number value to database field ?

Posted 01 October 2011 - 06:11 PM

Hi, now another problem again. Why only can updated the first row (row 0) of additional data field?
the other in additional data field (row 1,2,3..) still cannot.

my code:
Dim sql As String
        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim Result As Integer
        Dim qtty As Integer
        Dim t1, t3, t6, t7 As Integer

        If Not Integer.TryParse(TextBox1.Text, t1) Or TextBox2.Text = "" Or Not Integer.TryParse(TextBox3.Text, t3) Or TextBox4.Text = "" Or TextBox5.Text = "" Or Not Integer.TryParse(TextBox6.Text, t6) Or Not Integer.TryParse(TextBox7.Text, t7) Then
            MessageBox.Show("Please fill in the all field!")
            TextBox1.Focus()
            default2()

        Else

            sql1 = "Select count(Itemcode) from inventory Where Itemcode = @Itemcode"
            Dim com2 As OleDbCommand = New OleDbCommand(sql1, con4)
            com2.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
            Result = CInt(com2.ExecuteScalar)

            Dim tbval As Integer
            Dim a As Integer
            Integer.TryParse(TextBox3.Text, tbval)
            qtty = ds4.Tables(0).Rows(a).Item("Additional") + tbval

            If Result > 0 Then

                sql3 = "Update inventory set Description = @Description ,Additional = @Additional Where Itemcode = @Itemcode"
                Dim com3 As OleDbCommand = New OleDbCommand(sql3, con4)
                com3.Parameters.AddWithValue("@Description", TextBox2.Text)
                com3.Parameters.AddWithValue("@Additional", qtty)
                com3.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com3.ExecuteNonQuery()
                MsgBox("Updated Successfully")
                ConnectInventory()

            Else

                sql = "Insert into Receive(Itemcode,Description,Qty,DateEntry,ReceiveBy,Gin,Invoice) Values (@Itemcode ,@Description ,@Qty ,@DateEntry ,@ReceiveBy ,@Gin ,@Invoice)"
                Dim com As OleDbCommand = New OleDbCommand(sql, con2)
                com.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com.Parameters.AddWithValue("@Description", TextBox2.Text)
                com.Parameters.AddWithValue("@Qty", CInt(TextBox3.Text))
                com.Parameters.AddWithValue("@DateEntry", TextBox4.Text)
                com.Parameters.AddWithValue("@ReceiveBy", TextBox5.Text)
                com.Parameters.AddWithValue("@Gin", CInt(TextBox6.Text))
                com.Parameters.AddWithValue("@Invice", CInt(TextBox7.Text))

                com.ExecuteNonQuery()
                MsgBox("New Record Add Successfully")
                Connect2()

                sql2 = "Insert into Inventory(Itemcode,Description,additional) Values (@Itemcode ,@Description ,@additional)"
                Dim com1 As OleDbCommand = New OleDbCommand(sql2, con4)
                com1.Parameters.AddWithValue("@Itemcode", CInt(TextBox1.Text))
                com1.Parameters.AddWithValue("@Description", TextBox2.Text)
                com1.Parameters.AddWithValue("@Additional", CInt(TextBox3.Text))

                com1.ExecuteNonQuery()

            End If
        End If


Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3