[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3496
  • Last Modified:

"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

0
baz86
Asked:
baz86
  • 6
  • 6
1 Solution
 
jpaulinoCommented:
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
 
jpaulinoCommented:
Try
"INSERT INTO tblPlayer (FirstName,PlayerNo) VALUES ('Joe',1)"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
jpaulinoCommented:
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
 
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
 
jpaulinoCommented:
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
 
jpaulinoCommented:
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
 
jpaulinoCommented:
Ok, glad I could help!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now