Question Cant get DECIMAL and INTEGER values to be added to my database via form textboxes

stylus · Feb 7, 2013

Hi all,

Ive run into a problem with my ASP.NET eCommerce Site and I cant seem to figure this out and so any help would be extremely helpful.

Basically I have 2 ASP.NET textbox fields. 'Price' and 'Quantity'
'Price' is a DECIMAL field/Datatype in SQL and 'Quantity is a INTEGER field/Datatype. (MS SQL Server 2005)

Each time I try to add values from the textboxes into the database I again get the 'String cannot be converted' message. I have tried everything I have found on the web, including Convert.To, CInt, Parse and ParseExact but nothing works. I always get the same error.
However, its strange as although Visual Studio errors, usually the data is added to the database.

If I remove the DECIMAL and INTEGER values, the record is added successfully. I just seemingly need to know how to correctly add these values via user inputted textboxes.

Strangely, on top of this, when my code runs successfully it seems to be running twice. I get the record and a blank record. Does anyone know why this may be and how I can stop that?

VB.NET:

[COLOR=#2B91AF]Protected[/COLOR][COLOR=#2B91AF]Sub[/COLOR][COLOR=#000000] lbtnSubmit_Click[/COLOR][COLOR=#000000]([/COLOR][COLOR=#2B91AF]ByVal[/COLOR][COLOR=#000000] sender [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]Object[/COLOR][COLOR=#000000],[/COLOR][COLOR=#2B91AF]ByVal[/COLOR][COLOR=#000000] e [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]System[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]EventArgs[/COLOR][COLOR=#000000])[/COLOR][COLOR=#2B91AF]Handles[/COLOR][COLOR=#000000] lbtnSubmit[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Click[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#2B91AF]Using[/COLOR][COLOR=#000000] conn [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]New[/COLOR][COLOR=#2B91AF]SqlConnection[/COLOR][COLOR=#000000]([/COLOR][COLOR=#2B91AF]ConfigurationManager[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]ConnectionStrings[/COLOR][COLOR=#000000]([/COLOR][COLOR=#A31515]"StylusInnovationsDBConnection"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#2B91AF]ToString[/COLOR][COLOR=#000000]())[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#A31515]'Create a command object
            Dim cmd As New SqlCommand()

            '[/COLOR][COLOR=#2B91AF]Initialise[/COLOR][COLOR=#000000] connections
            [/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#000000] myAdaptor [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]New[/COLOR][COLOR=#2B91AF]SqlDataAdapter[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#000000] myCommand [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]New[/COLOR][COLOR=#2B91AF]SqlCommand[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#000000] myData [/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]SqlDataReader[/COLOR][COLOR=#000000]

            [/COLOR][COLOR=#A31515]'Populate Todays date for testing purposes
            lblCurrentDate.Text = Format(Date.Today, "dd/MM/yyyy")

            '[/COLOR][COLOR=#2B91AF]Build[/COLOR][COLOR=#000000] SQL query to check [/COLOR][COLOR=#0000FF]if[/COLOR][COLOR=#2b91af] ProductID[/COLOR][COLOR=#000000] already exists
            [/COLOR][COLOR=#2B91AF]Dim[/COLOR][COLOR=#2B91AF]CheckProductID[/COLOR][COLOR=#2B91AF]As[/COLOR][COLOR=#2B91AF]String[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]CheckProductID[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]"SELECT ProductID FROM PRODUCT WHERE ProductID = '"[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#000000] txtProductID[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]+[/COLOR][COLOR=#A31515]"'"[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#A31515]'Check that connection to the database is established
            Try
                conn.Open()
            Catch myError As SqlException '[/COLOR][COLOR=#2B91AF]If[/COLOR][COLOR=#000000] it fails [/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]i[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]e[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]No[/COLOR][COLOR=#2B91AF]Internet[/COLOR][COLOR=#2B91AF]Connection[/COLOR][COLOR=#000000] etc[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#2B91AF]MsgBox[/COLOR][COLOR=#000000]([/COLOR][COLOR=#A31515]"Error connecting to the database. Check your internet connection"[/COLOR][COLOR=#000000],[/COLOR][COLOR=#2B91AF]MsgBoxStyle[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Critical[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]End[/COLOR][COLOR=#2B91AF]Try[/COLOR][COLOR=#000000]

            [/COLOR][COLOR=#A31515]'Establish variables
            myCommand.Connection = conn
            myCommand.CommandText = CheckProductID
            myAdaptor.SelectCommand = myCommand
            myData = myCommand.ExecuteReader

            '[/COLOR][COLOR=#2B91AF]If ProductID [/COLOR][COLOR=#0000FF]is[/COLOR][COLOR=#000000] unique [/COLOR][COLOR=#0000FF]and[/COLOR][COLOR=#000000] doesnt exist [/COLOR][COLOR=#0000FF]in[/COLOR][COLOR=#000000] the database create [/COLOR][COLOR=#2B91AF]Product [/COLOR][COLOR=#0000FF]in [/COLOR][COLOR=#2B91AF]Product[/COLOR][COLOR=#2B91AF]Table[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]If[/COLOR][COLOR=#000000] myData[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]HasRows[/COLOR][COLOR=#000000]=[/COLOR][COLOR=red]0[/COLOR][COLOR=#2B91AF]Then[/COLOR][COLOR=#000000]
                conn[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Close[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#000000]

                [/COLOR][COLOR=#A31515]'Assign the connection to the command
                cmd.Connection = conn

                '[/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] the command type
                [/COLOR][COLOR=#A31515]'SQL Statements or the name of the stored procedure
                cmd.CommandText = "INSERT INTO Product ( ProductID, ProductArtist, ProductTitle, ProductYear, ProductFormat, ProductGenre, ProductMixes, ProductImage, ProductStocked, ProductPrice ) VALUES ( @ProductID, @ProductArtist, @ProductTitle, @ProductYear, @ProductFormat, @ProductGenre, @ProductMixes, @ProductImage, @ProductStocked, @ProductPrice )"

                '[/COLOR][COLOR=#2B91AF]Set[/COLOR][COLOR=#000000] the command type
                [/COLOR][COLOR=#A31515]'CommandType.Text for normal SQL Statements
                '[/COLOR][COLOR=#2B91AF]CommandType[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]StoredProcedure[/COLOR][COLOR=#0000FF]for[/COLOR][COLOR=#000000] stored procedures
                cmd[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]CommandType[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#2B91AF]CommandType[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]

                [/COLOR][COLOR=#A31515]'Append the parameters
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar, 50).Value = txtProductID.Text.ToString
                cmd.Parameters.Add("@ProductArtist", SqlDbType.NVarChar, 50).Value = txtProductArtist.Text
                cmd.Parameters.Add("@ProductTitle", SqlDbType.NVarChar, 50).Value = txtProductTitle.Text
                cmd.Parameters.Add("@ProductYear", SqlDbType.NVarChar, 4).Value = txtProductYear.Text
                cmd.Parameters.Add("@ProductFormat", SqlDbType.NVarChar, 50).Value = txtProductFormat.Text
                cmd.Parameters.Add("@ProductGenre", SqlDbType.NVarChar, 50).Value = txtProductGenre.Text
                cmd.Parameters.Add("@ProductMixes", SqlDbType.NVarChar, 500).Value = txtProductMixes.Text
                cmd.Parameters.Add("@ProductImage", SqlDbType.NVarChar, 50).Value = txtProductImage.Text
                cmd.Parameters.Add("@ProductStocked", SqlDbType.DateTime).Value = Date.Today
                cmd.Parameters.Add("@ProductPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(txtProductPrice.Text)


                '[/COLOR][COLOR=#2B91AF]Open[/COLOR][COLOR=#000000] the connection
                conn[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Open[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#000000]

                [/COLOR][COLOR=#A31515]'Execute the command
                cmd.ExecuteNonQuery()
                conn.Close()
                Response.Write("Record Saved Successfully")
            Else
                '[/COLOR][COLOR=#2B91AF]If ProductID[/COLOR][COLOR=#000000] does exist[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] show error message
                [/COLOR][COLOR=#2B91AF]MsgBox[/COLOR][COLOR=#000000]([/COLOR][COLOR=#A31515]"ProductID exists. Please choose another"[/COLOR][COLOR=#000000],[/COLOR][COLOR=#2B91AF]MsgBoxStyle[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Critical[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
                conn[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Close[/COLOR][COLOR=#000000]()[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#2B91AF]End[/COLOR][COLOR=#2B91AF]If[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#2B91AF]End[/COLOR][COLOR=#2B91AF]Using[/COLOR][COLOR=#000000]

        [/COLOR][COLOR=#A31515]'Rebind the GridView control to show inserted data
        BindGridView()

        '[/COLOR][COLOR=#2B91AF]Empty[/COLOR][COLOR=#000000] the [/COLOR][COLOR=#2B91AF]TextBox[/COLOR][COLOR=#000000] controls
        txtProductID[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductArtist[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductTitle[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductFormat[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductGenre[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductImage[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductMixes[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductPrice[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductQty[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductStocked[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]
        txtProductYear[/COLOR][COLOR=#000000].[/COLOR][COLOR=#2B91AF]Text[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#A31515]" "[/COLOR][COLOR=#000000]

        [/COLOR][COLOR=#A31515]'Show the Add Button hiding the Add Panel
        btnAddRecord.Visible = True
        pnlAdd.Visible = False
End Sub
[/COLOR]

jmcilhinney · Feb 7, 2013

Well, what's the actual value of the String you're trying to convert when it fails? If you're being told that the string cannot be converted then the first thing to do is to look at the string to see why it cannot be converted.

Question Cant get DECIMAL and INTEGER values to be added to my database via form textboxes

stylus

New member

jmcilhinney

VB.NET Forum Moderator

Similar threads

Share this page

Latest posts