V02Ado

Testing Vo2Ado Trial Version with VO2.6. SQL is MS 2005 and ADO 2.8.
Having problem with Append(), Deleted() and FieldPut() ( these are
the few I've tested). Error reported that FUNCTION NOT SUPPORTED.
APPEND NOT SUPPORTED BY PROVIDED.

What does this means? Can't I use the common dbServer functions? Why
should I look for the resolve this issue SQL server or VO2Ado? I'm use
ADOServer and ADOCommand.

Connection is
_SQLConnect := AdoConnection{}
_SQLConnectrovider := "SQLOLEDB"
_SQLConnect:cString := "Network Library=DBMSSOCN;" + ;
"Data Source=Home\SQL2005;" + ;
"Initial Catalog=TestDb;" + ;
"Trusted_Connection=yes;"
_SQLConnect:Open(_SQLConnect:cString,NIL,NIL,NIL)_ SQLConnect:CursorLocation :=
AdUseClient

_Svar_1:= "SELECT * FROM TestTable01"
oSqlSrv := AdoServer{_Svar_1, _SQLConnect}



Thanks.

Than Chan Weng,

For SQLServer 2005 you'll need another client/provider
I always include the provider in the connectinostring, do not know if that
should make any difference.
Try this:
_SqlConnectionString := "Provider=SQLNCLI;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=HavenSQL;Data "+;:
"Source=SCALEO\SQL2005;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=SCALEO;Use Encryption for
""+; "Data=False;Tag with column collation when possible=False;MARS
Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False

Another thing is, that for the use editing methods, you'll need a primary
key in the table.
Have a look at the samples that come with VO2ADO.

hth

Erik


"tham chan weng" <tcw@multiplex.com.my> schreef in bericht
news:be5c50d6-3079-4b96-a5bf-c6f23cf6a12d@i29g2000prf.googlegroups.com...
> Testing Vo2Ado Trial Version with VO2.6. SQL is MS 2005 and ADO 2.8.
> Having problem with Append(), Deleted() and FieldPut() ( these are
> the few I've tested). Error reported that FUNCTION NOT SUPPORTED.
> APPEND NOT SUPPORTED BY PROVIDED.
>
> What does this means? Can't I use the common dbServer functions? Why
> should I look for the resolve this issue SQL server or VO2Ado? I'm use
> ADOServer and ADOCommand.
>
> Connection is
> _SQLConnect := AdoConnection{}
> _SQLConnectrovider := "SQLOLEDB"
> _SQLConnect:cString := "Network Library=DBMSSOCN;" + ;
> "DaThta Source=Home\SQL2005;" + ;
> "Initial Catalog=TestDb;" + ;
> "Trusted_Connection=yes;"
> _SQLConnect:Open(_SQLConnect:cString,NIL,NIL,NIL)_ SQLConnect:CursorLocation
> :=
> AdUseClient
>
> _Svar_1:= "SELECT * FROM TestTable01"
> oSqlSrv := AdoServer{_Svar_1, _SQLConnect}
>
>
>
> Thanks.

Hi Erik,

Doesn't make any difference. Thanks.

Tham,

Assuming your select works (does it? You didn't say), you didn't show us
the code you used to do the update. Perhaps you are trying to update a
static cursor? You didn't show enough code to tell.

For example, use my SQLMaster tool to access your database and look at
the connection string it generates. Use SQLMaster to add rows to your
tables (it is like a DBF tool) and inspect the code it generates to do
this. SQLMaster gives you a perfect code template to do stuff in your
apps. It even generates you methods and VO or C# code to use in your
apps once you perfect statements etc.

Geoff


"tham chan weng" <tcw@multiplex.com.my> wrote in message
news:be5c50d6-3079-4b96-a5bf-c6f23cf6a12d@i29g2000prf.googlegroups.com:

> Testing Vo2Ado Trial Version with VO2.6. SQL is MS 2005 and ADO 2.8.
> Having problem with Append(), Deleted() and FieldPut() ( these are
> the few I've tested). Error reported that FUNCTION NOT SUPPORTED.
> APPEND NOT SUPPORTED BY PROVIDED.
>
> What does this means? Can't I use the common dbServer functions? Why
> should I look for the resolve this issue SQL server or VO2Ado? I'm use
> ADOServer and ADOCommand.
>
> Connection is
> _SQLConnect := AdoConnection{}
> _SQLConnectrovider := "SQLOLEDB"
> _SQLConnect:cString := "Network Library=DBMSSOCN;" + ;
> "Data Source=Home\SQL2005;" + ;
> "Initial Catalog=TestDb;" + ;
> "Trusted_Connection=yes;"
> _SQLConnect:Open(_SQLConnect:cString,NIL,NIL,NIL)_ SQLConnect:CursorLocation :=
> AdUseClient
>
> _Svar_1:= "SELECT * FROM TestTable01"
> oSqlSrv := AdoServer{_Svar_1, _SQLConnect}
>
>
>
> Thanks.

On Nov 17, 11:28 am, tham chan weng <t...@multiplex.com.my> wrote:
> Testing Vo2Ado Trial Version with VO2.6. SQL is MS 2005 and ADO 2.8.
> Having problem with Append(), Deleted() and FieldPut() ( these are
> the few I've tested). Error reported that FUNCTION NOT SUPPORTED.
> APPEND NOT SUPPORTED BY PROVIDED.
>
> What does this means? Can't I use the common dbServer functions? Why
> should I look for the resolve this issue SQL server or VO2Ado? I'm use
> ADOServer and ADOCommand.
>
> Connection is
> _SQLConnect := AdoConnection{}
> _SQLConnectrovider := "SQLOLEDB"
> _SQLConnect:cString := "Network Library=DBMSSOCN;" + ;
> "Data Source=Home\SQL2005;" + ;
> "Initial Catalog=TestDb;" + ;
> "Trusted_Connection=yes;"
> _SQLConnect:Open(_SQLConnect:cString,NIL,NIL,NIL)_ SQLConnect:CursorLocation :=
> AdUseClient
>
> _Svar_1:= "SELECT * FROM TestTable01"
> oSqlSrv := AdoServer{_Svar_1, _SQLConnect}
>
> Thanks.

Hey,

Looking at your code, I suspect that the issue is with the locking and
cursor type. The default for ADOServer is adOpenForwardOnly,
adLockReadOnly

If you change your ADOServer line to this, it should work:
oSqlSrv := AdoServer{_Svar_1,
_SQLConnect,adOpenDynamic,adLockOptimistic}


Regards,

Willie

Hi Willie,

Thanks. You are getting me somewhere. I don't get the error message
but still in Append I only get a blank row.

? Self:server:Append // returns True
? self:server:used // returns true
? self:server:Cust_Name // returns NIL
? SELFDCsleCUST_NAME:TextValue // returns correct input 'TEST'
? self:server:Cust_Name := selfDCsleCUST_NAME:TextValue
? self:server:Cust_Name // still returns NIL

The table only contains 1 field and no inkey key. NULL value is
allowed.

If I use strict T-SQL INSERT/DELETE I have no problem but do not wish
to rewrite my old programs that used std dbserver syntax. Maybe I have
been naive to think that by adding the connection to server and change
the way to open table, the rest will be the same as dbserver. But this
is what I understand by reading VO SQL Clas and VO2Ado Class.

Hey again,

After you do your append, write your data, make sure you do a commit. Otherwise the data will not be written back. Looking at the code snippet, I cannot say that I have ever done the self:server:cust_name := syntax with an ADOServer. Robert wil have to chime in to say it it is allowed. I to my fieldputs like this:
self:Server:fieldput("Cust_name", selfDCsleCust_Name:TextValue)

Regards,

Willie


"tham chan weng" <tcw@multiplex.com.my> wrote in message news:1bd903eb-4ddd-4667-a5c6-1caf0d4bf31a@e25g2000prg.googlegroups.com...
> Hi Willie,
>
> Thanks. You are getting me somewhere. I don't get the error message
> but still in Append I only get a blank row.
>
> ? Self:server:Append // returns True
> ? self:server:used // returns true
> ? self:server:Cust_Name // returns NIL
> ? SELFDCsleCUST_NAME:TextValue // returns correct input 'TEST'
> ? self:server:Cust_Name := selfDCsleCUST_NAME:TextValue
> ? self:server:Cust_Name // still returns NIL
>
> The table only contains 1 field and no inkey key. NULL value is
> allowed.
>
> If I use strict T-SQL INSERT/DELETE I have no problem but do not wish
> to rewrite my old programs that used std dbserver syntax. Maybe I have
> been naive to think that by adding the connection to server and change
> the way to open table, the rest will be the same as dbserver. But this
> is what I understand by reading VO SQL Clas and VO2Ado Class.

Hi Willie,
On Tue, 20 Nov 2007, at 07:16:05 [GMT EST] (which was 13:15 where I live)
you wrote about: 'V02Ado'

> After you do your append, write your data, make sure you do a
> commit. Otherwise the data will not be written back.
> Looking at the code snippet, I cannot say that I have ever done the
> self:server:cust_name := syntax with an
> ADOServer. Robert wil have to chime in to say it it is allowed. I to my fieldputs like this:
> self:Server:fieldput("Cust_name", selfDCsleCust_Name:TextValue)



self:server:cust_name := selfDCsleCust_Name:TextValue

This should work, but is redirected to

self:Server:fieldput("Cust_name", selfDCsleCust_Name:TextValue)

through the NoIvarPut() method in the AdoServer class.


--
Robert van der Hulst
AKA Mr. Data
Vo2Jet & Vo2Ado Support
VO & Vulcan.NET Development Team
www.heliks.nl

Tham.

You have two ways out: override the ADOServer class methods with your
own (to create SQL statements behind the scenes) or show us the full
code. You must be doing something like missing a Commit or something. It
will be depressingly simple when you find it. Remember that the cursor
you are using must be an updatable one and not client side and this is
not recommended for apps like VO ones. Perhaps if you create a small
demo app we can help. Seeing the code is all.

Alternately you can do what we did and using VO2Ado, write a provider
class that completely controls SQL access. The benefit is full DBF
syntax compatibility (ultra-minimal code rewrites to do... but there
will always be some) yet full access to the SQL engine. It has taken us
3 years to perfect but this approach also allows us to work to any DBMS
we care to implement. Not just MS SQL.

So, show us your code.

Geoff



"tham chan weng" <tcw@multiplex.com.my> wrote in message
news:1bd903eb-4ddd-4667-a5c6-1caf0d4bf31a@e25g2000prg.googlegroups.com:

> Hi Willie,
>
> Thanks. You are getting me somewhere. I don't get the error message
> but still in Append I only get a blank row.
>
> ? Self:server:Append // returns True
> ? self:server:used // returns true
> ? self:server:Cust_Name // returns NIL
> ? SELFDCsleCUST_NAME:TextValue // returns correct input 'TEST'
> ? self:server:Cust_Name := selfDCsleCUST_NAME:TextValue
> ? self:server:Cust_Name // still returns NIL
>
> The table only contains 1 field and no inkey key. NULL value is
> allowed.
>
> If I use strict T-SQL INSERT/DELETE I have no problem but do not wish
> to rewrite my old programs that used std dbserver syntax. Maybe I have
> been naive to think that by adding the connection to server and change
> the way to open table, the rest will be the same as dbserver. But this
> is what I understand by reading VO SQL Clas and VO2Ado Class.

Hi guys,

Below are extracts of my codes trimmed down but still long. The best I
can do to shorten but need to keep the essentials.

gobal _SQLConnect as object
gobal oSqlSrv_Cust as object
gobal oSqlSrv_Sales as object


Method Start() App
local oMainWindow AS MscMainMenuShellWindow

_SQLConnect := AdoConnection{}
_SQLConnect:ConnectionString := "Provider=SQLOLEDB;" + ;
"Integrated Security=SSPI;" + ;
"Persist Security Info=FALSE;" + ;
"Initial Catalog=MSC;" + ;
"Data Source=MSC-DELL\SQLEXPRESS;"+;
"Use PROCEDURE FOR Prepare=1;"+;
"Auto Translate=TRUE;"+;
"Packet Size=4096;"+;
"Use Encryption FOR Data=FALSE;"+;
"Tag WITH column collation when
possible=FALSE;"+;
"MARS Connection=FALSE;"+;
"DataTypeCompatibility=0;"+;
"Trust Server Certificate=FALSE;"

_SQLConnect:CursorLocation := adUseClient // see error 2. if set to
adUseServer see error 1
_SQLConnect:Open(_SQLConnect:ConnectionString,NIL, NIL,NIL)
AdoSetConnection(_SQLConnect)

oMainWindow := MscMainMenuShellWindow{SELF}
oMainWindow:Show(SHOWZOOMED) //
oMainWindow:Menu := MainMenuShellMenu{}

SELF:exec()

RETURN



METHOD _M_Cust() CLASS MscMainMenuShellWindow
LOCAL oWindow AS OBJECT
Local oTemp_Dbf AS dbserver
local _Svar_1 AS string

BEGIN SEQUENCE

// Opening Data Server
_Svar_1:= "SELECT * FROM TestTable01"
oSqlSrv_Cust := AdoServer{_Svar_1,
_SQLConnect,adOpenDynamic,adLockOptimistic}
oSqlSrv_Cust:NullAsBlank := TRUE
IF oSqlSrv_Cust==NULL_OBJECT .or. oSqlSrv_Cust:Used == false
.........Message
Break
ENDIF

_Svar_1:= "SELECT * FROM TestTable02"
oSqlSrv_Sales := AdoServer{_Svar_1,
_SQLConnect,adOpenDynamic,adLockOptimistic}
oSqlSrv_Sales :NullAsBlank := TRUE
IF ooSqlSrv_Sales ==NULL_OBJECT .or. oSqlSrv_Sales :Used == false
.........Message
Break
ENDIF

oTemp_Dbf := dbServer("c:\temp\tmp_001",DBEXCLUSIVE,DBREADWRITE )

oWindow := _M_Cust_BrowWin{SELF,,oTemp_Dbf,}

RECOVER
QUIT
END SEQUENCE

RETURN SELF
ACCESS oTemp_Dbf() CLASS MscMainMenuShellWindow
RETURE oTemp_Dbf

CLASS _M_Cust_BrowWin INHERIT DATAWINDOW
PROTECT oDCbSubWin_Cust AS bBrowser
PROTECT oDCbSubWin_Sales AS bBrowser
PROTECT oCCpbAdd_Cust AS MultiHoverButton
PROTECT oCCpbEdit_Cust AS MultiHoverButton
PROTECT oCCpbDelete_Cust AS MultiHoverButton
PROTECT oCCpbAdd_Sales AS MultiHoverButton
PROTECT oCCpbEdit_Sales AS MultiHoverButton
PROTECT oCCpbDelete_Sales AS MultiHoverButton
PROTECT oCCpbClose AS MultiHoverButton

//{{%UC%}} USER CODE STARTS HERE (do NOT remove this line)

METHOD Init(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_BrowWin

........ Normal Init() with 2 bBrowser and 7 buttons
self:Caption := ""
self:HyperLabel :=
HyperLabel{#_M_Cust_BrowWin ,NULL_STRING,NULL_STRING,NULL_STRING}
self:PreventAutoLayout := True

if !IsNil(oServer)
self:Use(oServer)
endif

self:PostInit(oWindow,iCtlID,oServer,uExtra)

return self

METHOD PostInit(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_BrowWin

SUPER:PostInit(oWindow,iCtlID,oServer,uExtra)

//***********bBrowser for Customer
SELFDCbSubWin_Cust:Use(oSqlSrv_Cust, {#CUST_NAME} )
SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Cus t)
SELFDCbSubWin_Cust:EnableColumnResize(FALSE)
SELFDCbSubWin_Cust:EnableColumnMove(FALSE)
SELFDCbSubWin_Cust:Freeze := 1

SELFDCbSubWin_Cust:CaptionHeight := 18 // bBrowser
Caption Height
// SELFDCbSubWin_Cust:FooterHeight := 18

SELFDCbSubWin_Cust:FooterView :=bViewStyle{ ,, ,Font{,
9,"Arial" },bGrid{BGRID_CONVEX} }
SELFDCbSubWin_Cust:FooterView:Font:Bold := TRUE

// Set Column Header #CUST_NAME
_zlO_Column_Header := SELFDCbSubWin_Cust:GetColumn("CUST_NAME")
_zlO_Column_Header:HyperLabel := HyperLabel{#CUST_NAME}
_zlO_Column_Header:caption := "#Name of Payee"
_zlO_Column_Header:Alignment := BALIGN_LEFT
_zlO_Column_Header:Width := 300 // calculate width of
column

SELFDCbSubWin_Cust:Refresh()
//***********bBrowser for Customer


//***********bBrowser for Sales
SELFDCbSubWin_Sales:Use(oSqlSrv_Cust, {#CUST_NAME} )
SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Cus t)
SELFDCbSubWin_Cust:EnableColumnResize(FALSE)
SELFDCbSubWin_Cust:EnableColumnMove(FALSE)
SELFDCbSubWin_Cust:Freeze := 1

SELFDCbSubWin_Cust:CaptionHeight := 18 // bBrowser
Caption Height
// SELFDCbSubWin_Cust:FooterHeight := 18

SELFDCbSubWin_Cust:FooterView :=bViewStyle{ ,, ,Font{,
9,"Arial" },bGrid{BGRID_CONVEX} }
SELFDCbSubWin_Cust:FooterView:Font:Bold := TRUE

// Set Column Header #CUST_NAME
_zlO_Column_Header := SELFDCbSubWin_Cust:GetColumn("CUST_NAME")
_zlO_Column_Header:HyperLabel := HyperLabel{#CUST_NAME}
_zlO_Column_Header:caption := "#Name of Customer"
_zlO_Column_Header:Alignment := BALIGN_LEFT
_zlO_Column_Header:Width := 300 // calculate width of
column

SELFDCbSubWin_Cust:Refresh()
//***********bBrowser for Customer

//***********bBrowser for Sales Man
SELFDCbSubWin_Sales:Use(oSqlSrv_Cust, {#CUST_NAME} )
SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Sal es)
SELFDCbSubWin_Sales:EnableColumnResize(FALSE)
SELFDCbSubWin_Sales:EnableColumnMove(FALSE)
SELFDCbSubWin_Sales:Freeze := 1

SELFDCbSubWin_Sales:CaptionHeight := 18 // bBrowser
Caption Height
// SELFDCbSubWin_Sales:FooterHeight := 18

SELFDCbSubWin_Sales:FooterView :=bViewStyle{ ,, ,Font{,
9,"Arial" },bGrid{BGRID_CONVEX} }
SELFDCbSubWin_Sales:FooterView:Font:Bold := TRUE

// Set Column Header #SM_NAME
_zlO_Column_Header := SELFDCbSubWin_Sales:GetColumn("SM_NAME")
_zlO_Column_Header:HyperLabel := HyperLabel{#SM_NAME}
_zlO_Column_Header:caption := "#Name of SalesMan"
_zlO_Column_Header:Alignment := BALIGN_LEFT
_zlO_Column_Header:Width := 300 // calculate width of
column

SELFDCbSubWin_Sales:Refresh()
//***********bBrowser for Sales Man

RETURN NIL

METHOD pbAdd_Cust() CLASS _M_Cust_BrowWin // Method for Call on Push
Button - ADD
_M_Cust_ADDWin{SELF:Owner,"Add",oSqlSrv_Cust, }
RETURN NIL

CLASS _M_Cust_ADDWin INHERIT DATAWINDOW

PROTECT oCCpbClose AS MultiHoverButton
PROTECT oCCpbSave AS MultiHoverButton
PROTECT oDCsleCUST_NAME AS SINGLELINEEDIT
INSTANCE sleCUST_NAME

METHOD PostInit(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_ADDWin
//Put your PostInit additions here

SUPER:PostInit(oWindow,iCtlID,oServer,uExtra)

SELF:Show()
RETURN NIL

METHOD pbAdd() CLASS _M_Cust_ADDWin // Method for UPDATING RECORD
LOCAL _zlL_Locked AS LOGIC

_zlL_Locked := self:Server:lock()

IF _zlL_Locked
SELF:server:Append()
SELF:server:CUST_NAME := SELFDCsleCUST_NAME:TextValue
//SELF:Server:FIELDPUT("Cust_name",
AllTrim(SELFDCsleCUST_NAME:TextValue) ) // no difference
ENDIF

SELF:server:Commit()
SELF:server:UnLock()
SELF:Server:Notify(NOTIFYFILECHANGE)
SELFwnerDCbSubWin_Cust:ReFresh()
RETURN NIL


Error 1 using _SQLConnect:CursorLocation := adUseServer during
connection
// when self:server:Append() is called
SELF:SERVER:APPEND()
? SELF:server:CUST_NAME // returns blank
? AllTrim(SELFDCsleCUST_NAME:TextValue) // returns correct value
SELF:server:CUST_NAME := SELFDCsleCUST_NAME:TextValue
? SELF:server:CUST_NAME // still returns blank

bBrowser is empty (oDCbSubWin_Cust)
Using SQL Master and SQL Server Management Studio Express show row
added but all field empty.


Error 2 using _SQLConnect:CursorLocation := adUseClient during
connection
// when self:server:Append() is called
SELF:SERVER:APPEND()

Error Message

Error Code : Empty row cannot be inserted. Row must have at lease one
column value set
SubSystem : Vo2Ado
Error Subcode : 16389
Function : Append

Sql = MS Sql Server 2005
Ado = Ver 2.8
Vo = 2.6
Vo2Ado = Trail Pro Verion

Further to my earlir post. Using T-SQL INSERT to replace
SELF:SERVER:Append() works perfectly.

Thanks guys. Your assistance appreciated.

Tham,

You are just running into 'usual' contention issues for SQL. If you use
a client side cursor, take off the auto commit, populate the fields and
then perform the append. You won't be able to append the blank row. If
you use the server side cursor you may need to refresh the record set
before the commit. But you should see the data after the commit.

I don't like doing things this way. I prefer my Commit statement to
execute the update or insert. This is particularly important if you have
not null fields or auto-increment fields. We can't see your database
design here so I don't know what else you have included.

You should avoid dynamic cursors. They are expensive in terms of
resources and can be slow. The AdoServer class also has some limitations
in its implementation of these dynamic updates. Unfortunately you will
just need to experiment.

Geoff


"tham chan weng" <tcw@multiplex.com.my> wrote in message
news:48f5b438-412a-4828-b1fc-605784dde659@a28g2000hsc.googlegroups.com:

> Further to my earlir post. Using T-SQL INSERT to replace
> SELF:SERVER:Append() works perfectly.
>
> Thanks guys. Your assistance appreciated.

Hi tham,
On Tue, 20 Nov 2007, at 22:26:12 [GMT -0800 (PST)] (which was 7:26 where I live)
you wrote about: 'V02Ado'

> Further to my earlir post. Using T-SQL INSERT to replace
> SELF:SERVER:Append() works perfectly.

> Thanks guys. Your assistance appreciated.


The problem is very likely the fact that you are using the server on a
Browser. The Sever:Append() triggers a refresh of the browser. The
browser then repaints itself by moving the record pointer.
As a result of this record pointer movement the newly appended record
is committed to the server and this causes the validation on the
server to fail.

This issue is documented in the 'Known Issues' section of the Vo2Ado
readme.





--
Robert van der Hulst
AKA Mr. Data
Vo2Jet & Vo2Ado Support
VO & Vulcan.NET Development Team
www.heliks.nl

Tham Chan Weng,

You could try to put a oAdoServer:SuspendNotification() before trhe append.
Works fine for me
Erik


"tham chan weng" <tcw@multiplex.com.my> schreef in bericht
news:e7383019-7bae-456d-be67-7098e51ce565@b36g2000hsa.googlegroups.com...
> Hi guys,
>
> Below are extracts of my codes trimmed down but still long. The best I
> can do to shorten but need to keep the essentials.
>
> gobal _SQLConnect as object
> gobal oSqlSrv_Cust as object
> gobal oSqlSrv_Sales as object
>
>
> Method Start() App
> local oMainWindow AS MscMainMenuShellWindow
>
> _SQLConnect := AdoConnection{}
> _SQLConnect:ConnectionString := "Provider=SQLOLEDB;" + ;
> "Integrated Security=SSPI;" + ;
> "Persist Security Info=FALSE;" + ;
> "Initial Catalog=MSC;" + ;
> "Data Source=MSC-DELL\SQLEXPRESS;"+;
> "Use PROCEDURE FOR Prepare=1;"+;
> "Auto Translate=TRUE;"+;
> "Packet Size=4096;"+;
> "Use Encryption FOR Data=FALSE;"+;
> "Tag WITH column collation when
> possible=FALSE;"+;
> "MARS Connection=FALSE;"+;
> "DataTypeCompatibility=0;"+;
> "Trust Server Certificate=FALSE;"
>
> _SQLConnect:CursorLocation := adUseClient // see error 2. if set to
> adUseServer see error 1
> _SQLConnect:Open(_SQLConnect:ConnectionString,NIL, NIL,NIL)
> AdoSetConnection(_SQLConnect)
>
> oMainWindow := MscMainMenuShellWindow{SELF}
> oMainWindow:Show(SHOWZOOMED) //
> oMainWindow:Menu := MainMenuShellMenu{}
>
> SELF:exec()
>
> RETURN
>
>
>
> METHOD _M_Cust() CLASS MscMainMenuShellWindow
> LOCAL oWindow AS OBJECT
> Local oTemp_Dbf AS dbserver
> local _Svar_1 AS string
>
> BEGIN SEQUENCE
>
> // Opening Data Server
> _Svar_1:= "SELECT * FROM TestTable01"
> oSqlSrv_Cust := AdoServer{_Svar_1,
> _SQLConnect,adOpenDynamic,adLockOptimistic}
> oSqlSrv_Cust:NullAsBlank := TRUE
> IF oSqlSrv_Cust==NULL_OBJECT .or. oSqlSrv_Cust:Used == false
> .........Message
> Break
> ENDIF
>
> _Svar_1:= "SELECT * FROM TestTable02"
> oSqlSrv_Sales := AdoServer{_Svar_1,
> _SQLConnect,adOpenDynamic,adLockOptimistic}
> oSqlSrv_Sales :NullAsBlank := TRUE
> IF ooSqlSrv_Sales ==NULL_OBJECT .or. oSqlSrv_Sales :Used == false
> .........Message
> Break
> ENDIF
>
> oTemp_Dbf := dbServer("c:\temp\tmp_001",DBEXCLUSIVE,DBREADWRITE )
>
> oWindow := _M_Cust_BrowWin{SELF,,oTemp_Dbf,}
>
> RECOVER
> QUIT
> END SEQUENCE
>
> RETURN SELF
> ACCESS oTemp_Dbf() CLASS MscMainMenuShellWindow
> RETURE oTemp_Dbf
>
> CLASS _M_Cust_BrowWin INHERIT DATAWINDOW
> PROTECT oDCbSubWin_Cust AS bBrowser
> PROTECT oDCbSubWin_Sales AS bBrowser
> PROTECT oCCpbAdd_Cust AS MultiHoverButton
> PROTECT oCCpbEdit_Cust AS MultiHoverButton
> PROTECT oCCpbDelete_Cust AS MultiHoverButton
> PROTECT oCCpbAdd_Sales AS MultiHoverButton
> PROTECT oCCpbEdit_Sales AS MultiHoverButton
> PROTECT oCCpbDelete_Sales AS MultiHoverButton
> PROTECT oCCpbClose AS MultiHoverButton
>
> //{{%UC%}} USER CODE STARTS HERE (do NOT remove this line)
>
> METHOD Init(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_BrowWin
>
> ........ Normal Init() with 2 bBrowser and 7 buttons
> self:Caption := ""
> self:HyperLabel :=
> HyperLabel{#_M_Cust_BrowWin ,NULL_STRING,NULL_STRING,NULL_STRING}
> self:PreventAutoLayout := True
>
> if !IsNil(oServer)
> self:Use(oServer)
> endif
>
> self:PostInit(oWindow,iCtlID,oServer,uExtra)
>
> return self
>
> METHOD PostInit(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_BrowWin
>
> SUPER:PostInit(oWindow,iCtlID,oServer,uExtra)
>
> //***********bBrowser for Customer
> SELFDCbSubWin_Cust:Use(oSqlSrv_Cust, {#CUST_NAME} )
> SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Cus t)
> SELFDCbSubWin_Cust:EnableColumnResize(FALSE)
> SELFDCbSubWin_Cust:EnableColumnMove(FALSE)
> SELFDCbSubWin_Cust:Freeze := 1
>
> SELFDCbSubWin_Cust:CaptionHeight := 18 // bBrowser
> Caption Height
> // SELFDCbSubWin_Cust:FooterHeight := 18
>
> SELFDCbSubWin_Cust:FooterView :=bViewStyle{ ,, ,Font{,
> 9,"Arial" },bGrid{BGRID_CONVEX} }
> SELFDCbSubWin_Cust:FooterView:Font:Bold := TRUE
>
> // Set Column Header #CUST_NAME
> _zlO_Column_Header := SELFDCbSubWin_Cust:GetColumn("CUST_NAME")
> _zlO_Column_Header:HyperLabel := HyperLabel{#CUST_NAME}
> _zlO_Column_Header:caption := "#Name of Payee"
> _zlO_Column_Header:Alignment := BALIGN_LEFT
> _zlO_Column_Header:Width := 300 // calculate width of
> column
>
> SELFDCbSubWin_Cust:Refresh()
> //***********bBrowser for Customer
>
>
> //***********bBrowser for Sales
> SELFDCbSubWin_Sales:Use(oSqlSrv_Cust, {#CUST_NAME} )
> SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Cus t)
> SELFDCbSubWin_Cust:EnableColumnResize(FALSE)
> SELFDCbSubWin_Cust:EnableColumnMove(FALSE)
> SELFDCbSubWin_Cust:Freeze := 1
>
> SELFDCbSubWin_Cust:CaptionHeight := 18 // bBrowser
> Caption Height
> // SELFDCbSubWin_Cust:FooterHeight := 18
>
> SELFDCbSubWin_Cust:FooterView :=bViewStyle{ ,, ,Font{,
> 9,"Arial" },bGrid{BGRID_CONVEX} }
> SELFDCbSubWin_Cust:FooterView:Font:Bold := TRUE
>
> // Set Column Header #CUST_NAME
> _zlO_Column_Header := SELFDCbSubWin_Cust:GetColumn("CUST_NAME")
> _zlO_Column_Header:HyperLabel := HyperLabel{#CUST_NAME}
> _zlO_Column_Header:caption := "#Name of Customer"
> _zlO_Column_Header:Alignment := BALIGN_LEFT
> _zlO_Column_Header:Width := 300 // calculate width of
> column
>
> SELFDCbSubWin_Cust:Refresh()
> //***********bBrowser for Customer
>
> //***********bBrowser for Sales Man
> SELFDCbSubWin_Sales:Use(oSqlSrv_Cust, {#CUST_NAME} )
> SELF:CheckRowMode(oSqlSrv_Cust,SELFDCbSubWin_Sal es)
> SELFDCbSubWin_Sales:EnableColumnResize(FALSE)
> SELFDCbSubWin_Sales:EnableColumnMove(FALSE)
> SELFDCbSubWin_Sales:Freeze := 1
>
> SELFDCbSubWin_Sales:CaptionHeight := 18 // bBrowser
> Caption Height
> // SELFDCbSubWin_Sales:FooterHeight := 18
>
> SELFDCbSubWin_Sales:FooterView :=bViewStyle{ ,, ,Font{,
> 9,"Arial" },bGrid{BGRID_CONVEX} }
> SELFDCbSubWin_Sales:FooterView:Font:Bold := TRUE
>
> // Set Column Header #SM_NAME
> _zlO_Column_Header := SELFDCbSubWin_Sales:GetColumn("SM_NAME")
> _zlO_Column_Header:HyperLabel := HyperLabel{#SM_NAME}
> _zlO_Column_Header:caption := "#Name of SalesMan"
> _zlO_Column_Header:Alignment := BALIGN_LEFT
> _zlO_Column_Header:Width := 300 // calculate width of
> column
>
> SELFDCbSubWin_Sales:Refresh()
> //***********bBrowser for Sales Man
>
> RETURN NIL
>
> METHOD pbAdd_Cust() CLASS _M_Cust_BrowWin // Method for Call on Push
> Button - ADD
> _M_Cust_ADDWin{SELF:Owner,"Add",oSqlSrv_Cust, }
> RETURN NIL
>
> CLASS _M_Cust_ADDWin INHERIT DATAWINDOW
>
> PROTECT oCCpbClose AS MultiHoverButton
> PROTECT oCCpbSave AS MultiHoverButton
> PROTECT oDCsleCUST_NAME AS SINGLELINEEDIT
> INSTANCE sleCUST_NAME
>
> METHOD PostInit(oWindow,iCtlID,oServer,uExtra) CLASS _M_Cust_ADDWin
> //Put your PostInit additions here
>
> SUPER:PostInit(oWindow,iCtlID,oServer,uExtra)
>
> SELF:Show()
> RETURN NIL
>
> METHOD pbAdd() CLASS _M_Cust_ADDWin // Method for UPDATING RECORD
> LOCAL _zlL_Locked AS LOGIC
>
> _zlL_Locked := self:Server:lock()
>
> IF _zlL_Locked
> SELF:server:Append()
> SELF:server:CUST_NAME := SELFDCsleCUST_NAME:TextValue
> //SELF:Server:FIELDPUT("Cust_name",
> AllTrim(SELFDCsleCUST_NAME:TextValue) ) // no difference
> ENDIF
>
> SELF:server:Commit()
> SELF:server:UnLock()
> SELF:Server:Notify(NOTIFYFILECHANGE)
> SELFwnerDCbSubWin_Cust:ReFresh()
> RETURN NIL
>
>
> Error 1 using _SQLConnect:CursorLocation := adUseServer during
> connection
> // when self:server:Append() is called
> SELF:SERVER:APPEND()
> ? SELF:server:CUST_NAME // returns blank
> ? AllTrim(SELFDCsleCUST_NAME:TextValue) // returns correct value
> SELF:server:CUST_NAME := SELFDCsleCUST_NAME:TextValue
> ? SELF:server:CUST_NAME // still returns blank
>
> bBrowser is empty (oDCbSubWin_Cust)
> Using SQL Master and SQL Server Management Studio Express show row
> added but all field empty.
>
>
> Error 2 using _SQLConnect:CursorLocation := adUseClient during
> connection
> // when self:server:Append() is called
> SELF:SERVER:APPEND()
>
> Error Message
>
> Error Code : Empty row cannot be inserted. Row must have at lease one
> column value set
> SubSystem : Vo2Ado
> Error Subcode : 16389
> Function : Append
>
> Sql = MS Sql Server 2005
> Ado = Ver 2.8
> Vo = 2.6
> Vo2Ado = Trail Pro Verion
>
>
>
>
>
>

Hi Guys,

Erik you hit it right. Now it is running as expected except that
bBrowser does not show any records even after closing, reopening and
restarting the program. The records are updated correctly as shown in
sql master.

Changed adOpenDynamic to adOpenStatic solved the problem
// Opening Data Server
_Svar_1:= "SELECT * FROM TestTable01"
oSqlSrv_Cust := AdoServer{_Svar_1,
_SQLConnect,adOpenStatic ,adLockOptimistic}
oSqlSrv_Cust:NullAsBlank := TRUE

Thanks a lot to everyone for assistance rendered.