SQLce: Data Conversion Failed

M

mikeb

To me, 'Data Conversion Failed' sounds like I'm matching the wrong data
types - but in all my code tracing and viewing data coming into my
database - its just doesn't seem to be the case.

I have a dataset loaded from a sqlserver stored procedure call. I'm then
taking that dataset and copying its contents to the sqlce database (code
sample below). It works up until the 10203rd row - and then it fails on
the cmd.ExecuteNonQuery() statement. I inserted the nested Try block to see
what happens after this one 'bad' row -- every insert fails following row
10203.

I check the emulator's memory status and all appears fine - this same error
happens on the actual device too.

I have a break in the code and check the row that is just preceding this row
causing the error - the data is exactly the same with the exception of one
column - and its the same number of characters.

There must be more to this "Data Conversion Failed" error??


:
:
'Create the table
sqlCreateTable.CommandText = "CREATE TABLE ppcVEKP (" & _
"ucc_exidv nvarchar (20), " & _
"trackno_signi nvarchar (20), " & _
"factory_lifnr nvarchar (10), " & _
"ffs_scnnr_id nvarchar (20), " & _
"carton_venum smallint, " & _
"ffs_crtn_size nvarchar (04), " & _
"ffs_scanned_pack_fl nvarchar (01), " & _
"ffs_chng_dttm datetime " & _
")"
sqlCreateTable.ExecuteNonQuery()

:
:
'Here's the code that fails after inserting the 10203rd row, and each row
following:

Public Function loadVEKP(ByVal ds As DataSet, Optional ByRef Counter As
Integer = 0, Optional ByVal errCounter As Integer = 0) As Boolean

Dim rtn As Boolean = False
Dim Rows() As DataRow

Try
Dim cmd As SqlCeCommand
SQLce_Connect()
Rows = ds.Tables(0).Select()
For i As Integer = 0 To (Rows.Length - 1)
cmd = cnSqlCe.CreateCommand()
cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"
With Rows(i)
cmd.Parameters.Add("ucc_exidv", .Item("EXIDV"))
cmd.Parameters.Add("trackno_signi", .Item("SIGNI"))
cmd.Parameters.Add("factory_lifnr", .Item("LIFNR"))
cmd.Parameters.Add("ffs_scnnr_id", .Item("FFS_SCNNR_ID"))
cmd.Parameters.Add("carton_venum", .Item("VENUM"))
cmd.Parameters.Add("ffs_crtn_size", .Item("FFS_CRTN_SIZE"))
cmd.Parameters.Add("ffs_scanned_pack_fl",
..Item("FFS_SCANNED_PACK_FL"))
cmd.Parameters.Add("ffs_chng_dttm", .Item("FFS_CHNG_DTTM"))
End With

'Open the connection, execute the query
Try
cmd.Prepare()
cmd.ExecuteNonQuery()
Counter += 1
Catch SqlCeEx As SqlCeException
errCounter += 1
Finally
cmd.Dispose()
End Try

Next i

rtn = True

Catch SqlCeEx As SqlCeException
errorHandlerSQLce("An error occurred - Please report this error:
modGlobal.loadVEKP", SQLceex)
Catch ex As Exception
errorHandler("An error occurred - Please report this error:
modGlobal.loadVEKP", ex)
Finally
SQLce_Disconnect()
End Try

Return rtn
End Sub
 
I

Ilya Tumanov [MS]

It might be helpful to print out errors from SqlCeException. There's a
sample in VS documentation, just look for SqlCeException.Errors property to
find it.

Next, why can't you use DataAdapter.Update() instead of this loop? I would
suspect it would be faster:

cmd = cnSqlCe.CreateCommand()

cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"

cmd.Parameters.Add("ucc_exidv", SqlDbType.<INSERT_YOUR_TYPE>, "EXIDV")
cmd.Parameters.Add("trackno_signi", SqlDbType.<INSERT_YOUR_TYPE>,
"SIGNI")
cmd.Parameters.Add("factory_lifnr", SqlDbType.<INSERT_YOUR_TYPE>,
"LIFNR")
cmd.Parameters.Add("ffs_scnnr_id", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCNNR_ID")
cmd.Parameters.Add("carton_venum", SqlDbType.<INSERT_YOUR_TYPE>,
"VENUM")
cmd.Parameters.Add("ffs_crtn_size", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CRTN_SIZE")
cmd.Parameters.Add("ffs_scanned_pack_fl",SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCANNED_PACK_FL")
cmd.Parameters.Add("ffs_chng_dttm", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CHNG_DTTM")

Dim adapter As New SqlCeDataAdapter()
adapter.InsertCommand = cmd

adapter.Update(ds.Tables(0));

If might also help if you post these different values you've mentioned and
a column name they are from. Also, try inserting just row #10203.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
M

mikeb

Thanks I will try the dataadapter - and post back.

The reason I hadn't tested just row 10203 was because it appears that *any*
row from 10203 to the end of the set fails; Not just that one row.

If it were truly a data conversion error, wouldn't you think the rest of the
records would succeed. Especially since data up to 10202 was fine, but
anything following and including 10203 works. I do, but curious...



"Ilya Tumanov [MS]" said:
It might be helpful to print out errors from SqlCeException. There's a
sample in VS documentation, just look for SqlCeException.Errors property
to
find it.

Next, why can't you use DataAdapter.Update() instead of this loop? I would
suspect it would be faster:

cmd = cnSqlCe.CreateCommand()

cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"

cmd.Parameters.Add("ucc_exidv", SqlDbType.<INSERT_YOUR_TYPE>, "EXIDV")
cmd.Parameters.Add("trackno_signi", SqlDbType.<INSERT_YOUR_TYPE>,
"SIGNI")
cmd.Parameters.Add("factory_lifnr", SqlDbType.<INSERT_YOUR_TYPE>,
"LIFNR")
cmd.Parameters.Add("ffs_scnnr_id", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCNNR_ID")
cmd.Parameters.Add("carton_venum", SqlDbType.<INSERT_YOUR_TYPE>,
"VENUM")
cmd.Parameters.Add("ffs_crtn_size", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CRTN_SIZE")
cmd.Parameters.Add("ffs_scanned_pack_fl",SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCANNED_PACK_FL")
cmd.Parameters.Add("ffs_chng_dttm", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CHNG_DTTM")

Dim adapter As New SqlCeDataAdapter()
adapter.InsertCommand = cmd

adapter.Update(ds.Tables(0));

If might also help if you post these different values you've mentioned and
a column name they are from. Also, try inserting just row #10203.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
From: "mikeb" <[email protected]>
Subject: SQLce: Data Conversion Failed
Date: Tue, 22 Feb 2005 17:27:39 -0800
Lines: 107
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: c-67-168-247-211.client.comcast.net 67.168.247.211
Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP14.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.framework.compactframework:71706
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

To me, 'Data Conversion Failed' sounds like I'm matching the wrong data
types - but in all my code tracing and viewing data coming into my
database - its just doesn't seem to be the case.

I have a dataset loaded from a sqlserver stored procedure call. I'm then
taking that dataset and copying its contents to the sqlce database (code
sample below). It works up until the 10203rd row - and then it fails on
the cmd.ExecuteNonQuery() statement. I inserted the nested Try block to see
what happens after this one 'bad' row -- every insert fails following row
10203.

I check the emulator's memory status and all appears fine - this same error
happens on the actual device too.

I have a break in the code and check the row that is just preceding this row
causing the error - the data is exactly the same with the exception of one
column - and its the same number of characters.

There must be more to this "Data Conversion Failed" error??


:
:
'Create the table
sqlCreateTable.CommandText = "CREATE TABLE ppcVEKP (" & _
"ucc_exidv nvarchar (20), " & _
"trackno_signi nvarchar (20), " & _
"factory_lifnr nvarchar (10), " & _
"ffs_scnnr_id nvarchar (20), " & _
"carton_venum smallint, " & _
"ffs_crtn_size nvarchar (04), " & _
"ffs_scanned_pack_fl nvarchar (01), " & _
"ffs_chng_dttm datetime " & _
")"
sqlCreateTable.ExecuteNonQuery()

:
:
'Here's the code that fails after inserting the 10203rd row, and each row
following:

Public Function loadVEKP(ByVal ds As DataSet, Optional ByRef Counter As
Integer = 0, Optional ByVal errCounter As Integer = 0) As Boolean

Dim rtn As Boolean = False
Dim Rows() As DataRow

Try
Dim cmd As SqlCeCommand
SQLce_Connect()
Rows = ds.Tables(0).Select()
For i As Integer = 0 To (Rows.Length - 1)
cmd = cnSqlCe.CreateCommand()
cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"
With Rows(i)
cmd.Parameters.Add("ucc_exidv", .Item("EXIDV"))
cmd.Parameters.Add("trackno_signi", .Item("SIGNI"))
cmd.Parameters.Add("factory_lifnr", .Item("LIFNR"))
cmd.Parameters.Add("ffs_scnnr_id", .Item("FFS_SCNNR_ID"))
cmd.Parameters.Add("carton_venum", .Item("VENUM"))
cmd.Parameters.Add("ffs_crtn_size", .Item("FFS_CRTN_SIZE"))
cmd.Parameters.Add("ffs_scanned_pack_fl",
.Item("FFS_SCANNED_PACK_FL"))
cmd.Parameters.Add("ffs_chng_dttm", .Item("FFS_CHNG_DTTM"))
End With

'Open the connection, execute the query
Try
cmd.Prepare()
cmd.ExecuteNonQuery()
Counter += 1
Catch SqlCeEx As SqlCeException
errCounter += 1
Finally
cmd.Dispose()
End Try

Next i

rtn = True

Catch SqlCeEx As SqlCeException
errorHandlerSQLce("An error occurred - Please report this error:
modGlobal.loadVEKP", SQLceex)
Catch ex As Exception
errorHandler("An error occurred - Please report this error:
modGlobal.loadVEKP", ex)
Finally
SQLce_Disconnect()
End Try

Return rtn
End Sub
Click to expand...
Click to expand...
 
M

mikeb

SmallInt - their data sent to me exceeded the smallint limit.

Thanks for the help - I'm still going to try the DataAdapter method - thanks
for that.


"Ilya Tumanov [MS]" said:
It might be helpful to print out errors from SqlCeException. There's a
sample in VS documentation, just look for SqlCeException.Errors property
to
find it.

Next, why can't you use DataAdapter.Update() instead of this loop? I would
suspect it would be faster:

cmd = cnSqlCe.CreateCommand()

cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"

cmd.Parameters.Add("ucc_exidv", SqlDbType.<INSERT_YOUR_TYPE>, "EXIDV")
cmd.Parameters.Add("trackno_signi", SqlDbType.<INSERT_YOUR_TYPE>,
"SIGNI")
cmd.Parameters.Add("factory_lifnr", SqlDbType.<INSERT_YOUR_TYPE>,
"LIFNR")
cmd.Parameters.Add("ffs_scnnr_id", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCNNR_ID")
cmd.Parameters.Add("carton_venum", SqlDbType.<INSERT_YOUR_TYPE>,
"VENUM")
cmd.Parameters.Add("ffs_crtn_size", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CRTN_SIZE")
cmd.Parameters.Add("ffs_scanned_pack_fl",SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCANNED_PACK_FL")
cmd.Parameters.Add("ffs_chng_dttm", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CHNG_DTTM")

Dim adapter As New SqlCeDataAdapter()
adapter.InsertCommand = cmd

adapter.Update(ds.Tables(0));

If might also help if you post these different values you've mentioned and
a column name they are from. Also, try inserting just row #10203.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
From: "mikeb" <[email protected]>
Subject: SQLce: Data Conversion Failed
Date: Tue, 22 Feb 2005 17:27:39 -0800
Lines: 107
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: c-67-168-247-211.client.comcast.net 67.168.247.211
Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP14.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.framework.compactframework:71706
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

To me, 'Data Conversion Failed' sounds like I'm matching the wrong data
types - but in all my code tracing and viewing data coming into my
database - its just doesn't seem to be the case.

I have a dataset loaded from a sqlserver stored procedure call. I'm then
taking that dataset and copying its contents to the sqlce database (code
sample below). It works up until the 10203rd row - and then it fails on
the cmd.ExecuteNonQuery() statement. I inserted the nested Try block to see
what happens after this one 'bad' row -- every insert fails following row
10203.

I check the emulator's memory status and all appears fine - this same error
happens on the actual device too.

I have a break in the code and check the row that is just preceding this row
causing the error - the data is exactly the same with the exception of one
column - and its the same number of characters.

There must be more to this "Data Conversion Failed" error??


:
:
'Create the table
sqlCreateTable.CommandText = "CREATE TABLE ppcVEKP (" & _
"ucc_exidv nvarchar (20), " & _
"trackno_signi nvarchar (20), " & _
"factory_lifnr nvarchar (10), " & _
"ffs_scnnr_id nvarchar (20), " & _
"carton_venum smallint, " & _
"ffs_crtn_size nvarchar (04), " & _
"ffs_scanned_pack_fl nvarchar (01), " & _
"ffs_chng_dttm datetime " & _
")"
sqlCreateTable.ExecuteNonQuery()

:
:
'Here's the code that fails after inserting the 10203rd row, and each row
following:

Public Function loadVEKP(ByVal ds As DataSet, Optional ByRef Counter As
Integer = 0, Optional ByVal errCounter As Integer = 0) As Boolean

Dim rtn As Boolean = False
Dim Rows() As DataRow

Try
Dim cmd As SqlCeCommand
SQLce_Connect()
Rows = ds.Tables(0).Select()
For i As Integer = 0 To (Rows.Length - 1)
cmd = cnSqlCe.CreateCommand()
cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"
With Rows(i)
cmd.Parameters.Add("ucc_exidv", .Item("EXIDV"))
cmd.Parameters.Add("trackno_signi", .Item("SIGNI"))
cmd.Parameters.Add("factory_lifnr", .Item("LIFNR"))
cmd.Parameters.Add("ffs_scnnr_id", .Item("FFS_SCNNR_ID"))
cmd.Parameters.Add("carton_venum", .Item("VENUM"))
cmd.Parameters.Add("ffs_crtn_size", .Item("FFS_CRTN_SIZE"))
cmd.Parameters.Add("ffs_scanned_pack_fl",
.Item("FFS_SCANNED_PACK_FL"))
cmd.Parameters.Add("ffs_chng_dttm", .Item("FFS_CHNG_DTTM"))
End With

'Open the connection, execute the query
Try
cmd.Prepare()
cmd.ExecuteNonQuery()
Counter += 1
Catch SqlCeEx As SqlCeException
errCounter += 1
Finally
cmd.Dispose()
End Try

Next i

rtn = True

Catch SqlCeEx As SqlCeException
errorHandlerSQLce("An error occurred - Please report this error:
modGlobal.loadVEKP", SQLceex)
Catch ex As Exception
errorHandler("An error occurred - Please report this error:
modGlobal.loadVEKP", ex)
Finally
SQLce_Disconnect()
End Try

Return rtn
End Sub
Click to expand...
Click to expand...
 
M

mikeb

I tried the code you suggested - all appeared to work fine, without error
that is, but it doesn't appear to have dumped the data into my sqlce table -
the table is still empty. It definitely executed quickly though -wink.

What might I have done wrong?

SqlServer_StoredProcudure -> dataset -> dataadapter -> sqlce table

Can this be done?


mikeb said:
Thanks I will try the dataadapter - and post back.

The reason I hadn't tested just row 10203 was because it appears that
*any* row from 10203 to the end of the set fails; Not just that one row.

If it were truly a data conversion error, wouldn't you think the rest of
the records would succeed. Especially since data up to 10202 was fine,
but anything following and including 10203 works. I do, but curious...



"Ilya Tumanov [MS]" said:
It might be helpful to print out errors from SqlCeException. There's a
sample in VS documentation, just look for SqlCeException.Errors property
to
find it.

Next, why can't you use DataAdapter.Update() instead of this loop? I
would
suspect it would be faster:

cmd = cnSqlCe.CreateCommand()

cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"

cmd.Parameters.Add("ucc_exidv", SqlDbType.<INSERT_YOUR_TYPE>, "EXIDV")
cmd.Parameters.Add("trackno_signi", SqlDbType.<INSERT_YOUR_TYPE>,
"SIGNI")
cmd.Parameters.Add("factory_lifnr", SqlDbType.<INSERT_YOUR_TYPE>,
"LIFNR")
cmd.Parameters.Add("ffs_scnnr_id", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCNNR_ID")
cmd.Parameters.Add("carton_venum", SqlDbType.<INSERT_YOUR_TYPE>,
"VENUM")
cmd.Parameters.Add("ffs_crtn_size", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CRTN_SIZE")
cmd.Parameters.Add("ffs_scanned_pack_fl",SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCANNED_PACK_FL")
cmd.Parameters.Add("ffs_chng_dttm", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CHNG_DTTM")

Dim adapter As New SqlCeDataAdapter()
adapter.InsertCommand = cmd

adapter.Update(ds.Tables(0));

If might also help if you post these different values you've mentioned
and
a column name they are from. Also, try inserting just row #10203.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
From: "mikeb" <[email protected]>
Subject: SQLce: Data Conversion Failed
Date: Tue, 22 Feb 2005 17:27:39 -0800
Lines: 107
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: c-67-168-247-211.client.comcast.net 67.168.247.211
Path: TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP14.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.framework.compactframework:71706
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

To me, 'Data Conversion Failed' sounds like I'm matching the wrong data
types - but in all my code tracing and viewing data coming into my
database - its just doesn't seem to be the case.

I have a dataset loaded from a sqlserver stored procedure call. I'm
then
taking that dataset and copying its contents to the sqlce database (code
sample below). It works up until the 10203rd row - and then it fails
on
the cmd.ExecuteNonQuery() statement. I inserted the nested Try block to see
what happens after this one 'bad' row -- every insert fails following
row
10203.

I check the emulator's memory status and all appears fine - this same error
happens on the actual device too.

I have a break in the code and check the row that is just preceding this row
causing the error - the data is exactly the same with the exception of one
column - and its the same number of characters.

There must be more to this "Data Conversion Failed" error??


:
:
'Create the table
sqlCreateTable.CommandText = "CREATE TABLE ppcVEKP (" & _
"ucc_exidv nvarchar (20), " & _
"trackno_signi nvarchar (20), " & _
"factory_lifnr nvarchar (10), " & _
"ffs_scnnr_id nvarchar (20), " & _
"carton_venum smallint, " & _
"ffs_crtn_size nvarchar (04), " & _
"ffs_scanned_pack_fl nvarchar (01), " & _
"ffs_chng_dttm datetime " & _
")"
sqlCreateTable.ExecuteNonQuery()

:
:
'Here's the code that fails after inserting the 10203rd row, and each
row
following:

Public Function loadVEKP(ByVal ds As DataSet, Optional ByRef Counter As
Integer = 0, Optional ByVal errCounter As Integer = 0) As Boolean

Dim rtn As Boolean = False
Dim Rows() As DataRow

Try
Dim cmd As SqlCeCommand
SQLce_Connect()
Rows = ds.Tables(0).Select()
For i As Integer = 0 To (Rows.Length - 1)
cmd = cnSqlCe.CreateCommand()
cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"
With Rows(i)
cmd.Parameters.Add("ucc_exidv", .Item("EXIDV"))
cmd.Parameters.Add("trackno_signi", .Item("SIGNI"))
cmd.Parameters.Add("factory_lifnr", .Item("LIFNR"))
cmd.Parameters.Add("ffs_scnnr_id", .Item("FFS_SCNNR_ID"))
cmd.Parameters.Add("carton_venum", .Item("VENUM"))
cmd.Parameters.Add("ffs_crtn_size", .Item("FFS_CRTN_SIZE"))
cmd.Parameters.Add("ffs_scanned_pack_fl",
.Item("FFS_SCANNED_PACK_FL"))
cmd.Parameters.Add("ffs_chng_dttm", .Item("FFS_CHNG_DTTM"))
End With

'Open the connection, execute the query
Try
cmd.Prepare()
cmd.ExecuteNonQuery()
Counter += 1
Catch SqlCeEx As SqlCeException
errCounter += 1
Finally
cmd.Dispose()
End Try

Next i

rtn = True

Catch SqlCeEx As SqlCeException
errorHandlerSQLce("An error occurred - Please report this error:
modGlobal.loadVEKP", SQLceex)
Catch ex As Exception
errorHandler("An error occurred - Please report this error:
modGlobal.loadVEKP", ex)
Finally
SQLce_Disconnect()
End Try

Return rtn
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I

Ilya Tumanov [MS]

I would suspect rows in your DataTable are "Unchanged" and DataAdapter
won't insert/update/delete unchanged rows.
You see, DataAdapter uses row state to determine how to treat it. If it's
unchanged, it would do nothing.
If it's "Inserted", it would use insert command to insert it and so on.

Now, why rows in your table are unchanged?
If you're inserting rows to the DataTable manually, that might be because
you call AcceptChanges().

Or, if you're using SqlDataAdapter to populate table, it would accept
changes by default.
You can override this by setting SqlDataAdapter.AcceptChangesDuringFill to
false:

SqlCommand command = new SqlCommand("select * from customers");

SqlConnection conn = new SqlConnection("Connection string here");

conn.Open();

command.Connection = conn;

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(command);

da.AcceptChangesDuringFill = false; // Do not accept changes, so we can
insert rows to SQL CE later on.

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

da.Fill(ds);

Best regards,

Ilya

PS No, it won't be _that_ fast if it needs to actually insert rows, sorry.

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
From: "mikeb" <[email protected]>
References: <#[email protected]>
Click to expand...
Subject: Re: SQLce: Data Conversion Failed
Date: Thu, 24 Feb 2005 07:36:31 -0800
Lines: 225
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: c-67-168-247-211.client.comcast.net 67.168.247.211
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP0
9.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.dotnet.framework.compactframework:71831
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

I tried the code you suggested - all appeared to work fine, without error
that is, but it doesn't appear to have dumped the data into my sqlce table -
the table is still empty. It definitely executed quickly though -wink.

What might I have done wrong?

SqlServer_StoredProcudure -> dataset -> dataadapter -> sqlce table

Can this be done?


mikeb said:
Thanks I will try the dataadapter - and post back.

The reason I hadn't tested just row 10203 was because it appears that
*any* row from 10203 to the end of the set fails; Not just that one row.

If it were truly a data conversion error, wouldn't you think the rest of
the records would succeed. Especially since data up to 10202 was fine,
but anything following and including 10203 works. I do, but curious...



"Ilya Tumanov [MS]" said:
It might be helpful to print out errors from SqlCeException. There's a
sample in VS documentation, just look for SqlCeException.Errors property
to
find it.

Next, why can't you use DataAdapter.Update() instead of this loop? I
would
suspect it would be faster:

cmd = cnSqlCe.CreateCommand()

cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"

cmd.Parameters.Add("ucc_exidv", SqlDbType.<INSERT_YOUR_TYPE>, "EXIDV")
cmd.Parameters.Add("trackno_signi", SqlDbType.<INSERT_YOUR_TYPE>,
"SIGNI")
cmd.Parameters.Add("factory_lifnr", SqlDbType.<INSERT_YOUR_TYPE>,
"LIFNR")
cmd.Parameters.Add("ffs_scnnr_id", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_SCNNR_ID")
cmd.Parameters.Add("carton_venum", SqlDbType.<INSERT_YOUR_TYPE>,
"VENUM")
cmd.Parameters.Add("ffs_crtn_size", SqlDbType.<INSERT_YOUR_TYPE>,
"FFS_CRTN_SIZE")
Click to expand...
cmd.Parameters.Add("ffs_scanned_pack_fl" said:
"FFS_SCANNED_PACK_FL")
cmd.Parameters.Add("ffs_chng_dttm",
Click to expand...
SqlDbType. said:
"FFS_CHNG_DTTM")

Dim adapter As New SqlCeDataAdapter()
adapter.InsertCommand = cmd

adapter.Update(ds.Tables(0));

If might also help if you post these different values you've mentioned
and
a column name they are from. Also, try inserting just row #10203.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
From: "mikeb" <[email protected]>
Subject: SQLce: Data Conversion Failed
Date: Tue, 22 Feb 2005 17:27:39 -0800
Lines: 107
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: c-67-168-247-211.client.comcast.net 67.168.247.211
Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP14.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.dotnet.framework.compactframework:71706
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

To me, 'Data Conversion Failed' sounds like I'm matching the wrong data
types - but in all my code tracing and viewing data coming into my
database - its just doesn't seem to be the case.

I have a dataset loaded from a sqlserver stored procedure call. I'm
then
taking that dataset and copying its contents to the sqlce database (code
sample below). It works up until the 10203rd row - and then it fails
on
the cmd.ExecuteNonQuery() statement. I inserted the nested Try block to
see
what happens after this one 'bad' row -- every insert fails following
row
10203.

I check the emulator's memory status and all appears fine - this same
error
happens on the actual device too.

I have a break in the code and check the row that is just preceding this
row
causing the error - the data is exactly the same with the exception of
one
column - and its the same number of characters.

There must be more to this "Data Conversion Failed" error??


:
:
'Create the table
sqlCreateTable.CommandText = "CREATE TABLE ppcVEKP (" & _
"ucc_exidv nvarchar (20), " & _
"trackno_signi nvarchar (20), " & _
"factory_lifnr nvarchar (10), " & _
"ffs_scnnr_id nvarchar (20), " & _
"carton_venum smallint, " & _
"ffs_crtn_size nvarchar (04), " & _
"ffs_scanned_pack_fl nvarchar (01), " & _
"ffs_chng_dttm datetime " & _
")"
sqlCreateTable.ExecuteNonQuery()

:
:
'Here's the code that fails after inserting the 10203rd row, and each
row
following:

Public Function loadVEKP(ByVal ds As DataSet, Optional ByRef Counter As
Integer = 0, Optional ByVal errCounter As Integer = 0) As Boolean

Dim rtn As Boolean = False
Dim Rows() As DataRow

Try
Dim cmd As SqlCeCommand
SQLce_Connect()
Rows = ds.Tables(0).Select()
For i As Integer = 0 To (Rows.Length - 1)
cmd = cnSqlCe.CreateCommand()
cmd.CommandText = _
"INSERT INTO ppcVEKP (" & _
"ucc_exidv, " & _
"trackno_signi, " & _
"factory_lifnr, " & _
"ffs_scnnr_id, " & _
"carton_venum, " & _
"ffs_crtn_size, " & _
"ffs_scanned_pack_fl, " & _
"ffs_chng_dttm " & _
") " & _
"VALUES " & _
"(?,?,?,?,?,?,?,?)"
With Rows(i)
cmd.Parameters.Add("ucc_exidv", .Item("EXIDV"))
cmd.Parameters.Add("trackno_signi", .Item("SIGNI"))
cmd.Parameters.Add("factory_lifnr", .Item("LIFNR"))
cmd.Parameters.Add("ffs_scnnr_id", .Item("FFS_SCNNR_ID"))
cmd.Parameters.Add("carton_venum", .Item("VENUM"))
cmd.Parameters.Add("ffs_crtn_size", .Item("FFS_CRTN_SIZE"))
cmd.Parameters.Add("ffs_scanned_pack_fl",
.Item("FFS_SCANNED_PACK_FL"))
cmd.Parameters.Add("ffs_chng_dttm", .Item("FFS_CHNG_DTTM"))
End With

'Open the connection, execute the query
Try
cmd.Prepare()
cmd.ExecuteNonQuery()
Counter += 1
Catch SqlCeEx As SqlCeException
errCounter += 1
Finally
cmd.Dispose()
End Try

Next i

rtn = True

Catch SqlCeEx As SqlCeException
errorHandlerSQLce("An error occurred - Please report this error:
modGlobal.loadVEKP", SQLceex)
Catch ex As Exception
errorHandler("An error occurred - Please report this error:
modGlobal.loadVEKP", ex)
Finally
SQLce_Disconnect()
End Try

Return rtn
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQLCE Insert Bug Found? 7

Top