billb59

hey sync'ers... anyone having trouble with converting Btye's to DateTime problems I have stepped through every line and double checked both the client and server db's and cannot isolate this error in code... hopefully i'm being an idiot and someone can look at the below and tell me so... the exception is above the sync call below somewhere... i've tried datetime instead of DbType.Binary with no success... anyone else have this problem solution Thanks!

I'm using this:

SqlSyncAdapterBuilder ProposalDetailBuilder = new SqlSyncAdapterBuilder();

ProposalDetailBuilder.Connection = serverConnection;

ProposalDetailBuilder.SyncDirection = SyncDirection.Bidirectional;

// base table

ProposalDetailBuilder.TableName = "ProposalDetail";

ProposalDetailBuilder.DataColumns.Add("tblID");

ProposalDetailBuilder.DataColumns.Add("SQLkey");

ProposalDetailBuilder.DataColumns.Add("CreateDateTime");

ProposalDetailBuilder.DataColumns.Add("Alias");

ProposalDetailBuilder.DataColumns.Add("PropDNumb");

ProposalDetailBuilder.DataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.DataColumns.Add("PropDLine");

ProposalDetailBuilder.DataColumns.Add("PropDItem");

ProposalDetailBuilder.DataColumns.Add("PropDMfg");

ProposalDetailBuilder.DataColumns.Add("PropDCat");

ProposalDetailBuilder.DataColumns.Add("PropDList");

ProposalDetailBuilder.DataColumns.Add("PropDCost");

ProposalDetailBuilder.DataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.DataColumns.Add("PropDGM");

ProposalDetailBuilder.DataColumns.Add("PropDOGM");

ProposalDetailBuilder.DataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDHideValue");

ProposalDetailBuilder.DataColumns.Add("PropDShipQty");

ProposalDetailBuilder.DataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.DataColumns.Add("PropDUOM");

ProposalDetailBuilder.DataColumns.Add("PropDStatus");

ProposalDetailBuilder.DataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.DataColumns.Add("PropDNotes");

ProposalDetailBuilder.DataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.DataColumns.Add("create_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_originator_id");

// tombstone table

ProposalDetailBuilder.TombstoneTableName = "ProposalDetail_tombstone";

ProposalDetailBuilder.TombstoneDataColumns.Add("tblID");

ProposalDetailBuilder.TombstoneDataColumns.Add("SQLkey");

ProposalDetailBuilder.TombstoneDataColumns.Add("CreateDateTime");

ProposalDetailBuilder.TombstoneDataColumns.Add("Alias");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDLine");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDMfg");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCat");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDList");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCost");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDHideValue");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDShipQty");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDUOM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDStatus");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNotes");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.TombstoneDataColumns.Add("create_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_originator_id");

// tracking\sync columns

ProposalDetailBuilder.CreationTrackingColumn = @"create_timestamp";

ProposalDetailBuilder.UpdateTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.DeletionTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

SyncAdapter ProposalDetailSyncAdapter = ProposalDetailBuilder.ToSyncAdapter();

((SqlParameter)ProposalDetailSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;

((SqlParameter)ProposalDetailSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = DbType.Binary;

serverSyncProvider.SyncAdapters.Add(ProposalDetailSyncAdapter);




Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

Rafik Robeal


One way to narrow down debugging is to use the SQL Profiler. The profiler will show the last statement executed successfully prior to hitting this error. It may turn out that you need to fix the type for different command than the SelectIncrementalInsertsCommand.

If your tracking columns on the server side are of type datetime then I would expect to pass a parameter type of datetime two and the builder should have figured that out. Let's first find which command that is causing this error.

Thanks!






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

 

Rafik thanks....

outside of the SELECT @@DBTS and SELECT 1 statements this is what is showing in the profiler...

exec sp_executesql N'SELECT [tblID], [SQLkey], [CreateDateTime], [Alias], [PropDNumb], [PropDRevNumb], [PropDLine], [PropDItem], [PropDMfg], [PropDCat], [PropDList], [PropDCost], [PropDGMDollar], [PropDGM],
[PropDOGM], [PropDSellPrice], [PropDXSellPrice], [PropDHideValue], [PropDShipQty], [PropDOptionalItem], [PropDUOM], [PropDStatus], [PropDGMPercent], [PropDNotes], [PropDItemTotal], [create_timestamp],
[update_timestamp], [update_originator_id] FROM [ProposalDetail] WHERE (create_timestamp > @sync_last_received_anchor AND create_timestamp <= @sync_new_received_anchor AND update_originator_id <>
@sync_originator_id)',N'@sync_last_received_anchor varbinary(8000),@sync_new_received_anchor varbinary(8),@sync_originator_id
nvarchar(4000)',@sync_last_received_anchor=0x,@sync_new_received_anchor=0x0000000000018A88,@sync_originator_id=N'1'

Make any sense






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

Rafik Robeal


Something does not look right in this command: N'@sync_last_received_anchor varbinary(8000),

Why is the sync_last_received_anchor is of type varbinary(8000) Try to check the types of the tracking columns on the table and see if something is fishy.






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

 

Here's the line in code... ((SqlParameter)ProposalDetailSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;

 

Did a drop on each... sync_last_received_anchor and sync_new_received_anchor in the table and re-inserted both using datetime, varbinary(50), varbinary(MAX),  and nvarchar(50)... still no luck and the same error. I'm currently using varbinary(MAX) and pretty much getting the same return in the profiler...

exec sp_executesql N'SELECT [tblID], [SQLkey], [CreateDateTime], [Alias], [PropDNumb], [PropDRevNumb], [PropDLine], [PropDItem], [PropDMfg], [PropDCat], [PropDList], [PropDCost], [PropDGMDollar], [PropDGM],
[PropDOGM], [PropDSellPrice], [PropDXSellPrice], [PropDHideValue], [PropDShipQty], [PropDOptionalItem], [PropDUOM], [PropDStatus], [PropDGMPercent], [PropDNotes], [PropDItemTotal], [create_timestamp],
[update_timestamp], [update_originator_id], [sync_last_received_anchor], [sync_new_received_anchor] FROM [ProposalDetail] WHERE (create_timestamp > @sync_last_received_anchor AND create_timestamp <=
@sync_new_received_anchor AND update_originator_id <> @sync_originator_id)',N'@sync_last_received_anchor varbinary(8000),@sync_new_received_anchor varbinary(8),@sync_originator_id
nvarchar(4000)',@sync_last_received_anchor=0x,@sync_new_received_anchor=0x0000000000018A88,@sync_originator_id=N'1'

 

Thanks, Bill






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

Rafik Robeal


Hmm, I think you could fix this issue by using datetime for both create_time and update_time. In the demo, I had to use bigint instead of timestamp since I cannot have more than one column per table of type timestamp. With datetime you don't have this limitation. Since you are using the SyncAdapterBuilder, you are limited with its ability to generate code for you. The builder does not know how to transform between types, that said, you need to use same data type for both create and update columns. (or different types that are implicitly converted to each others, like timestamp and bigint)

Hope that helps






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

Hey Rafik... good morning... what is "AllChanges" and where does it reside.... metadata

SyncGroup orderGroup = new SyncGroup("AllChanges");

tableProposalDetail.SyncGroup = orderGroup;






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

Rafik Robeal

Hi bill,

SyncGroup is a way to ask the agent to group bunch of tables together during synchronization. In the example code you are referring to. I attached the same syncgroup to two tables, I named the group "AllChanges". The outcome of that is that the changes for both order and order_details will be in one dataset and will be shipped to the server as one unit. Same thing will happen when getting changes from the server.

Without sync group, each table changes will go in a separate dataset and will be applied separately.

Thanks






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

Rafik, Hi,

I'm still getting the byte to datetime conversion error... thought I'd show you the code I'm currently using... sorry for the large code blast! Do I need to include the tombstone tables in the client build

 

Thanks a ton!

Bill

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data;

using System.Data.SqlClient;

using Microsoft.Synchronization.Data;

using Microsoft.Synchronization.Data.Client;

using Microsoft.Synchronization.Data.Server;

using System.Data.SqlServerCe;

 

namespace SyncApplication

{

public partial class SyncForm : Form

{

ProgressForm _progressForm;

public SyncForm()

{

InitializeComponent();

dbPathTextBox.Text = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\ffgscrm.sdf";

 

if (null == textServerMachine.Text || 0 == textServerMachine.Text.Length)

{

textServerMachine.Text = Environment.MachineName;

}

if (null == textClientMachine.Text || 0 == textClientMachine.Text.Length)

{

textClientMachine.Text = Environment.MachineName;

}

_progressForm = null;

}

 

//

// Synchronize Call

//

private void buttonSynchronize_Click(object sender, EventArgs e)

{

try

{

//

// 1. Create instance of the sync components (client, agent, server)

// This demo illustrates direct connection to server database. In this scenario,

// sync components - client provider, sync agent and server provider - reside at

// the client side. On the server, each table might need to be extended with sync

// related columns to store metadata. This demo adds three more columns to the

// orders and order_details tables for bidirectional sync. The changes are illustrated

// in demo.sql file.

//

//

DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();

SyncAgent syncAgent = new SyncAgent();

syncAgent.ServerSyncProvider = serverSyncProvider;

 

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

//

// 2. Prepare server db connection and attach it to the sync agent

//

builder["Data Source"] = textServerMachine.Text;

builder["integrated Security"] = true;

builder["Initial Catalog"] = "ffgscrm";

SqlConnection serverConnection = new SqlConnection(builder.ConnectionString);

serverSyncProvider.Connection = serverConnection;

//

// 3. Prepare client db connection and attach it to the sync provider

//

string connString = "Data Source=" + dbPathTextBox.Text;

if (false == File.Exists(dbPathTextBox.Text))

{

SqlCeEngine clientEngine = new SqlCeEngine(connString);

clientEngine.CreateDatabase();

clientEngine.Dispose();

}

SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(connString);

syncAgent.ClientSyncProvider = (ClientSyncProvider)clientSyncProvider;

//

// 4. Create SyncTables and SyncGroups

// To sync a table, a SyncTable object needs to be created and setup with desired properties:

// TableCreationOption tells the agent how to initialize the new table in the local database

// SyncDirection is how changes from with respect to client {Download, Upload, Bidirectional or Snapshot}

//

//

SyncTable tableProposalDetail = new SyncTable("ProposalDetail");

tableProposalDetail.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;

tableProposalDetail.SyncDirection = SyncDirection.Bidirectional;

 

// Sync changes for both tables as one bunch, using SyncGroup object

// This is important if the tables has PK-FK relationship, grouping will ensure that

// and FK change won't be applied before its PK is applied

//

//

 SyncGroup orderGroup = new SyncGroup("AllChanges");

tableProposalDetail.SyncGroup = orderGroup;

syncAgent.SyncTables.Add(tableProposalDetail);

//

// 5. Create sync adapter for each sync table and attach it to the agent

// Following DataAdapter style in ADO.NET, SyncAdapte is the equivelent for

// Sync. SyncAdapterBuilder is a helper class to simplify the process of

// creating sync commands.

//

//

SqlSyncAdapterBuilder ProposalDetailBuilder = new SqlSyncAdapterBuilder();

ProposalDetailBuilder.Connection = serverConnection;

ProposalDetailBuilder.SyncDirection = SyncDirection.Bidirectional;

// base table

ProposalDetailBuilder.TableName = "ProposalDetail";

ProposalDetailBuilder.DataColumns.Add("tblID");

ProposalDetailBuilder.DataColumns.Add("SQLkey");

ProposalDetailBuilder.DataColumns.Add("CreateDateTime");

ProposalDetailBuilder.DataColumns.Add("Alias");

ProposalDetailBuilder.DataColumns.Add("PropDNumb");

ProposalDetailBuilder.DataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.DataColumns.Add("PropDLine");

ProposalDetailBuilder.DataColumns.Add("PropDItem");

ProposalDetailBuilder.DataColumns.Add("PropDMfg");

ProposalDetailBuilder.DataColumns.Add("PropDCat");

ProposalDetailBuilder.DataColumns.Add("PropDList");

ProposalDetailBuilder.DataColumns.Add("PropDCost");

ProposalDetailBuilder.DataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.DataColumns.Add("PropDGM");

ProposalDetailBuilder.DataColumns.Add("PropDOGM");

ProposalDetailBuilder.DataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.DataColumns.Add("PropDHideValue");

ProposalDetailBuilder.DataColumns.Add("PropDShipQty");

ProposalDetailBuilder.DataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.DataColumns.Add("PropDUOM");

ProposalDetailBuilder.DataColumns.Add("PropDStatus");

ProposalDetailBuilder.DataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.DataColumns.Add("PropDNotes");

ProposalDetailBuilder.DataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.DataColumns.Add("create_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_timestamp");

ProposalDetailBuilder.DataColumns.Add("update_originator_id");

ProposalDetailBuilder.DataColumns.Add("sync_last_received_anchor");

ProposalDetailBuilder.DataColumns.Add("sync_new_received_anchor");

 

// tombstone table

ProposalDetailBuilder.TombstoneTableName = "ProposalDetail_tombstone";

ProposalDetailBuilder.TombstoneDataColumns.Add("tblID");

ProposalDetailBuilder.TombstoneDataColumns.Add("SQLkey");

ProposalDetailBuilder.TombstoneDataColumns.Add("CreateDateTime");

ProposalDetailBuilder.TombstoneDataColumns.Add("Alias");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDRevNumb");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDLine");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDMfg");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCat");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDList");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDCost");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMDollar");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOGM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDXSellPrice");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDHideValue");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDShipQty");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDOptionalItem");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDUOM");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDStatus");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDGMPercent");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDNotes");

ProposalDetailBuilder.TombstoneDataColumns.Add("PropDItemTotal");

ProposalDetailBuilder.TombstoneDataColumns.Add("create_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_timestamp");

ProposalDetailBuilder.TombstoneDataColumns.Add("update_originator_id");

ProposalDetailBuilder.TombstoneDataColumns.Add("sync_last_received_anchor");

ProposalDetailBuilder.TombstoneDataColumns.Add("sync_new_received_anchor");

// tracking\sync columns

ProposalDetailBuilder.CreationTrackingColumn = @"create_timestamp";

ProposalDetailBuilder.UpdateTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.DeletionTrackingColumn = @"update_timestamp";

ProposalDetailBuilder.UpdateOriginatorIdColumn = @"update_originator_id";

SyncAdapter ProposalDetailSyncAdapter = ProposalDetailBuilder.ToSyncAdapter();

serverSyncProvider.SyncAdapters.Add(ProposalDetailSyncAdapter);

 //

// 6. Setup provider wide commands

// There are two commands on the provider itself and not on a table sync adapter:

// SelectNewAnchorCommand: Returns the new high watermark for current sync, this value is

// stored at the client and used the low watermark in the next sync

// SelectClientIdCommand: Finds out the client ID on the server, this command helps

// avoid downloading changes that the client had made before and applied to the server

//

//

// select new anchor command

SqlCommand anchorCmd = new SqlCommand();

anchorCmd.CommandType = CommandType.Text;

anchorCmd.CommandText = "SELECT GetDate()";

serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

// client ID command (give the client id of 1)

// in remote server scenario (middle tear), this command will reference a local client table for the ID

//SqlCommand clientIdCmd = new SqlCommand();

//clientIdCmd.CommandType = CommandType.Text;

//clientIdCmd.CommandText = "SELECT 1";

//serverSyncProvider.SelectClientIdCommand = clientIdCmd;

//

// 7. Kickoff sync process

//

// Setup the progress form and sync progress event handler

_progressForm = new ProgressForm();

_progressForm.Show();

clientSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);

clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(ShowFailures);

serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);

SyncStatistics syncStats = syncAgent.Synchronize();

// Update the UI

_progressForm.EnableClose();

_progressForm = null;

buttonRefreshOrders_Click(null, null);

//buttonRefreshOrderDetails_Click(null, null);

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

if (_progressForm != null)

{

_progressForm.EnableClose();

_progressForm = null;

}

}

}

#region UI Code

private void buttonExit_Click(object sender, EventArgs e)

{

Application.Exit();

}

public void ShowProgress(object syncAgent, SyncProgressEventArgs args)

{

if (null != _progressForm)

{

_progressForm.Report(args);

}

}

public void ShowFailures(object syncAgent, ApplyChangeFailedEventArgs args)

{

args.Action = ApplyAction.Continue;

}

protected static void RemoveServerTrackingColumns(DataTable dataTable)

{

if (dataTable.Columns.Contains("update_timestamp"))

{

dataTable.Columns.Remove("update_timestamp");

}

if (dataTable.Columns.Contains("create_timestamp"))

{

dataTable.Columns.Remove("create_timestamp");

}

if (dataTable.Columns.Contains("update_originator_id"))

{

dataTable.Columns.Remove("update_originator_id");

}

}

protected static void RemoveClientTrackingColumns(DataTable dataTable)

{

if (dataTable.Columns.Contains("__sysInsertTxBsn"))

{

dataTable.Columns.Remove("__sysInsertTxBsn");

}

if (dataTable.Columns.Contains("__sysChangeTxBsn"))

{

dataTable.Columns.Remove("__sysChangeTxBsn");

}

}

private void buttonRefreshOrders_Click(object sender, EventArgs e)

{

try

{

if (radioSubOrders.Checked)

{

CheckClientDb();

string connString = "Data Source=" + dbPathTextBox.Text;

string commandString = "Select * from ProposalDetail";

SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, connString);

DataTable dataTable = new DataTable();

dataAdapter.Fill(dataTable);

RemoveClientTrackingColumns(dataTable);

dataGridOrders.DataSource = dataTable;

buttonApplyOrdersInserts.Enabled = true;

buttonApplyOrdersUpdates.Enabled = true;

buttonApplyOrdersDeletes.Enabled = true;

}

else

{

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

builder["Data Source"] = textServerMachine.Text;

builder["integrated Security"] = true;

builder["Initial Catalog"] = "ffgscrm";

string commandString = "Select * from ProposalDetail";

SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, builder.ConnectionString);

DataTable dataTable = new DataTable();

dataAdapter.Fill(dataTable);

RemoveServerTrackingColumns(dataTable);

dataGridOrders.DataSource = dataTable;

}

Application.DoEvents();

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

}

}

private void buttonRefreshOrderDetails_Click(object sender, EventArgs e)

{

try

{

if (radioSubOrderDetails.Checked)

{

CheckClientDb();

string connString = "Data Source=" + dbPathTextBox.Text;

string commandString = "Select * from ProposalHeader";

SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, connString);

DataTable dataTable = new DataTable();

dataAdapter.Fill(dataTable);

RemoveClientTrackingColumns(dataTable);

dataGridOrderDetails.DataSource = dataTable;

buttonApplyOrderDetailsInserts.Enabled = true;

buttonApplyOrderDetailsUpdates.Enabled = true;

buttonApplyOrderDetailsDeletes.Enabled = true;

}

else

{

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

builder["Data Source"] = textServerMachine.Text;

builder["integrated Security"] = true;

builder["Initial Catalog"] = "ffgscrm";

string commandString = "Select * from ProposalHeader";

SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, builder.ConnectionString);

DataTable dataTable = new DataTable();

dataAdapter.Fill(dataTable);

RemoveServerTrackingColumns(dataTable);

dataGridOrderDetails.DataSource = dataTable;

}

Application.DoEvents();

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

}

}

private void radioSubOrders_CheckedChanged(object sender, EventArgs e)

{

buttonApplyOrdersInserts.Enabled = true;

buttonApplyOrdersUpdates.Enabled = true;

buttonApplyOrdersDeletes.Enabled = true;

buttonRefreshOrders_Click(sender, e);

}

private void radioPubOrders_CheckedChanged(object sender, EventArgs e)

{

buttonApplyOrdersInserts.Enabled = false;

buttonApplyOrdersUpdates.Enabled = false;

buttonApplyOrdersDeletes.Enabled = false;

}

private void radioSubOrderDetails_CheckedChanged(object sender, EventArgs e)

{

buttonApplyOrderDetailsInserts.Enabled = true;

buttonApplyOrderDetailsUpdates.Enabled = true;

buttonApplyOrderDetailsDeletes.Enabled = true;

buttonRefreshOrderDetails_Click(sender, e);

}

private void radioPubOrderDetails_CheckedChanged(object sender, EventArgs e)

{

buttonApplyOrderDetailsInserts.Enabled = false;

buttonApplyOrderDetailsUpdates.Enabled = false;

buttonApplyOrderDetailsDeletes.Enabled = false;

}

#endregion

#region Random Inserts, Updates and Delets to client DB

private void CheckClientDb()

{

if (false == File.Exists(dbPathTextBox.Text))

{

throw new FileNotFoundException("Client DB not found. You need to Synchronize first before excuting random operations on the local db.");

}

}

private void buttonApplyOrdersInserts_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "INSERT INTO orders(order_id, order_date) values(@order_id, @order_date)";

cmd.Parameters.AddWithValue("@order_id", rand.Next((int)(DateTime.Now.ToFileTime() % 10000)));

cmd.Parameters.AddWithValue("@order_date", DateTime.Now);

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

private void buttonApplyOrderDetailsInserts_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "INSERT INTO order_details(order_id, order_details_id, product, quantity) values(@order_id, @order_details_id, @product, @quantity)";

cmd.Parameters.AddWithValue("@order_id", rand.Next((int)(DateTime.Now.ToFileTime() % 10000)));

cmd.Parameters.AddWithValue("@order_details_id", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));

cmd.Parameters.AddWithValue("@product", "NEW");

cmd.Parameters.AddWithValue("@quantity", rand.Next((int)(DateTime.Now.ToFileTime() % 1000000)));

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

private void buttonApplyOrdersUpdates_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "UPDATE orders SET order_date = @order_date where order_id % @factor < 10";

cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));

cmd.Parameters.AddWithValue("@order_date", DateTime.Now);

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

private void buttonApplyOrderDetailsUpdates_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "UPDATE order_details SET quantity = @quantity, product = @product where order_id % @factor < 10";

cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));

cmd.Parameters.AddWithValue("@product", "UPD");

cmd.Parameters.AddWithValue("@quantity", rand.Next((int)(DateTime.Now.ToFileTime() % 1000000)));

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

private void buttonApplyOrdersDeletes_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "DELETE orders WHERE order_id % @factor < 5";

cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

private void buttonApplyOrderDetailsDeletes_Click(object sender, EventArgs e)

{

Random rand = new Random();

string connString = "Data Source=" + dbPathTextBox.Text;

SqlCeConnection conn = new SqlCeConnection(connString);

SqlCeCommand cmd = new SqlCeCommand();

cmd.CommandText = "DELETE order_details WHERE order_id % @factor < 10";

cmd.Parameters.AddWithValue("@factor", rand.Next((int)(DateTime.Now.ToFileTime() % 1000)));

cmd.Connection = conn;

try

{

CheckClientDb();

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, "Item Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

conn.Close();

}

}

#endregion

}

}






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

 

Here's the SQL Script... had no place to upload a link.... sorry again for the blast!

 

Here is the SQL Script.... ----------------------------------------------------------------------------------------------------------------

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProposalDetail_tombstone]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ProposalDetail_tombstone](

[tblID] [int] IDENTITY(1,1) NOT NULL,

[SQLkey] [int] NOT NULL,

[CreateDateTime] [datetime] NULL,

[Alias] [nvarchar](50) NOT NULL,

[PropDNumb] [nvarchar](10) NOT NULL,

[PropDRevNumb] [nvarchar](30) NULL,

[PropDLine] [numeric](4, 0) NULL,

[PropDItem] [nvarchar](50) NULL,

[PropDMfg] [nvarchar](3) NULL,

[PropDCat] [nvarchar](75) NULL,

[PropDList] [nvarchar](10) NULL,

[PropDCost] [numeric](10, 2) NULL,

[PropDGMDollar] [numeric](10, 2) NULL,

[PropDGM] [numeric](10, 2) NULL,

[PropDOGM] [numeric](10, 2) NULL,

[PropDSellPrice] [numeric](10, 2) NULL,

[PropDXSellPrice] [numeric](10, 2) NULL,

[PropDHideValue] [nvarchar](1) NULL,

[PropDShipQty] [numeric](9, 0) NULL,

[PropDOptionalItem] [nvarchar](1) NULL,

[PropDUOM] [nvarchar](6) NULL,

[PropDStatus] [nvarchar](18) NULL,

[PropDGMPercent] [numeric](10, 2) NULL,

[PropDNotes] [nvarchar](max) NULL,

[PropDItemTotal] [numeric](10, 2) NULL,

[create_timestamp] [bigint] NULL,

[update_timestamp] [bigint] NULL,

[update_originator_id] [nvarchar](50) NULL,

[sync_last_received_anchor] [bigint] NULL,

[sync_new_received_anchor] [bigint] NULL,

CONSTRAINT [PK_ProposalDetail_tombstone] PRIMARY KEY CLUSTERED

(

[tblID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProposalDetail]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ProposalDetail](

[tblID] [int] IDENTITY(1,1) NOT NULL,

[SQLkey] [int] NOT NULL,

[CreateDateTime] [datetime] NULL,

[Alias] [nvarchar](50) NOT NULL,

[PropDNumb] [nvarchar](10) NOT NULL,

[PropDRevNumb] [nvarchar](30) NULL,

[PropDLine] [numeric](4, 0) NULL,

[PropDItem] [nvarchar](50) NULL,

[PropDMfg] [nvarchar](3) NULL,

[PropDCat] [nvarchar](75) NULL,

[PropDList] [nvarchar](10) NULL,

[PropDCost] [numeric](10, 2) NULL,

[PropDGMDollar] [numeric](10, 2) NULL,

[PropDGM] [numeric](10, 2) NULL,

[PropDOGM] [numeric](10, 2) NULL,

[PropDSellPrice] [numeric](10, 2) NULL,

[PropDXSellPrice] [numeric](10, 2) NULL,

[PropDHideValue] [nvarchar](1) NULL,

[PropDShipQty] [numeric](9, 0) NULL,

[PropDOptionalItem] [nvarchar](1) NULL,

[PropDUOM] [nvarchar](6) NULL,

[PropDStatus] [nvarchar](18) NULL,

[PropDGMPercent] [numeric](10, 2) NULL,

[PropDNotes] [nvarchar](max) NULL,

[PropDItemTotal] [numeric](10, 2) NULL,

[create_timestamp] [datetime] NULL,

[update_timestamp] [datetime] NULL,

[update_originator_id] [varchar](max) NULL,

[sync_last_received_anchor] [datetime] NULL,

[sync_new_received_anchor] [datetime] NULL,

CONSTRAINT [PK_ProposalDetail] PRIMARY KEY CLUSTERED

(

[tblID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

Rafik Robeal


Hi bill,

The reason you get type mismatch is that your anchor command generates a new timestamp value (not datetime) at every sync while your tracking columns are of type datetime. Here is the code:

// select new anchor command
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.Text;
anchorCmd.CommandText = "SELECT @@DBTS";
serverSyncProvider.SelectNewAnchorCommand = anchorCmd;

To fix this, simply change the command text to return datetime value instead:

anchorCmd.CommandText = "SELECT GetDate()";

The following two statements should be removed from all adapters:

((SqlParameter)VendorsSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;
((
SqlParameter)VendorsSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = DbType.Binary;

This code fixes up data type that we incorrectly generated by the builder in my demo. In your case, the data type is datetime and builder will set it correctly for you.

Give it another spin with these changes and let me know how it goes.






Re: Microsoft Synchronization Services for ADO.NET Failed to convert Parameter Value from a Byte[] to a Datetime?

billb59

 

Thanks Rafik!