Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #737 > unrolled thread

Problem with VO- duplicate registers in sql server

Started byJoseMaria <jmespinosabaviera@yahoo.com>
First post2011-04-07 02:48 -0700
Last post2011-04-07 22:25 +0000
Articles 10 — 6 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  Problem with VO- duplicate registers in sql server JoseMaria <jmespinosabaviera@yahoo.com> - 2011-04-07 02:48 -0700
    Re: Problem with VO- duplicate registers in sql server Meinhard Schnoor-Matriciani <msm@appfact.de> - 2011-04-07 13:46 +0200
    Re: Problem with VO- duplicate registers in sql server E®!k \\/!$$E® <nospam@nospam.com> - 2011-04-07 21:27 +0200
      Re: Problem with VO- duplicate registers in sql server JoseMaria <jmespinosabaviera@yahoo.com> - 2011-04-07 14:24 -0700
        Re: Problem with VO- duplicate registers in sql server Karl Faller <k.faller_withoutthat_@onlinehome.de> - 2011-04-07 23:45 +0200
          Re: Problem with VO- duplicate registers in sql server JoseMaria <jmespinosabaviera@yahoo.com> - 2011-04-07 14:54 -0700
            Re: Problem with VO- duplicate registers in sql server "Willie Moore" <williem@wmconsulting.com> - 2011-04-08 08:22 -0500
              Re: Problem with VO- duplicate registers in sql server JoseMaria <jmespinosabaviera@yahoo.com> - 2011-04-08 11:46 -0700
                Re: Problem with VO- duplicate registers in sql server "Willie Moore" <williem@wmconsulting.com> - 2011-04-08 14:46 -0500
        Re: Problem with VO- duplicate registers in sql server "Geoff Schaller" <geoffx@softwarexobjectives.com.au> - 2011-04-07 22:25 +0000

#737 — Problem with VO- duplicate registers in sql server

FromJoseMaria <jmespinosabaviera@yahoo.com>
Date2011-04-07 02:48 -0700
SubjectProblem with VO- duplicate registers in sql server
Message-ID<bdfa758b-f750-4484-b417-a7002c6a98c9@a26g2000vbo.googlegroups.com>
using VO 2.8 sp3 and RDD version of VO2ADO to access SQL server 2008
R2 express:

I have noticed that whenever there are in one table duplicate records
(2 or 3 records with exactly the same information) and I try to delete/
update/insert records to this table I have sometimes problems with
strange crashes in my application.

Is there any easy solution to this problem ?

Is it possible to add one column to each table with a numeric value
that is automatically informed with a different value each time I
insert a record ? I have tried adding column uniqueidentifier to a
table but it is populated with nulls, not values.

Thanks for your help.

[toc] | [next] | [standalone]


#739

FromMeinhard Schnoor-Matriciani <msm@appfact.de>
Date2011-04-07 13:46 +0200
Message-ID<4d9da73a$0$7663$fca84a42@newsspool1.arcor-ip.de>
In reply to#737
JoseMaria,

you apparently haven't worked much with SQL servers, have you ? In SQL a 
table should ALWAYS have a column with values which are unique across 
all columns, so that you can adress every single row uniquely (this is 
referred to as Primary Key). Your approach with a uniqueidentifier is 
one way to do so, but if you don't put the guid's in there when 
inserting, you have to  tell the db to do so automatically, which is 
usually done by a default value in the column definition.

I strongly recommend to get yourself a book about SQL and the way it works.

Regards
Meinhard

Am 07.04.2011 11:48, schrieb JoseMaria:
> using VO 2.8 sp3 and RDD version of VO2ADO to access SQL server 2008
> R2 express:
>
> I have noticed that whenever there are in one table duplicate records
> (2 or 3 records with exactly the same information) and I try to delete/
> update/insert records to this table I have sometimes problems with
> strange crashes in my application.
>
> Is there any easy solution to this problem ?
>
> Is it possible to add one column to each table with a numeric value
> that is automatically informed with a different value each time I
> insert a record ? I have tried adding column uniqueidentifier to a
> table but it is populated with nulls, not values.
>
> Thanks for your help.

[toc] | [prev] | [next] | [standalone]


#744

FromE®!k \\/!$$E® <nospam@nospam.com>
Date2011-04-07 21:27 +0200
Message-ID<906hghFpceU1@mid.individual.net>
In reply to#737
JoseMaria,

In addition of what Meinhard wrote:
The Vo2Ado RDD is not showing you the new added record after an append if 
you have a ClientSide cursor.
This can be solved with a serverside cursor, but that will slow down the 
whole thing dramaticly.
My suggestion will be not using the VO2ADO.RDD at all. You do not take full 
advantage of SqlServer if you use it like a DBF table.
You best use the AdoServer class for data attached to a form, or use your 
own scatter/gather.

I always use a AdoCommand with an Insert command to add records to a table 
and retrieve the PrimaryKey value of the new row with SELECT @@IDENTITY

hth

Erik


"JoseMaria"  wrote in message 
news:bdfa758b-f750-4484-b417-a7002c6a98c9@a26g2000vbo.googlegroups.com...

using VO 2.8 sp3 and RDD version of VO2ADO to access SQL server 2008
R2 express:

I have noticed that whenever there are in one table duplicate records
(2 or 3 records with exactly the same information) and I try to delete/
update/insert records to this table I have sometimes problems with
strange crashes in my application.

Is there any easy solution to this problem ?

Is it possible to add one column to each table with a numeric value
that is automatically informed with a different value each time I
insert a record ? I have tried adding column uniqueidentifier to a
table but it is populated with nulls, not values.

Thanks for your help. 

[toc] | [prev] | [next] | [standalone]


#745

FromJoseMaria <jmespinosabaviera@yahoo.com>
Date2011-04-07 14:24 -0700
Message-ID<f3515e10-f5b1-421f-bc5e-d3e3bc73507c@p3g2000vbv.googlegroups.com>
In reply to#744
Thanks to bothe of you for the help. Meinhard is right I have very
little experience using SQL server.

I have solved the problem of application crashes (for now) by adding a
bigint identity column to each table, so whenever I append a record,
sql server informs this column with a sequential number.

What worries me now is what erik has said, which I don't understand.
Does it mean that if one user appends a record to a table, the other
users in the network are not going to see that record until they close
the database and use it again ?

Regarding the rdd, it is the only solution I know to avoid recoding
hundreds or thousands of code lines, of an application that so far has
used DBFs.

Thanks
Jose Maria

On Apr 7, 9:27 pm, E®!k \\/!$$E® <nos...@nospam.com> wrote:
> JoseMaria,
>
> In addition of what Meinhard wrote:
> The Vo2Ado RDD is not showing you the new added record after an append if
> you have a ClientSide cursor.
> This can be solved with a serverside cursor, but that will slow down the
> whole thing dramaticly.
> My suggestion will be not using the VO2ADO.RDD at all. You do not take full
> advantage of SqlServer if you use it like a DBF table.
> You best use the AdoServer class for data attached to a form, or use your
> own scatter/gather.
>
> I always use a AdoCommand with an Insert command to add records to a table
> and retrieve the PrimaryKey value of the new row with SELECT @@IDENTITY
>
> hth
>
> Erik
>
> "JoseMaria"  wrote in message
>
> news:bdfa758b-f750-4484-b417-a7002c6a98c9@a26g2000vbo.googlegroups.com...
>
> using VO 2.8 sp3 and RDD version of VO2ADO to access SQL server 2008
> R2 express:
>
> I have noticed that whenever there are in one table duplicate records
> (2 or 3 records with exactly the same information) and I try to delete/
> update/insert records to this table I have sometimes problems with
> strange crashes in my application.
>
> Is there any easy solution to this problem ?
>
> Is it possible to add one column to each table with a numeric value
> that is automatically informed with a different value each time I
> insert a record ? I have tried adding column uniqueidentifier to a
> table but it is populated with nulls, not values.
>
> Thanks for your help.

[toc] | [prev] | [next] | [standalone]


#746

FromKarl Faller <k.faller_withoutthat_@onlinehome.de>
Date2011-04-07 23:45 +0200
Message-ID<9qbsp6tfvefmn08rjfeu9ijbb89tv9kek0@4ax.com>
In reply to#745
JoseMaria 
>Does it mean that if one user appends a record to a table, the other
>users in the network are not going to see that record until they close
>the database and use it again ?
No - AFAIK the others will, as fast as your append/insert is written
through. But with a clientside cursor YOU are looking  at a "snapshot"
of the db, taken at the moment you issued your select. So, this cursor
will not reflect any operations you perform later on that table - to
see them, you'll have to repeat the select statement.

Beware, that others might correct me, as my knowlegde of these things
is rather limited ;-)

Karl

[toc] | [prev] | [next] | [standalone]


#747

FromJoseMaria <jmespinosabaviera@yahoo.com>
Date2011-04-07 14:54 -0700
Message-ID<623f0ac9-ab83-480d-85d5-8f83b8822f94@p3g2000vbv.googlegroups.com>
In reply to#746
Karl,

And when do I issue a "select" ? I don't issue selects as I use the
rdd version, I use dbseek( cKey ) to search for records and after that
a "while .not. eof() + dbskip" loop.

You mean every time I do a dbseek I refresh the information and can
see all new data added ?

Thanks
Jose Maria

On Apr 7, 11:45 pm, Karl Faller <k.faller_withoutth...@onlinehome.de>
wrote:
> JoseMaria>Does it mean that if one user appends a record to a table, the other
> >users in the network are not going to see that record until they close
> >the database and use it again ?
>
> No - AFAIK the others will, as fast as your append/insert is written
> through. But with a clientside cursor YOU are looking  at a "snapshot"
> of the db, taken at the moment you issued your select. So, this cursor
> will not reflect any operations you perform later on that table - to
> see them, you'll have to repeat the select statement.
>
> Beware, that others might correct me, as my knowlegde of these things
> is rather limited ;-)
>
> Karl

[toc] | [prev] | [next] | [standalone]


#767

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-08 08:22 -0500
Message-ID<inn25l$ui4$1@speranza.aioe.org>
In reply to#747
Jose,

While this is not a free solution, the quickest way to get a DBF application 
up and running in SQL is to buy Geoff's SQL classes. They do a really good 
job of shielding you from SQL but do not limit you to just DBF style coding. 
As you get more comfortable with SQL, you can start using proper selects 
which will speed up your program.

I have used Geoff's classes and they work.

Regards,
Willie

"JoseMaria"  wrote in message 
news:623f0ac9-ab83-480d-85d5-8f83b8822f94@p3g2000vbv.googlegroups.com...

Karl,

And when do I issue a "select" ? I don't issue selects as I use the
rdd version, I use dbseek( cKey ) to search for records and after that
a "while .not. eof() + dbskip" loop.

You mean every time I do a dbseek I refresh the information and can
see all new data added ?

Thanks
Jose Maria

On Apr 7, 11:45 pm, Karl Faller <k.faller_withoutth...@onlinehome.de>
wrote:
> JoseMaria>Does it mean that if one user appends a record to a table, the 
> other
> >users in the network are not going to see that record until they close
> >the database and use it again ?
>
> No - AFAIK the others will, as fast as your append/insert is written
> through. But with a clientside cursor YOU are looking  at a "snapshot"
> of the db, taken at the moment you issued your select. So, this cursor
> will not reflect any operations you perform later on that table - to
> see them, you'll have to repeat the select statement.
>
> Beware, that others might correct me, as my knowlegde of these things
> is rather limited ;-)
>
> Karl 

[toc] | [prev] | [next] | [standalone]


#770

FromJoseMaria <jmespinosabaviera@yahoo.com>
Date2011-04-08 11:46 -0700
Message-ID<dffada46-6485-4a96-8fa3-8e970d2a2dc7@u12g2000vbf.googlegroups.com>
In reply to#767
Thanks to all for your sugestions and your help.

Jose Maria

[toc] | [prev] | [next] | [standalone]


#771

From"Willie Moore" <williem@wmconsulting.com>
Date2011-04-08 14:46 -0500
Message-ID<innomp$mo0$1@speranza.aioe.org>
In reply to#770
Jose,

As you move into SQL, feel free to ask questions. Most of us who have made 
the jump are willing to help. SQL can be frustrating at times but it is well 
worth the move (in my opinion).

Regards,
Willie

[toc] | [prev] | [next] | [standalone]


#749

From"Geoff Schaller" <geoffx@softwarexobjectives.com.au>
Date2011-04-07 22:25 +0000
Message-ID<4d9e39c6$0$11192$c30e37c6@exi-reader.telstra.net>
In reply to#745
Jose,

> I have solved the problem of application crashes (for now) by adding a
> bigint identity column to each table, so whenever I append a record,
> sql server informs this column with a sequential number.

Do not waste a big int - just use an int. Are you going to add more than 
2,000 billion rows? <g>.

> Does it mean that if one user appends a record to a table, the other
> users in the network are not going to see that record until they close
> the database and use it again ?

No. Simply call Refresh() on the record set. The server class also has a 
refresh. If you are using bBrowser, set up and auto refresh for every 30 
seconds or something and do oa deliberate one prior to an action like 
editing. This can be automated in your base classes.

> Regarding the rdd, it is the only solution I know to avoid recoding
> hundreds or thousands of code lines, of an application that so far has
> used DBFs.

DO NOT use the RDD. It was really only written as an example of the code 
(because you have the source) and cannot be used in a live situation. I 
have a series of classes (which I sell - they are not free) which allows 
you to substantially use your existing code without modification. But 
you can then use pure DFB syntax without having to know SQL.

But I encourage you to learn SQL. Ultimately it is the only way.

Geoff

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web