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.
slb2008Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.