data type mismatch in criteria expression

[phpman]

This error is perplexing as I cannot make it happen on my win7 64bit. But when I compile and send to my mom she gets it every time. I have tried 4 different boxes ranging from xp to win7 and none get this error. Yet when she runs it (win7 64bit) she gets it every time

Below is the save function. I have tried to do

SQLInsert.Parameters.AddWithValue("@ID", CInt(Label3.Text))

and it errors with string to integer error. It will always be a number so I want integer. And it is set to number in the access database. I believe that is where it is error but I don't know. Is there a way to get the line number when the error shows to user?

 Private SQLInsert As New OleDbCommand("INSERT INTO Recipe (Routine, Description, Snippet,ParentID) VALUES (@Name, @Descr, @Snip,@ID)", Me.connection)

Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click

        Try
            If Len(txRoutine.Text) < 1 Then
                MsgBox("Please enter recipe name.", MsgBoxStyle.OkOnly, "Validation error")
                txRoutine.Focus()
                Exit Sub
            End If

            If Len(RichTextBox1.Text) < 1 Then
                MsgBox("Please enter Ingredients.", MsgBoxStyle.OkOnly, "Validation error")
                RichTextBox1.Focus()
                Exit Sub
            End If

            If Len(RichTextBox2.Text) < 1 Then
                MsgBox("Please enter Process.", MsgBoxStyle.OkOnly, "Validation error")
                RichTextBox2.Focus()
                Exit Sub
            End If
            ' checks to see if name is already taken
            If Len(txRoutine.Text) > 100 Then
                MsgBox("Title of recipe is too long", MsgBoxStyle.OkOnly, "Validation error")
                txRoutine.Focus()
                Exit Sub
            End If
            ' check to see if we are adding a new one or updating a record
            If Not tvEdit Then
                ' MsgBox("we are adding")

                SQLInsert.Parameters.AddWithValue("@Name", CStr(Replace(txRoutine.Text, "'", "`")))
                SQLInsert.Parameters.AddWithValue("@Descr", CStr(Replace(RichTextBox1.Text, "'", "`")))
                SQLInsert.Parameters.AddWithValue("@Snip", CStr(Replace(RichTextBox2.Text, "'", "`")))
                SQLInsert.Parameters.AddWithValue("@ID", Label3.Text)
                'connection.Open()
                SQLInsert.ExecuteNonQuery()
                Me.adapter.InsertCommand = SQLInsert
                Dim row = table.NewRow()
                row("Routine") = CStr(Replace(txRoutine.Text, "'", "`"))
                row("Description") = CStr(Replace(RichTextBox1.Text, "'", "`"))
                row("Snippet") = CStr(Replace(RichTextBox2.Text, "'", "`"))
                row("ParentID") = Label3.Text
                table.Rows.Add(row)
                ' add to listview
            Else

                ' MsgBox("we are edting")
                SQLupdate.Parameters.AddWithValue("@Name", CStr(Replace(txRoutine.Text, "'", "`")))
                SQLupdate.Parameters.AddWithValue("@Descr", CStr(Replace(RichTextBox1.Text, "'", "`")))
                SQLupdate.Parameters.AddWithValue("@Snip", CStr(Replace(RichTextBox2.Text, "'", "`")))
                SQLupdate.Parameters.AddWithValue("@ID", LblCounter.Text)
                'connection.Open()
                SQLupdate.ExecuteNonQuery()
                Me.adapter.UpdateCommand = SQLupdate
                Dim row = table.NewRow()
                row("Routine") = CStr(Replace(txRoutine.Text, "'", "`"))
                row("Description") = CStr(Replace(RichTextBox1.Text, "'", "`"))
                row("Snippet") = CStr(Replace(RichTextBox2.Text, "'", "`"))
                row("ParentID") = LblCounter.Text
                table.Rows.Add(row)
                ' Me.adapter.Update(Me.table)

            End If
            ' End Using
            MsgBox("Recipe: " & txRoutine.Text & vbCrLf & " Has been saved successfully")
            BtnDegree.Visible = False
            tvwMain.Show()
            txRoutine.Visible = False
            BtnAbort.Visible = False
            BtnSave.Visible = False
            LblRecipeName.Visible = False
            RichTextBox1.ReadOnly = True
            RichTextBox2.ReadOnly = True
            ' reload treeview

            RefreshTree()
        Catch ex As Exception
            MsgBox("Failed to Save Recipe" & Chr(13) & Chr(13) & ex.Message, vbOKOnly, "Failed To Save")
            Debug.WriteLine(ex.Message)
        End Try
    End Sub

[jmcilhinney]

When you use AddWithValue, the data type of the parameter is inferred from the value. You are using Strings in all cases. Presumably one of your columns does not contain text, hence the data type mismatch. Data types are NOT interchangeable. If something is a number then use a number, not a String containing numeric characters. Etc.

[stanav]

What are the datatypes of those fields in your database? And what database is it?

[Ram2Curious]

@phpman

The ID field in the access database is expecting a number. You can use the Integer.Parse Function.

Dim labelvalue As String =label3.Text
Dim value As Integer = Integer.Parse(label3.Text)

[jmcilhinney]

@phpman

The ID field in the access database is expecting a number. You can use the Integer.Parse Function.

Dim labelvalue As String =label3.Text
Dim value As Integer = Integer.Parse(label3.Text)

Why would you need to parse the contents of a Label? That Label must have been populated using a number in the first place. It's that number that should be being used.

[Ram2Curious]

Why would you need to parse the contents of a Label? That Label must have been populated using a number in the first place. It's that number that should be being used.

Hi jm, i have a doubt here. If i try to insert the digit " 1 " using a textbox or a label, then is it going to be inserted as a string datatype or an integer ? Why would someone have to use the CInt Function to convert a value to integer ?

.

[jmcilhinney]

The Text of a control is a String, plain and simple. It doesn't matter what characters it contains, it's a String. If your database is supposed to store a number then you need to pass a number. A String containing digit is still a String, not a number.

[Ram2Curious]

Jm, does it mean that an Integer or a Decimal value is still a string ? Is it just a string representation ?

Edit : If it is so, then when should the CInt or CDbl be used ?

[phpman]

Thanks guys.

My database is access database. I have the correct structure in the database. The @ID field is a number in the database. But I think I know what is happening. I will find out later today. It maybe a user problem on how they are entering it.

So I should be using CInt() for that label anyway huh?