Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #5275 > unrolled thread
| Started by | Frans Wauters <info@wautersit.com> |
|---|---|
| First post | 2013-05-24 07:28 -0700 |
| Last post | 2013-05-24 09:17 -0700 |
| Articles | 5 — 3 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 07:28 -0700
Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky "andrej Terkaj" <aterkaj@task.si> - 2013-05-24 16:53 +0200
Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Malcolm G <malcolm-news2012delete@delete.jobstream.com> - 2013-05-24 17:15 +0100
Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 09:27 -0700
Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky Frans Wauters <info@wautersit.com> - 2013-05-24 09:17 -0700
| From | Frans Wauters <info@wautersit.com> |
|---|---|
| Date | 2013-05-24 07:28 -0700 |
| Subject | VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky |
| Message-ID | <e08bf7ae-8fa1-44d3-b7de-04b64dff7b36@googlegroups.com> |
Hi there,
I’m having a “spooky” problem. I can use INSERT to put in a new row, but when I request this using a SELECT, it does not find it. More, when I do a new INSERT, it seems to update the existent row. I know, spooky, but I when I do a select from SQL Server Management Studio, I see the inserted row and I also see that the content is updated after a new INSERT…
I'm updating some routines to VO2ADO to let my program running with the OLE-DB (VO2ADO), so clients don’t need to prep an ODBC configuration (standard VO SQL).
I create the connect string once from AdoDriverEdit(…) and save this connect string encrypted in a file. Other programs can read this file, decrypt de connect string and connect to the database. If the database does not exist, it will be created with all tables needed.
I create tables like :
FUNCTION SQL_CreateTable( oServer AS OBJECT, cTableName AS STRING, aSQLstruct AS ARRAY ) AS LOGIC
LOCAL oAdoCommand AS AdoCommand
LOCAL cStatement AS STRING
oAdoCommand := AdoCommand{}
oAdoCommand:ActiveConnection := oServer
cStatement := SQL_CreateTableString( cTableName, aSQLstruct )
oAdoCommand:CommandText := cStatement
oAdoCommand:Execute( NIL, NIL ,NIL )
RETURN TRUE
In a nutshell, the content of the cStatement variable will look like this:
CREATE TABLE SYS_Users (
[UserID] NUMERIC PRIMARY KEY IDENTITY,
[Account] varchar(32) not null default '',
[FirstName] varchar(32) not null default '',
[LastName] varchar(32) not null default '',
[Password] varchar(32) not null default '',
[Level] numeric not null default 0,
[ExpireDate] date )
or
CREATE TABLE SYS__VERSIE (
[VERSIE] numeric not null default 0,
[DATUM] date )
and it creates my table.
My insert instruction:
METHOD pbn_Save( ) CLASS dlw_User
LOCAL oAdoCommand AS AdoCommand
LOCAL cStatement AS STRING
cStatement := "INSERT INTO SYS_USERS ( "
cStatement := cStatement + "Account, "
cStatement := cStatement + "FirstName, "
cStatement := cStatement + "LastName, "
cStatement := cStatement + "Password, "
cStatement := cStatement + "Level, "
cStatement := cStatement + "ExpireDate ) VALUES ( "
cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
cStatement := cStatement + AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"
oAdoCommand := AdoCommand{}
oAdoCommand:ActiveConnection := gloServer
oAdoCommand:CommandType := adCmdText
oAdoCommand:CommandText := cStatement
* gloServer:BeginTrans()
oAdoCommand:Execute( NIL, NIL ,NIL )
* gloServer:CommitTrans()
SELF:EndDialog()
RETURN NIL
In a the content of the cStatement variable will look like this:
INSERT INTO SYS_USERS ( Account, FirstName, LastName, Password, Level, ExpireDat
e ) VALUES ( 'FWA', 'Fxxxxx', 'Wxxxxxxxx', 'xxxxxxxxxx', 8, '20130621' )
My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie.
FUNCTION CheckUser( cUserName AS STRING ) AS LOGIC
LOCAL oAdoRecSet AS AdoRecordSet
LOCAL oGebruiker AS dlw_Gebruiker
LOCAL cStatement AS STRING
cStatement := "SELECT * FROM SYS_Users WHERE Account='" + cUserName + "'"
* cStatement := "SELECT * FROM SYS_Users"
* cStatement := "SELECT * FROM SYS__Versie"
oAdoRecSet := AdoRecordset{}
oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly, AdLockReadOnly, adCmdText )
IF oAdoRecSet:RecordCount == 0
oAdoRecSet:Close()
lSuperUser := TRUE
oGebruiker := dlw_Gebruiker{}
oGebruiker:Show( SHOWCENTERED )
RETURN FALSE
ELSE
lSuperUser := FALSE
oAdoRecSet:Close()
ENDIF
RETURN TRUE
Thanks,
Frans
[toc] | [next] | [standalone]
| From | "andrej Terkaj" <aterkaj@task.si> |
|---|---|
| Date | 2013-05-24 16:53 +0200 |
| Message-ID | <knnut5$kg3$1@news.siol.net> |
| In reply to | #5275 |
Hi Frans !
Probably, there is a mistake in a parameter adLockReadOnly --> try
AdLockOptimistic in a method Adorecordset:Open()
- You can try also with:
oSqlSrv := AdoServer{sStatement, oGL_Conn, adOpenForwardOnly,
adLockOptimistic, ,TRUE}
oSqlSrv:Append()
oSqlSrv:FIELDPUT("FieldName_1",newValue)
I hope this will help !
Best regards Andrej Terkaj
"Frans Wauters" <info@wautersit.com> wrote in message
news:e08bf7ae-8fa1-44d3-b7de-04b64dff7b36@googlegroups.com...
Hi there,
I’m having a “spooky” problem. I can use INSERT to put in a new row, but
when I request this using a SELECT, it does not find it. More, when I do a
new INSERT, it seems to update the existent row. I know, spooky, but I when
I do a select from SQL Server Management Studio, I see the inserted row and
I also see that the content is updated after a new INSERT…
I'm updating some routines to VO2ADO to let my program running with the
OLE-DB (VO2ADO), so clients don’t need to prep an ODBC configuration
(standard VO SQL).
I create the connect string once from AdoDriverEdit(…) and save this connect
string encrypted in a file. Other programs can read this file, decrypt de
connect string and connect to the database. If the database does not exist,
it will be created with all tables needed.
I create tables like :
FUNCTION SQL_CreateTable( oServer AS OBJECT, cTableName AS STRING,
aSQLstruct AS ARRAY ) AS LOGIC
LOCAL oAdoCommand AS AdoCommand
LOCAL cStatement AS STRING
oAdoCommand := AdoCommand{}
oAdoCommand:ActiveConnection := oServer
cStatement := SQL_CreateTableString( cTableName, aSQLstruct )
oAdoCommand:CommandText := cStatement
oAdoCommand:Execute( NIL, NIL ,NIL )
RETURN TRUE
In a nutshell, the content of the cStatement variable will look like this:
CREATE TABLE SYS_Users (
[UserID] NUMERIC PRIMARY KEY IDENTITY,
[Account] varchar(32) not null default '',
[FirstName] varchar(32) not null default '',
[LastName] varchar(32) not null default '',
[Password] varchar(32) not null default '',
[Level] numeric not null default 0,
[ExpireDate] date )
or
CREATE TABLE SYS__VERSIE (
[VERSIE] numeric not null default 0,
[DATUM] date )
and it creates my table.
My insert instruction:
METHOD pbn_Save( ) CLASS dlw_User
LOCAL oAdoCommand AS AdoCommand
LOCAL cStatement AS STRING
cStatement := "INSERT INTO SYS_USERS ( "
cStatement := cStatement + "Account, "
cStatement := cStatement + "FirstName, "
cStatement := cStatement + "LastName, "
cStatement := cStatement + "Password, "
cStatement := cStatement + "Level, "
cStatement := cStatement + "ExpireDate ) VALUES ( "
cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "',
"
cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
cStatement := cStatement + AllTrim( Str( oDCcbx_Level:VALUE ) ) +
", "
cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) )
+ "' )"
oAdoCommand := AdoCommand{}
oAdoCommand:ActiveConnection := gloServer
oAdoCommand:CommandType := adCmdText
oAdoCommand:CommandText := cStatement
* gloServer:BeginTrans()
oAdoCommand:Execute( NIL, NIL ,NIL )
* gloServer:CommitTrans()
SELF:EndDialog()
RETURN NIL
In a the content of the cStatement variable will look like this:
INSERT INTO SYS_USERS ( Account, FirstName, LastName, Password, Level,
ExpireDat
e ) VALUES ( 'FWA', 'Fxxxxx', 'Wxxxxxxxx', 'xxxxxxxxxx', 8, '20130621' )
My User test function. This function gives always a 0 (zero) for
RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT *
FROM SYS__Versie.
FUNCTION CheckUser( cUserName AS STRING ) AS LOGIC
LOCAL oAdoRecSet AS AdoRecordSet
LOCAL oGebruiker AS dlw_Gebruiker
LOCAL cStatement AS STRING
cStatement := "SELECT * FROM SYS_Users WHERE Account='" + cUserName + "'"
* cStatement := "SELECT * FROM SYS_Users"
* cStatement := "SELECT * FROM SYS__Versie"
oAdoRecSet := AdoRecordset{}
oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly,
AdLockReadOnly, adCmdText )
IF oAdoRecSet:RecordCount == 0
oAdoRecSet:Close()
lSuperUser := TRUE
oGebruiker := dlw_Gebruiker{}
oGebruiker:Show( SHOWCENTERED )
RETURN FALSE
ELSE
lSuperUser := FALSE
oAdoRecSet:Close()
ENDIF
RETURN TRUE
Thanks,
Frans
[toc] | [prev] | [next] | [standalone]
| From | Malcolm G <malcolm-news2012delete@delete.jobstream.com> |
|---|---|
| Date | 2013-05-24 17:15 +0100 |
| Message-ID | <UmMnt.48257$LC7.45593@fx06.am4> |
| In reply to | #5275 |
> My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie. interesting (I would expect you to get -1 some of the time but zero is a surprise) http://msdn.microsoft.com/en-us/library/windows/desktop/ms676701%28v=vs.85%29.aspx The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
[toc] | [prev] | [next] | [standalone]
| From | Frans Wauters <info@wautersit.com> |
|---|---|
| Date | 2013-05-24 09:27 -0700 |
| Message-ID | <e7987bac-5cd5-4f33-bd5b-0d28a96dee7c@googlegroups.com> |
| In reply to | #5278 |
Op vrijdag 24 mei 2013 18:15:47 UTC+2 schreef Malcolm G het volgende: > > My User test function. This function gives always a 0 (zero) for RecordCount. Even for SELECT * FROM SYS_Users, but not when I do a SELECT * FROM SYS__Versie. > > > > interesting (I would expect you to get -1 some of the time > > but zero is a surprise) > > > > http://msdn.microsoft.com/en-us/library/windows/desktop/ms676701%28v=vs.85%29.aspx > > > > The cursor type of the Recordset object affects whether the number of > > records can be determined. The RecordCount property will return -1 for a > > forward-only cursor; the actual count for a static or keyset cursor; and > > either -1 or the actual count for a dynamic cursor, depending on the > > data source. Hi Malcom, And 0 if there is no error and no row in the table. I found that a subroutine re-created the SYS_Users tabel in the INI section. I Fixed that and I found in the maintime several ways to add data using VO2ADO :) Kind regards, Frans
[toc] | [prev] | [next] | [standalone]
| From | Frans Wauters <info@wautersit.com> |
|---|---|
| Date | 2013-05-24 09:17 -0700 |
| Message-ID | <d809b738-56f9-4e56-a1c1-2a1310fca119@googlegroups.com> |
| In reply to | #5275 |
Hi Terki,
I tried your suggestion... In fact I have created 3 save (test)buttons:
METHOD pbn_Save1( ) CLASS dlw_Gebruiker
LOCAL oAdoRecSet AS AdoRecordSet
LOCAL cStatement AS STRING
cStatement := "INSERT INTO SYS_USERS ( "
cStatement := cStatement + "Account, "
cStatement := cStatement + "FirstName, "
cStatement := cStatement + "LastName, "
cStatement := cStatement + "Password, "
cStatement := cStatement + "Level, "
cStatement := cStatement + "ExpireDate ) VALUES ( "
cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
cStatement := cStatement + AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"
oAdoRecSet := AdoRecordset{}
oAdoRecSet:Open( cStatement, gloServer, adOpenForwardOnly, adLockOptimistic, adCmdText )
RETURN NIL
METHOD pbn_Save2( ) CLASS dlw_Gebruiker
LOCAL oUsers AS AdoServer
oUsers := AdoServer{ 'SYS_USERS', gloServer, adOpenForwardOnly, adLockOptimistic, adCmdTable }
oUsers:Append()
oUsers:FIELDPUT( 'Account', AllTrim( oDCsle_Account:VALUE ) )
oUsers:FIELDPUT( 'FirstName', AllTrim( oDCsle_FirstName:VALUE ) )
oUsers:FIELDPUT( 'LastName', AllTrim( oDCsle_LastName:VALUE ) )
oUsers:FIELDPUT( 'Password', AllTrim( oDCsle_Password:VALUE ) )
oUsers:FIELDPUT( 'Level', oDCcbx_Level:VALUE )
oUsers:FIELDPUT( 'ExpireDate', DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) )
oUsers:Commit()
oUsers:Close()
RETURN NIL
METHOD pbn_Save3( ) CLASS dlw_Gebruiker
LOCAL oAdoCommand AS AdoCommand
LOCAL cStatement AS STRING
cStatement := "INSERT INTO SYS_USERS ( "
cStatement := cStatement + "Account, "
cStatement := cStatement + "FirstName, "
cStatement := cStatement + "LastName, "
cStatement := cStatement + "Password, "
cStatement := cStatement + "Level, "
cStatement := cStatement + "ExpireDate ) VALUES ( "
cStatement := cStatement + "'" + AllTrim( oDCsle_Account:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_FirstName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_LastName:VALUE ) + "', "
cStatement := cStatement + "'" + AllTrim( oDCsle_Password:VALUE ) + "', "
cStatement := cStatement + AllTrim( Str( oDCcbx_Level:VALUE ) ) + ", "
cStatement := cStatement + "'" + DToS( CToD( oDCdtp_ExpiredDate:VALUE ) ) + "' )"
oAdoCommand := AdoCommand{}
oAdoCommand:ActiveConnection := gloServer
oAdoCommand:CommandType := adCmdText
oAdoCommand:CommandText := cStatement
* gloServer:BeginTrans()
oAdoCommand:Execute( NIL, NIL ,NIL )
* gloServer:CommitTrans()
RETURN NIL
All buttons write a row to the table. I think I will go for the pbn_Save2() way: less typing and more readable :)
As all buttons work like it supposed... I was thinking it is something else, so I debug all routines and found a wrong IF switch what recreated the SYS_Users tabel... Oeps, silly me.
Kind regards,
Frans
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.clipper.visual-objects
csiph-web