Question Why number not accurate ??

[hwkong1688]

Hi,
I am new in VB 2008, why this code the number i add not expected what i want??

anyone help appreciated.

pls advised.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim com As New OleDbCommand
        Dim d As Integer
        Dim qty1 As Integer
        Dim result
        com.Connection = con2

        com.CommandText = "Select count(Itemcode) from da where Itemcode ='" & TextBox1.Text & "'"
        result = CInt(com.ExecuteScalar)

        qty1 = ds2.Tables(0).Rows(d).Item("Qty") + Val(TextBox3.Text)

        If result > 0 Then

            com.CommandText = "Update da set Description ='" & TextBox2.Text & "',Qty=" & qty1 & " Where Itemcode ='" & TextBox1.Text & "'"
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
        Else
            com.CommandText = "Insert into da(Itemcode,Description,Qty) values (@Itemcode, @Description, @Qty)"
            With com.Parameters
                .AddWithValue("@Itemcode", TextBox1.Text)
                .AddWithValue("@Description", TextBox2.Text)
                .AddWithValue("@Qty", TextBox3.Text)
            End With
            com.ExecuteNonQuery()
            MsgBox("Record Inserted")

        End If
    End Sub

 

[jmcilhinney]

There are a few issues with your code that you should address. First, you should pretty much never use Val. If you want to convert a String to a number then you should generally use the TryParse method of the numeric type you want to convert to. If you want an Integer then use Integer.TryParse, if you want a Double then use Double.TryParse, etc. TryParse will tell you whether the String was valid or not and, if it was, give you the converted value.

Second, always use parameters to insert variables into SQL code in preference to string concatenation. You have three SQL statements there and in one case you use parameters and in the other two you use string concatenation. Inconsistency is never a good thing. Why do the same thing in two different ways? Use parameters every time.

When you do use parameters, always make sure that the value you assign to them is the correct type. Your Qty is presumably a number and you're assigning a String to the @Qty parameter.

You are using a 'd' variable in there that you never assign a value to, so it's always going to be zero.

I suggest that you start by following the Blog link in my signature and reading the post on ADO.NET Parameters to learn why and how to use parameters properly. You should then address the issues I've mentioned above. If that doesn;t fix the issue then you should try providing us with a FULL and CLEAR description of the problem. That would include not just the fact that the code doesn't do what you expect, but also an explanation of what you do expect and what actually happens. A description of your data might help too, e.g. what the TextBoxes contain.

 

[hwkong1688]

thank for your advised, sorry for make your guy confusing what i want it, actually i want is quite simple when i update the table if the record is exist, then only update the other field, If the record does then create the new record.
Let say my qty field is 10 when I update the existing record qty 5 then will total up to 15
Last question is why I change the select statement with parameter my description field and qty field exchanged?

you can refer to my attachment

My table name:da
Table field:Itemcode(textbox1), description(textbox2), qty(textbox3)

my code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim com As New OleDbCommand
        Dim d As Integer = 0
        Dim qty1 As Integer
        Dim result
        com.Connection = con2

        'com.CommandText = "Select count(Itemcode) from da where Itemcode ='" & TextBox1.Text & "'"
        com.CommandText = "Select count(Itemcode) from da where Itemcode = @Itemcode"
        With com.Parameters
            .AddWithValue("@Itemcode", TextBox1.Text)
        End With

        result = CInt(com.ExecuteScalar)

        qty1 = ds2.Tables(0).Rows(d).Item("Qty") + Val(TextBox3.Text)

        If result > 0 Then

            com.CommandText = "Update da set Description = @description,Qty = @Qty Where Itemcode = @Itemcode"
            With com.Parameters
                .AddWithValue("@description", TextBox2.Text)
                .AddWithValue("@Qty", qty1)
                .AddWithValue("@Itemcode", TextBox1.Text)
            End With
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
        Else
            com.CommandText = "Insert into da(Itemcode,Description,Qty) values (@Itemcode, @Description, @Qty)"
            With com.Parameters
                .AddWithValue("@Itemcode", TextBox1.Text)
                .AddWithValue("@Description", TextBox2.Text)
                .AddWithValue("@Qty", Val(TextBox3.Text))
            End With
            com.ExecuteNonQuery()
            MsgBox("Record Inserted")

        End If


    End Sub

:blue:

 

[jmcilhinney]

Well, you haven't addressed any of the issues I mentioned earlier so I'm not going to comment further on that until you do. If you fix those issues then you'd likely find that your current problem would go away.

Apart from that though, you obviously have an issue with your database design. A column named "Qty" is obviously intended to store numbers, so it shouldn't possibly be able to store the value "maker pen". You need to go back and design your database correctly first, before you try to use it. That involves defining each column with the appropriate data type. If the Qty column is supposed to store integers only then it should be defined with the data type Integer. If it's supposed to store some other kind of numbers then use some other numeric data type. Only use the Text data type for columns that are supposed to store text.

 

[hwkong1688]

Hi jmcilhinney,

Now I already change my database itemcode field to integer type, description field to text type and quantity is integer type. Sorry im not familiar with interger.tryprase conversation, can you show me where to correct it.

Now I getting this error when I create the new record:

Data type mismatch in criteria expression.

my code:

 Dim com As New OleDbCommand
        Dim d As Integer = 0
        Dim result
        com.Connection = con2


        com.CommandText = "Select count(Itemcode) from da where Itemcode = @Itemcode"
        With com.Parameters
            .AddWithValue("@Itemcode", TextBox1.Text)
        End With


        result = CInt(com.ExecuteScalar)

        TextBox3.Text = ds2.Tables(0).Rows(d).Item("Quantity") + TextBox3.Text

        If result > 0 Then

            com.CommandText = "Update da set Description = @description,Quantity = @Quantity Where Itemcode = @Itemcode"
            With com.Parameters
                .AddWithValue("@Description", TextBox2.Text)
                .AddWithValue("@Quantity", TextBox3.Text)
                .AddWithValue("@Itemcode", TextBox1.Text)
            End With
            com.ExecuteNonQuery()
            MsgBox("Record Updated")
        Else
            com.CommandText = "Insert into da(Itemcode,Description,Quantity) values (@Itemcode, @Description, @Quantity)"
            With com.Parameters
                .AddWithValue("@Itemcode", TextBox1.Text)
                .AddWithValue("@Description", TextBox2.Text)
                .AddWithValue("@Quantity", TextBox3.Text)
            End With
            com.ExecuteNonQuery()
            MsgBox("Record Inserted")

        End If

please help..

 

[jmcilhinney]

It would be quite simple to find information and examples on using Integer.TryParse on the web. Asking people to provide you with the information should be the last option, not the first.

As for the data type mismatch, it is exactly as I said in my first post:

When you do use parameters, always make sure that the value you assign to them is the correct type. Your Qty is presumably a number and you're assigning a String to the @Qty parameter.

If you are comparing your parameter to an Integer column then your parameter value must be an Integer too, not a String. That's why you need to use Integer.TryParse: to convert the String to an Integer.