0

I'm having an issue trying to update a field in my database that is a INT type. I'm using this field for a checkbox on my form. The checkbox works correctly by using the field in the database containing a 0 for unchecked and a 1 for checked. The problem is when I try to update my form the checkbox field errors out. The error says:

Conversion failed when converting the varchar value 'on' to data type int.


Below is my code which works fine asides from this problem:

Sub Page_Load()
strSQL = "SELECT * FROM tblCustomers WHERE Customer_ID=" & Request.QueryString("Customer_ID")
    
        DataBase_Connect(strSQL)
        objDataReader.Read()

        'This will be the value to be populated into the textboxes
        Customer_ID.Text = objDataReader("Customer_ID").ToString()
        Customer_Status.Checked = objDataReader("Customer_Status").ToString()
        Customer_Code.Text = objDataReader("Customer_Code").ToString()
        Customer_Name.Text = RTRIM(objDataReader("Customer_Name").ToString())
        Customer_Address1.Text = RTRIM(objDataReader("Customer_Address1").ToString())
        Customer_Address2.Text = RTRIM(objDataReader("Customer_Address2").ToString())
        Customer_City.Text = RTRIM(objDataReader("Customer_City").ToString())
        Customer_State.Text = RTRIM(objDataReader("Customer_State").ToString())
        Customer_Zip.Text = RTRIM(objDataReader("Customer_Zip").ToString())
        Customer_Phone1.Text = RTRIM(objDataReader("Customer_Phone1").ToString())
        Customer_Phone2.Text = RTRIM(objDataReader("Customer_Phone2").ToString())
        Customer_Fax.Text = RTRIM(objDataReader("Customer_Fax").ToString())
        Customer_Email.Text = RTRIM(objDataReader("Customer_Email").ToString())
        Customer_Tax.Text = RTRIM(objDataReader("Customer_Tax").ToString())
        Customer_SalesmanID.Text = objDataReader("Customer_SalesmanID").ToString()
        Customer_Galvanized.Text = objDataReader("Customer_Galvanized").ToString()
        Customer_DoubleWall.Text = objDataReader("Customer_DoubleWall").ToString()
        Customer_Comments.Text = RTRIM(objDataReader("Customer_Comments").ToString())
        Customer_CreatedDate.Text = objDataReader("Customer_CreatedDate").ToString()
        Customer_CreatedBy.Text = objDataReader("Customer_CreatedBy").ToString()
        Customer_ModifiedDate.Text = objDataReader("Customer_ModifiedDate").ToString()
        Customer_ModifiedBy.Text = objDataReader("Customer_ModifiedBy").ToString()
        
       Phone.Text = objDataReader("Phone").ToString()

        link.NavigateUrl = "CustomerAddress_Add.aspx?Customer_ID=" & Customer_ID.Text
    
        DataBase_Disconnect()
End Sub
Sub Change_Customer(ByVal sender As Object, ByVal e As System.EventArgs)

        objConnection = New OleDbConnection(strConnection)
        objConnection.Open()

        strSQL = "update tblCustomers set Customer_Name='" & Replace(Request("TabContainer1$TabPanel1$Customer_Name"), "'", "''")
        strSQL += "', Customer_Status='" & Request("TabContainer1$TabPanel1$Customer_Status")
        strSQL += "', Customer_Address1='" & Replace(Request("TabContainer1$TabPanel1$Customer_Address1"), "'", "''")
        strSQL += "', Customer_Address2='" & Replace(Request("TabContainer1$TabPanel1$Customer_Address2"), "'", "''")
        strSQL += "', Customer_City='" & Replace(Request("TabContainer1$TabPanel1$Customer_City"), "'", "''")
        strSQL += "', Customer_State='" & Replace(Request("TabContainer1$TabPanel1$Customer_State"), "'", "''")
        strSQL += "', Customer_Zip='" & Replace(Request("TabContainer1$TabPanel1$Customer_Zip"), "'", "''")
        strSQL += "', Customer_Phone1='" & Replace(Request("TabContainer1$TabPanel1$Customer_Phone1"), "'", "''")
        strSQL += "', Customer_Phone2='" & Replace(Request("TabContainer1$TabPanel1$Customer_Phone2"), "'", "''")
        strSQL += "', Customer_Fax='" & Replace(Request("TabContainer1$TabPanel1$Customer_Fax"), "'", "''")
        strSQL += "', Customer_Email='" & Replace(Request("TabContainer1$TabPanel1$Customer_Email"), "'", "''")
        strSQL += "', Customer_Tax='" & Replace(Request("TabContainer1$TabPanel1$Customer_Tax"), "'", "''")
        strSQL += "', Customer_SalesmanID='" & Replace(Request("TabContainer1$TabPanel1$Customer_SalesmanID"), "'", "''")
        strSQL += "', Customer_Galvanized='" & Replace(Request("TabContainer1$TabPanel1$Customer_Galvanized"), "'", "''")
        strSQL += "', Customer_DoubleWall='" & Replace(Request("TabContainer1$TabPanel1$Customer_DoubleWall"), "'", "''")
        strSQL += "', Customer_Comments='" & Replace(Request("TabContainer1$TabPanel1$Customer_Comments"), "'", "''")
        strSQL += "', Customer_ModifiedDate='" & Now()
        strSQL += "', Customer_ModifiedBy='" & Session("userid")
        strSQL += "' where Customer_ID = " & Request("Customer_ID")


        objCommand = New OleDbCommand(strSQL, objConnection)
        objCommand.ExecuteNonQuery()
    
        objCommand = Nothing
        objConnection.Close()
        objConnection = Nothing
End Sub

The code below is a sample of my form code:

<td width="125"><b class="sortcat">&nbsp;Inactive:</b></td><td class="cred2"><asp:CheckBox ID="Customer_Status" runat="server" Checked='<%#Bind("Customer_Status")%>' /></td></tr>
<tr><td width="125"><b class="sortcat">&nbsp;Customer Code:</b></td><td class="cred2"><asp:Label ID="Customer_Code" cssClass="content2" runat="server" /></td></tr>
<tr><td><b class="sortcat">&nbsp;Company Name:</b></td><td><asp:TextBox runat="server" id="Customer_Name" class="textbox" size="60" 
              CssClass="input_text" MaxLength="65" />

Any help would be appreciated. I would like to use the code I have just with correcting the checkbox field updating problem.

Thanks

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by zid8ne
0

strSQL += "', Customer_Status='" & Request("TabContainer1$TabPanel1$Customer_Status")

Customer_Status = 'value'

Conversion failed when converting the varchar value 'on' to data type int.

do not include a single quote when you try to update int data type

UPDATE table SET Customer_Status = 23

Edited by zid8ne: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.