asked on

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.

Guy Hengel [angelIII / a3]

"Admin = " & ChkBoxUpdAdmin.Checked ? "1" : "0" & " " & _

tedh7552

convert (bit,'false') does work
convert (int,Convert (bit,'false')) may be the score in your case

ASKER CERTIFIED SOLUTION

hes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Todd Gerbert

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

slb2008

ASKER

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.

slb2008

ASKER

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.

slb2008

ASKER