Cant find the Error Conversion

[Gen_2]

i can't find the error converting data type varchar to numeric pls help..
i already wasted a lot of hours to identify this error.

 conn = New SqlConnection("Server=GEN-PC;Data Source=GEN-PC\SQLEXPRESS;Initial Catalog=Brgy;Integrated Security=True;")
        If ResidentialComboBox.Text = "" Or LNameTextBox.Text = "" Or FNameTextBox.Text = "" Or MNameTextBox.Text = "" Or GenderComboBox.Text = "" Or CivilStatusComboBox.Text = "" Or ReligionTextBox.Text = "" Or EducationTextBox.Text = "" Or ProfessionTextBox.Text = "" Or SalaryTextBox.Text = "" Then
            MsgBox("Please fill out all the fields.", MsgBoxStyle.OkOnly)
            Try
                conn.Open()
                Dim ra As New Integer
                Dim rb As String
                Dim rd As Date
                rd = Date.Now
                rb = BODDateTimePicker.Text
                ra = DateDiff("yyyy", rd, rb)
                autonum()
                cmd = conn.CreateCommand
                cmd.CommandText = "INSERT INTO Resident(ResidentNo, Residential, Street, Telephone, LName, FName, MName, Gender, Birthday, Age, CivilStatus, Religion, Education, Profession, Salary) VALUES('" & Trim(resident) & "','" & Trim(ResidentialComboBox.Text) & "','" & Trim(StreetTextBox.Text) & "','" & Trim(TelephoneTextBox.Text) & "','" & Trim(LNameTextBox.Text) & "','" & Trim(FNameTextBox.Text) & "','" & Trim(MNameTextBox.Text) & "','" & Trim(GenderComboBox.Text) & "','" & Trim(BODDateTimePicker.Text) & "','" & Trim(ra) & "','" & Trim(CivilStatusComboBox.Text) & "','" & Trim(ReligionTextBox.Text) & "','" & Trim(EducationTextBox.Text) & "','" & Trim(ProfessionTextBox.Text) & "','" & Trim(SalaryTextBox.Text) & "')"
                cmd.ExecuteNonQuery()
                If CheckBox2.Checked = True Then
                    'nothing
                ElseIf CheckBox2.Checked = False Then
                    aspouse()
                End If
                If CheckBox3.Checked = True Then
                    'nothing
                ElseIf CheckBox3.Checked = False Then
                    achild()
                End If
                If CheckBox4.Checked = True Then
                    'nothing
                ElseIf CheckBox4.Checked = False Then
                    asibling()
                End If
                clear()
                conn.Close()
            Catch ex As Exception
                MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
            End Try
        End If

[Philippe.Lahaie]

what is the error message you get and on which line does it happen?

also :

If CheckBox2.Checked = True Then
    'nothing
ElseIf CheckBox2.Checked = False Then
    aspouse()
End If

'is the same as :

If Not CheckBox2.Cheked Then
    aspouse()
End If

[Gen_2]

it says on line 1
system.data: error converting data type varchar to numeric

the functions called in if statements are also the same with above codes.
its an adding function

[Philippe.Lahaie]

i dont think the line #1 in the code you posted is the same as the line #1 in your code, can you post the line that the error appears on?

[Gen_2]

its not actually in the line of code

[Reverend Jim]

You should put single quotes around string (character) values and not around numeric values. You didn't include your field definitions for the database table so we can't tell which values are which. For example,

INSERT INTO myTable (idnum) VALUES('123')

where idnum is defined as INT may cause a problem because the actual value you are passing is a string, not a number. It should be

INSERT INTO myTable (idnum) VALUES(123)

In any case, you should be using parameterized queries which would not only handle that for you but also prevent against SQL injection attacks. Please see the example here.

[Gen_2]

still not working o.O i tried everything

conn = New SqlConnection("Server=GEN-PC;Data Source=GEN-PC\SQLEXPRESS;Initial Catalog=Brgy;Integrated Security=True;")
        If ResidentialComboBox.Text = "" Or LNameTextBox.Text = "" Or FNameTextBox.Text = "" Or MNameTextBox.Text = "" Or GenderComboBox.Text = "" Or CivilStatusComboBox.Text = "" Or ReligionTextBox.Text = "" Or EducationTextBox.Text = "" Or ProfessionTextBox.Text = "" Or SalaryTextBox.Text = "" Then
            MsgBox("Please fill out all the fields.", MsgBoxStyle.OkOnly)
            Try
                conn.Open()
                Dim ra As New Integer
                Dim rb As String
                Dim rd As Date
                rd = Date.Now
                rb = BODDateTimePicker.Text
                ra = DateDiff("yyyy", rd, rb)
                autonum()
                cmd = conn.CreateCommand
                cmd.CommandText = "INSERT INTO Resident(ResidentNo, Residential, Street, Telephone, LName, FName, MName, Gender, Birthday, Age, CivilStatus, Religion, Education, Profession, Salary) " _
                & ("VALUES @ResidentNo, @Residential, @Street, @Telephone, @LName, @FName, @MName, @Gender, @Birthday, @Age, @CivilStatus, @Religion, @Education, @Profession, @Salary")
                cmd.Parameters.AddWithValue("@ResidentNo", resident)
                cmd.Parameters.AddWithValue("@Residential", ResidentialComboBox.Text)
                cmd.Parameters.AddWithValue("@Street", StreetTextBox.Text)
                cmd.Parameters.AddWithValue("@Telephone", TelephoneTextBox.Text)
                cmd.Parameters.AddWithValue("@LName", LNameTextBox.Text)
                cmd.Parameters.AddWithValue("@FName", FNameTextBox.Text)
                cmd.Parameters.AddWithValue("@MName", MNameTextBox.Text)
                cmd.Parameters.AddWithValue("@Gender", GenderComboBox.Text)
                cmd.Parameters.AddWithValue("@Birthday", BODDateTimePicker.Text)
                cmd.Parameters.AddWithValue("@Age", ra)
                cmd.Parameters.AddWithValue("@CivilStatus", CivilStatusComboBox.Text)
                cmd.Parameters.AddWithValue("@Religion", ReligionTextBox.Text)
                cmd.Parameters.AddWithValue("@Education", EducationTextBox.Text)
                cmd.Parameters.AddWithValue("@Profession", ProfessionTextBox.Text)
                cmd.Parameters.AddWithValue("@Salary", SalaryTextBox.Text)
                cmd.ExecuteNonQuery()
                If CheckBox2.Checked = True Then
                    'nothing
                ElseIf CheckBox2.Checked = False Then
                    aspouse()
                End If
                If CheckBox3.Checked = True Then
                    'nothing
                ElseIf CheckBox3.Checked = False Then
                    achild()
                End If
                If CheckBox4.Checked = True Then
                    'nothing
                ElseIf CheckBox4.Checked = False Then
                    asibling()
                End If
                clear()
                conn.Close()
            Catch ex As Exception
                MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
            End Try
        End If

[cgeier]

You're still passing "string" data.

    Private Sub insertMyTable(ByRef conn As SqlConnection, ByVal myVal1 As Decimal, ByVal myVal2 As String)

        Dim sqlCmd As SqlCommand

        Dim sqlText As String

        sqlCmd = New SqlCommand()

        sqlText = @"INSERT INTO myTable(col1, col2) VALUES(@myVal1, @myVal2)"

        Dim paramName0 As SqlParameter
        paramName0 = New SqlParameter()

        paramName0.SqlDbType = SqlDbType.Decimal
        'paramName0.SqlDbType = SqlDbType.Float
        'paramName0.SqlDbType = SqlDbType.Int
        'paramName0.SqlDbType = SqlDbType.Money
        paramName0.Direction = ParameterDirection.Input
        paramName0.Value = myVal1
        sqlCmd.Parameters.Add(paramName0)

        Dim paramName1 As SqlParameter
        paramName1 = New SqlParameter()
        paramName1.SqlDbType = SqlDbType.VarChar
        paramName1.Direction = ParameterDirection.Input
        paramName1.Value = myVal2
        sqlCmd.Parameters.Add(paramName1)

        sqlCmd.ExecuteNonQuery()
    End Sub

[cgeier]

I left out some things in my previous post. Try this:

Add Imports System.Data.SqlClient

    Private Sub insertMyTable(ByRef conn As SqlConnection, ByVal myVal1 As Decimal, ByVal myVal2 As String)

        Dim sqlCmd As SqlCommand
        Dim sqlText As String

        If (conn.State <> System.Data.ConnectionState.Open) Then
            conn.Open()
        End If

        sqlCmd = New SqlCommand()

        sqlText = @"INSERT INTO myTable(col1, col2) VALUES(@myVal1, @myVal2)"
        sqlCmd.Connection = conn
        sqlCmd.CommandText = sqlText

        Dim paramName0 As SqlParameter
        paramName0 = New SqlParameter()

        paramName0.SqlDbType = SqlDbType.Decimal
        'paramName0.SqlDbType = SqlDbType.Float
        'paramName0.SqlDbType = SqlDbType.Int
        'paramName0.SqlDbType = SqlDbType.Money
        paramName0.Direction = ParameterDirection.Input
        paramName0.Value = myVal1
        sqlCmd.Parameters.Add(paramName0)

        Dim paramName1 As SqlParameter
        paramName1 = New SqlParameter()
        paramName1.SqlDbType = SqlDbType.VarChar
        paramName1.Direction = ParameterDirection.Input

        If (myVal2 Is Nothing) Then
            paramName1.Value = DBNull.Value
        Else
            paramName1.Value = myVal2
        End If

        sqlCmd.Parameters.Add(paramName1)

        sqlCmd.ExecuteNonQuery()
    End Sub

Usage:

        Dim conn As SqlConnection
        conn = New SqlConnection("Server=GEN-PC;Data Source=GEN-PC\SQLEXPRESS;Initial Catalog=Brgy;Integrated Security=True;")

        Dim myVal1 As Decimal
        Dim myVal2 As String

        myVal1 = Convert.ToDecimal(TextBox1)
        myVal2 = TextBox2.Text

        insertMyTable(conn, myVal1, myVal2)

Alternatively you could use sqlCmd.Parameters.AddWithValue("@myVal1", myVal1). The important thing is to make sure "myVal1" is the correct data type before passing it to the statement. This version may cause implicit data conversions during insert which may cause performance issues.

[Reverend Jim]

In your earlier post you had

cmd.CommandText = "INSERT INTO Resident(ResidentNo, Residential, Street, Telephone, LName, FName, MName, Gender, Birthday, Age, CivilStatus, Religion, Education, Profession, Salary) " _
            & ("VALUES @ResidentNo, @Residential, @Street, @Telephone, @LName, @FName, @MName, @Gender, @Birthday, @Age, @CivilStatus, @Religion, @Education, @Profession, @Salary")

The actual string created is

INSERT INTO Resident(ResidentNo, Residential, Street, Telephone, LName, FName, MName, Gender, Birthday, Age, CivilStatus, Religion, Education, Profession, Salary) VALUES @ResidentNo, @Residential, @Street, @Telephone, @LName, @FName, @MName, @Gender, @Birthday, @Age, @CivilStatus, @Religion, @Education, @Profession, @Salary

You can see that you are missing the parentheses around the values. The format is

INSERT INTO tablename (field, field,...) VALUES(value, value,...)

[cgeier]

I think Reverend Jim found your error. An update to my post. I incorrectly converted to decimal. Line 7 in "Usage" should be Decimal.TryParse(TextBox1.Text, myVal1) instead of myVal1 = Convert.ToDecimal(TextBox1).

Also, you may consider doing some error handling:

    Dim sqlText as String
    Dim sqlCmd As SqlCommand

    Try

        .......

        sqlText = @"INSERT INTO myTable(col1, col2) VALUES(@myVal1, @myVal2)"

        .......

    Catch ex As System.Data.SqlClient.SqlException
            Console.WriteLine(ex.Message + " (SQL: " + query + ")")

            'if using OleDb
    Catch ex As System.Data.OleDb.OleDbException
        Console.WriteLine(ex.Message + " (SQL: " + sqlText + ")")

    Catch ex As Exception
        Console.WriteLine(ex.Message + " (SQL: " + sqlText + ")")
    Finally
        sqlCmd.Dispose()
    End Try

[Gen_2]

Thanks Guys i already figure out the mistake i made. =D