• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

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

Dear Experts-Exchange,

I have a sql table called ‘UserLogin”:

UserID    Password   EmplID  Admin

Pbrown     123ss          5            0
Jmark      edfer             12          1
Ldinah     wert              22          1
Rsims      hgfd               9            0
Schai      wqwe             33           1

I am trying to update the information of userlogin:

In the asp.net web form has :

lblEmplNumID  -  TxtNumID.Text
lblUserID -   TxtUserID.Text
lblPassword   - TxtPwd.Text
lblAdmin:  ChkBoxUpdAdmin  (yes if checked)
Button Update -  BtnUpdateUserLogin

When I click checked box or unchecked box, after clicked the button update doesn’t update any information into SQL table and displays an error:  Conversion failed when converting the varchar value 'False' to data type int.

I can’t figure out where is the problem??  

Please see the code below (VB.NET):

Protected Sub BtnUpdateUserLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpdateUserLogin.Click

        Dim cmd As SqlClient.SqlCommand
        Dim strSQL As String
        Dim strCnn As String
        Dim cnn As SqlConnection

        If Me. ChkBoxUpdAdmin.Checked = 1 Then
       
            strSQL = "UPDATE UserLogin SET UserID = '" & TxtUserID.Text & "', " & _
                    "Password = '" & TxtPwd.Text & "', " & _
                    "Admin = '" & ChkBoxUpdAdmin.Checked & "' " & _
                    "FROM UserLogin WHERE EmplID = '" & txtNumID.Text & "'"

            ElseIf Me.ChkBoxUpdAdmin.Checked = 0 Then

      strSQL = "UPDATE UserLogin SET UserID = '" & TxtUserID.Text & "', " & _
                    "Password = '" & TxtPwd.Text & "', " & _
                    "Admin = '" & ChkBoxUpdAdmin.Checked & "' " & _
                    "FROM UserLogin WHERE EmplID = '" & txtNumID.Text & "'"

            strCnn = System.Configuration.ConfigurationManager.AppSettings("SQLConnectionString")
            cnn = New SqlConnection(strCnn)

            cmd = New SqlCommand(strSQL, cnn)
            cnn.Open()
            cmd.ExecuteNonQuery()
            'Conversion failed when converting the varchar value 'False' to data type int.
        End If

    End Sub

Your attention and help is much appreciated.  Thanks.
0
slb2008
Asked:
slb2008
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

                    "Admin = " & ChkBoxUpdAdmin.Checked ? "1" : "0" & " " & _
0
 
tedh7552Commented:
convert (bit,'false') does work
convert (int,Convert (bit,'false')) may be the score in your case
0
 
hesCommented:
Try using this:
Dim MyValue as Integer
 If Me. ChkBoxUpdAdmin.Checked = "True"Then
    MyValue = 1
Else
    MyValue = 0
End If
 strSQL = "UPDATE UserLogin SET UserID = '" & TxtUserID.Text & "', " & _
                    "Password = '" & TxtPwd.Text & "', " & _
                    "Admin = '" & MyValue & "' " & _
                    "FROM UserLogin WHERE EmplID = '" & txtNumID.Text & "'"

            strCnn = System.Configuration.ConfigurationManager.AppSettings("SQLConnectionString")
            cnn = New SqlConnection(strCnn)

            cmd = New SqlCommand(strSQL, cnn)
            cnn.Open()
            cmd.ExecuteNonQuery()
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Todd GerbertIT ConsultantCommented:
Were I you, I think that
1) I'd change the "Admin" field in your database from an int to a bit field (a bit field in SQL is boolean).
2) I'd use parameters with my SQL command

Sub BtnUpdateUserLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpdateUserLogin.Click
		Dim strSQL As String
		Dim strCnn As String

		If Me.ChkBoxUpdAdmin.Checked = 1 Then

			...

		ElseIf Me.ChkBoxUpdAdmin.Checked = 0 Then

			strSQL = "UPDATE UserLogin SET UserID=@UserID,Password=@Password,Admin=@Admin FROM UserLogin WHERE EmplID=@EmplID"

			Using cnn As SqlConnection = New (System.Configuration.ConfigurationManager.AppSettings("SQLConnectionString"))
				cnn.Open()
				Using cmd As SqlCommand = cnn.CreateCommand()
					cmd.CommandText = strSQL

					cmd.Parameters.AddWithValue("@UserID", TxtUserID.Text).DbType = DbType.String
					cmd.Parameters.AddWithValue("@Password", TxtPwd.Text).DbType = DbType.String
					cmd.Parameters.AddWithValue("@Admin", ChkBoxUpdAdmin.Checked).DbType = DbType.Boolean
					cmd.Parameters.AddWithValue("@EmplID", Int32.Parse(txtNumID.Text)).DbType = DbType.Int32

					cmd.ExecuteNonQuery()
				End Using
				cnn.Close()
			End Using
		End If

	End Sub

Open in new window

0
 
slb2008Author Commented:
thanks everyone for your quick response.
Sorry my tardiness.  I am going to test some of your SQL query syntaxes right and I'll let you know.
0
 
slb2008Author Commented:
Hes,
I tried your code and worked well.  I can't believe how I missed the Dim myValue as Integer....
before I put in the line of code like this:  
Dim admin as Boolean....
If Me. ChkBoxUpdAdmin.Checked = "True"Then
    Admin = True
else
Admin = False....   but wasn't not working , maybe I miss to put the variable value '" & Admin & "' in SQL syntax after Administrator =  '" & Admin & "'.
Hes, you're going to get 500 points.  Thanks to other experts like angelII, tedh7552, tgerbert (I'll use for future reference) for sharing with me with the best answer/solution.
Thanks.
 
 

 
0
 
slb2008Author Commented:
.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now