VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky

[Frans Wauters]

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

[andrej Terkaj]

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" <in...@wautersit.com> wrote in message
news:e08bf7ae-8fa1-44d3...@googlegroups.com...

[Malcolm G]

> 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.

[Frans Wauters]

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

[Frans Wauters]

Op vrijdag 24 mei 2013 18:15:47 UTC+2 schreef Malcolm G het volgende:

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