RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx
|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA with Identity column.

Hi Everyone:

I am new to Mobile programming. I am now working on a mobile project. I encounter an issue when I sync the data:

cause I can't modify the schema, so I have to use RDA instead of Merge replication on sql server 2005.

However, There is an identiy column on each table I will pull them down to local mobile database. And I will use those identity columns to connect tables. Even worse, the photo's new name will combine the photoID which is an identity column. There would an issue, if i sync data, the photoID would be same on different local mobile databases. And there would be generate same identity value when users sync data.

How can I avoid those issues? If you have any good ideas, please help me out so that I can meet the project deadline.

Thanks

James

Anyone knows how to manually handle identity management.

Cause my identity column will clash when Sync data.

James

|||

You need to call

ALTER TABLE <TableName> ALTER COLUMN <ColumnName> <ColumnDataType> IDENTITY(<Seed>,<Step>)

to set the different identity on the pulled table.

Note, only Seed and Step can be changed in the above table.

Thanks,

Laxmi Narsimha Rao ORUGANTI

|||

Hi Laxmi,

Thank you for your reply. I realize that I can use the command you gave to change the SEED range to avoid the clash. But if I change seed, when I push the data back to Remote SQL server 2005 database, should I disable the identity column on remote database, then push mobile client table data, then set identity column back on remote DB. It seems lots of work to manually handle identity column clash. Do we have any alternative way to do it, since I can't use merge replication to change the database schema, what about XML web service? I think should be same, but if we use Web Service, the performance should be down 10-15%, that's why i don't wanna use it since we choose SQL server 2005 as our database.

Your reply is really appreciated. Thanks!

James

|||

I need to point is that someone will work on remote db through classical ASP. If I change the identity Seed. It will effect mobile client?

If anybody has the same issues?

Thanks

James

|||

No need to worry about disabling the IDENTITY on server. RDA while pushing automatically does "SET IDENTITY_INSERT <TableName> ON/OFF" on SQL Server to get the same IDENTITY column values onto the server table.

Thanks,

Laxmi

|||

Thank Laxmi,

your reply does help me. I will try it out. But we have 200 hundred of Mobile clients. We have to set the identity range for each one.

Any other good ideas for solving the identity clash.

Thanks.

James

|||That is why there is Merge Replication! Why not go for that?|||

Because I can't modify the Sql server 2005 DB schema. They are using identity ID. I wish I could use Merge replication. I can save my code lines as well.

Thank you . Laxmi. You are the rock!

James

|||Same here, merge replication is not a option.

Anyone found a suitable solution for this challenge?

Thanx|||What I have done is to set the ID range for each person. So that they won't clash.

RDA Push with Identity column

Hi Everyone:

I am new to Mobile programming. I am now working on a small mobile app.

I encounter an issue when I sync the data:

Using RDA Pull, I can create the database and populate my table fine on my pocket pc device.

After updating the data on the device, I encounter an error when I try to Push my table back to the back-end SQL Server 2005 DB.

Error: "The identity range was not established."

VB CODE:
Private Sub RdaConnKeyHeaderPush()

Dim RemoteAccess As New Data.SqlServerCe.SqlCeRemoteDataAccess
Dim RDAConnectingString As String
RemoteAccess.InternetLogin = "<mylogin>"
RemoteAccess.InternetPassword = "<mypassword>"
RDAConnectingString = "Provider=SQLOLEDB;Persist Security Info=True;Data Source=<MySQLServer>;Initial Catalog=MobileKeyDB;" & _
"User ID=MobileUser;Password=<mypassword>"
RemoteAccess.InternetUrl = "https://www.<mysite>.com/SQLMobile/sqlcesa30.dll"
RemoteAccess.LocalConnectionString = "Data Source=Program Files\SQL Mobile\en\MobileKeyDB.sdf; Password = <mypassword>"
Cursor.Current = Cursors.WaitCursor
Try

RemoteAccess.Push("KeyHeader", RDAConnectingString, Data.SqlServerCe.RdaBatchOption.BatchingOn)

Catch RDAConnectionException As Exception
MessageBox.Show("Can not push Header Data: " & RDAConnectionException.ToString, "Loading Key Tracker")
Finally

RemoteAccess.Dispose()
End Try
Cursor.Current = Cursors.Default
End Sub

TABLE DEFINITION:
USE [MobileKeyDB]
GO
/****** Object: Table [dbo].[KeyHeader] Script Date: 07/11/2007 09:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyHeader](
[trans_id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [int] NOT NULL,
[date_stamp] [datetime] NOT NULL,
[signature] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[status] [int] NOT NULL,
[id] [int] NOT NULL,
[date_stamp2] [datetime] NOT NULL,
CONSTRAINT [PK_KeyHeader] PRIMARY KEY CLUSTERED
(
[trans_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Anyone have any insight on a solution? Thanks.

For synch scenarios, you should always avoid identity columns, for an elaboration, see http://blogs.msdn.com/synchronizer/archive/2007/06/28/stay-away-from-auto-increment-columns.aspx|||

Ok....then I will try using the unique identifier.

My problem is here that I am unsure of how to add the guid to my insert statement in my application.

I have a dataset created that has an Insert menthod for a my table.

How do I set the properties of this Insert statement to use a GUID in it's execution?

Thanks.

|||Modify your insert command to use NEWID(), see http://msdn2.microsoft.com/en-us/library/ms173249.aspx|||

Ok...thanks for your help.

I tried inserting the NEWID() method into my sql insert statement, however when I run my application in debug mode,

I get an Null Value Exception on that UniqueIdentifier field....what would the best way to identify where and why this is happening?

The error occurs when I try to close the form by clicking 'ok' after entering a record......

Error....

NoNullAllowedException was unhandled

Column 'ID' does not allow nulls.

Code Point where error is generated....

Private Sub KeysEditViewDialog_Closing(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

Me.KeysBindingSource.EndEdit()

End Sub

Some Dataset Code.....is the null error on the sql side or app side, I am not sure of this?

Me._adapter.InsertCommand = New System.Data.SqlServerCe.SqlCeCommand

Me._adapter.InsertCommand.Connection = Me.Connection

Me._adapter.InsertCommand.CommandText = "INSERT INTO Keys"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&" (key_number, key_desc, building, inv_qty,"& _

" avail_qty, id)"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"VALUES (@.p1, @.p2, @.p3, @.p4, @.p5, NEWID())"

Me._adapter.InsertCommand.CommandType = System.Data.CommandType.Text

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p1"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_number"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p2"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "key_desc"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p3"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "building"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p4"

param.DbType = System.Data.DbType.Int32

param.Size = 4

param.IsNullable = true

param.SourceColumn = "inv_qty"

Me._adapter.InsertCommand.Parameters.Add(param)

param = New System.Data.SqlServerCe.SqlCeParameter

param.ParameterName = "@.p5"

param.Size = 100

param.IsNullable = true

param.SourceColumn = "avail_qty"

Me._adapter.InsertCommand.Parameters.Add(param)