Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Limit characters in data type decimal so it can be saved in database field float

Posted on 2008-10-19
10
Medium Priority
?
452 Views
Last Modified: 2012-05-05
I am developing a web site in ASP.NET with VB.NET on MS Visual Web Developer.  I have a page that queries the values for city and state tax, add those together, and multiply it against the product total.  The calulations are correct, but when it goes to save the tax value, at first I got the error that "Conversion from string "Tax: $0.93590" to type 'Decimal' is not valid." I corrected that so the variable is just the number, declared as decimal.  Now I get "There are more columns in the INSERT statement than values specified in the VALUES clause."  Having resolved this earlier, the latter error is actually caused because one of the data types doesn't match.  Since the only thing I did was change the variable from decimal to string, that's proably where my problem lies.  How do I need to declare the variable in order to limit it to just two decimal places so I may insert it into the database record?
Sub CalcTax(ByVal s As Object, ByVal e As EventArgs)
        Dim ProductTotal As Decimal = Session("ProductTotal")
        Dim ShipState As String = ddlShipState.SelectedValue
        Dim ShipZIP As String = txtShipZIP.Text
        Dim strConn As String = (ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
        Dim GetTax As String = "Select * FROM taxtable WHERE (state = '" & ShipState & "' AND  zipcode = '" & ShipZIP & "')"
        Dim MyConn3 As New SqlConnection(strConn)
        Dim Cmd8 As New SqlCommand(GetTax, MyConn3)
        Dim objDR3 As SqlDataReader
        Dim TaxRate As Decimal = "0.00"
        Dim TaxTotal As Decimal = "0.00"
        Dim ShippingCost As Decimal = Session("ShippingCost")
        MyConn3.Open()
        objDR3 = Cmd8.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        If objDR3.Read() Then 'if a record exists
            Dim CityTax As Decimal = objDR3("taxrate")
            Dim StateTax As Decimal = "0.00"
            If ShipState = "NE" Then
                StateTax = "0.055"
            End If
            TaxRate = (CityTax + StateTax)
            TaxTotal = (ProductTotal * TaxRate)
            Session("myTaxTotal") = TaxTotal
            lblTax.Text = "Tax: $" & TaxTotal
        Else
            lblTax.Text = " Tax: $0.00"
        End If
        
        lblGrandTotal.Text = "Grand Total: $" & (ProductTotal + ShippingCost + TaxTotal)
        MyConn3.Close()
    End Sub
 
Dim strComments As String = txtComments.Text
        Dim strTax As Decimal = Session("myTaxTotal")
        Dim strShipMethod As String = ddlShippingMethod.Text
        Dim strShipAmount As String = lblShipping.Text
        Dim strShipMethodID As String = ddlShippingMethod.SelectedValue
        Dim strGrandTotal As String = lblGrandTotal.Text
        Dim cartNr As String = CStr(ShoppingCartNr)
        
        Dim myCmd8 As New SqlCommand
        myCmd8.Connection = myConn2
        myCmd8.CommandType = CommandType.Text
        myCmd8.CommandText = "Insert into orders (id, customerid, last_updated, first_name, last_name, company_name, billing_address1, billing_address2, " & _
        "billing_city, billing_state, billing_zip, billing_plusfour, email, phone1, phone2, receive_pet_newsletter, receive_equine_newsletter, " & _
        "receive_psd_catalog, receive_fpe_catalog, shipping_first_name, shipping_last_name, shipping_company_name, shipping_address1, shipping_address2, " & _
        "shipping_city, shipping_state, shipping_zip, shipping_plusfour, shipping_phone, cc1, cc1_exp, name_on_card, comments, state_tax, " & _
        "shipping_method, shipping_amount, shipping_method_id, printed, grand_total) Values ('" & cartNr & " ','" & custID & "', " & _
        "'" & myTime & "','" & strFname & "','" & strLname & "','" & strCompany & "','" & strAddress1 & "','" & strAddress2 & "', " & _
        "'" & strCity & "','" & strState & "','" & strZIP & " ','" & strPlusFour & "','" & strEmail & "','" & strPhone1 & "','" & strPhone2 & "','" & strShipFirst & "','" & strShipLast & "','" & strShipCompany & "','" & strShipAddress1 & "','" & strShipAddress2 & "', " & _
        "'" & strShipCity & " ','" & strShipState & " ','" & strShipZIP & " ','" & strShipPlusFour & "','" & strShipPhone & "','" & strCC & "','" & strCCExp & "','" & strNameOnCard & "','" & strComments & "','" & strTax & "', " & _
        "'" & strShipMethod & "','" & strShipAmount & "','" & strShipMethodID & "','N','" & strGrandTotal & "')"
        myConn2.Open()
        myCmd8.ExecuteNonQuery()
        myConn2.Close()
        
        Session("Tax") = strTax
        Session("Shipping") = strShipAmount
        Session("GrandTotal") = strGrandTotal

Open in new window

0
Comment
Question by:OVC-it-guy
  • 6
  • 4
10 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22754490
The missing values correspond to these columns:
receive_pet_newsletter, receive_equine_newsletter, receive_psd_catalog, receive_fpe_catalog

OR at least I don't see their values being set.

FYI, for Decimal values I would just do this to be more OPTION EXPLICIT:
Dim StateTax As Decimal = 0.00
0
 

Author Comment

by:OVC-it-guy
ID: 22754504
Yeah, before you even responded, I saw that.  Corrected those so number of columns matches number of values.  Now I get the error, "Error converting data type varchar to float."  While waiting for a reply, I'll go do that in my declaration.
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22754561
You have all the VALUES surrounded by single quotes (').  Double check what the column data type of each of the columns are.  And make sure there are no extra spaces, characters showing up like $ in numbers, etc.  Do a print of myCmd8.CommandText before using it and see if the generated SQL makes sense.  You can even try to run that SQL directly as a query on SQL Server to test it's validity.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:OVC-it-guy
ID: 22754610
Yes, did that.  Found two other variables that are also data type float in the database table.  Once I applied the same changes to those two other variables, VOILA!  Thanks, mwvisa1.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22754623
You are welcome.
0
 

Author Comment

by:OVC-it-guy
ID: 22754624
Spoke a little tto soon.  Yes, that was the solution, but I have one other little follow-on.  The state tax for the item I "purchased" was recorded in the SQL database field as 0.8365.  Do you know a little routine (TRIM or similar) to limit it to two places after the decimal?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22754642
You can use a round function in either SQL or .NET and specify 2 decimal places.

If you have difficulties with that, you can post a question; however, round should be pretty straight forward solution for you.

Regards,
kevin
0
 

Author Comment

by:OVC-it-guy
ID: 22754772
Git it.  Thanks again.
0
 

Author Comment

by:OVC-it-guy
ID: 22754796
Hey, Kevin, can you lep me on my other thread/issues, titled: Inserting rows from DataTable into SQL Server database table?
0
 

Author Comment

by:OVC-it-guy
ID: 22754852
Nevermind.  I solved it.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month12 days, 8 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question