[RESOLVED] Arithmetic Overflow Error?

[blakemckenna]

I'm getting this error message (see image). I've defined most of my fields in the SQL table as decimal(5,2). I'm guessing this message is a result of trying to put a value larger than what the field is defined for.

Here's my code in my VB.Net app as well as a screenshot of my SQL DB Table.

    Public Function Insertcustomer() As Boolean
        Try
            Insertcustomer = False

            strSQL = "INSERT INTO tblCustomers " & _
                       "(customerID," & _
                        "custName," & _
                        "useBackOrders," & _
                        "usePurchaseOrders," & _
                        "applyDiscounts," & _
                        "email," & _
                        "shipMethod," & _
                        "website," & _
                        "addrDesc," & _
                        "paymentTerms," & _
                        "businessID," & _
                        "systemStatus) " & _
                     "VALUES " & _
                       "('" & customer.customerID & "'," & _
                       "'" & customer.custName & "'," & _
                       "'" & customer.UseBackOrders & "'," & _
                       "'" & customer.UsePurchaseOrders & "'," & _
                       "'" & customer.ApplyDiscounts & "'," & _
                       "'" & customer.Email & "'," & _
                       "'" & customer.ShipMethod & "'," & _
                       "'" & customer.Website & "'," & _
                       "'" & customer.AddrDesc & "'," & _
                       customer.PaymentTerms & "," & _
                       "'" & customer.BusinessID & "'," & _
                       "'" & customer.SystemStatus & "')"

            Dim ins As New SqlCommand(strSQL, cnn)

            customer.adpcustomers.InsertCommand = ins
            customer.adpcustomers.InsertCommand.ExecuteNonQuery()

            Insertcustomer = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function

Thanks,

[Shaggy Hiker]

The SQL query references tblCustomers, while you then show a screen shot of a different table. Frankly, most of the fields in your query look like text fields or booleans. I don't see any that look like they would necessarily be numeric.

[blakemckenna]

oops...my bad! Here ya go.

    Public Function InsertItem() As Boolean
        Try
            Insertitem = False

            strSQL = "INSERT INTO tblItemMaster " & _
                        "(itmID," & _
                         "itmName," & _
                         "itmCatg," & _
                         "itmType," & _
                         "itmDescription," & _
                         "itmPrimaryVendorID," & _
                         "itmVendorItemID," & _
                         "itmQtyOnhand," & _
                         "itmReorderQty," & _
                         "itmReorderPoint," & _
                         "itmRetailPrice," & _
                         "itmWhslPrice," & _
                         "itmUOI," & _
                         "itmUnitsPerIssue," & _
                         "itmLastPurchaseDate," & _
                         "itmMTDSold," & _
                         "itmYTDSold," & _
                         "itmPictureLocation," & _
                         "itmApplyDiscount," & _
                         "itmDiscountPercent) " & _
                      "VALUES " & _
                        "('" & item.ItemID & "'," & _
                         "'" & item.Name & "'," & _
                         "'" & item.Catg & "'," & _
                         "'" & item.ItemType & "'," & _
                         "'" & item.Desc & "'," & _
                         "'" & item.PrimaryVendorID & "'," & _
                         "'" & item.VendorItemID & "'," & _
                         CDec(item.OnhandQty) & "," & _
                         CDec(item.ReorderQty) & "," & _
                         CDec(item.ReorderPt) & "," & _
                         item.RetailPrice & "," & _
                         CDec(item.WhslPrice) & "," & _
                         "'" & item.UOI & "'," & _
                         CDec(item.UnitsPerIssue) & "," & _
                         "'" & item.LastPurchaseDate & "'," & _
                         CDec(item.MTDSold) & "," & _
                         item.YTDSold & "," & _
                         "'" & item.PictureLocation & "'," & _
                         "'" & item.ApplyDiscount & "'," & _
                         CInt(item.DiscountPercent) & ")"

            Dim ins As New SqlCommand(strSQL, cnn)

            item.adpitems.InsertCommand = ins
            item.adpitems.InsertCommand.ExecuteNonQuery()

            Insertitem = True

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Function

[chris128]

Take out the Try/Catch and tell us which line the exception actually gets thrown on first of all

Also, use parameters instead of all those strings - it will make your code a lot more readable and means you dont have to worry about adding inverted commas all over the place but it will possibly also get rid of this error you are getting. There are plenty of examples on this forum of how to use parameters with an SQL statement so do a quick search

[blakemckenna]

Ok,

I did what you suggested Shaggy and now I'm getting this error (see image).
Here is the revised code.

    Public Function InsertItem() As Boolean
        Try
            Insertitem = False

            strSQL = "INSERT INTO tblItemMaster " & _
                        "(itmID," & _
                         "itmName," & _
                         "itmCatg," & _
                         "itmType," & _
                         "itmDescription," & _
                         "itmPrimaryVendorID," & _
                         "itmVendorItemID," & _
                         "itmQtyOnhand," & _
                         "itmReorderQty," & _
                         "itmReorderPoint," & _
                         "itmRetailPrice," & _
                         "itmWhslPrice," & _
                         "itmUOI," & _
                         "itmUnitsPerIssue," & _
                         "itmLastPurchaseDate," & _
                         "itmMTDSold," & _
                         "itmYTDSold," & _
                         "itmPictureLocation," & _
                         "itmApplyDiscount," & _
                         "itmDiscountPercent) " & _
                      "VALUES " & _
                        "(@itemID," & _
                         "@name," & _
                         "@catg," & _
                         "@type," & _
                         "@desc," & _
                         "@primaryVendorID," & _
                         "@vendorItemID," & _
                         "@onhandQty," & _
                         "@reorderQty," & _
                         "@reorderPt," & _
                         "@retailPrice," & _
                         "@whslPrice," & _
                         "@uoi," & _
                         "@unitsPerIssue," & _
                         "@lastPurchaseDate," & _
                         "@mtdSold," & _
                         "@ytdSold," & _
                         "@pictureLocation," & _
                         "@imgFileName," & _
                         "@applyDiscount," & _
                         "@discountPercent)"

            Dim ins As New SqlCommand(strSQL, cnn)

            ins.Parameters.AddWithValue("@itemID", item.ItemID)
            ins.Parameters.AddWithValue("@name", item.Name)
            ins.Parameters.AddWithValue("@catg", item.Catg)
            ins.Parameters.AddWithValue("@type", item.ItemType)
            ins.Parameters.AddWithValue("@desc", item.Desc)
            ins.Parameters.AddWithValue("@primaryVendorID", item.PrimaryVendorID)
            ins.Parameters.AddWithValue("@vendorItemID", item.VendorItemID)
            ins.Parameters.AddWithValue("@onhandQty", item.OnhandQty)
            ins.Parameters.AddWithValue("@reorderQty", item.ReorderQty)
            ins.Parameters.AddWithValue("@reorderPt", item.ReorderPt)
            ins.Parameters.AddWithValue("@retailPrice", item.RetailPrice)
            ins.Parameters.AddWithValue("@whslPrice", item.WhslPrice)
            ins.Parameters.AddWithValue("@uoi", item.UOI)
            ins.Parameters.AddWithValue("@unitsPerIssue", item.UnitsPerIssue)
            ins.Parameters.AddWithValue("@lastPurchaseDate", item.LastPurchaseDate)
            ins.Parameters.AddWithValue("@mtdSold", item.MTDSold)
            ins.Parameters.AddWithValue("@ytdSold", item.YTDSold)
            ins.Parameters.AddWithValue("@pictureLocation", item.PictureLocation)
            ins.Parameters.AddWithValue("@imgFileName", item.PictureLocation)
            ins.Parameters.AddWithValue("@applyDiscount", item.ApplyDiscount)
            ins.Parameters.AddWithValue("@discountPercent", item.DiscountPercent)

            item.adpitems.InsertCommand = ins
            item.adpitems.InsertCommand.ExecuteNonQuery()

            Insertitem = True

        Catch ex As Exception
            dErr.ModuleInError = "clsItems"
            dErr.ErrorMsg = ex.Message
            dErr.SubProcedure = "InsertItem()"
            dErr.DisplayErrMsg(dErr.ErrorMsg, dErr.ModuleInError, dErr.SubProcedure)
        End Try
    End Function

Thanks,

[chris128]

I dont know how I can explain that any clearer than what the error message tells you... you've used the parameter "@type" in your SQL query but you havent actually added that parameter. In other words you need to add a line like this:

ins.Parameters.AddWithValue("@Type", SomeValueHere)

Also, I'd recommend adding the parameters like this instead of using AddWithValue :

ins.Parameters.Add("@Type", SqlDataTypeHere).Value = SomeValueHere

When you get to the second parameter of the Add method (where I have put SqlDataTypeHere) you should be able to tell what you need to choose from the enum just by looking at the list that intellisense will show you at that point

[Shaggy Hiker]

Actually, that wasn't my suggestion.

[MaximilianMayrhofer]

Actually, I would suggest that you go back to your original code for a second, and then do Debug.Print of your SQL expression after it has been assembled. That way you'll see the actual values being sent to the DB. And post it here.

[blakemckenna]

Well,

I actually changed the types in my DB Table from Decimal to Float and that worked, although I'm not real sure what the difference between those two types are. I think that Float takes more space but not sure.

Thanks,

[chris128]

google it