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


Groups > comp.lang.clipper.visual-objects > #5276

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

From "andrej Terkaj" <aterkaj@task.si>
Newsgroups comp.lang.clipper.visual-objects
Subject Re: VO2ADO - MSSQL 2008 - SELECT/UPDATE/... spooky
Date 2013-05-24 16:53 +0200
Organization news.siol.net
Message-ID <knnut5$kg3$1@news.siol.net> (permalink)
References <e08bf7ae-8fa1-44d3-b7de-04b64dff7b36@googlegroups.com>

Show all headers | View raw


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


Back to comp.lang.clipper.visual-objects | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread


Thread

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

csiph-web