"Data conversion failed. [ OLE DB status value (if known) = 2 ]"

Hi All,

I get this error when i run the query below. (with the pointer at cmd.ExecuteNonQuery).

Im not sure what data conversion its trying to do as im using simple data types...

can someone give me any pointers/advice as what to change or what the problem is.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New SqlCeConnection("Data Source=\My Documents\ECADDY.sdf")
 
        conn.Open()
        Dim sql As String = "INSERT tblPlayer (FirstName,PlayerNo) VALUES ('Joe','1')"
        Dim cmd As New SqlCeCommand(sql, conn)
        cmd.ExecuteNonQuery()        ' Execute the command here
 
        'and then read
        Dim myreader As SqlCeDataReader
 
        myreader = cmd.ExecuteReader
 
        conn.Close()
 
    End Sub
End Class

Open in new window

baz86Asked:
Who is Participating?
 
Jorge PaulinoIT Pro/DeveloperCommented:
Have you checked in the db if it was inserted ?
Try this way to be shure

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New SqlCeConnection("Data Source=\My Documents\ECADDY.sdf")
 
        conn.Open()
        Dim sql As String = "INSERT INTO tblPlayer (FirstName,PlayerNo) VALUES ('Joe',1)"
        Dim cmd As New SqlCeCommand(sql, conn)
        Dim result As Integer = cmd.ExecuteNonQuery()        ' Execute the command here
	If result <> 1 Then
	  MessageBox.Show("Not inserted!")
	End If
 
        'and then read
        Dim myreader As SqlCeDataReader
 
        myreader = cmd.ExecuteReader
 
        conn.Close()
 
    End Sub
End Class

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Change the T-SQL to:
 "INSERT INTO tblPlayer (FirstName,PlayerNo) VALUES ('Joe','1')"
 
0
 
baz86Author Commented:
Thanks for the reply. I still get the same SqlCeException was unhandled: Data Conversion failed error message with the change.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jorge PaulinoIT Pro/DeveloperCommented:
Try
"INSERT INTO tblPlayer (FirstName,PlayerNo) VALUES ('Joe',1)"
0
 
baz86Author Commented:
Thanks jpaulio. that seemed to work! (in that there were no errors) but none of the values seemed to be added to the table. would you know why that would be?
0
 
baz86Author Commented:
i made the addition to the code above....but still when i preview the data for the form..there seems to be nothing in there...the code runs fine but doesnt seem to place the values into the table
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
To be sure you have to check direcly on  the table.
 
How do you preview the data for the form ?  With this ?
Dim myreader As SqlCeDataReader
myreader = cmd.ExecuteReader
 
0
 
baz86Author Commented:
not sure if this was the right way to check but on Data Sources, right click then Preview Data?
i also binded the table onto another form to show its contents but shows nothing
0
 
baz86Author Commented:
[ive just increased the points for this question]
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Double click on the ECADDY.sdf to open the server explorer and look in the right table (tblPlayer)
0
 
baz86Author Commented:
jpaulino thanks for all your help on this. The query now works and it adds to the database.
when i originally checked server explorer and the table as you stated above, all the columns were NULL but when i checked the query analyzer on the PDA it showed the entries.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, glad I could help!
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.

All Courses

From novice to tech pro — start learning today.